| Author |
 Topic  |
|
|
cordeparker
Starting Member
USA
5 Posts |
 Posted - 07/16/2012 : 13:25:08
|
I've been having an issue with this and can't figure it out. This is the SQL select statement
SELECT ReorderDirections.FacID, Reorders.PatID,
Reorders.LastRxNo, Patients.PatLName,
Patients.PatFName, Reorders.DrugLabelName, ReorderDirections.Sig, ReordersAdminTimes.AdminTime
FROM Pat.dbo.Patients Patients, Pat.dbo.ReorderDirections ReorderDirections,
Pat.dbo.Reorders Reorders, Pat.dbo.ReordersAdminTimes ReordersAdminTimes
WHERE ReorderDirections.FacID = Patients.FacID AND ReorderDirections.PatID = Patients.PatID
AND Reorders.FacID = Patients.FacID AND Reorders.FacID = ReorderDirections.FacID
AND Reorders.PatID = Patients.PatID AND Reorders.PatID = ReorderDirections.PatID
AND Reorders.RoNo = ReorderDirections.RoNo AND ReordersAdminTimes.FacID = Patients.FacID
AND ReordersAdminTimes.FacID = ReorderDirections.FacID AND ReordersAdminTimes.FacID = Reorders.FacID
AND ReordersAdminTimes.PatID = Patients.PatID AND ReordersAdminTimes.PatID = ReorderDirections.PatID
AND ReordersAdminTimes.PatID = Reorders.PatID AND ReordersAdminTimes.RoNo = ReorderDirections.RoNo
AND ReordersAdminTimes.RoNo = Reorders.RoNo AND ReordersAdminTimes.Split = ReorderDirections.Split
AND ((Patients.PatStatus='A') AND (Reorders.CutoffDt Is Null))
It come out into excel like this
but I want the ReordersAdminTimes.AdminTime to be on the same line where the Reorders.LastRxNo is the same. So it will look like this.
Any ideas? I tried posting on an excel forum, and they suggested having them combined using a SQL script instead. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/16/2012 : 22:13:44
|
SELECT ReorderDirections.FacID, Reorders.PatID,
Reorders.LastRxNo, Patients.PatLName,
Patients.PatFName, Reorders.DrugLabelName, ReorderDirections.Sig,
STUFF((SELECT ',' + AdminTime FROM Pat.dbo.ReordersAdminTimes ReordersAdminTimes
WHERE ReordersAdminTimes.FacID = Patients.FacID
AND ReordersAdminTimes.FacID = ReorderDirections.FacID AND ReordersAdminTimes.FacID = Reorders.FacID
AND ReordersAdminTimes.PatID = Patients.PatID AND ReordersAdminTimes.PatID = ReorderDirections.PatID
AND ReordersAdminTimes.PatID = Reorders.PatID AND ReordersAdminTimes.RoNo = ReorderDirections.RoNo
AND ReordersAdminTimes.RoNo = Reorders.RoNo AND ReordersAdminTimes.Split = ReorderDirections.Split
ORDER BY AdminTime
FOR XML PATH('')),1,1,'') AS AdminTime
FROM Pat.dbo.Patients Patients, Pat.dbo.ReorderDirections ReorderDirections,
Pat.dbo.Reorders Reorders
WHERE ReorderDirections.FacID = Patients.FacID AND ReorderDirections.PatID = Patients.PatID
AND Reorders.FacID = Patients.FacID AND Reorders.FacID = ReorderDirections.FacID
AND Reorders.PatID = Patients.PatID AND Reorders.PatID = ReorderDirections.PatID
AND Reorders.RoNo = ReorderDirections.RoNo
AND ((Patients.PatStatus='A') AND (Reorders.CutoffDt Is Null))
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
cordeparker
Starting Member
USA
5 Posts |
Posted - 07/17/2012 : 14:14:35
|
| Wow, that is exactly what I needed. Thanks a lot |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/17/2012 : 14:43:17
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cordeparker
Starting Member
USA
5 Posts |
Posted - 07/17/2012 : 15:31:08
|
how about this one....same concept but with the ReorderDirections.Sig column needing to be combined with a ";" when the Reorders.LastRxNo are the same.
SELECT Patients.FacID, Patients.NsID, Reorders.LastRxNo, Patients.PatLName, Patients.PatFName, Reorders.DrugLabelName, ReorderDirections.Sig, KeyIdentifiers.DeaClass, Reorders.DateWritten, Reorders.PhRxExpireDate, Reorders.PhName, Reorders.TotalQtyWritten, Reorders.TotalQtyDispensed
FROM Drug.dbo.KeyIdentifiers KeyIdentifiers, Pat.dbo.Patients Patients, Pat.dbo.ReorderDirections ReorderDirections, Pat.dbo.Reorders Reorders
WHERE Reorders.FacID = Patients.FacID AND Reorders.PatID = Patients.PatID AND ReorderDirections.FacID = Patients.FacID AND ReorderDirections.FacID = Reorders.FacID AND ReorderDirections.PatID = Patients.PatID AND ReorderDirections.PatID = Reorders.PatID AND ReorderDirections.RoNo = Reorders.RoNo AND KeyIdentifiers.NDC = Reorders.NDC AND ((KeyIdentifiers.DeaClass<>'') AND (Patients.PatStatus='A') AND (Reorders.CutoffDt Is Null))
I can post a picture if you need |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/17/2012 : 22:59:04
|
would be better if you can post a sample
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cordeparker
Starting Member
USA
5 Posts |
Posted - 07/18/2012 : 13:28:15
|
This is what it looks like
and this is what I want it to look like
Notice the ones I highlighted. Same thing. When the LastRXNo Match, merge the cells and combine the Sig with a ";"
Thanks again Vishkh, you've been a lot of help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/18/2012 : 16:46:43
|
its same way
;With TempTable
AS
(
SELECT columns...,
ROW_NUMBER() OVER (PARTITION BY FacID,NsID,LastRxNo ORDER BY Sig) AS Rn
FROM Table
)
SELECT all other columns...,
STUFF((SELECT ';' FROM TempTable WHERE facID = t.FacID AND NsID = t.NsID AND LastRxNo = t.LastrxNo AND Rn >1 FOR XML PATH('')),1,1,'') AS Sig
FROM TempTable t
WHERE Rn=1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cordeparker
Starting Member
USA
5 Posts |
Posted - 07/26/2012 : 13:14:08
|
| Sorry, I'm still having trouble. I'm trying to learn |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/26/2012 : 14:20:07
|
quote:
Originally posted by cordeparker
Sorry, I'm still having trouble. I'm trying to learn
can you elaborate on what issue you're facing with some data. Without that we wont be able to understand issue as we cant access your tables or see your s/m
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Merging Columns to one line to export to Excel
