query help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-11-05 : 13:05:46

Hello,

Sorry I have to remove my original query because of my company policy. Charlies and Peso: Would you do me a favor! please delete the code that I could not delete from your response. Sorry for this inconvenience.

Thanks,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-06 : 05:02:28

There's probably a much more elegant way of doing this but I don't know your schema at all and you haven't posted and sample table structures and data. This assumes that your select statement brings back correct data but you only want the most recent date for each caseID. In particular I find any posted query that uses SELECT DISTINCT to be a little suspect.

Anyway, how about this.....

Actually I think this might be so much horses**t

SELECT DISTINCT
A.ID_CASE
, MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT
, MAX(D.DT_ACTION) AS CLOSEDDATE
, datediff(dd, min(A.DT_ASSIGNMENT), MAX(D.dt_action)) as Age
, A.ID_ORG AS AGENCY, B.NM_ORG AS AGENCYNAME
, D.AM_CASE_TOTAL AS BALANCE
, a.id_employee_update
FROM
DBA.TF1CASDEH A

JOIN (
SELECT
[ID_CASE] AS [caseId]
, MAX([DT_ASSIGNMENT]) AS [latestAssign]
FROM
DBA.TF1CASDEH
GROUP BY
[ID_CASE]
)
la ON
la.[caseId] = A.[ID_CASE]
AND la.[latestAssign] = A.[DT_ASSIGNMENT]

LEFT JOIN DBA.TF1ORG AS B ON B.ID_ORG = A.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS C ON C.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1CASDET AS D ON D.ID_CASE = A.ID_CASE
WHERE
A.ID_ORG in ('1396', '1395','1397', '1393', '1394', '1398','1399')
AND A.ID_CASE IN (
SELECT a.ID_CASE
FROM DBA.TF1CASDEH a
WHERE
DT_ACTION >'2008-01-01'
AND ID_ORG not in ('1396', '1395','1397', '1393','1394', '1398','1399')
)
AND D.AM_CASE_TOTAL >0
AND C.AM_TOT_COLL_EFFECT IS NULL
AND A.DT_ASSIGNMENT<D.DT_ACTION
and D.dt_action>'2008-01-01'
and a.id_employee_update like 'R%'
GROUP BY
A.ID_CASE
, AGENCY, AGENCYNAME
, D.AM_CASE_TOTAL
, a.id_employee_update
ORDER BY
A.ID_CASE

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-06 : 05:03:23

NB :: Sorry duplicate post. Couldn't delete for some reason

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 05:13:21

[code]SELECT a.ID_CASE,
MIN(a.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
MAX(d.DT_ACTION) AS CLOSEDDATE,
DATEDIFF(DAY, MIN(a.DT_ASSIGNMENT), MAX(d.dt_action)) as Age,
a.ID_ORG AS AGENCY,
b.NM_ORG AS AGENCYNAME,
d.AM_CASE_TOTAL AS BALANCE,
a.id_employee_update
FROM DBA.TF1CASDEH AS a
LEFT JOIN DBA.TF1ORG AS b ON b.ID_ORG = a.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS c ON c.ID_CASE = a.ID_CASE
AND c.AM_TOT_COLL_EFFECT IS NULL
LEFT JOIN DBA.TF1CASDET AS d ON d.ID_CASE = a.ID_CASE
AND d.AM_CASE_TOTAL > 0
AND d.dt_action > '2008-01-01'
AND d.DT_ACTION > a.DT_ASSIGNMENT
WHERE a.ID_ORG BETWEEN '1393' AND '1399'
AND EXISTS (SELECT * FROM FROM DBA.TF1CASDEH AS x WHERE x.ID_CASE = a.ID_CASE AND x.DT_ACTION > '2008-01-01' AND x.ID_ORG NOT BETWEEN '1393' AND '1399')
AND a.id_employee_update LIKE 'R%'
GROUP BY a.ID_CASE,
a.ID_ORG,
b.NM_ORG,
d.AM_CASE_TOTAL,
a.id_employee_update
ORDER BY a.ID_CASE[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources