June 3, 2013 at 8:42 am
I have a table -
TrnDate, TrnYear, TrnMonth, StockCode, InvoiceQty
The TrnYear and TrnMonth are the Financial Periods and dont always tie back to the TrnDate, Month/Year.
for example - 31/05/2013 - might fall into Period 6 / 2013
What I am after is a script that will calculate historic InvoiceQty for ;
StockCode, MonthToDate, LastMonth, Previous6Months, Previous12Months, Previous18Months, Previous24Months, Previous36Months
the hard bit is when you want to go back 6 months and the year changes ....
hopefully this makes sense ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 3, 2013 at 8:44 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2013 at 8:44 am
The easy bit -
SELECT
StockCode,
SUM(CASE TrnYear WHEN '2009' THEN InvoiceQty ELSE 0 END) AS Sales2009,
SUM(CASE TrnYear WHEN '2010' THEN InvoiceQty ELSE 0 END) AS Sales2010,
SUM(CASE TrnYear WHEN '2011' THEN InvoiceQty ELSE 0 END) AS Sales2011,
SUM(CASE TrnYear WHEN '2012' THEN InvoiceQty ELSE 0 END) AS Sales2012,
SUM(CASE TrnYear WHEN '2013' THEN InvoiceQty ELSE 0 END) AS Sales2013
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 3, 2013 at 11:21 am
SteveEClarke (6/3/2013)
The easy bit -SELECT
StockCode,
SUM(CASE TrnYear WHEN '2009' THEN InvoiceQty ELSE 0 END) AS Sales2009,
SUM(CASE TrnYear WHEN '2010' THEN InvoiceQty ELSE 0 END) AS Sales2010,
SUM(CASE TrnYear WHEN '2011' THEN InvoiceQty ELSE 0 END) AS Sales2011,
SUM(CASE TrnYear WHEN '2012' THEN InvoiceQty ELSE 0 END) AS Sales2012,
SUM(CASE TrnYear WHEN '2013' THEN InvoiceQty ELSE 0 END) AS Sales2013
This?
SELECT
StockCode,
SUM(CASE TrnYear WHEN '2009' THEN InvoiceQty ELSE 0 END) AS Sales2009,
SUM(CASE TrnYear WHEN '2010' THEN InvoiceQty ELSE 0 END) AS Sales2010,
SUM(CASE TrnYear WHEN '2011' THEN InvoiceQty ELSE 0 END) AS Sales2011,
SUM(CASE TrnYear WHEN '2012' THEN InvoiceQty ELSE 0 END) AS Sales2012,
SUM(CASE TrnYear WHEN '2013' THEN InvoiceQty ELSE 0 END) AS Sales2013
FROM
dbo.MyTable -- put your actual table name here
GROUP BY
StockCode
ORDER BY -- If you also want it ordered in this order as well, the GROUP BY does not ensure this
StockCode
June 3, 2013 at 11:35 am
Completely untested due to the lack of sample data, but it can give you an idea.
DECLARE @test-2TABLE(
TrnDatedate,
TrnYearint,
TrnMonthint,
StockCodeint,
InvoiceQty int);
DECLARE @Date date;
WITH CTE AS(
SELECT DISTINCT
TrnYear,
TrnMonth,
ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn
FROM @test-2
WHERE TrnDate <= @Date)
SELECT StockCode,
SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,
SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,
SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,
SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,
SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,
SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,
SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months
FROM @test-2 t
JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear
GROUP BY StockCode
June 4, 2013 at 1:49 am
Okay ... table and data.
The results I would expect are .
StockCode, MonthToDate, LastMonth, Previous6Months, Previous12Months, Previous18Months, Previous24Months, Previous36Months
38-1446-9912, 31, 98, 253, 253, 253, 253, 253
DROP TABLE #mytemptable
create table #mytemptable
( TrnDatedatetime,
InvoiceQtydecimal(10,2),
TrnMonth decimal(4),
TrnYeardecimal(4),
StockCodechar(30)
)
insert into #mytemptable
(TrnDate, InvoiceQty, TrnMonth, TrnYear, StockCode)
select '2013-04-10',40.000,4,2013,'38-1446-9912' union all
select '2013-04-25',75.000,4,2013,'38-1446-9912' union all
select '2013-05-09',5.000,5,2013,'38-1446-9912' union all
select '2013-05-16',30.000,5,2013,'38-1446-9912' union all
select '2013-05-22',50.000,5,2013,'38-1446-9912' union all
select '2013-05-30',30.000,6,2013,'38-1446-9912' union all
select '2013-05-30',1.000,6,2013,'38-1446-9912' union all
select '2013-04-16',1.000,4,2013,'38-1446-9912' union all
select '2013-04-16',3.000,4,2013,'38-1446-9912' union all
select '2013-04-25',1.000,4,2013,'38-1446-9912' union all
select '2013-04-26',1.000,4,2013,'38-1446-9912' union all
select '2013-05-07',1.000,5,2013,'38-1446-9912' union all
select '2013-05-17',2.000,5,2013,'38-1446-9912' union all
select '2013-05-24',1.000,5,2013,'38-1446-9912' union all
select '2013-05-24',1.000,5,2013,'38-1446-9912' union all
select '2013-05-22',3.000,5,2013,'38-1446-9912' union all
select '2013-04-18',3.000,4,2013,'38-1446-9912' union all
select '2013-05-09',4.000,5,2013,'38-1446-9912' union all
select '2013-05-09',1.000,5,2013,'38-1446-9912'
DECLARE @Date DateTime;
set @Date = GetDate();
WITH CTE AS(
SELECT DISTINCT
TrnYear,
TrnMonth,
ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn
FROM #mytemptable
WHERE TrnDate <= @Date)
SELECT StockCode,
SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,
SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,
SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,
SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,
SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,
SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,
SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months
FROM #mytemptable t
JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear
GROUP BY StockCode
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 4, 2013 at 4:27 am
see if this helps....
DECLARE @Date DateTime;
set @Date = GetDate();
/* see changes made to the CTE */
WITH CTE AS
(
SELECT
TrnYear,
TrnMonth,
ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn
FROM (
SELECT DISTINCT
TrnYear,
TrnMonth
FROM #mytemptable
WHERE TrnDate <= @Date) dt
)
SELECT StockCode,
SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,
SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,
SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,
SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,
SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,
SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,
SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months
FROM #mytemptable t
JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear
GROUP BY StockCode
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 4, 2013 at 4:48 am
hmmm....take a look at this data set....I don't think you are going to get what you expect.
suggest that you read up on using a calendar table instead of the CTE for your dates.
create table #mytemptable
( TrnDate datetime,
InvoiceQty decimal(10,2),
TrnMonth decimal(4),
TrnYear decimal(4),
StockCode char(30)
)
insert into #mytemptable
(TrnDate, InvoiceQty, TrnMonth, TrnYear, StockCode)
select '2013-04-10',40.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-04-25',75.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-05-09',5.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-16',30.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-22',50.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-30',30.000 ,6 ,2013 ,'38-1446-9912' union all
select '2013-05-30',1.000 ,6 ,2013 ,'38-1446-9912' union all
select '2013-04-16',1.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-04-16',3.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-04-25',1.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-04-26',1.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-05-07',1.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-17',2.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-24',1.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-24',1.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-22',3.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-04-18',3.000 ,4 ,2013 ,'38-1446-9912' union all
select '2013-05-09',4.000 ,5 ,2013 ,'38-1446-9912' union all
select '2013-05-09',1.000 ,5 ,2013 ,'38-1446-9912' union all
/* added another date period this will not give you the results I think you are expecting*/
select '2000-05-09',1.000 ,5 ,2000 ,'99-9999-9999'
/* suggest that instead of using the CTE method..that you use a calendar table */
DECLARE @Date DateTime
set @Date = GetDate();
/* see changes made to the CTE */
WITH CTE AS
(
SELECT
TrnYear,
TrnMonth,
ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn
FROM (
SELECT DISTINCT
TrnYear,
TrnMonth
FROM #mytemptable
WHERE TrnDate <= @Date) dt
)
SELECT StockCode,
SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,
SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,
SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,
SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,
SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,
SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,
SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months
FROM #mytemptable t
JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear
GROUP BY StockCode
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 5, 2013 at 12:38 pm
You're also going to need a reference (either for yourself but definitely for us) of what actual dates fall into which trnMonths and trnYears. We don't have a way to make a comparison without that data.
June 6, 2013 at 1:23 am
Yes - I understand - but that is what the TrnYear and TrnMonth fields are.
so forget the TrnDate Fields -
I suppose something like ....
Max TrnYear, TrnMonth to give 2013, 06 - this would give current
Max TrnYear, TrnMonth -1 to give 2013, 05 - this would give previous month
the problem comes when it rolls back over a year !?
does that make sense ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 6, 2013 at 7:32 am
SteveEClarke (6/6/2013)
Yes - I understand - but that is what the TrnYear and TrnMonth fields are.so forget the TrnDate Fields -
I suppose something like ....
Max TrnYear, TrnMonth to give 2013, 06 - this would give current
Max TrnYear, TrnMonth -1 to give 2013, 05 - this would give previous month
the problem comes when it rolls back over a year !?
does that make sense ?
Are TrnMonth and TrnYear based off of TrnDate and if so how?
June 7, 2013 at 4:09 am
Hello,
I think your answer is Previous6Months..
June 10, 2013 at 4:30 am
Thinking about this over the weekend - I think as per the previous suggestion - I need to create a Fiscal Calender with counts for the months/years.
so the table would look like ;
YearMonth count
200901 001
200902 002
200903 003
etc..
201306 054
201307 055
then just take the (TrnDate ) which would give me the TrnYear/TrnMonth - then using calulations to go backwards - so 055 - 36 = 019 for Previous36.
I know where I am going now -
Thanks to all for their input.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply