Solved

SQL Loop, If/Else, Case Statements

Posted on 2010-09-16
11
535 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now