CODEWITHSUNDEEP
sql-server
Thapld
Thapld

Reputation: 37

How group by data every 15 days

I have a query group by every 15 minutes.

select count(1) as total,
    dateadd(minute,(datepart(minute, t.createdtime) / 15) * 15,(dateadd(hour, datediff(hour, 0, t.createdtime), 0))) as datetime
from
    (select accountid,
        createdtime,
        serviceid,
        roomid,
        sourceid
    from inputtransactions
    where createdtime between #{fromdate} and #{todate}
    union all
    select accountid,
        createdtime,
        serviceid,
        roomid,
        sourceid
    from outputtransactions
    where createdtime between #{fromdate} and #{todate}
    ) t
group by dateadd(minute,(datepart(minute, createdtime) / 15) * 15,(dateadd(hour, datediff(hour, 0, createdtime), 0)))

How can I play with 15 days of month?

Upvotes: 1

Views: 960

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

For appropriate answer, sample and expected data always help. But if I got you correctly, you are trying to group by month's data by first and second 15 days. If this is true, you can take help from the following script-

SELECT 
    YEAR(your_date_column) AS YR,   
    MONTH(your_date_column) AS MN,
    CASE WHEN DATEPART(DD,your_date_column) <16 THEN 'First Half' ELSE 'Second Half' END Month_Part,
    COUNT(1) AS total
FROM your_table
GROUP BY 
    YEAR(your_date_column),
    MONTH(your_date_column),
    CASE WHEN DATEPART(DD,your_date_column) <16 THEN 'First Half' ELSE 'Second Half' END

Upvotes: 2

Related Questions