Cognos Queries
Sometimes people want to see every day in a month even if there aren’t records. For instance show me the entire month and the number of marketing transactions that happened per day (even if there weren’t any). If there is no data for a day then you won’t have that day in your results. One way to get around that is to have a query that returns every day and then “outer join” to it to get every day.
Here is a query that returns every day in the current month. You’ll have to tweak it if you need a different set of days but it’s a great start.
select CAST(DATEADD(dd, -number, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as date) dt
from master..spt_values
where type = ‘P’ and CAST(DATEADD(dd, -number, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as date) >= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))
Cheers!
