• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Running an If Else within a SQL Statement.

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
TCCIRM
Asked:
TCCIRM
  • 5
  • 4
  • 3
  • +1
1 Solution
 
tlaytonCommented:
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
 
TCCIRMAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
tlaytonCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
hnasrCommented:
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
 
TCCIRMAuthor Commented:
Both statements work great!  Not sure how yet, but worth investigating further.....
0
 
TCCIRMAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
tlaytonCommented:
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
 
tlaytonCommented:
Then to call for 2012 you would use:
EXEC prGetWorkOrderByYear(12, '2012')

Open in new window

0
 
TCCIRMAuthor Commented:
Good stuff.  Thank you for your expertise.  A lot to wrap my head around....
0
 
tlaytonCommented:
You're welcome, good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now