January 12, 2010 at 5:35 am
The below SQL givese me the date range from dec26 to jan01. In my data base there are not txs on 1st, but i want to display the date range, programname with '0' transactions. Please help he regarding this
SELECT DATEADD(dd,DATEDIFF(dd,6,getdate()+1)/7*7,-9)+t.Number as [date],
ProgramName =
CASE WHEN ProgramName = 'ABC' THEN 'A'
END,
count(*) TotalTransactions
FROM CSLoyaltycard,
Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND 6
and datecolumn = DATEADD(dd,DATEDIFF(dd,6,getdate()+1)/7*7,-9)+t.Number
and groupnumber in ('12345') group by programname,t.number
January 12, 2010 at 5:47 am
You should think about using a calendar table as a left join together with your query.
The calendar table can also be helpful when dealing with business days, holidays and so on.
Please search this site for some samples. Or you could have a lokk at the Tally Table link in my signature. There's an example on how to build a calendar table as well.
January 12, 2010 at 6:23 am
Yes That is right. I have used that logic, but i did not get the data for other dates as well
January 12, 2010 at 6:43 am
something along those lines?
select *
from calendar c
left outer join YourTable y on c.date = y.date
where y.column=criteria or y.column is null
January 12, 2010 at 6:55 am
Can you please describe me what is criteria here. Is this column name?
January 12, 2010 at 9:46 am
'criteria' is actually the WHERE condition you'd like to use.
It would help a lot more if you could set up some ready to use sample data so we can show you how it works using your own data .
Please have a look at the first link in my signature on how to post sample data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply