Solved

SQL Loop, If/Else, Case Statements

Posted on 2010-09-16
11
546 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:mjburgard
  • 6
  • 5
11 Comments
 
LVL 11

Expert Comment

by:aelliso3
ID: 33694315
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

0
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 500 total points
ID: 33694378
sorry ... I just ready the part about returning all rows if the @Count Type was not a 0 or 2. You can replace the where clause above with the following:

WHERE
     UpdateDate BETWEEN @StartDate AND @EndDate
     AND LCCN = @Press
     AND (prefix = @CountType
               OR @CountType NOT IN (0,2))
0
 
LVL 1

Author Comment

by:mjburgard
ID: 33694410
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:mjburgard
ID: 33694528
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.
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33694892
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

0
 
LVL 1

Author Comment

by:mjburgard
ID: 33695016
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!
0
 
LVL 1

Author Comment

by:mjburgard
ID: 33695029
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.
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33695351
OK, I think I get it, but it sounds like it's just another grouping ...
This will give you something like:

LCCN   | Year    | Month   | Total    | Number of Jobs
52015   | 2010   | July       | 1000    | 74
52015   | 2010   | August | 5000    | 58
55215   | 2010   | July       | 2000    | 74
55215   | 2010   | August | 1000    | 62
39010   | 2010   | July       | 3000    | 11
39010   | 2010   | August | 4000    | 500
39100   | 2010   | July       | 8000    | 123
39100   | 2010   | August | 9000    | 234
 

/*
	@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 
      LCCN,
      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
      LCCN,
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      LCCN,
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)

Open in new window

0
 
LVL 1

Author Comment

by:mjburgard
ID: 33696186
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!
0
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 500 total points
ID: 33697117
There just so happens to be and it references something that you spoke of earlier ... the CASE statement.
 

/*
	@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


SELECT 
       Press = CASE WHEN LCCN = 52015 THEN '6/c Press'
			WHEN LCCN = 55215 THEN 'PM'
			WHEN LCCN = 39010 THEN 'M700'
			WHEN LCCN = 39100 THEN 'DE'
		END,
      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
      CASE WHEN LCCN = 52015 THEN '6/c Press'
			WHEN LCCN = 55215 THEN 'PM'
			WHEN LCCN = 39010 THEN 'M700'
			WHEN LCCN = 39100 THEN 'DE'
		END,
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      LCCN,
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)

Open in new window

0
 
LVL 1

Author Comment

by:mjburgard
ID: 33697232
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.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question