Is there a way to subtract one rowset from another

Hi - I have a query that returns a reulst set:
AcctNumber tdactdatetimenurpcwork
V04356634      2010-08-02 09:17:00.000
V04356634      2010-08-02 09:32:00.000
V04356634      2010-08-02 09:47:00.000
V04356634      2010-08-02 10:05:00.000
V04356634      2010-08-02 10:20:00.000
V04356634      2010-08-02 10:50:00.000
V04356634      2010-08-02 11:20:00.000
V04356634      2010-08-02 11:50:00.000
V04356634      2010-08-02 12:20:00.000

There are no unique idetifiers in the table other than the difference in time.  I need to know the difference in time between each row.  Any ideas?  I am writing in Visual Studio 2005.
SELECT    acctnumber,tdactdatetimenurpcwork
FROM         Picis.dbo.[Cases by Day] AS p LEFT OUTER JOIN
                          (SELECT     UrnADMPAT, AcctNumber, Location, Name, Status, CASE WHEN servicedate IS NULL 
                                                   THEN admitdate ELSE servicedate END AS AdmitDate
                            FROM          AdmPatientFile 
                            WHERE      (Status LIKE '%sdc%')) AS a ON a.AcctNumber = p.crp_acct_nbr LEFT OUTER JOIN
                          (SELECT     NURPCWORKDocumentedResults.PatientNURPCWORK, NURPCWORKDocumentedResults.IntBaseNURPCWORK, 
                                                   NURPCWORKDocumentedResults.IntUrnNURPCWORK, NURPCWORKDocumentedResults.TdActDateNURPCWORK, 
                                                   NURPCWORKDocumentedResults.TdActTimeNURPCWORK, NURPCWORKDocumentedResults.TdActCtrNURPCWORK, 
                                                   NURPCWORKDocumentedResults.TdQueryNURPCWORK, NURPCWORKDocumentedResults.TdValue, 
                                                   NURPCWORKDocumentedResults.tdactdatetimenurpcwork
                            FROM          NURPCWORKDocumentedResults INNER JOIN
                                                   NURPCWORKIntDocumentedActivity ON 
                                                   NURPCWORKDocumentedResults.PatientNURPCWORK = NURPCWORKIntDocumentedActivity.PatientNURPCWORK
                            WHERE      
                                                   (NURPCWORKDocumentedResults.IntBaseNURPCWORK = '25030')) AS n ON 
                      n.PatientNURPCWORK = a.UrnADMPAT
WHERE     (a.Location = 'ambsurg') AND ACCTNUMBER = 'V04356634'
group by acctnumber,tdactdatetimenurpcwork
ORDER BY TDACTDATETIMENURPCWORK

Open in new window

palmer9Asked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
sorry like this
;with CTE as (
SELECT    acctnumber,tdactdatetimenurpcwork, row_number() over (order by tdactdatetimenurpcwork) rn
FROM         Picis.dbo.[Cases by Day] AS p LEFT OUTER JOIN
                          (SELECT     UrnADMPAT, AcctNumber, Location, Name, Status, CASE WHEN servicedate IS NULL 
                                                   THEN admitdate ELSE servicedate END AS AdmitDate
                            FROM          AdmPatientFile 
                            WHERE      (Status LIKE '%sdc%')) AS a ON a.AcctNumber = p.crp_acct_nbr LEFT OUTER JOIN
                          (SELECT     NURPCWORKDocumentedResults.PatientNURPCWORK, NURPCWORKDocumentedResults.IntBaseNURPCWORK, 
                                                   NURPCWORKDocumentedResults.IntUrnNURPCWORK, NURPCWORKDocumentedResults.TdActDateNURPCWORK, 
                                                   NURPCWORKDocumentedResults.TdActTimeNURPCWORK, NURPCWORKDocumentedResults.TdActCtrNURPCWORK, 
                                                   NURPCWORKDocumentedResults.TdQueryNURPCWORK, NURPCWORKDocumentedResults.TdValue, 
                                                   NURPCWORKDocumentedResults.tdactdatetimenurpcwork
                            FROM          NURPCWORKDocumentedResults INNER JOIN
                                                   NURPCWORKIntDocumentedActivity ON 
                                                   NURPCWORKDocumentedResults.PatientNURPCWORK = NURPCWORKIntDocumentedActivity.PatientNURPCWORK
                            WHERE      
                                                   (NURPCWORKDocumentedResults.IntBaseNURPCWORK = '25030')) AS n ON 
                      n.PatientNURPCWORK = a.UrnADMPAT
WHERE     (a.Location = 'ambsurg') AND ACCTNUMBER = 'V04356634'
group by acctnumber,tdactdatetimenurpcwork
)
select a.acctnumber,a.tdactdatetimenurpcwork, b.tdactdatetimenurpcwork, datediff(minute, a.tdactdatetimenurpcwork, b.tdactdatetimenurpcwork) as difference
from CTE a
left join CTE b on a.rn + 1 = b.rn
ORDER BY TDACTDATETIMENURPCWORK

Open in new window

0
 
ralmadaCommented:
try
;with CTE as (
SELECT    acctnumber,tdactdatetimenurpcwork, row_number() over (order by tdactdatetimenurpcwork) rn
FROM         Picis.dbo.[Cases by Day] AS p LEFT OUTER JOIN
                          (SELECT     UrnADMPAT, AcctNumber, Location, Name, Status, CASE WHEN servicedate IS NULL 
                                                   THEN admitdate ELSE servicedate END AS AdmitDate
                            FROM          AdmPatientFile 
                            WHERE      (Status LIKE '%sdc%')) AS a ON a.AcctNumber = p.crp_acct_nbr LEFT OUTER JOIN
                          (SELECT     NURPCWORKDocumentedResults.PatientNURPCWORK, NURPCWORKDocumentedResults.IntBaseNURPCWORK, 
                                                   NURPCWORKDocumentedResults.IntUrnNURPCWORK, NURPCWORKDocumentedResults.TdActDateNURPCWORK, 
                                                   NURPCWORKDocumentedResults.TdActTimeNURPCWORK, NURPCWORKDocumentedResults.TdActCtrNURPCWORK, 
                                                   NURPCWORKDocumentedResults.TdQueryNURPCWORK, NURPCWORKDocumentedResults.TdValue, 
                                                   NURPCWORKDocumentedResults.tdactdatetimenurpcwork
                            FROM          NURPCWORKDocumentedResults INNER JOIN
                                                   NURPCWORKIntDocumentedActivity ON 
                                                   NURPCWORKDocumentedResults.PatientNURPCWORK = NURPCWORKIntDocumentedActivity.PatientNURPCWORK
                            WHERE      
                                                   (NURPCWORKDocumentedResults.IntBaseNURPCWORK = '25030')) AS n ON 
                      n.PatientNURPCWORK = a.UrnADMPAT
WHERE     (a.Location = 'ambsurg') AND ACCTNUMBER = 'V04356634'
group by acctnumber,tdactdatetimenurpcwork
)
select a.acctnumber,a.tdactdatetimenurpcwork, datediff(minute, a.tdactdatetimenurpcwork, b.tdactdatetimenurpcwork) as difference
from CTE a
left join CTE b on a.rn = b.rn + 1
ORDER BY TDACTDATETIMENURPCWORK

Open in new window

0
 
palmer9Author Commented:
Genius - Thank you SO MUCH!!!
0
All Courses

From novice to tech pro — start learning today.