tsql When Case Expression

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

Hi,
I am using this sp which gives me Information based on two chosen months (Comparisim). I now need a syntax within this store procedure which should generate a new column and then indicate me whether a product in month 1 remained (unchanged) in month 2 or whether a product is new in month 2 etc.

My store procedure:

SQL

CREATE PROC [dbo].[spMonthInfo]
 (   
  @Periode1 NVARCHAR (255),
  @Periode2 NVARCHAR (255) 
 )
AS
BEGIN
DECLARE @Dynamictbl nvarchar(MAX) =
 N'SELECT
 [t].[Product],[t].[Description],
 [t].[Finance], --[t].[Product] AS [Entrance],

 CASE 
    // when Products are in both months
  WHEN ....... THEN ''Unchanged''
    // When Product is in Feb but not in March
  WHEN  ....... THEN ''Out''
    // When Product is in March but not in Feb
  WHEN ......... THEN ''New''
END AS Entrance, 

 SUM(CASE WHEN [t].Monat = ' + @Periode1 + ' THEN [t].[Amount1] END) AS     [Amount1Feb],
 SUM(CASE WHEN [t].Monat = ' + @Periode2 + ' THEN [t].[Amount1] END) AS [Amount1March],
 SUM(CASE WHEN [t].Monat = ' + @Periode1 + ' THEN [t].[Amount2] END) AS [Amount2Feb],
 SUM(CASE WHEN [t].Monat = ' + @Periode2 + ' THEN [t].[ Amount2] END) AS [Amount2March],

FROM [dbo].[tblSales] AS t


GROUP BY [t].[Project],[t].[Description],
[t].[Finance], --[t].[Project] AS [Entrance]


 EXECUTE sp_executesql @Dynamictbl

 END
 GO
 EXEC spMonthInfo @Periode1 = '02.2011', @Periode2 = '03.2011'

Expected Result: [^]

In my image example, we can identify that the products 'Soap' and 'Cerelac'are new in March whereas the product 'Brownie' is only in Feb and the rest of the products are in both months. How can I accomplish that within my sp?

Posted 1-Jun-15 21:22pm

mikybrain1

Updated 2-Jun-15 1:36am

v2

Add a Solution

Comments

CHill60 2-Jun-15 7:33am

What is [t]? How do you determine whether a product is "new" in a month?
Are Feb and March the only months in your table?

mikybrain1 2-Jun-15 7:46am

Hi CHill60;
t is the alias of the tblSales. No I have from Jan - Dec and I have put them all into one table (tblSales) as u adviced me before. Have u please checked my image? If yes u can see that the products soap and cerelac are new products. That means they wern't in Feb. So I want to look through the table and check if the product is not in the first month but in the last month write me "new" and if the products are in both months write me "unchanged". I hope u understand me. I don't really know to get the right syntax.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-06-02T03:40:00

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

SQL

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:

SQL

SELECT * FROM tblSales WHERE monat = @P1Monat

and

SQL

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[^])

SQL

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)

SQL

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