SQL Server - Convert Weekly range - Asked By farrukh on 13-Jul-12 07:53 AM

Hello All,

Iam trying to convert the query to weekly range what iam doing wrong


DATEADD

(WW, DATEDIFF(W,1,Otr.START_DATETIME),0)




My results like now when passing the dates '1/11/2011' to

'12/31/2011'



Date      Data
2677-03-05 00:00:00.000   17983.880000000000
2677-03-12 00:00:00.000   18927.590000000000
2677-03-19 00:00:00.000   18246.410000000000
2677-03-26 00:00:00.000   18632.550000000000
2677-04-02 00:00:00.000   18911.180000000000




Thanks
hameed
Sathish S replied to farrukh on 13-Jul-12 08:02 AM
Do you have a group by clause

ex.
--Weekly
SELECT Department, DATEADD(wk, DATEDIFF(wk, 0, DateCol ), 0) WeekStart, COUNT(EmpID) [Weekly] 
FROM DData
GROUP BY Department, DATEADD(wk, DATEDIFF(wk, 0, DateCol ), 0) 

Chintan Vaghela replied to farrukh on 13-Jul-12 08:11 AM
Make query as folloiwng way

Use SUM with Group By


SELECT  DATEADD(wk, DATEDIFF(wk, 0, DateFiled ), 0) Date, SUM(DataField) as Data
FROM TableName
GROUP BY  DATEADD(wk, DATEDIFF(wk, 0, DateFiled ), 0)
farrukh replied to Chintan Vaghela on 13-Jul-12 10:55 AM

RB/SATISH S,

Thank you very much the totals (vol) are fine have problem in date if i run 1/1/2012 to 1/14/2012 . Is it possible that date show 2012-01-01 00:00:00.000  ,  2012-01-08 00:00:00.000 ?

Result now is below .
Date                                     VOL
2012-01-02 00:00:00.000    1085.00
2012-01-09 00:00:00.000    1100.00


I am doing fine below to get desired dates  2012-01-01 00:00:00.000  ,  2012-01-08 00:00:00.000  ?

SELECT  DATEADD(wk, DATEDIFF(wk, 0, START_DATETIME ), -1) Date, SUM(vol)
FROM TABLE WHERE ...
GROUP BY DATEADD(wk, DATEDIFF(wk, 0, START_DATETIME ), -1)
ORDER BY DATEADD(wk, DATEDIFF(wk, 0, START_DATETIME ), -1)



Thanks and Regards,
Hameed
Chintan Vaghela replied to farrukh on 14-Jul-12 04:35 AM
Make as folloiwing wya SELECT  DATEADD(wk, DATEDIFF(wk, 0, START_DATETIME ), 0) Date, SUM(vol)
FROM TABLE WHERE ...
GROUP BY DATEADD(wk, DATEDIFF(wk, 0, START_DATETIME ), 0)
ORDER BY DATEADD(wk, DATEDIFF(wk, 0, START_DATETIME ), 0)
farrukh replied to Chintan Vaghela on 15-Jul-12 12:27 AM
Thanks you very much :)
For your kindness and help always.


Thanks
Hameed