SELECT 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)

SELECT HELP

Author

Topic

david13
Starting Member

2 Posts

Posted - 2009-10-06 : 17:27:01

I would like some help unlocking the last piece of a reporting puzzle I have been tasked with.

Based the on the table and data below here is what I am trying to get out. I want all of the distinct FacID/PatID/RoNo where the FacID/PatID/RoNo does not have a DocTypeName of "C SIGNED".

Within the results returned by that condition I want the distinct FacID/PatID/RoNo that have a DocTypeName of "C UNSIGNED" to return a status column "PENDING" and all of the distinct FacID/PatID/RoNo that do not have either DocTypeName listed above i want to output a status column "ATTENTION".

The FacID/PatID/RoNo is a foreign key to another table whose primary key contains all 3 of those columns. This table lists documents associated with those keys. Each key can have multiple documents associated with it and documents can be assigned to more than one key.

I have played around with HAVINGs, GROUPings, and some EXISTS statements, I could be making this more complicated than it is.

Based on critera the output of the SELECT statement on the data given below would be

ABC/5566/88261 - ATTENTION
EE/2597/567 - PENDING
TGIF/1065/5820 - ATTENTION
TGIF/1065/5821 - ATTENTION

CREATE TABLE #Temp (
FacID varchar(6),
PatID int,
RoNo int,
Document int,
DocTypeName varchar(20)
)
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('ABC','123','96754','675889','ORDERS')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('ABC','123','96754','675890','ORDERS')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('ABC','123','96754','675892','C UNSIGNED')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('ABC','123','96754','675894','C SIGNED')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('ABC','5566','88261',NULL,NULL)
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('EE','2597','567','562325','ORDERS')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('EE','2597','567','562325','C UNSIGNED')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('TGIF','1065','5820','675894','ORDERS')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('TGIF','1065','5821','675894','ORDERS')
INSERT INTO #Temp (FacID, PatID, RoNo,Document,DocTypeName) VALUES ('EE','74','854','45890','C SIGNED')

SELECT * FROM #Temp
DROP TABLE #Temp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-06 : 19:50:37

[code]
SELECT FacID, PatID, RoNo, DocTypeName,
coalesce(max(case when DocTypeName = 'C UNSIGNED' then 'PENDING' end), 'ATTENTION')
FROM #Temp t
WHERE NOT EXISTS
(
select *
from #Temp x
where x.FacID = t.FacID
and x.PatID = t.PatID
and x.RoNo = t.RoNo
and x.DocTypeName = 'C SIGNED'
)
group by FacID, PatID, RoNo, DocTypeName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

david13
Starting Member

2 Posts

Posted - 2009-10-07 : 09:14:52

Thanks! I have racked my brain on this. I slightly modified your response to get exactly the results I am expecting. The "coalesce(max(case when DocTypeName = 'C UNSIGNED' then 'PENDING' end), 'ATTENTION')" section is what solved this for me.

SELECT FacID, PatID, RoNo,
coalesce(max(case when DocTypeName = 'C UNSIGNED' then 'PENDING' end), 'ATTENTION')
FROM #Temp t
WHERE NOT EXISTS
(
select *
from #Temp x
where x.FacID = t.FacID
and x.PatID = t.PatID
and x.RoNo = t.RoNo
and x.DocTypeName = 'C SIGNED'
)
group by FacID, PatID, RoNo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-07 : 09:18:25

oh forgot to remove column DocTypeName from the SELECT & GROUP BY. Any way you got the solution.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources