June 4, 2012 at 6:57 am
I have below table:
ColA ColB ColC ColD ColE ColF
Host1 P PP date A 98
Host1 Q pp date B 99
Host1 R QQ DATE C 81
Host2 X RR DATE D 55
In the date column I have data for about 3 months
I need to display the data where I select colA colB ColC colD colE but group the data
The format should be:
ColA ColB ColC (data for past week)week1 week2 week2
Need to display for past 2 months and each column should display the data for one week.
June 4, 2012 at 7:38 am
Without your DDL, it's a little hard to get specific. But you probably want to look at the T-SQL DATEDIFF() function.
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx
Something like:
SELECT ColA,
ColB,
DATEDIFF(ww, ColC, GETDATE()) AS WeekNumber
GROUP BY ColA, ColB, DATEDIFF(ww, ColC, GETDATE())
WHERE <date range you want>
ORDER BY DATEDIFF(ww, ColC, GETDATE())
HTH,
Rob
June 4, 2012 at 7:46 am
You probably want to group the data by date first, then the other columns, but are you showing all the data for a week?
If I have
Red, 4, 4/1/2012
Blue, 5, 4/2/2012
Blue, 8, 4/3/2012
Am I showing two results in the final tally? As in the same data above, but ordered or grouped together? Or am I trying to summarize something? As in perhaps
Red, 1, Week 1
Blue, 2, Week 1
It matters for how you do the query.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 4, 2012 at 10:54 pm
If none of the solutions have worked for you yet, then please post some adequate sample data which also contains "Dates".
The DDL and sample data should be adequate enough so that people who want to help you can get a clear view of the requirement and also can test any or all solutions on the sample data before they post it on the forum.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply