November 24, 2015 at 1:23 pm
The need is to compare the revenues of each month with the revenues of the same in the previous year.
For instance, customer 'A' was lost in 2014, so the last invoice in the table is for year 2013.
How could it be possible to create a record for year 2014 for that customer even though there is no invoice for that customer in year 2014 ? But there should be no additional record created for the second year following the year the customer was lost.
Note that for a given month there may be more than one invoice for a given customer but what is required in the results is the total for each month. So much with updating a table with a sum ...
The order should be by InvoiceYearMonth and Customer.
Table variables are used here only to avoid creating them on the server - they are simplified versions of the actual tables which are permanent tables in the database.
There are no restrictions on creating staging tables or stored procedures.
Here is the table creation and stuffing script.
[font="Courier New"]
DECLARE @Invoice TABLE
(
InvoiceID int,
AccountID int,
InvoiceYearMonth int,
Amount money NOT NULL DEFAULT 0.00
)
DECLARE @Results TABLE
(
AccountID int,
InvoiceYearMonth int,
Amount money NOT NULL DEFAULT 0.00,
PreviousYearSameMonth int,
PreviousAmount money NOT NULL DEFAULT 0.00,
LostRevenue money NOT NULL DEFAULT 0.00
)
INSERT INTO @Invoice
(
InvoiceID,
AccountID,
InvoiceYearMonth,
Amount
)
VALUES
(1, 1000, 201204, 12345),
(2, 1000, 201204, 45),
(3, 1001, 201204, 10000),
(4, 1002, 201204, 11000),
(5, 1003, 201205, 12000),
(6, 1000, 201304, 12346),
(7, 1000, 201304, 67),
(8, 1000, 201304, 78),
(9, 1001, 201304, 10000),
(10, 1002, 201304, 11000),
(11, 1003, 201305, 12000),
(12, 1001, 201404, 10000),
(13, 1002, 201404, 11000),
(14, 1003, 201405, 12000),
(15, 1001, 201504, 10000)
-- REQUIRED RESULT
-- *** Indicates the records which do not exist in the Invoice table and must be added to show the loss
AccountID Invoice Amount PreviousYear Previous Change
YearMonth SameMonth Amount
1000 201204 12390.00 NULL 0.00 12390.00
1001 201204 10000.00 NULL 0.00 10000.00
1002 201204 11000.00 NULL 0.00 11000.00
1003 201205 12000.00 NULL 0.00 12000.00
1000 201304 12491.00 201204 12390.00 101.00
1001 201304 10000.00 201204 10000.00 0.00
1002 201304 11000.00 201204 11000.00 0.00
1003 201305 12000.00 201205 12000.00 0.00
1000 *** 201404 0.00 201304 12491.00 -12491.00
1001 201404 10000.00 201304 10000.00 0.00
1002 201404 11000.00 201304 11000.00 0.00
1003 201405 12000.00 201304 12000.00 0.00
1001 201504 10000.00 201404 10000.00 0.00
1002 *** 201504 0.00 201404 11000.00 -11000.00
1003 *** 201505 0.00 201405 12000.00 -12000.00
[/font]
November 24, 2015 at 4:50 pm
The short answer is to create an auxiliary Calendar table and then cross join that to the Customer table and then outer join that to invoices. The (Calendar X Customer) table will give you all customers and all MonthYear combinations. Then you can left join that to sales. You'll get NULLs for missing values, which you can COALESCE to zero if necessary and then do math on them.
November 24, 2015 at 5:03 pm
Here is what I came up with. It does return an extra line of data and I'm not clear which one of us has the correct output. C'est la vie!
;with
SummedByYearMonth
as(
select
AccountID,
InvoiceYearMonth,
sum(Amount) Amount
from
@Invoice
group by
AccountID,
InvoiceYearMonth
)--select * from SummedByYearMonth order by AccountID, InvoiceYearMonth
select --o.AccountID, n.AccountID,
coalesce(o.AccountID, n.AccountID) AccountID2,
coalesce(n.InvoiceYearMonth, o.InvoiceYearMonth + 100) InvoiceYearMonth2,
coalesce(n.Amount, 0.0) Amount,
o.InvoiceYearMonth,
coalesce(o.Amount, 0.0) PreviousAmount,
coalesce(n.Amount, 0.0) - coalesce(o.Amount, 0.0) Change
from
SummedByYearMonth o
full outer join
SummedByYearMonth n
ono.AccountID = n.AccountID
andn.InvoiceYearMonth = o.InvoiceYearMonth + 100
order by
AccountID2,
InvoiceYearMonth2
November 24, 2015 at 5:24 pm
j-1064772 (11/24/2015)
The need is to compare the revenues of each month with the revenues of the same in the previous year.For instance, customer 'A' was lost in 2014, so the last invoice in the table is for year 2013.
How could it be possible to create a record for year 2014 for that customer even though there is no invoice for that customer in year 2014 ? But there should be no additional record created for the second year following the year the customer was lost.
Note that for a given month there may be more than one invoice for a given customer but what is required in the results is the total for each month. So much with updating a table with a sum ...
The order should be by InvoiceYearMonth and Customer.
Table variables are used here only to avoid creating them on the server - they are simplified versions of the actual tables which are permanent tables in the database.
There are no restrictions on creating staging tables or stored procedures.
Here is the table creation and stuffing script.
[font="Courier New"]
DECLARE @Invoice TABLE
(
InvoiceID int,
AccountID int,
InvoiceYearMonth int,
Amount money NOT NULL DEFAULT 0.00
)
DECLARE @Results TABLE
(
AccountID int,
InvoiceYearMonth int,
Amount money NOT NULL DEFAULT 0.00,
PreviousYearSameMonth int,
PreviousAmount money NOT NULL DEFAULT 0.00,
LostRevenue money NOT NULL DEFAULT 0.00
)
INSERT INTO @Invoice
(
InvoiceID,
AccountID,
InvoiceYearMonth,
Amount
)
VALUES
(1, 1000, 201204, 12345),
(2, 1000, 201204, 45),
(3, 1001, 201204, 10000),
(4, 1002, 201204, 11000),
(5, 1003, 201205, 12000),
(6, 1000, 201304, 12346),
(7, 1000, 201304, 67),
(8, 1000, 201304, 78),
(9, 1001, 201304, 10000),
(10, 1002, 201304, 11000),
(11, 1003, 201305, 12000),
(12, 1001, 201404, 10000),
(13, 1002, 201404, 11000),
(14, 1003, 201405, 12000),
(15, 1001, 201504, 10000)
-- REQUIRED RESULT
-- *** Indicates the records which do not exist in the Invoice table and must be added to show the loss
AccountID Invoice Amount PreviousYear Previous Change
YearMonth SameMonth Amount
1000 201204 12390.00 NULL 0.00 12390.00
1001 201204 10000.00 NULL 0.00 10000.00
1002 201204 11000.00 NULL 0.00 11000.00
1003 201205 12000.00 NULL 0.00 12000.00
1000 201304 12491.00 201204 12390.00 101.00
1001 201304 10000.00 201204 10000.00 0.00
1002 201304 11000.00 201204 11000.00 0.00
1003 201305 12000.00 201205 12000.00 0.00
1000 *** 201404 0.00 201304 12491.00 -12491.00
1001 201404 10000.00 201304 10000.00 0.00
1002 201404 11000.00 201304 11000.00 0.00
1003 201405 12000.00 201304 12000.00 0.00
1001 201504 10000.00 201404 10000.00 0.00
1002 *** 201504 0.00 201404 11000.00 -11000.00
1003 *** 201505 0.00 201405 12000.00 -12000.00
[/font]
Tried to get fancy but have to learn more about the new functionality in 2012 as I was getting weird results. Brute force and this is what I got for you. You will also notice that I terminated your statements with a semicolon as this is a requirement for using CTE's. Pet peeve, but sorry, semicolons belong at the END of statements not the beginning. It is a terminator not a begininator. Microsoft documentation in Books Online is wrong to start CTE's with a semicolon when the documentation itself clearly states that it is the preceding statement that must be terminated. It would be nice if Microsoft followed its own specifications.
DECLARE @Invoice TABLE
(
InvoiceID int,
AccountID int,
InvoiceYearMonth int,
Amount money NOT NULL DEFAULT 0.00
);
DECLARE @Results TABLE
(
AccountID int,
InvoiceYearMonth int,
Amount money NOT NULL DEFAULT 0.00,
PreviousYearSameMonth int,
PreviousAmount money NOT NULL DEFAULT 0.00,
LostRevenue money NOT NULL DEFAULT 0.00
);
INSERT INTO @Invoice
(
InvoiceID,
AccountID,
InvoiceYearMonth,
Amount
)
VALUES
(1, 1000, 201204, 12345),
(2, 1000, 201204, 45),
(3, 1001, 201204, 10000),
(4, 1002, 201204, 11000),
(5, 1003, 201205, 12000),
(6, 1000, 201304, 12346),
(7, 1000, 201304, 67),
(8, 1000, 201304, 78),
(9, 1001, 201304, 10000),
(10, 1002, 201304, 11000),
(11, 1003, 201305, 12000),
(12, 1001, 201404, 10000),
(13, 1002, 201404, 11000),
(14, 1003, 201405, 12000),
(15, 1001, 201504, 10000);
with basedata as (
select
i.AccountID,
i.InvoiceYearMonth,
i.InvoiceYearMonth / 100 InvoiceYear,
sum(i.Amount) Amount
from
@Invoice i
group by
i.AccountID,
i.InvoiceYearMonth
), InvoiceYearDates as (
select
InvoiceYearMonth
from
basedata
union
select
InvoiceYearMonth + 100
from
basedata
where
(InvoiceYearMonth + 100) / 100 <= (select max(InvoiceYear) from basedata)
)
select
isnull(oa1.AccountID,oa2.AccountID) AccountID,
iyd.InvoiceYearMonth,
isnull(oa1.Amount,0.00) Amount,
oa2.InvoiceYearMonth PreviousYearSameMonth,
isnull(oa2.Amount,0.00) PreviousAmount,
isnull(oa1.Amount,0.00) - isnull(oa2.Amount,0.00) Change
from
InvoiceYearDates iyd
cross apply (select distinct AccountID from basedata)ca1(AccountID)
outer apply (select AccountID, InvoiceYearMonth, Amount from basedata bd where iyd.InvoiceYearMonth = bd.InvoiceYearMonth and ca1.AccountID = bd.AccountID)oa1(AccountID, InvoiceYearMonth, Amount)
outer apply (select AccountID, InvoiceYearMonth, Amount from basedata bd where iyd.InvoiceYearMonth - 100 = bd.InvoiceYearMonth and ca1.AccountID = bd.AccountID)oa2(AccountID, InvoiceYearMonth, Amount)
where
(oa1.AccountID is not null and oa2.AccountID is null) or
(oa1.AccountID is null and oa2.AccountID is not null) or
(oa1.AccountID is not null and oa2.AccountID is not null)
November 25, 2015 at 9:06 am
Thank you all for your assistance.
My solution looks amateurish next to yours - I had a second insert based on the results of the first insert selecting only where there no yearmonth + 100 for the same customer... Yecch.
November 25, 2015 at 10:21 am
The last condition can be simplified to
where
oa1.AccountID is not null or oa2.AccountID is not null
November 25, 2015 at 10:47 am
Luis Cazares (11/25/2015)
The last condition can be simplified to
whereoa1.AccountID is not null or oa2.AccountID is not null
Thanks. Normally I'm pretty good about reducing Boolean logic, just couldn't seem to do it yesterday.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply