March 3, 2014 at 12:57 pm
I have a sales history table with invoice totals by period. (1=Jan, 2=Feb,...) My sales fall into two categories, Summer and Winter. Summer months are periods 4-9 and winter are 1-3 and 10-12. When I run the report I want to break down the last 6 month of each period, so if I ran the report in March 2014, I would expect to see periods 1-3 of 2014 and 10-12 of 2013 for winter and 4-9 of 2013 for summer.
However if I run it in June of 2014 (period 6), my last six winter months are still the same, but I want to see the last six summer months which should be 4-6 of 2014 and 7-9 of 2013. I assume I will have to use some type of CASE statement. Here's a short sample of the script
DECLARE @D datetime
SET @D = '3-1-14'
SELECT h.period
, h.year_for_period
, CASE WHEN DATEPART(MONTH, h.invoice_date) BETWEEN DATEPART (MONTH,DATEADD(MONTH, 0,@D)) AND DATEPART (MONTH,DATEADD(MONTH, -6,@D ))THEN SUM(h.total_amount) END
FROM invoice_hdr h
WHERE h.customer_id = 10058475
AND h.invoice_date > '01-01-13'
GROUP BY h.period
, h.year_for_period
, h.invoice_date
but I can't seem to get it to work. Based on @D = 3-1-14 the return is for period 3 of 2014 and 2013 but I just want to see the last 6 months. The full script is rather long, 120 lines or more, so just a nudge in the right direction is what I'm looking for. Thanks in advance.
March 4, 2014 at 8:23 am
It would much easier to provide a working solution if you provided a small set of sample data and the expected output. I've read your post several times and I'm still not sure what you want for the output. It sounds like you want the output to be a sum per period for the last year. So you may be over thinking what you need to do. Would something like this work:
DECLARE @D DATETIME
SET @D = '3-1-14'
SELECT h.period ,
h.year_for_period ,
SUM(h.total_amount)
FROM invoice_hdr h
WHERE h.customer_id = 10058475
AND h.invoice_date >= DATEADD(YEAR, -1, @D)
AND h.invoice_date <= @D
GROUP BY h.period ,
h.year_for_period
or maybe this:
DECLARE @D DATETIME
SET @D = '3-1-14'
SELECT h.period ,
h.year_for_period ,
SUM(h.total_amount)
FROM invoice_hdr h
WHERE h.customer_id = 10058475
AND h.invoice_date > '1/1/2013'
AND ( ( h.period <= DATEPART(MONTH, @d)
AND h.year_for_period = DATEPART(YEAR, @D)
)
OR ( h.period > DATEPART(MONTH, @d)
AND h.year_for_period = DATEPART(YEAR, @D) - 1
)
)
GROUP BY h.period ,
h.year_for_period
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 9:03 am
You're right, I don't think I need the period or year in my return because then I get muliple lines of return. What I have is a table of sales by month. I want to run the report today and group sales by either summer or winter months, most current, as in my first post. If report was run today (3-4-14), I would expect to see Winter - $1700 (Oct2013-Mar2014) and Summer - $3800 (Apr2013-Sep2013). But when I run it on Jun 4, 2014, I expect to see Winter $1700 (Oct2013-Mar2014) and Summer - $4400 (Jul-Sep2013 and Apr-Jun2014).
Only the last six months of the Summer seson regardless of year. Each month causes a shift in the data field pulled. Winter months would start to shift when I run the report in Oct 2014.
Sample data for above numbers.
Jan2013 100
Feb2013 200
Mar2013 300
Apr2013 100
May2013 600
Jun2013 700
Jul2013 900
Aug2013 1000
Sep2013 500
Oct2013 400
Nov2013 300
Dec2013 100
Jan2014 200
Feb2014 200
Mar2014 500
Apr2014 400
May2014 600
Jun2014 1000
March 4, 2014 at 9:11 am
Like Jack mentioned above it would be really helpful if you could provide a create table script along with insert statements for your sample data. Please read the article in my signature on posting questions to the forum. Thanks
March 4, 2014 at 9:53 am
Here's a create table with an insert for the same sample data as earlier.
CREATE TABLE sample_sales
(customer_id varchar(8) not null
, period decimal(3,0) null
, year_for_period decimal (4,0) null
, total_amount decimal(19,4) not null)
INSERT INTO sample_sales
(customer_id , period , year_for_period , total_amount)
VALUES (10058475,1,2013,100)
,(10058475,2,2013,200)
,(10058475,3,2013,300)
,(10058475,4,2013,100)
,(10058475,5,2013,600)
,(10058475,6,2013,700)
,(10058475,7,2013,900)
,(10058475,8,2013,1000)
,(10058475,9,2013,500)
,(10058475,10,2013,400)
,(10058475,11,2013,300)
,(10058475,12,2013,100)
,(10058475,1,2014,200)
,(10058475,2,2014,200)
,(10058475,3,2014,500)
,(10058475,4,2014,400)
,(10058475,5,2014,600)
,(10058475,6,2014,1000)
March 4, 2014 at 9:57 am
So if, I'm understanding better you need something like this:
DECLARE @D DATETIME;
SET @D = '3-1-14';
DECLARE @sales TABLE
(
period TINYINT ,
year_for_period INT ,
total_amount INT
);
INSERT INTO @sales
( period, year_for_period, total_amount )
VALUES ( 1, 2013, 100 ),
( 2, 2013, 200 ),
( 3, 2013, 300 ),
( 4, 2013, 100 ),
( 5, 2013, 600 ),
( 6, 2013, 700 ),
( 7, 2013, 900 ),
( 8, 2013, 1000 ),
( 9, 2013, 500 ),
( 10, 2013, 400 ),
( 11, 2013, 300 ),
( 12, 2013, 100 ),
( 1, 2014, 200 ),
( 2, 2014, 200 ),
( 3, 2014, 500 ),
( 4, 2014, 400 ),
( 5, 2014, 600 ),
( 6, 2014, 1000 );
WITH salesCategory
AS ( SELECT h.period ,
h.year_for_period ,
CASE WHEN h.period BETWEEN 4 AND 9 THEN 'Summer'
ELSE 'Winter'
END AS category ,
SUM(h.total_amount) OVER ( PARTITION BY h.period,
h.year_for_period ) AS periodTotal
FROM @sales h
WHERE ( ( h.period <= DATEPART(MONTH, @d)
AND h.year_for_period = DATEPART(YEAR, @D)
)
OR ( h.period > DATEPART(MONTH, @d)
AND h.year_for_period = DATEPART(YEAR, @D)
- 1
)
)
)
SELECT period ,
year_for_period ,
category ,
periodTotal ,
SUM(periodTotal) OVER ( PARTITION BY category ) AS categoryTotal
FROM salesCategory
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 10:09 am
That's what I'm looking for. Wow. Now to digest it a little so I can implement. Thanks so much. I would not have figured this out without help.
March 4, 2014 at 10:16 am
Great. Glad it looks like we were able to piece together something that helped.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 7:24 pm
Taking Jack's setup data and @d variable, I propose this.
WITH Sales AS
(
SELECT *, category=CASE WHEN period BETWEEN 4 AND 9 THEN 0 ELSE 1 END
,d=DATEADD(month, period-1, DATEADD(year, year_for_period-1900, 0))
FROM @sales
),
PreAggregate AS
(
SELECT category
,categoryTotal=SUM(total_amount)
FROM Sales
WHERE d BETWEEN DATEADD(month, -11, @d) AND @d
GROUP BY category
)
SELECT b.period, year_for_period
,category=CASE a.category WHEN 0 THEN 'Summer' ELSE 'Winter' END
,periodTotal=total_amount, categoryTotal
FROM PreAggregate a
JOIN Sales b ON a.category = b.category
WHERE d BETWEEN DATEADD(month, -11, @d) AND @d;
Maybe it's just me but I think this looks simpler. I also have reason to believe it will perform better doing the PreAggregate instead of the window aggregate. Reference:
The Performance of the T-SQL Window Functions [/url]
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
March 4, 2014 at 7:47 pm
Dwain,
Cool solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 8:02 pm
Actually, this might be a little more straightforward.
WITH Sales AS
(
SELECT *, category=CASE WHEN period BETWEEN 4 AND 9 THEN 'Summer' ELSE 'Winter' END
FROM @sales a
CROSS APPLY
(
SELECT d=DATEADD(month, period-1, DATEADD(year, year_for_period-1900, 0))
) b
WHERE d BETWEEN DATEADD(month, -11, @d) AND @d
),
PreAggregate AS
(
SELECT category
,categoryTotal=SUM(total_amount)
FROM Sales
GROUP BY category
)
SELECT b.period, year_for_period
,a.category
,periodTotal=total_amount, categoryTotal
FROM PreAggregate a
JOIN Sales b ON a.category = b.category;
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply