Re: SELECT by quarter of hour
From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 11/30/04
- Next message: Bernie Yaeger: "Re: SUM() Taking Too Long"
- Previous message: Aaron [SQL Server MVP]: "Re: Searching a string with spaces and nospaces in a table."
- In reply to: Tom Moreau: "Re: SELECT by quarter of hour"
- Next in thread: Itzik Ben-Gan: "Re: SELECT by quarter of hour"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 21:43:37 -0500
Thanks for explaining, Tom. :-)
I should have provided some comments explaining the query.
It should be much faster than the alternatives because it costs only a
single scan of the table, which is also sequential, as opposed to multiple
seek operations within an index, which in total cost much more I/O, which is
also random.
Cheers,
-- BG, SQL Server MVP www.SolidQualityLearning.com "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OtXHXBh1EHA.4004@tk2msftngp13.phx.gbl... > I'd give both solutions a try. Itzik's solution is quite novel. He's > basically choosing a benchmark date, then taking the difference in min > between your data and that data. He rounds down (truncates) to the > nearest > 15 min interval and then adds the benchmark date back. After that, he > does > a GROUP BY. I'm curious as to the speed differences in the two solutions. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinnaclepublishing.com > . > "simon" <simon.zupan@stud-moderna.si> wrote in message > news:eoqdZ8g1EHA.2568@TK2MSFTNGP11.phx.gbl... > Thank you Tom, > > I thought that I should create some new table with minutes :) > Do you know, what Itzik Ben-Gan thought? > I don't understand his solution. I think it won't work. > > Best regards, > Simon > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:%23KbG3Zg1EHA.3908@TK2MSFTNGP12.phx.gbl... >> First, create a table that has the minutes of the day in 15-min chunks, >> beginning with 0 and ending with 1440. Next, determine midnight of the >> target date. Store that in a datetime variable or use a proc argument. >> Finally, do the select: >> >> select >> m1.MinuteOfDay >> , m2.MinuteOfDay >> , sum (s.Quantity) >> , sum (case when s.type = 1 then s.Quantity else 0 end) >> from >> Minutes m1 >> join Minutes m2 on m2.MinuteOfDay = m1.MinuteOfDay + 15 >> left >> join Sell s on s.sellDate >= dateadd (mm, >> @MidnightToday, >> m1.MinuteOfDay) >> and s.sellDate < dateadd (mm, >> @MidnightToday, >> m2.MinuteOfDay) >> group by >> m1.MinuteOfDay >> , m2.MinuteOfDay >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinnaclepublishing.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: Bernie Yaeger: "Re: SUM() Taking Too Long"
- Previous message: Aaron [SQL Server MVP]: "Re: Searching a string with spaces and nospaces in a table."
- In reply to: Tom Moreau: "Re: SELECT by quarter of hour"
- Next in thread: Itzik Ben-Gan: "Re: SELECT by quarter of hour"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
