from one row join many rows withour relation

Question

I really appreciate if you can help me with this. What I need is create every date in one specific range, this is done with the follow query. but I need to assign this range dates per every one row.

--Dates in rage
SELECT DATEADD(DAY, nbr - 1, '2016-02-08')
FROM   (
SELECT ROW_NUMBER() OVER(ORDER BY c.object_id) AS Nbr
FROM   sys.columns c
) nbrs
WHERE  nbr - 1 <= DATEDIFF(DAY, '2016-02-08', '2016-02-11')

Table_A
Jhon
JhonB
JhonC

Table_Calendar (2016-02-08 / 2016-02-11)
2016-02-08
2016-02-09
2016-02-10
2016-02-11

UNION 
JHON  2016-02-08
JHON  2016-02-09
JHON  2016-02-10
JHON  2016-02-11
JHONB 2016-02-08
JHONB 2016-02-09
JHONB 2016-02-10
JHONB 2016-02-11

Thanks for your help

You could use CROSS APPLY or a CROSS JOIN from your base table to your derived dates table. — Sean Lange, Feb 18 '16 at 15:41
Possible duplicate of [SQL join two tables without keys/relations](http://stackoverflow.com/questions/1797785/sql-join-two-tables-without-keys-relations) — Tab Alleman, Feb 18 '16 at 15:42

score 1 · Answer 1 · answered Feb 18 '16 at 16:00

You are half way through by achieving ranges.As sean pointed out you need to apply cross join/cross apply when you don't have relations,though each one will have different usages..

with cte
as
(
SELECT DATEADD(DAY, nbr - 1, '2016-02-08') as dates
FROM   (
SELECT ROW_NUMBER() OVER(ORDER BY c.object_id) AS Nbr
FROM   sys.columns c
) nbrs
WHERE  nbr - 1 <= DATEDIFF(DAY, '2016-02-08', '2016-02-11')
)
select 
* from cte
cross join
yourtable

---one more version using numbers

declare @rangestartdate datetime
set @rangestartdate=getdate()-10
declare @rangeenddate datetime
set @rangeenddate=getdate()

    ;with cte
    as
    (
    select 
    dateadd(day,n,@rangestartdate) 
    from dbo.numbers
    where n<datediff(day,@rangestartdate,@rangeenddate)
    )
    select * from cte
    cross join
    yourtable

You can get different ways to populate numbers table here and its uses

from one row join many rows withour relation

1 Answers1