| Author |
 Topic  |
|
|
k_sampal
Starting Member
India
1 Posts |
 Posted - 05/07/2012 : 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
USA
15559 Posts |
Posted - 05/07/2012 : 09:33:04
|
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 |
 |
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/07/2012 : 17:47:32
|
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
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/07/2012 : 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]) |
|
|
| |
Topic |
|
|
|
Get weekly data from huge data
