Solved

SQL Query - Case and Union

Posted on 2014-01-08
8
390 Views
Last Modified: 2014-01-09
I am building a query from SQL database tables for a report on my webpage - I have my datagrid populating but the counts are incorrect.  Please check to see if I have the query correct.  This is my query:

l_strsql = ";WITH  MyCounts  AS (
                 SELECT  
                   CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))
                    THEN 1  ELSE 0 END AS NV,
                    CASE WHEN (a = 'Bad' AND b = 'Bad' AND c = 'Bad' ) THEN 1 ELSE 0  END AS Neg,
                    CASE WHEN (a = 'Good' AND b = 'Good' AND c = 'Good' )THEN 1 ELSE 0  END as Pos,
                    CASE WHEN (a = 'Neutral' AND b = 'Neutral' AND c = 'Neutral' ) THEN 1 ELSE 0  END as Neut, 'Table 1' AS TableName, Status
                  FROM tbl_1            WHERE (Status='Done')
                 UNION SELECT  
                   CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))
                    THEN 1  ELSE 0 END AS NV,
                    CASE WHEN (a = 'Bad' AND b = 'Bad' AND c = 'Bad' ) THEN 1 ELSE 0  END AS Neg,
                   CASE WHEN (a = 'Good' AND b = 'Good' AND c = 'Good' )THEN 1 ELSE 0  END as Pos,
                   CASE WHEN (a = 'Neutral' AND b = 'Neutral' AND c = 'Neutral' ) THEN 1 ELSE 0  END as Neut, 'Table 2' AS TableName, Status
                  FROM tbl_2    WHERE (Status='Done')
                  UNION SELECT  
                   CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))  THEN 1  ELSE 0 END AS NV,
                   CASE WHEN (a = 'Bad' AND b = 'Bad' AND c = 'Bad' ) THEN 1 ELSE 0  END AS Neg,
                   CASE WHEN (a = 'Good' AND b = 'Good' AND c = 'Good' )THEN 1 ELSE 0  END as Pos,
                    CASE WHEN (a = 'Neutral' AND b = 'Neutral' AND c = 'Neutral' ) THEN 1 ELSE 0  END as Neut, 'Table 3' AS TableName, Status             FROM tbl_3
                  WHERE (Status='Done')
                  )
                  SELECT SUM(NV) AS NotViewed, SUM(Neg) AS Negative, SUM(Good) AS Positive, SUM(Neut) AS Neutral, TableName FROM MyCounts GROUP BY TableName, Status;";
                 

Correct output should be:

TableName       NotViewed     Negative     Positive    Neutral
Table 1                  3                   2               4              1
Table 2                  0                   1               3              3
Table 3                  1                   1               2              1


My output is showing:

TableName       NotViewed     Negative     Positive    Neutral
Table 1                  1                   0               0             0
Table 2                  1                   0               0              0
Table 3                  1                   0               0             0
0
Comment
Question by:ptslv
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39766395
<wild knee-jerk reactions>

UNION eliminates duplicates, UNION ALL does not.

>CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))
CASE WHEN ISNULL(a, '') = '' AND ISNULL(b, '') = '' AND ISNULL(c, '')  = ''
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39766399
can you also provide the sample data of table1,2,3
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39766401
Without sample data for tbl_1, tbl_2, tbl_3, it is hard to determine if there is an error in your SQL.

If you run this in SSMS you are getting the same invalid output?
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 39766412
I think some of your field names may be inconsistant.

NV,NEG,POS, NEUT or NV,NEG,GOOD,NEUT

Can you repost using a code block?
Can you post some sample code to create the tables and insert some sample data?



SELECT CASE 
		WHEN (
				(
					a IS NULL
					OR a = ''
					)
				AND (
					b IS NULL
					OR b = ''
					)
				AND (
					c IS NULL
					OR c = ''
					)
				)
			THEN 1
		ELSE 0
		END AS NV
	,CASE 
		WHEN (
				a = 'Bad'
				AND b = 'Bad'
				AND c = 'Bad'
				)
			THEN 1
		ELSE 0
		END AS Neg
	,CASE 
		WHEN (
				a = 'Good'
				AND b = 'Good'
				AND c = 'Good'
				)
			THEN 1
		ELSE 0
		END AS Pos
	,CASE 
		WHEN (
				a = 'Neutral'
				AND b = 'Neutral'
				AND c = 'Neutral'
				)
			THEN 1
		ELSE 0
		END AS Neut
	,'Table 1' AS TableName
	,STATUS
FROM tbl_1
WHERE (STATUS = 'Done')

UNION

SELECT CASE 
		WHEN (
				(
					a IS NULL
					OR a = ''
					)
				AND (
					b IS NULL
					OR b = ''
					)
				AND (
					c IS NULL
					OR c = ''
					)
				)
			THEN 1
		ELSE 0
		END AS NV
	,CASE 
		WHEN (
				a = 'Bad'
				AND b = 'Bad'
				AND c = 'Bad'
				)
			THEN 1
		ELSE 0
		END AS Neg
	,CASE 
		WHEN (
				a = 'Good'
				AND b = 'Good'
				AND c = 'Good'
				)
			THEN 1
		ELSE 0
		END AS Pos
	,CASE 
		WHEN (
				a = 'Neutral'
				AND b = 'Neutral'
				AND c = 'Neutral'
				)
			THEN 1
		ELSE 0
		END AS Neut
	,'Table 2' AS TableName
	,STATUS
FROM tbl_2
WHERE (STATUS = 'Done')

UNION

SELECT CASE 
		WHEN (
				(
					a IS NULL
					OR a = ''
					)
				AND (
					b IS NULL
					OR b = ''
					)
				AND (
					c IS NULL
					OR c = ''
					)
				)
			THEN 1
		ELSE 0
		END AS NV
	,CASE 
		WHEN (
				a = 'Bad'
				AND b = 'Bad'
				AND c = 'Bad'
				)
			THEN 1
		ELSE 0
		END AS Neg
	,CASE 
		WHEN (
				a = 'Good'
				AND b = 'Good'
				AND c = 'Good'
				)
			THEN 1
		ELSE 0
		END AS Pos
	,CASE 
		WHEN (
				a = 'Neutral'
				AND b = 'Neutral'
				AND c = 'Neutral'
				)
			THEN 1
		ELSE 0
		END AS Neut
	,'Table 3' AS TableName
	,STATUS
FROM tbl_3
WHERE (STATUS = 'Done')

SELECT SUM(NV) AS NotViewed
	,SUM(Neg) AS Negative
	,SUM(Good) AS Positive
	,SUM(Neut) AS Neutral
	,TableName
FROM MyCounts
GROUP BY TableName
	,STATUS

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:ptslv
ID: 39766461
Jim, I changed UNION to UNION ALL, but did not make any other changes; now I am getting correct counts in the NotViewed column, but am still getting errors in the others.  

Shaun, when I run the query in SSMS, I do not get any errors in syntax.  

Jeffld, I will relook at my fields to see if they are consistent.

In my table records, I have groups (a, b, and c) who are rating the overall information of the record.  If they all rate the same accross the board, then I want a 1 added to the count for the table.  If the overall rating varies, then the record does not count.  That is the way my customer wants to view this particular report.
0
 

Author Closing Comment

by:ptslv
ID: 39766621
Thank you, everyone, for your input.  I made all the changes that Jim suggested, and my query worked.  My numbers were wrong because the data itself was not consistent - I was looking at an incorrect field.  Changing up the CASE for NULL cleaned my query up a bit.  Thank you again for all your help!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39766714
btw I wrote an article on SQL Server CASE solutions if that helps.

Thanks for the grade.  Good luck with your project.  -Jim
0
 

Author Comment

by:ptslv
ID: 39769139
Thank you, Jim.  I read your article.  Very informative.  Unfortunately, our company has not been authorized to use SQL 2012 yet.  I will keep your article in my SQL tool box for reference.  Have a good day!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

912 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

19 Experts available now in Live!

Get 1:1 Help Now