February 9, 2009 at 9:23 am
Table contains data of 2 yrs.
Table contains InDate column (datetime)
I want to do row count and date for each day from last 6 months
please help
February 9, 2009 at 9:46 am
You probably need to provide more information as, from your current explanation, all you need seems to beselect indate,count(*)
from xxx -- insert table name here
where indate>dateadd(m,-6,getdate())
group by indate
Derek
February 9, 2009 at 12:54 pm
SELECT C.[Date], COUNT(T.indate)
from dbo.Calendar C
LEFT JOIN dbo.YourTable T ON T.indate = C.[Date]
WHERE C.[Date] > dateadd(m,-6,getdate()) -- probably you need to remove time portion here
group by [Date]
_____________
Code for TallyGenerator
February 10, 2009 at 3:38 am
Sergiy (2/9/2009)
SELECT C.[Date], COUNT(T.indate)
from dbo.Calendar C
LEFT JOIN dbo.YourTable T ON T.indate = C.[Date]
WHERE C.[Date] > dateadd(m,-6,getdate()) -- probably you need to remove time portion here
group by [Date]
The OP didn't mention that he had a calendar table and, in this case, I can't see that it's adding anything to the query.
Derek
February 10, 2009 at 3:57 am
Derek Dongray (2/10/2009)
The OP didn't mention that he had a calendar table and, in this case, I can't see that it's adding anything to the query.
If you need to paint a frame you need a brush.
If you don't have one - go and get it.
_____________
Code for TallyGenerator
February 10, 2009 at 4:23 am
Sergiy (2/10/2009)
Derek Dongray (2/10/2009)
The OP didn't mention that he had a calendar table and, in this case, I can't see that it's adding anything to the query.If you need to paint a frame you need a brush.
If you don't have one - go and get it.
Actually, on rereading, I can see that your solution adds a line for all dates in the range, whereas mine wouldn't report dates for zero values.
Hence if the OP want all dates, he needs a list, so may as well create a calendar table rather than do it on the fly.
Derek
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply