Re: SELECT by quarter of hour
From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 11/29/04
- Next message: Vinod Kumar: "Re: Trigger problem"
- Previous message: Tom Moreau: "Re: SELECT by quarter of hour"
- In reply to: simon: "SELECT by quarter of hour"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 06:24:14 -0500
Simon, here's one way to achieve this (untested):
select dt as fromtime, dateadd(mi, 15, dt) as totime,
sum(quantity) as totalqty, sum(qty1) as totalqty1
from (select
dateadd(mi, datediff(mi, '20000101', getdate())
/15*15, '20000101') as dt,
quantity,
case when type = 1 then quantity end as qty1
from t1) as d
group by dt
-- BG, SQL Server MVP www.SolidQualityLearning.com "simon" <simon.zupan@stud-moderna.si> wrote in message news:uu9b5Ig1EHA.2876@TK2MSFTNGP12.phx.gbl... >I have table SELL with columns: >productID(varchar(20)),quantity(int),type(tinyInt),sellDate(datetime) > > I would like to get sum of sell for one day, devided by quarter of hour > for all products and for only products where type=1. > > The result should be like his: > > time sum(quantity) sum(quantity where > type=1) > --------------------------------------------------------------------- > 0-0.15 1000 300 > 0.15-0.30 200 120 > 0.30-0.45 566 322 > 0.45-1 222 29 > 1-1.15 50 17 > 1.15-1.30 0 0 > .....and so on > ....until > 23.45-24.00 > > Any idea? > > Thank you, > Simon > >
- Next message: Vinod Kumar: "Re: Trigger problem"
- Previous message: Tom Moreau: "Re: SELECT by quarter of hour"
- In reply to: simon: "SELECT by quarter of hour"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
