May 15, 2012 at 1:29 pm
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
isOpenAllegation= 1, Count of all docs for this AgencyID 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
May 15, 2012 at 1:32 pm
What is the question?
Jared
CE - Microsoft
May 15, 2012 at 1:36 pm
???
Select AgencyID, AgencyName,AgencyType, Count(docs)
from Agencies
where isOpenAllegation= 1 and WorkType = 'Agreed-upon Procedures'
group by AgencyID, AgencyName, AgencyType
???
From your brief description is appears that your data is not normalized. You should not have docs and Agencies in the same table. You have to maintain multiple rows of agency information when it is updated, etc. Instead you should have a docs table with a foreign key to the Agencies table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2012 at 1:40 pm
OP's request is to create a WHERE clause for these conditions...........
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
.............i beleive 😀
May 15, 2012 at 1:43 pm
By docs, I mean documents. There is only one table containing all my data.
May 15, 2012 at 1:48 pm
So...
SELECT AgencyID, AgencyName,AgencyType, COUNT(AgencyID)
FROM Agencies
WHERE (Grade = 'D' AND PeriodFinishDate > GETDATE() - (365 * 3))
OR
(YEAR(ReportReceivedDate) > YEAR(ReportDueDate) - 3)
OR
(Other= -1 AND OtherReason='High Profile' AND ReportDueDate > GETDATE() - (365 * 3))
GROUP BY AgencyID
That is a start, I think... However, I would change the GETDATE() - (365 * 3) to use a parameter that is calculated at the beginning of the query.
Jared
CE - Microsoft
May 15, 2012 at 2:16 pm
Here is my code, but i get an error that says:
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
)
May 15, 2012 at 2:24 pm
pgmr1998 (5/15/2012)
Here is my code, but i get an error that says: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, <-- ERROR IS HERE
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
)
You have a comma before the FROM
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2012 at 2:26 pm
Start with this:
;WITH docs AS (
Select AgencyID, Count(*) As Count3yrDocs
From LLA_Audits
Where IsOpenAllegation = 1
GROUP BY agencyID),
scsdocs AS (
Select AgencyID, Count(*) AS CountScsDocs
From LLA_Audits
Where WorkType = 'Agreed-upon Procedures'
GROUP BY AgencyID
)
SELECT a.AgencyId, a.AgencyName, a.AgencyType, d.Count3yrDocs, sd.CountScsDocs
FROM LLA_Audits a
LEFT JOIN docs d
ON a.AgencyID = d.AgencyId
LEFT JOIN scsdocs sd
ON a.AgencyID = sd.AgencyID
WHERE a.Grade = 'D'
OR (a.Other= -1 and a.OtherReason='High Profile')
GROUP BY a.agencyId, a.agencyname, a.agencytype
You will have to add in the part about the EXISTS. I don't think you are using that correctly and you still have not spelled out exactly what you are trying to do.
Jared
CE - Microsoft
May 15, 2012 at 2:42 pm
Here is my initial problem:
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
isOpenAllegation= 1, Count of all docs for this AgencyID 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
HERE is my Current code: It is pulling multiple entries for AgencyID, but I want a selected AgencyID to appear only once.
SELECT AgencyId, AgencyName,
(
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
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(t1.ReportDueDAte) + 1
And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
)
May 15, 2012 at 2:46 pm
I just altered the code from my previous post. Please try that.
This:
Select AgencyID, AgencyName,AgencyType, Count of all docs for this AgencyID where
isOpenAllegation= 1, Count of all docs for this AgencyID where WorkType field = "Agreed-upon Procedures"
is not a list of columns, so when you keep saying that it is, it is not telling me what you are looking for. Please explain this as it is half SQL and half just talking.
Jared
CE - Microsoft
May 15, 2012 at 2:46 pm
Group by would likely get you what you are looking for.
so what is this query doing?
Select *
From LLA_Audits t12
Join LLA_Audits t13
ON t12.AgencyID = t13.AgencyID
Where t12.AgencyID = t1.AgencyID
And year(t12.ReportDueDate) = year(t1.ReportDueDAte) + 1
And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2012 at 2:50 pm
Here is the updated code: Is there something wrong on the EXIST
clause?
SELECT AgencyId, AgencyName,
(
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
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 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
)
May 15, 2012 at 2:52 pm
OH!!! I got it now!
SELECT AgencyID, AgencyName,AgencyType,
SUM(CASE WHEN isOpenAllegation = 1 THEN 1
ELSE 0 END) AS countAllegations,
SUM(CASE WHEN WorkType field = 'Agreed-upon Procedures' THEN 1
ELSE 0 END) AS countAgreedUpon
FROM Agencies
GROUP BY AgencyID, AgencyName, AgencyType
Start there and then add in your WHERE clause.
Jared
CE - Microsoft
May 15, 2012 at 4:03 pm
I am getting duplicate output whenever an AgencyID meets more than one condition. How can I stop this?
Code is here:
SELECT AgencyId, AgencyName,
(
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
WHERE (Grade = 'D' and year(PeriodFinish) >= DateAdd(yy, -3,getdate()))
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 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
)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply