Eliminating Rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Eliminating Rows

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-17 : 13:52:51

Is there a way to eliminate Rows from my output that have both counts - Count(*)= 0
Here is my code:

SELECT AgencyId as AgencyNumber, CustName as AgencyName,CustType as AgencyType,

(
Select Count(*) From LLA_Audits t2
Where t2.AgencyID= t1.AgencyID
And IsOpenAllegation =1
) As CountAllegations,
(
Select Count(*) From LLA_Audits t12
Where t12.AgencyID= t1.AgencyID
And t12.WorkType = 'Agreed-upon Procedures'
) As CountAgreedUpon
FROM LLA_Audits t1
inner join Customers on custcode=agencyID
WHERE (Grade = 'D' and PeriodFinish >= DateAdd(yy, -3,getdate()))
OR (Other= -1 and OtherReason='High Profile' and ReportReceivedDate >= DateAdd(yy, -3,getdate()))
OR EXISTS
(
Select *
From LLA_Audits t12
Join LLA_Audits t13
ON t12.AgencyID = t13.AgencyID
Where t12.AgencyID = t1.AgencyID
And t12.ReportReceivedDate > t12.ReportDueDate
And t13.ReportReceivedDate > t13.ReportDueDate
And year(t12.ReportDueDate) = year(t1.ReportDueDate) + 1
And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
)
Group by AgencyId, CustName,CustType
Order by AgencyNumber

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-17 : 14:28:29

Try this:


WITH CTE AS
(
   SELECT AgencyId as AgencyNumber, CustName as AgencyName,CustType as AgencyType,
   (
   Select Count(*) From LLA_Audits t2 
   Where t2.AgencyID= t1.AgencyID
   And IsOpenAllegation =1
   ) As CountAllegations,
   (
   Select Count(*) From LLA_Audits t12 
   Where t12.AgencyID= t1.AgencyID
   And t12.WorkType = 'Agreed-upon Procedures'
   ) As CountAgreedUpon
   FROM LLA_Audits t1
   inner join Customers on custcode=agencyID
   WHERE (Grade = 'D' and PeriodFinish >= DateAdd(yy, -3,getdate())) 
   OR (Other= -1 and OtherReason='High Profile' and ReportReceivedDate >= DateAdd(yy, -3,getdate())) 
   OR EXISTS
   (
   Select *
   From LLA_Audits t12
   Join LLA_Audits t13 
   ON t12.AgencyID = t13.AgencyID
   Where t12.AgencyID = t1.AgencyID
   And t12.ReportReceivedDate > t12.ReportDueDate
   And t13.ReportReceivedDate > t13.ReportDueDate
   And year(t12.ReportDueDate) = year(t1.ReportDueDate) + 1 
   And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
   )
   Group by AgencyId, CustName,CustType
)
SELECT T.AgencyNumber, T.AgencyName, T.AgencyType, T.CountAllegations, T.CountAgreedUpon
  FROM CTE AS T
 WHERE T.CountAllegations <> 0
    OR T.CountAgreedUpon <> 0
 ORDER BY T.AgencyNumber

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-17 : 15:52:56

For some reason, Line 1 produced this error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-17 : 16:13:53

I'm running on
8.00.0194 SQL Server 2000 RTM

Could this be my problem?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-17 : 16:22:28

Try this instead:


SELECT T.AgencyNumber, T.AgencyName, T.AgencyType, T.CountAllegations, T.CountAgreedUpon
  FROM (SELECT AgencyId as AgencyNumber, CustName as AgencyName,CustType as AgencyType,
        (
        Select Count(*) From LLA_Audits t2 
        Where t2.AgencyID= t1.AgencyID
        And IsOpenAllegation =1
        ) As CountAllegations,
        (
        Select Count(*) From LLA_Audits t12 
        Where t12.AgencyID= t1.AgencyID
        And t12.WorkType = 'Agreed-upon Procedures'
        ) As CountAgreedUpon
        FROM LLA_Audits t1
        inner join Customers on custcode=agencyID
        WHERE (Grade = 'D' and PeriodFinish >= DateAdd(yy, -3,getdate())) 
        OR (Other= -1 and OtherReason='High Profile' and ReportReceivedDate >= DateAdd(yy, -3,getdate())) 
        OR EXISTS
        (
        Select *
        From LLA_Audits t12
        Join LLA_Audits t13 
        ON t12.AgencyID = t13.AgencyID
        Where t12.AgencyID = t1.AgencyID
        And t12.ReportReceivedDate > t12.ReportDueDate
        And t13.ReportReceivedDate > t13.ReportDueDate
        And year(t12.ReportDueDate) = year(t1.ReportDueDate) + 1 
        And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
        )
        Group by AgencyId, CustName,CustType) AS T
 WHERE T.CountAllegations <> 0
    OR T.CountAgreedUpon <> 0
 ORDER BY T.AgencyNumber

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-17 : 16:35:17

Thanks... Worked like a charm...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-05-17 : 23:09:42

try try luck


SELECT
	[AgencyNumber]	=	AgencyId
,	[AgencyName]	=	CustName
,	[AgencyType]	=	CustType
,	[CountAllegations]
,	[CountAgreedUpon]

FROM	LLA_Audits t1
		join Customers
		on custcode = agencyID

CROSS APPLY 
(SELECT	
	[CountAllegations]	=	SUM(CASE IsOpenAllegation WHEN 1 THEN 1 ELSE 0 END)
,	[CountAgreedUpon]	=	SUM(CASE WorkType WHEN 'Agreed-upon Procedures' THEN 1 ELSE 0 END)
FROM	LLA_Audits t2
WHERE	t1.AgencyID = t2.AgencyID
		AND (t2.IsOpenAllegation = 1 OR t2.WorkType = 'Agreed-upon Procedures'))t2
		
WHERE	(Grade = 'D' and PeriodFinish >= DateAdd(yy, -3,getdate()))
		OR (Other = -1 and OtherReason = 'High Profile' and ReportReceivedDate >= DateAdd(yy, -3,getdate()))
		OR EXISTS
			(Select *
			From LLA_Audits t12
			Join LLA_Audits t13
			ON t12.AgencyID = t13.AgencyID
			Where t12.AgencyID = t1.AgencyID
			And t12.ReportReceivedDate > t12.ReportDueDate
			And t13.ReportReceivedDate > t13.ReportDueDate
			And year(t12.ReportDueDate) = year(t1.ReportDueDate) + 1
			And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
			)
		AND([CountAllegations] <> 0 OR [CountAgreedUpon] <> 0)

Group by AgencyId, CustName,CustType
Order by AgencyNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources