Hi,I am trying to check if there was a blood drive for a given center on each day of the week with in a time span. My query is doing this just when there actually is a drive. I would like the query to return a null value for the opid if there was not a drive on that given day and not skip the days where there are no drives for that given blood drive. the #Nums table is a table with a column called N and its values are 1 - 10000. This table allows me to loop through all days within the given time span. thanks for any helpdeclare @startDate as DATETIME, @endDate as DATETIME;set @startDate = dateadd(yyyy,-5,getdate());set @endDate = dateadd(yyyy,-4,getdate());select b.bloodbanksitename, o.opid, convert(varchar , DATEADD(day, #Nums.n - 1, @startDate), 101) AS thedatefrom #Numsleft join op as o on convert(varchar, DATEADD(day, #Nums.n - 1, @startDate), 112) = convert(varchar, o.opStartDateTime, 112)left join bloodbanksite AS b ON b.bloodbanksiteid = o.bloodbanksiteidWHERE #Nums.n <= DATEDIFF(day, convert(varchar, @startDate, 112), convert(varchar, @endDate, 112)) + 1 order by b.bloodbanksitename, thedate
select null from each day with in a date span
Posted - 2011-08-08 : 12:41:10
