As I think this is homework I'm going to walk you through my thought process rather than just give you a solution ...
First of all I recreated your sample data
create table tblSales
(
monat int,
jahre int,
product varchar(5),
descript varchar(30),
Finace varchar(2),
Amount1 float,
Amount2 float
)
insert into tblSales values
(2,2011,'A.123' ,'Milk', 'AZ', 0, 0),
(2,2011,'A.123' ,'Milk', 'AB', 1200, 1000),
(2,2011,'A.478' ,'Sugar', 'ZE', 1300, 600),
(2,2011,'A.478' ,'Sugar', 'ZB', 1400, 150),
(2,2011,'B.156' ,'Coffee', 'CD', 1000, 2000),
(2,2011,'C.123' ,'Brownie', 'QP', 500, 300),
(3,2011,'A.123' ,'Milk', 'AZ', 0, 0),
(3,2011,'A.123' ,'Milk', 'AB', 1200, 1000),
(3,2011,'A.478' ,'Sugar', 'ZE', 1300, 600),
(3,2011,'A.478' ,'Sugar', 'ZB', 1400, 150),
(3,2011,'B.156' ,'Coffee', 'CD', 1000, 2000),
(3,2011,'D.007' ,'Celeriac', 'JD', 2000, 350),
(3,2011,'E.789' ,'Soap', 'MD', 900, 450)
Note that I've separated out the month (monat) and year (jahre) - this is a personal preference, I simply don't like composite columns. I could have chosen
Date
instead and ignored the day part. It shouldn't make this any harder to follow and You must do as
you feel.
I don't dive straight into writing stored procedures, I write them in the query window first (and again until I think I've got it right), so I also set up these local variables
DECLARE @P1Monat INT = 2
DECLARE @P1Jahre INT = 2011
DECLARE @P2Monat INT = 3
DECLARE @P2Jahre INT = 2011
Having got an environment together where I can play, I then thought, what are we really trying to do here. We're trying to compare the results of these two queries:
SELECT * FROM tblSales WHERE monat = @P1Monat
and
SELECT * FROM tblSales WHERE monat = @P2Monat
Okay, I'll take the results from those two queries as tables and join them... but which join?
This is where sometimes it pays to "get down and dirty" - in other words, experiment! Try it out! See what happens! (this bit can actually be fun and funny). Eventually the lessons learned by the experiment will stick and you will just know what to do next, but I'll keep the story going ...
So ... I tried an INNER JOIN ... hm, only get to see things that in both sets
...a LEFT OUTER JOIN ... nope - can now see Brownie but nothing for Celeriac or soup
...a RIGHT OUTER JOIN...no, Celeriac and Soup now appearing but Brownie has gone again. But that has given me a clue ... try a
FULL OUTER JOIN
(There are some good articles here on CodeProject that will help with decisions like this...e.g.
Visual Representation of SQL Joins[
^])
SELECT *
FROM
(SELECT * FROM tblSales WHERE monat = @P1Monat) A
full OUTER JOIN
(SELECT * FROM tblSales WHERE monat = @P2Monat) B ON A.product=B.product
gives me a resultset of (some columns removed for formatting clarity)
Month product Descr Amnt1 Amnt2 month product Descr Amnt1 Amnt2
2 A.123 Milk 0 0 3 A.123 Milk 0 0
2 A.123 Milk 1200 1000 3 A.123 Milk 0 0
2 A.123 Milk 0 0 3 A.123 Milk 1200 1000
2 A.123 Milk 1200 1000 3 A.123 Milk 1200 1000
2 A.478 Sugar 1300 600 3 A.478 Sugar 1300 600
2 A.478 Sugar 1400 150 3 A.478 Sugar 1300 600
2 A.478 Sugar 1300 600 3 A.478 Sugar 1400 150
2 A.478 Sugar 1400 150 3 A.478 Sugar 1400 150
2 B.156 Coffee 1000 2000 3 B.156 Coffee 1000 2000
NULL NULL NULL NULL NULL 3 D.007 Celerac 2000 350
NULL NULL NULL NULL NULL 3 E.789 Soap 900 450
2 C.123 Brownie 500 300 NULL NULL NULL NULL NULL
Now I've got everything but some columns have null in them. It's also all a bit untidy so I'm going to use
ISNULL[
^] to tidy up the results (I could use
COALESCE[
^] instead)
SELECT ISNULL(A.Product, B.Product) As Product,
ISNULL(A.descript, B.descript) As 'Description',
ISNULL(A.Finace, B.Finace) As Finace,
A.Amount1 as P1Amt1, A.Amount2 as P1Amt2,
B.Amount1 as P2Amt1, B.Amount2 as P2Amt2
FROM
(SELECT * FROM tblSales WHERE monat = @P1Monat) A
FULL OUTER JOIN
(SELECT * FROM tblSales WHERE monat = @P2Monat) B ON A.product=B.product
Product Description Finace P1Amt1 P1Amt2 P2Amt1 P2Amt2
A.123 Milk AZ 0 0 0 0
A.123 Milk AB 1200 1000 0 0
A.123 Milk AZ 0 0 1200 1000
A.123 Milk AB 1200 1000 1200 1000
A.478 Sugar ZE 1300 600 1300 600
A.478 Sugar ZB 1400 150 1300 600
A.478 Sugar ZE 1300 600 1400 150
A.478 Sugar ZB 1400 150 1400 150
B.156 Coffee CD 1000 2000 1000 2000
D.007 Celeriac JD NULL NULL 2000 350
E.789 Soap MD NULL NULL 900 450
C.123 Brownie QP 500 300 NULL NULL
Now we can see what is going on ... if
P1Amt2
(or 2) is
NULL
then it wasn't in the table of stuff in period 1, in other words it is "new" to period 2
Similarly if
P2Amt2
is
NULL
then the product used to be there in Period 1 but is no longer there in Period 2.
Anything else is listing products that were there (sold?) in both Periods.
With that in mind, I leave the actual derivation of
Entrance
as an exercise for you.
Hopefully you can also see now how to easily calculate the values for Amount1Feb, Amount1Mar etc because now all of the figures you need are on a single row