Home T-Sql fill fill a select with all day in a month
Reply: 0

T-Sql fill fill a select with all day in a month

user1223 Published in June 23, 2018, 9:18 pm

I need to create a select that shows all days in a month and for the days that I've found a registration the registration will show on. Actually the code is:

DECLARE @YrSel varchar(4)='2017'
declare @MtSel varchar(2)='11'

from dbo.listOFRegistration
right join  (  SELECT [DayDate] = dateadd(day, number, @YrSel + '-' 
+@MtSel + '-01' ) 

FROM  master..spt_values 
WHERE Type='P'
AND DATEADD(day,Number,+@Yrsel + '-' + @Mtsel + '-01') <= dateadd(DAY, -1,dateadd(month, 1, +@Yrsel + '-' + @Mtsel + '-01'))
) as dates
on tdate = DayDate 
WHERE datepart(yyyy, tdate) =@Yrsel AND datepart(m, tdate)= @Mtsel 

I'd like to receive an output like

|tdate        |DayDate       |Reginfo
|NULL         |2017-11-01    |NULL
|2017-11-02   |2017-11-02    |Registered
|NULL         |2017-03-11    !NULL

but I receive as output only the record where tdate and daydate are same (as if the join was an INNER JOIN.

if I change to a CROSS JOIN I can see that (correctly) the records are duplicated as per the number of records in each table/select.

where I'm in fault? Any suggestion?

Thank you. Regards, Nick

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.328175 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO