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)