Link to home
Start Free TrialLog in
Avatar of mjburgard
mjburgardFlag for United States of America

asked on

SQL Loop, If/Else, Case Statements

I have an SQL query that I am trying to edit to make it more effective.  I didn't write the original and I'm reasonably new to SQL so its kinda touch and go if I can figure something out.  The query basically pulls and displays some information from the database based on start and end dates and a index number (Called an LCCN in this case).  How it was originally written required the person executing it to go in and change dates and numbers in multiple places throughout the query, and it had to be run for times to cover each of the four LCCNs.

I'm trying to make it so there are very few changes that need to be done and they are all in the first few lines.  Also I'm trying to make it run all four LCCNs in one query.  The original is below.


SELECT
      YEAR(UpdateDate) as 'Year',
      DATENAME(mm,UpdateDate) as 'Month',
      SUM(LaborQuantity) as 'Total',
      COUNT(DISTINCT JobN) as 'Number of Jobs'
FROM
      JobLabor
WHERE
      UpdateDate BETWEEN (d '2006-01-01) AND (d '2006-03-01')

      -- Change the below based on press
        -- 52015              6/c Press
        -- 55215              PM
        -- 39010              M700
        -- 39100              DE
      AND LCCN = 52015

      -- Change the below to 0 for regular counts; 2 for rework; comment out for total count
      AND prefix = 0
      
GROUP BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)


This is what I have gotten so far with my changes.

DECLARE @StartDate DATETIME, @EndDate DATETIME, @CountType INT, @6C INT, @PM INT, @M700 INT, @DE INT
SET @StartDate = '2010-07-01'
SET @EndDate = '2010-08-31'
SET @CountType = 0
SET @6C = 52015
SET @PM = 55215
SET @M700 = 39010
SET @DE = 39100
SELECT
      YEAR(UpdateDate) as 'Year',
      DATENAME(mm,UpdateDate) as 'Month',
      SUM(LaborQuantity) as 'Total',
      COUNT(DISTINCT JobN) as 'Number of Jobs'
FROM
      JobLabor
WHERE
      UpdateDate BETWEEN @StartDate AND @EndDate

      -- Change the below based on press
        -- 52015              6/c Press
        -- 55215              PM
        -- 39010              M700
        -- 39100              DE
      AND LCCN = 52015

      -- Change the below to 0 for regular counts; 2 for rework; comment out for total count
      AND prefix = @CountType
GROUP BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)

So I can copy and paste everything from SELECT down four times and change the LCCN numbers to each of the four options, but I know that not only is that the wrong way of doing things, but its messy and if someone else is looking at the code they might not be able to figure it out.

So the main think I'm looking for is how to do a loop for the four LCCN numbers (I think it would have to start above SELECT, not quite sure how far down it would have to go).  I know the basics of loops in some other languages but I haven't been able to figure out exactly what I want to do here.  I've also gotten hints that I want to use a CASE statement to do what I want, but I know very little about them.

The other thing I want to do that isn't as major is embed the line
                         AND prefix = @CountType
in an IF ELSE statement so if the number @CountType is 0 or 2 (don't ask me why its those two, the database is how it is) then it runs but if its any other number then the AND prefix statement is skipped entirely (so we don't have to manually go in and edit it out when we want a total count).

I know this might not be super clear or I might be asking two many things at once, I did what I could to make it easy to understand but I'm still new to this myself.  Any help would be greatly appreciated, thanks.
Avatar of aelliso3
aelliso3
Flag of United States of America image

Is this along the lines of what you are looking for? It keeps all the varaiables in one location to be changed:
/*
       @Press Values to change
       ---------------------
         6/c Press	= 52015
         PM			= 55215
         M700		= 39010
         DE			= 39100
         
         
	@Count Value to change 0 for regular counts; 2 for rework; comment out for total count
	
		regular counts	= 0
		rework			= 2
		Total Count		= 999
*/



DECLARE @StartDate DATETIME
	, @EndDate DATETIME
	, @CountType INT
	, @Press INT

-- Change all values here
SET @StartDate = '2010-07-01'
SET @EndDate = '2010-08-31'
SET @CountType = 0
SET @Press = 52015


SELECT 
      YEAR(UpdateDate) as 'Year',
      DATENAME(mm,UpdateDate) as 'Month',
      SUM(LaborQuantity) as 'Total',
      COUNT(DISTINCT JobN) as 'Number of Jobs'
FROM
      JobLabor
WHERE
      UpdateDate BETWEEN @StartDate AND @EndDate
      AND LCCN = @Press
      AND (prefix = @CountType
		OR @CountType = '999')
GROUP BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)

Open in new window

SOLUTION
Avatar of aelliso3
aelliso3
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mjburgard

ASKER

Not quite what I'm looking for.  That would work but I'd really like to not have to change the @Press (LCCN) and have it loop four times and run all four of them in one run.

The prefix part is interesting though.  By doing that does it skip running that if @CountType is set to 999?
Ok so I tried the
     AND (prefix = @CountType
               OR @CountType NOT IN (0,2))
and it worked perfect.  So all I need now is to make it loop and cycle through the four LCCNs.  I know is programing I've done before I'd add all the LCCNs to a list and have a loop go through each part of the list, but I'm not sure how to do this in SQL or if that's even the best way.
At least that takes care of one part, but I'm a little confused about the loop part. If you are returning the same thing for each, then what's the loop for?
 
The code below will return the results from each of the LCCN's in the list (which I have set to all 4). Is this what's expected?
 
The only thing that's really changed is removing the @Press part and adding in:
 

AND LCCN IN (52015, 55215, 39010, 39100)  

/*
	@Count Value to change 0 for regular counts; 2 for rework; comment out for total count
	
		regular counts	= 0
		rework			= 2
*/



DECLARE @StartDate DATETIME
	, @EndDate DATETIME
	, @CountType INT
	, @Press INT

-- Change all values here
SET @StartDate = '2010-07-01'
SET @EndDate = '2010-08-31'
SET @CountType = 0
--SET @Press = 52015


SELECT 
      YEAR(UpdateDate) as 'Year',
      DATENAME(mm,UpdateDate) as 'Month',
      SUM(LaborQuantity) as 'Total',
      COUNT(DISTINCT JobN) as 'Number of Jobs'
FROM
      JobLabor
WHERE 
	UpdateDate BETWEEN @StartDate AND @EndDate 
	AND LCCN IN (52015, 55215, 39010, 39100) 
	AND (prefix = @CountType 
		OR @CountType NOT IN (0,2)) 
GROUP BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)

Open in new window

Sorry, I guess I'm having a hard time explaining it.  When its run as it is now (with just one LCCN number per run) the result is in a table form.

Year | Month   |    Total   | Number of Jobs
2010 | July      |    57848 |      74
2010 | August |  784215 |      58

Or what not, depending on the dates entered.  The table splits it by month, but the Total and Number of Jobs field are populated based on which LCCN is used.  If I copy the entire code from SELECT down and paste it below (so its running twice) but change the LCCN number in the second part, the output will be two separate tables with the results for the first and then the second LCCN using the same dates.  This is what I want for all four, except I would rather not just copy and paste the code four times, cause that would be a mess.  So I want it to just loop through the section of the code needed (probably from SELECT down but I'm not sure) four times basically, but each time use a different LCCN.  I'm sure if I knew the terms and such it would be easier to explain, but then it would also be pretty easy for me to do.  Some stuff I have read implied that a CASE statement would be used to accomplish this, but I really have no idea.  I hope I was able to clear up what I'm trying to do.  Thanks for all the help!
Also, I tried the code in your last comment, that outputs one table and just adds the Totals of all the LCCNs together with no way to tell how much was with each.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yep, that pretty much gives me what I want.  As I said, a loop was the only way I could think of to do it but I didn't know if it was the best way.  Thanks for the help, although I have one last question now (not a huge deal).  Is there a way to make the output display a string in the place of the LCCN?  Still have everything else the same, just instead of

55215 | 2010 | August | 8000 | 50

getting something like?

6Color | 2010 | August | 8000 | 50

Not a huge deal, the program does what it needs to right now, but it would make it simpler to understand.  Thanks again for the help!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did it.  Thanks for all the help, got what I needed and managed to learn a bit too.  Hopefully next time I'll be able to get a bit further on my own.  Thanks again.