mjburgard
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
The prefix part is interesting though. By doing that does it skip running that if @CountType is set to 999?
ASKER
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.
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)
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)
ASKER
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!
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!
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Open in new window