Get weekly data from huge data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get weekly data from huge data

Author

Topic

k_sampal
Starting Member

1 Post

Posted - 2012-05-07 : 09:07:57

I have a table with month long data
Date Value
2012-05-03 12:55:39.197 2
2012-05-04 12:55:39.197 6
2012-05-05 12:55:39.197 4
2012-05-07 12:55:39.197 6
2012-05-08 12:55:39.197 8

I want to display whole data of the week having column name as the date of the monday in that week(mon-Sun) like
Date Value
2012-04-30 12:55:39.197 12
2012-05-07 12:55:39.197 14
etc

Can any get the query to display data in above format.
Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-07 : 09:33:04

[code]SELECT WEEK, Mon, Tue, Wed, Thu, Fri, Sat, Sun
FROM (SELECT DATEADD(wk,DATEDIFF(wk,0,[DATE]),0) WEEK, VALUE,
LEFT(DATENAME(dw,[DATE]),3) DOW FROM myTable) a
PIVOT (SUM(VALUE) FOR DOW IN (Mon,[Tue],[Wed],[Thu],[Fri],[Sat],[Sun])) b[/code]

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-07 : 17:47:32

[code]

select [WEEK] ,isnull(mon,0)+ isnull(tue,0)+isnull(wed,0)+isnull(Thu,0)
+isnull(Fri,0)+isnull(Sat,0)+isnull(sun,0) as total from
(
SELECT WEEK, Mon, Tue, Wed, Thu, Fri, Sat, Sun
FROM (SELECT DATEADD(wk,DATEDIFF(wk,0,[DATE]),0) WEEK, VALUE,
LEFT(DATENAME(dw,[DATE]),3) DOW FROM #myTable) a
PIVOT (SUM(VALUE) FOR DOW IN (Mon,[Tue],[Wed],[Thu],[Fri],[Sat],[Sun])) b) Tab
[/code]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-07 : 17:58:22

Ugh, thanks vijay, I TOTALLY misread the question, AGAIN.

SELECT DATEADD(wk,DATEDIFF(wk,0,[DATE]),0) WEEK, SUM(VALUE) FROM myTable
GROUP BY DATEDIFF(wk,0,[DATE])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources