CODEWITHSUNDEEP
sqldate
angryBird
angryBird

Reputation: 44

sql query for selecting 30 days data with time interval

SQL Query not giving expected answer 

SELECT CAST(PR.DateTimeStamp as date) AS PRDate,COUNT(PR.ID) AS PRCount
FROM tbl_Purchase PR
INNER JOIN tbl_PurchaseCategory PTC ON PR.ID = PTC.ID
WHERE PR.DateTimeStamp BETWEEN  DATEADD(DAY,-30,'2017-12-07 09:00:00') AND 
'2017-12-07 09:00:00' and PR.DepartmentID=1 and PTC.CategoryID=1 group by 
CAST(PR.DateTimeStamp as date) order by CAST(PR.DateTimeStamp as date)

i want to select data like

PRDate         PRCount

2017-12-07    3         // from 2017-12-08 09:00:00 to 2017-12-07 09:00:00
2017-12-06    31        // from 2017-12-07 09:00:00 to 2017-12-06 09:00:00
2017-12-05    10        // from 2017-12-06 09:00:00 to 2017-12-05 09:00:00
2017-12-04    23
2017-12-03    27
2017-12-02    15
2017-12-01    27
2017-11-30    39
2017-11-29    25
2017-11-28    27
2017-11-27    36
2017-11-26    30
2017-11-25    23
2017-11-24    18
2017-11-23    13
2017-11-22    16
2017-11-21    25
2017-11-20    15
2017-11-19    41
2017-11-18    11
2017-11-17    9
2017-11-16    19
2017-11-15    23
2017-11-14    17
2017-11-13    23
2017-11-12    20
2017-11-11    31
2017-11-10    29
2017-11-09    18
2017-11-08    29
2017-11-07    24

the above query is proving me data

12 to 12 time interval not from 9 to 9

Upvotes: 0

Views: 319

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13403

You should subtract 9 hours from the date for the group by.

SELECT 
    CAST( DATEADD(HOUR,-9, PR.DateTimeStamp) as date) AS PRDate
    , COUNT(PR.ID) AS PRCount
FROM tbl_Purchase PR
    INNER JOIN tbl_PurchaseCategory PTC ON PR.ID = PTC.ID
WHERE 
    PR.DateTimeStamp BETWEEN  DATEADD(DAY,-30,'2017-12-07 09:00:00') AND '2017-12-07 09:00:00' 
    AND PR.DepartmentID=1 and PTC.CategoryID=1 
group by 
    CAST(DATEADD(HOUR,-9, PR.DateTimeStamp) as date) 
order by 
    CAST(DATEADD(HOUR,-9, PR.DateTimeStamp) as date)

Upvotes: 1

Related Questions