April 16, 2012 at 6:09 pm
Hi
I was asked to do a query that performs a grouping by ten days period and i don't know how to begin
April 16, 2012 at 6:22 pm
If you provide the DDL (CREATE TABLE statement) for the table(s) involved, provide sample data (as a series of INSERT INTO statements) for the table(s) involved, the expected resutls of the query I am sure we could provide you with something that meets your requirements.
April 16, 2012 at 6:36 pm
sorry, here is the data
CREATE TABLE sample(id int identity(1,1), s_date datetime, amount int)
insert into sample
select '20100101', 1
union all
select '20100102', 1
union all
select '20100103', 1
union all
select '20100104', 1
union all
select '20100111', 1
union all
select '20100112', 1
union all
select '20100113', 1
union all
select '20100121', 1
union all
select '20100122', 1
union all
select '20100123', 1
Expected output
20100101 to 20100110 4
20100111 to 20100120 3
20100121 to 20100130 3
April 16, 2012 at 6:39 pm
dfarina (4/16/2012)
sorry, here is the data
CREATE TABLE sample(id int identity(1,1), s_date datetime, amount int)
insert into sample
select '20100101', 1
union all
select '20100102', 1
union all
select '20100103', 1
union all
select '20100104', 1
union all
select '20100111', 1
union all
select '20100112', 1
union all
select '20100113', 1
union all
select '20100121', 1
union all
select '20100122', 1
union all
select '20100123', 1
Expected output
20100101 to 20100110 4
20100111 to 20100120 3
20100121 to 20100130 3
What sets the start date and end date for the query or is it based on the earliest and latest dates in the table?
April 16, 2012 at 7:23 pm
Just for the year 2010
April 16, 2012 at 8:09 pm
A possible solution. I am assuming every month is 31 days but an idea to help you get started.
WITH TenDates
AS
(
SELECT
s_date
, DayAdd =
CASE WHEN DAY(s_date) <= Increment
THEN Increment
ELSE NULL
END
,amount
FROM sample
CROSS JOIN (VALUES(10),(20),(31)) AS Calendar(Increment)
),TenDatesFinal
AS
(
SELECT s_date
,FromDate = MIN(s_date)
,ToDate = DATEADD(dd, MIN(DayAdd)-1,DATEADD(MONTH, DATEDIFF(MONTH,'19000101',s_date), '19000101'))
,Amount = MIN(Amount)
FROM TenDates
GROUP BY s_date
)
SELECT FromDate= MIN(FromDate)
,ToDate = MAX(ToDate)
,Amount = SUM(Amount)
FROM TenDatesFinal
GROUP BY ToDate;
April 18, 2012 at 12:06 am
DDL=Data Definition Language
That and the INSERT to set up your sample data looks like this:
DECLARE @sample TABLE (id int identity(1,1), s_date datetime, amount int)
insert into @sample
select '2010-01-01', 1
union all select '2010-01-02', 1
union all select '2010-01-03', 1
union all select '2010-01-04', 1
union all select '2010-01-11', 1
union all select '2010-01-12', 1
union all select '2010-01-13', 1
union all select '2010-01-21', 1
union all select '2010-01-22', 1
union all select '2010-01-23', 1
--union all select '2010-02-05', 1
--union all select '2010-02-15', 1
union all select '2010-02-23', 1
Here's a solution that will create the 10 day groups for only the groups where you have data.
DECLARE @startdate DATETIME
SELECT @startdate = DATEADD(year, DATEDIFF(year, 0, '2010-05-31'), 0)
;WITH Dates AS (
SELECT DATEADD(day, 10*(DATEDIFF(day, @startdate, s_date)/10), @startdate) as s_date
,DATEADD(day, 9+10*(DATEDIFF(day, @startdate, s_date)/10), @startdate) as e_date
,amount
FROM @sample)
SELECT s_date, e_date, SUM(amount) As amount
FROM Dates
GROUP BY s_date, e_date
I've used a little trick I learned to set the starting date for the period (@startdate) using any day in that particular year. If you don't need to start on the year boundary (01-Jan) just set @startdate = '2010-05-31' or whatever your start date is.
However as Mr. Celko has pointed out, if you aren't satisfied when there are gaps in the output not covered by your input data, you'll need to use either a Calendar or a Tally table to generate the missing 10 day periods.
Notice how a couple of 10 day periods are missing with the additional data I added. You can fill those in by uncommenting the two SELECTs in the set up data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 8:46 am
Simplest form:
select Decade = convert(VARCHAR(9), s.s_date, 120), Amount = sum(s.amount)
from dbo.sample s
group by convert(VARCHAR(9), s.s_date, 120) -- 120 = yyyy-mm-dd hh:mi:ss
You could use variation, e.g. month + decade, fill empty gaps etc but the trick is to group by formula.
April 18, 2012 at 4:20 pm
Thanks to all of you!!!
April 18, 2012 at 5:11 pm
Vedran Kesegic (4/18/2012)
Simplest form:
select Decade = convert(VARCHAR(9), s.s_date, 120), Amount = sum(s.amount)from dbo.sample s
group by convert(VARCHAR(9), s.s_date, 120) -- 120 = yyyy-mm-dd hh:mi:ss
You could use variation, e.g. month + decade, fill empty gaps etc but the trick is to group by formula.
Oh, be careful now. Although it makes coding very simple, grouping by conversion to VARCHAR can be a whole lot slower than using the ol' >= and < method.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2012 at 7:54 pm
dwain.c (4/18/2012)
Here's a solution that will create the 10 day groups for only the groups where you have data.
DECLARE @startdate DATETIME
SELECT @startdate = DATEADD(year, DATEDIFF(year, 0, '2010-05-31'), 0)
;WITH Dates AS (
SELECT DATEADD(day, 10*(DATEDIFF(day, @startdate, s_date)/10), @startdate) as s_date
,DATEADD(day, 9+10*(DATEDIFF(day, @startdate, s_date)/10), @startdate) as e_date
,amount
FROM @sample)
SELECT s_date, e_date, SUM(amount) As amount
FROM Dates
GROUP BY s_date, e_date
Excellent example! You are using the same table as its own tally table and I never thought about those neat calculations before? Have you thought about that using your own formula or is it based in an article from the past?
Learned something new. Thank you.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
April 18, 2012 at 8:23 pm
It's nice to hear when an original work is appreciated!
Actually, in most of my posts where I'm suggesting a solution, I usually put something together from scratch. There is the occasional snippet (e.g., the XML method to put together a delimited string) of course.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply