?
Solved

Running an If Else within a SQL Statement.

Posted on 2012-09-19
13
Medium Priority
?
333 Views
Last Modified: 2012-09-20
The following are run against SQL Server 2005

(1) This SQL Statement counts the total completed workorders for 2012:

SELECT     COUNT(dbo.Workorder.year_id) AS TotalCount
FROM         dbo.Admin INNER JOIN
                      dbo.Workorder ON dbo.Admin.wo_id = dbo.Workorder.wo_id
WHERE     (dbo.Workorder.year_id = '12') AND (dbo.Admin.status = 'Completed')

(2) This SQL Statement outputs the sum of all the rows for the completed workorders for  2012:

SELECT     dbo.Workorder.organ, dbo.Workorder.other, SUM(CASE WHEN year_id = '12' THEN 1 ELSE 0 END) AS [2012], dbo.Admin.status
FROM         dbo.Workorder INNER JOIN
                      dbo.Admin ON dbo.Workorder.wo_id = dbo.Admin.wo_id
WHERE     (dbo.Workorder.year_id = '12')
GROUP BY dbo.Workorder.organ, dbo.Workorder.other, dbo.Admin.status
HAVING      (NOT (dbo.Workorder.organ LIKE 'Other')) AND (dbo.Admin.status LIKE 'Completed')

Is it possible to write a single SQL statement that first checks to see if there are any completed workorders for 2012 (SQL 1) and if there are to run the sum for all the rows (SQL 2)?  Thanks
0
Comment
Question by:TCCIRM
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 5

Accepted Solution

by:
tlayton earned 2000 total points
ID: 38413830
You can do this in a Stored Proc and just use an IF statement?

IF (SELECT COUNT(dbo.Workorder.year_id)
FROM         dbo.Admin INNER JOIN
                      dbo.Workorder ON dbo.Admin.wo_id = dbo.Workorder.wo_id
WHERE     (dbo.Workorder.year_id = '12') AND (dbo.Admin.status = 'Completed')) > 0
	SELECT     dbo.Workorder.organ, dbo.Workorder.other, SUM(CASE WHEN year_id = '12' THEN 1 ELSE 0 END) AS [2012], dbo.Admin.status
	FROM         dbo.Workorder INNER JOIN
						  dbo.Admin ON dbo.Workorder.wo_id = dbo.Admin.wo_id
	WHERE     (dbo.Workorder.year_id = '12')
	GROUP BY dbo.Workorder.organ, dbo.Workorder.other, dbo.Admin.status
	HAVING      (NOT (dbo.Workorder.organ LIKE 'Other')) AND (dbo.Admin.status LIKE 'Completed')

Open in new window

0
 

Author Comment

by:TCCIRM
ID: 38413865
Very nice.  I certainly didn't get that far....   Before I butcher this sql, would you mind adding the year 2011 so it would check the same thing for both years?   This will help me wrap my head around this.  Thanks
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 38413869
Your HAVING clause is exaggerated for several reasons:
1. HAVING is used if you need to refer to the accumulated (sum'd, max'd, ...) data generated by the GROUP BY. All other cases should have conditions in WHERE only.
2. LIKE needs to be used only with patterns. For exact match, use = (or != resp. <>).

Next, you do not need to check for the count - the select will not return anything if the basic condition is not fulfilled anyway.
SELECT     dbo.Workorder.organ, dbo.Workorder.other, SUM(CASE WHEN year_id = '12' THEN 1 ELSE 0 END) AS [2012], dbo.Admin.status
FROM         dbo.Workorder INNER JOIN
                      dbo.Admin ON dbo.Workorder.wo_id = dbo.Admin.wo_id
WHERE     dbo.Workorder.year_id = '12'
AND  Workorder.organ != 'Other'
AND dbo.Admin.status = 'Completed'
GROUP BY dbo.Workorder.organ, dbo.Workorder.other, dbo.Admin.status

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Expert Comment

by:tlayton
ID: 38413880
Something like this?

IF (SELECT COUNT(dbo.Workorder.year_id)
FROM         dbo.Admin INNER JOIN
                      dbo.Workorder ON dbo.Admin.wo_id = dbo.Workorder.wo_id
WHERE     (dbo.Workorder.year_id = '11') AND (dbo.Admin.status = 'Completed')) > 0
	SELECT     dbo.Workorder.organ, dbo.Workorder.other, SUM(CASE WHEN year_id = '11' THEN 1 ELSE 0 END) AS [2011], dbo.Admin.status
	FROM         dbo.Workorder INNER JOIN
						  dbo.Admin ON dbo.Workorder.wo_id = dbo.Admin.wo_id
	WHERE     (dbo.Workorder.year_id = '11')
	GROUP BY dbo.Workorder.organ, dbo.Workorder.other, dbo.Admin.status
	HAVING      (NOT (dbo.Workorder.organ LIKE 'Other')) AND (dbo.Admin.status LIKE 'Completed')

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 38413882
For both 2011 and 2012:
SELECT     dbo.Workorder.organ, dbo.Workorder.other,
 SUM(CASE WHEN year_id = '12' THEN 1 ELSE 0 END) AS [2012],
 SUM(CASE WHEN year_id = '11' THEN 1 ELSE 0 END) AS [2011],
 dbo.Admin.status
FROM         dbo.Workorder INNER JOIN
                      dbo.Admin ON dbo.Workorder.wo_id = dbo.Admin.wo_id
WHERE     dbo.Workorder.year_id in ('11', '12')
AND  Workorder.organ != 'Other'
AND dbo.Admin.status = 'Completed'
GROUP BY dbo.Workorder.organ, dbo.Workorder.other, dbo.Admin.status, year_id

Open in new window

0
 
LVL 31

Expert Comment

by:hnasr
ID: 38413904
If problem not resolved:

List few records under their column headings,  and the required output.

One way to select from table where exists (select with criteria).
0
 

Author Comment

by:TCCIRM
ID: 38413906
Both statements work great!  Not sure how yet, but worth investigating further.....
0
 

Author Comment

by:TCCIRM
ID: 38413945
However, to Qlemo your sql "does" output years that do not have totals where tlayon's "does not."  I was looking to not output years without totals.  

To tlayton, to modify the sql to include multiple years within the same sql, it looks like I simply need change the Where clause.....
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 38413974
My solution of course shows counts where one year has no records, but the other - but should not show any counts if there is neither 2011 nor 2012 data.
0
 
LVL 5

Expert Comment

by:tlayton
ID: 38416821
You could take it one step further and add a YearID parameter (as well as a YearHeader for a dynamic SQL column name) in order to allow for all years, like so:

CREATE PROC prGetWorkOrderByYear
(
	@YearID int = 11,
	@YearHeader varchar = '2011'
)
AS
IF (SELECT COUNT(dbo.Workorder.year_id)
	FROM         dbo.Admin INNER JOIN
						  dbo.Workorder ON dbo.Admin.wo_id = dbo.Workorder.wo_id
	WHERE     (dbo.Workorder.year_id = @YearID) AND (dbo.Admin.status = 'Completed')) > 0
	BEGIN
		DECLARE @SQL varchar
		SELECT @SQL = 
		'SELECT     dbo.Workorder.organ, dbo.Workorder.other, SUM(CASE WHEN year_id = ' + @YearID + ' THEN 1 ELSE 0 END) AS ' + @YearHeader + ', dbo.Admin.status
		FROM         dbo.Workorder INNER JOIN
							  dbo.Admin ON dbo.Workorder.wo_id = dbo.Admin.wo_id
		WHERE     (dbo.Workorder.year_id = ' + @YearID + ')
		GROUP BY dbo.Workorder.organ, dbo.Workorder.other, dbo.Admin.status
		HAVING      (NOT (dbo.Workorder.organ LIKE ''Other'')) AND (dbo.Admin.status LIKE ''Completed'')'
		
		EXECUTE (@SQL)
	END

Open in new window

0
 
LVL 5

Expert Comment

by:tlayton
ID: 38416827
Then to call for 2012 you would use:
EXEC prGetWorkOrderByYear(12, '2012')

Open in new window

0
 

Author Comment

by:TCCIRM
ID: 38417247
Good stuff.  Thank you for your expertise.  A lot to wrap my head around....
0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417285
You're welcome, good luck!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

864 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