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

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

Nick979
1#
Nick979 Published in 2017-11-14 16:22:37Z

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'

SELECT  *
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

Nick979
2#
Nick979 Reply to 2017-11-15 13:31:08Z

Thank you for the suggestions but it doesn't work. It shows only the common records between the two select.

I did some modification and I can see 99% of what I need:

DECLARE @YrSel varchar(4)='2017'
declare @MtSel varchar(2)='11'
SELECT  *
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 email like 'user@email.som' or email is null
order by daydate

I said 99% because I always miss the first day of the month (if it is not registered).

If I run only the "month generator":

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

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'))

I can see days starting from 01. Any suggestion?

Thank you again!

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO