Display records where column equals False? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Display records where column equals False?

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-17 : 17:08:58

So there is a table that keeps track of what questions have been asked during an audit. These questions have clauses associated with them and the clauses are not always covered during the audit. I need a query that will allow me to see what Group has clauses that are not covered during the year. The only challenge is that I can't simply use a WHERE clause because there may be records where the clause is not covered in a previous audit, but then covered in the next audit.

Looking at the sample data will make it much easier to understand.

Here's my table:


	CREATE TABLE jntbl_AuditToClauses
	(
	Audit_ID int,
	Clause_ID smallint,
	Group_ID smallint,
	Question_ID smallint,
	Covered bit,
	Scheduled_Year smallint,
	Primary key Clustered (Audit_ID)
	)

Sample of what's in the table right now:


+----------+----------+---------+------------+--------+-----+
| Audit_Id | Clause_ID| Group_ID| Question_ID| Covered| Year|
+----------+----------+---------+------------+--------+-----+
| 1        | 1        | 10      | 12         | 0      | 2001
| 2        | 1        | 10      | 13         | 1      | 2001
| 3        | 1        | 10      | 14         | 0      | 2001
| 4        | 2        | 10      | 15         | 0      | 2001
| 5        | 2        | 10      | 12         | 1      | 2001
| 6        | 3        | 10      | 17         | 0      | 2001
+----------+----------+---------+------------+--------+-----+

What I'd like to see in a View:


+----------+---------+--------+-----+
| Clause_ID| Group_ID| Covered| Year|
+----------+---------+--------+-----+
| 3        | 10      | 0      | 2001
+----------+---------+--------+-----+

The above record should only be displayed because Clause 3 for Group 10 has not been covered for the year 2001 yet.

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 18:37:46

This maybe?


DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(
	Audit_ID int,
	Clause_ID smallint,
	Group_ID smallint,
	Question_ID smallint,
	Covered bit,
	Scheduled_Year smallint,
	Primary key Clustered (Audit_ID)
)

INSERT INTO #TEMP
SELECT	1, 1, 10, 12, 0, 2001 UNION ALL
SELECT	2, 1, 10, 13, 1, 2001 UNION ALL
SELECT	3, 1, 10, 14, 0, 2001 UNION ALL
SELECT	4, 2, 10, 15, 0, 2001 UNION ALL
SELECT	5, 2, 10, 12, 1, 2001 UNION ALL
SELECT	6, 3, 10, 17, 0, 2001

SELECT	*
FROM	#TEMP AS T1
WHERE	[Covered] = 0
	AND NOT EXISTS
	(
		SELECT	*
		FROM	#TEMP AS T2
		WHERE	    T2.[Covered] = 1
			AND T2.Clause_ID = T1.Clause_ID
			AND T2.Group_ID = T1.Group_ID
			and T2.Scheduled_Year = T1.Scheduled_Year
	)
DROP TABLE #TEMP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 04:24:21

[code]select t.*
FROM table t
inner join (select Clause_ID
from Table
and Year=@year
GROUP BY Clause_ID
HAVING SUM(CASE WHEN Covered=1 THEN 1 ELSE 0 END)=0
)t1
ON t1.Clause_ID = t.Clause_ID
AND t.Year=@Year
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources