Cleaner method than a bunch of IF statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cleaner method than a bunch of IF statements

Author

Topic

hk13
Starting Member

12 Posts

Posted - 2014-10-09 : 12:19:05

Hello,

I only recently started learning SQL at the request of my employer and I've been working on a query for them.

Basically, they want to be able to compare sales for the last 3 completed months to that of the previous last 4-6 months. Some quirks that make things a bit more complicated is that the data does not have any date columns. Instead, it only has 2 INT columns for year and month.

The only way I was able to figure out a way to do this was to create 12 IF statements in order to assign parameters depending on the month. If there's a cleaner way to do this, I'd appreciate the learning experience.

Thanks in advance.

-----------------------------------

DECLARE @Year0 INT, @Year1 INT, @Month INT
DECLARE @Y0S3MonthStart INT, @Y1S3MonthStart INT, @Y0S3MonthEnd INT, @Y1S3MonthEnd INT
DECLARE @Y0S6MonthStart INT, @Y1S6MonthStart INT, @Y0S6MonthEnd INT, @Y1S6MonthEnd INT
DECLARE @M3 TABLE (CustomerID INT, TotalSales NUMERIC(18,4))
DECLARE @M6 TABLE (CustomerID INT, TotalSales NUMERIC(18,4))

SET @Year0 = YEAR(GETDATE())
SET @Year1 = @Year0 - 1
SET @Month = MONTH(GETDATE())

IF @Month = 1
BEGIN
SET @Y0S3MonthStart = 99
SET @Y0S3MonthEnd = 0
SET @Y1S3MonthStart = 10
SET @Y1S3MonthEnd = 12
SET @Y0S6MonthStart = 99
SET @Y0S6MonthEnd = 0
SET @Y1S6MonthStart = 9
SET @Y1S6MonthEnd = 11
END

IF @Month = 2
BEGIN
SET @Y0S3MonthStart = 99
SET @Y0S3MonthEnd = 1
SET @Y1S3MonthStart = 11
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 99
SET @Y0S6MonthEnd = 0
SET @Y1S6MonthStart = 8
SET @Y1S6MonthEnd = 10
END

IF @Month = 3
BEGIN
SET @Y0S3MonthStart = 99
SET @Y0S3MonthEnd = 2
SET @Y1S3MonthStart = 12
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 99
SET @Y0S6MonthEnd = 0
SET @Y1S6MonthStart = 9
SET @Y1S6MonthEnd = 11
END

IF @Month = 4
BEGIN
SET @Y0S3MonthStart = 1
SET @Y0S3MonthEnd = 3
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 99
SET @Y0S6MonthEnd = 0
SET @Y1S6MonthStart = 10
SET @Y1S6MonthEnd = 12
END

IF @Month = 5
BEGIN
SET @Y0S3MonthStart = 2
SET @Y0S3MonthEnd = 4
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 99
SET @Y0S6MonthEnd = 1
SET @Y1S6MonthStart = 11
SET @Y1S6MonthEnd = 0
END

IF @Month = 6
BEGIN
SET @Y0S3MonthStart = 3
SET @Y0S3MonthEnd = 5
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 99
SET @Y0S6MonthEnd = 2
SET @Y1S6MonthStart = 12
SET @Y1S6MonthEnd = 0
END

IF @Month = 7
BEGIN
SET @Y0S3MonthStart = 4
SET @Y0S3MonthEnd = 6
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 1
SET @Y0S6MonthEnd = 3
SET @Y1S6MonthStart = 99
SET @Y1S6MonthEnd = 0
END

IF @Month = 8
BEGIN
SET @Y0S3MonthStart = 5
SET @Y0S3MonthEnd = 7
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 2
SET @Y0S6MonthEnd = 4
SET @Y1S6MonthStart = 99
SET @Y1S6MonthEnd = 0
END

IF @Month = 9
BEGIN
SET @Y0S3MonthStart = 6
SET @Y0S3MonthEnd = 8
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 3
SET @Y0S6MonthEnd = 5
SET @Y1S6MonthStart = 99
SET @Y1S6MonthEnd = 0
END

IF @Month = 10
BEGIN
SET @Y0S3MonthStart = 7
SET @Y0S3MonthEnd = 9
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 4
SET @Y0S6MonthEnd = 6
SET @Y1S6MonthStart = 99
SET @Y1S6MonthEnd = 0
END

IF @Month = 11
BEGIN
SET @Y0S3MonthStart = 8
SET @Y0S3MonthEnd = 10
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 5
SET @Y0S6MonthEnd = 7
SET @Y1S6MonthStart = 99
SET @Y1S6MonthEnd = 0
END

IF @Month = 12
BEGIN
SET @Y0S3MonthStart = 9
SET @Y0S3MonthEnd = 11
SET @Y1S3MonthStart = 99
SET @Y1S3MonthEnd = 0
SET @Y0S6MonthStart = 6
SET @Y0S6MonthEnd = 8
SET @Y1S6MonthStart = 99
SET @Y1S6MonthEnd = 0
END

INSERT @M3
SELECT
SA2.CustomerID,
SUM(SA2.TotalSales) [TotalSales]
FROM SalesAnalysis2 SA2 WITH(NOLOCK)
LEFT JOIN Customer C WITH(NOLOCK) ON C.CustomerID = SA2.CustomerID
WHERE SA2.BranchID=0 AND C.CashAccount=0
AND ((SA2.CalendarYear=@Year0 AND SA2.CalendarMonth>=@Y0S3MonthStart AND SA2.CalendarMonth<=@Y0S3MonthEnd) OR (SA2.CalendarYear=@Year1 AND SA2.CalendarMonth>=@Y1S3MonthStart AND SA2.CalendarMonth<=@Y1S3MonthEnd))
GROUP BY SA2.CustomerID

INSERT @M6
SELECT
SA2.CustomerID,
SUM(SA2.TotalSales) [TotalSales]
FROM SalesAnalysis2 SA2 WITH(NOLOCK)
LEFT JOIN Customer C WITH(NOLOCK) ON C.CustomerID = SA2.CustomerID
WHERE SA2.BranchID=0 AND C.CashAccount=0
AND ((SA2.CalendarYear=@Year0 AND SA2.CalendarMonth>=@Y0S6MonthStart AND SA2.CalendarMonth<=@Y0S6MonthEnd) OR (SA2.CalendarYear=@Year1 AND SA2.CalendarMonth>=@Y1S6MonthStart AND SA2.CalendarMonth<=@Y1S6MonthEnd))
GROUP BY SA2.CustomerID

SELECT
C.CustomerCode,
C.Name [Customer],
M3.TotalSales [TotalSales Last3Mo],
M6.TotalSales [TotalSales Last6Mo],
M3.TotalSales-M6.TotalSales [Differece],
U.Name [SalesRep]

FROM Customer C WITH(NOLOCK)
LEFT JOIN @M3 M3 ON M3.CustomerID = C.CustomerID
LEFT JOIN @M6 M6 ON M6.CustomerID = C.CustomerID
LEFT JOIN SalesRep SR ON SR.SalesRepID = C.SalesRepID
LEFT JOIN Users U ON U.UserID = SR.UserID

WHERE M3.TotalSales-M6.TotalSales >= 5000 OR M3.TotalSales-M6.TotalSales <= -5000

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 13:05:19

Create a table variable like this:


declare @monthtable table (
    [Month] int
  , Y0S3MonthStart int
  , Y0S3MonthEnd   int
  , Y1S3MonthStart int
  , Y1S3MonthEnd   int
  , Y0S6MonthStart int
  , Y0S6MonthEnd   int
  , Y1S6MonthStart int
  , Y1S6MonthEnd int
)

Insert into @monthtable (
    [Month]
  , Y0S3MonthStart
  , Y0S3MonthEnd
  , Y1S3MonthEnd
  , Y0S6MonthStart
  , Y0S6MonthEnd
  , Y1S6MonthStart
  , Y1S6MonthEnd int
)

values

(1, 99, 0, 10, 12, 99, 0, 9, 11),
 ..

Then join on that table in your insert logic and use the column names instead of variable names.

kostya1122
Starting Member

15 Posts

Posted - 2014-10-09 : 13:10:19

you could use case

like

SET @Y0S3MonthEnd = case when @Month =1 then 0
WHEN @Month =2 then 1 ...ETC

hk13
Starting Member

12 Posts

Posted - 2014-10-09 : 14:15:52

Very cool idea, gbritton

I never thought to use tables like that before. It's definitely much cleaner looking now. Unfortunately, it does run noticeably slower due to making the extra table, I guess. Nevertheless, a nice learning experience.

Thanks again!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 20:32:03

try changing the table to start ...[Month] int primary key

With such a small table it shouldn't make a difference but its worth a try. also, compare the query plans with and without the table variable with and without the primary key. It could be interesting. post your results so we all can see

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources