Data Pull - 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

Data Pull

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-15 : 14:32:28

Can someone help with this? I have been playing around with this and having trouble. I need to pull info as follows:

I have a single table called Agencies and I am trying to pull these columns from it:

Select AgencyID, AgencyName,AgencyType, Count of all docs for this AgencyID where
isAllegation= 1, Count of all docs where WorkType field = "Agreed-upon Procedures"

This table contains these fields:
AgencyID
WorkType
Grade
ReportDueDate
ReportReceivedDate
Other
OtherReason
isAllegation
PeriodFinishDate

Selection of documents based on these OR conditions:
1. Grade = 'D' and PeriodFinishDate within last 3 years
OR
2. ReportReceivedDate > ReportDueDate for 3 consecutive years (use the year portion of ReportDueDate)
OR
3. Other= -1 and OtherReason="High Profile" and ReportDueDate within last 3 years
Thanks for any advice/assistance from all

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-15 : 14:44:59

Correction

Select AgencyID, AgencyName,AgencyType, Count of all docs for this AgencyID where
isAllegation= 1, Count of all docs for this AgencyID where WorkType field = "Agreed-upon Procedures"

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-15 : 16:13:44

Here is my code. I get this error:
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FROM'.

SELECT AgencyId, AgencyName, AgencyType,

(
Select Count(*) From LLA_Audits t2
Where t2.AgencyID= t1.AgencyID
And IsOpenAllegation =1
) As Count3yrDocs,
(
Select Count(*) From LLA_Audits t12
Where t12.AgencyID= t1.AgencyID
And t12.WorkType = 'Agreed-upon Procedures'
) As CountScsDocs,
FROM LLA_Audits t1
WHERE Grade = 'D'
OR (Other= -1 and OtherReason='High Profile')
OR EXISTS
(
Select *
From LLA_Audits t12
Join LLA_Audits t13
ON t12.AgencyID = t13.AgencyID
Where t12.AgencyID = t1.AgencyID
And year(t12.ReportDueDate) = year(y1.ReportDueDAte) + 1
And year(t13.ReportDueDate) = year(y1.ReportDueDate) + 2
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-15 : 17:07:00

There is an extra comma after "As CountScsDocs"

quote:
Originally posted by pgmr1998

Here is my code. I get this error:
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FROM'.

SELECT AgencyId, AgencyName, AgencyType,

(
Select Count(*) From LLA_Audits t2
Where t2.AgencyID= t1.AgencyID
And IsOpenAllegation =1
) As Count3yrDocs,
(
Select Count(*) From LLA_Audits t12
Where t12.AgencyID= t1.AgencyID
And t12.WorkType = 'Agreed-upon Procedures'
) As CountScsDocs,
FROM LLA_Audits t1
WHERE Grade = 'D'
OR (Other= -1 and OtherReason='High Profile')
OR EXISTS
(
Select *
From LLA_Audits t12
Join LLA_Audits t13
ON t12.AgencyID = t13.AgencyID
Where t12.AgencyID = t1.AgencyID
And year(t12.ReportDueDate) = year(y1.ReportDueDAte) + 1
And year(t13.ReportDueDate) = year(y1.ReportDueDate) + 2
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources