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'
right join ( SELECT [DayDate] = dateadd(day, number, @YrSel + '-'
+@MtSel + '-01' )
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?