Solved

Is there a way to subtract one rowset from another

Posted on 2010-09-14
3
298 Views
Last Modified: 2012-05-10
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
Comment
Question by:palmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33674674
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
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33674693
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
 

Author Comment

by:palmer9
ID: 33674710
Genius - Thank you SO MUCH!!!
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question