[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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

0
palmer9
Asked:
palmer9
  • 2
1 Solution
 
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
 
ralmadaCommented:
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
 
palmer9Author Commented:
Genius - Thank you SO MUCH!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now