[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Loop, If/Else, Case Statements

Posted on 2010-09-16
11
Medium Priority
?
555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

650 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