Group By 30 Minutes
-
Hi All, I have used (datepart(hour,CR_callstart)) As Hours to group Data by Hours. What i would like is to group them by Every 30 Minutes. Is there any thing i can use so that I can get data grouped every hlaf an hour. At the moment i can get : Time Revenue 10:00 $100 11:00 $200 With : Select (datepart(hour,CR_callstart)) As Hours,sum(cost)as Revenue from dbo.Tablename Group by Hours But I want to get Time Revenue 10:00 $75 10:30 $25 11:00 $100 11:30 $100 Please advice. Thanks
-
Hi All, I have used (datepart(hour,CR_callstart)) As Hours to group Data by Hours. What i would like is to group them by Every 30 Minutes. Is there any thing i can use so that I can get data grouped every hlaf an hour. At the moment i can get : Time Revenue 10:00 $100 11:00 $200 With : Select (datepart(hour,CR_callstart)) As Hours,sum(cost)as Revenue from dbo.Tablename Group by Hours But I want to get Time Revenue 10:00 $75 10:30 $25 11:00 $100 11:30 $100 Please advice. Thanks
Test the time component (using datepart) for the number of minutes, set a value 0 or 30 and then group by the value. This probably can all me done in 1 select statement.
Never underestimate the power of human stupidity RAH
-
Test the time component (using datepart) for the number of minutes, set a value 0 or 30 and then group by the value. This probably can all me done in 1 select statement.
Never underestimate the power of human stupidity RAH
-
Hi All, I have used (datepart(hour,CR_callstart)) As Hours to group Data by Hours. What i would like is to group them by Every 30 Minutes. Is there any thing i can use so that I can get data grouped every hlaf an hour. At the moment i can get : Time Revenue 10:00 $100 11:00 $200 With : Select (datepart(hour,CR_callstart)) As Hours,sum(cost)as Revenue from dbo.Tablename Group by Hours But I want to get Time Revenue 10:00 $75 10:30 $25 11:00 $100 11:30 $100 Please advice. Thanks
This should do it.
SELECT
DATEPART(hour,CR_callstart) as hour,
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END as minute,
sum(cost) as revenue
from tablename
group by DATEPART(hour,CR_callstart),
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END -
This should do it.
SELECT
DATEPART(hour,CR_callstart) as hour,
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END as minute,
sum(cost) as revenue
from tablename
group by DATEPART(hour,CR_callstart),
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
ENDThat is Fantastic.Is there any way I can put Hour and Minute in One Column. Like : Time 10:00 10:30 11:00 11:30 etcc.. Thanks so much for your time.
-
That is Fantastic.Is there any way I can put Hour and Minute in One Column. Like : Time 10:00 10:30 11:00 11:30 etcc.. Thanks so much for your time.
-
It_tech wrote:
Is there any way I can put Hour and Minute in One Column.
Sure, but thats presentation logic and should be handled appropriately.
-
Thanks For your reply and sorry to bother you.Can i do that from a Sql level? It would be great if you can give me an example. Many thanks.
He wrote the code for you - what do you want, that he should go out and test your app as well. Jamie stated that the requirement is PRESENTATION logic therefore it does not belong in the database and you then ask him if it can be done in the database. Do your own work and use your brain instead of relying on the forum for ideas.
Never underestimate the power of human stupidity RAH
-
He wrote the code for you - what do you want, that he should go out and test your app as well. Jamie stated that the requirement is PRESENTATION logic therefore it does not belong in the database and you then ask him if it can be done in the database. Do your own work and use your brain instead of relying on the forum for ideas.
Never underestimate the power of human stupidity RAH