Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate days Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 1:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:42 AM
Points: 309, Visits: 585
I need help to code days. As the sample data below, days between [ADMDATE] and [DSCHDATE] need to be count as this way (at different rows):

MEMBID ADMDATE DSCHDATE
06000248 08/02/2010 08/04/2010
06000248 08/10/2010 08/12/2010 (08/10/2010 - 08/04/2010=6)
06000248 08/20/2010 08/22/2010 (08/20/2010 - 08/12/2012=8)

The final result is 6 and 8.
Post #1344980
Posted Tuesday, August 14, 2012 1:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:49 AM
Points: 92, Visits: 341
EDITED: Yeah, what Lynn said below. That's why I'm the Grasshopper and he's the coach
Post #1344993
Posted Tuesday, August 14, 2012 1:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 23,397, Visits: 32,240
Not difficult. One thing you need to do is to look carefully at my code below. You notice I am creating the table, populating it with sample data, and after my query I delete the table used for development. The first 2 items and the last you should provide instead of relying on us to do it for you. So happens, it is a slow day today but don't always count on that.


/*
MEMBID ADMDATE DSCHDATE
06000248 08/02/2010 08/04/2010
06000248 08/10/2010 08/12/2010 (08/10/2010 - 08/04/2010=6)
06000248 08/20/2010 08/22/2010 (08/20/2010 - 08/12/2012=8)
*/

CREATE TABLE dbo.PatientInfo (
MEMBID CHAR(8),
ADMDATE DATE,
DSCHDATE DATE
);
go

INSERT INTO dbo.PatientInfo
VALUES ('06000248','08/02/2010','08/04/2010'),
('06000248','08/10/2010','08/12/2010'),
('06000248','08/20/2010','08/22/2010');
GO

WITH BaseData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY MEMBID ORDER BY DSCHDATE) rn,
MEMBID,
ADMDATE,
DSCHDATE
FROM
dbo.PatientInfo
)
SELECT
bd1.MEMBID,
bd1.ADMDATE,
bd1.DSCHDATE,
ISNULL(DATEDIFF(dd, bd2.DSCHDATE, bd1.ADMDATE),0) DaysOut
FROM
BaseData bd1
LEFT OUTER JOIN BaseData bd2
ON (bd1.rn = bd2.rn + 1)
;
GO

DROP TABLE dbo.PatientInfo;
GO





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344994
Posted Tuesday, August 14, 2012 2:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:42 AM
Points: 309, Visits: 585
Thank you. I almost get there.
But, if I inserted more values, the duplicate result will displays.

How to fix it?

INSERT INTO dbo.PatientInfo
VALUES ('06000248','08/02/2010','08/04/2010'),
('06000248','08/10/2010','08/12/2010'),
('06000248','08/20/2010','08/22/2010'),
('06000348','08/02/2011','08/04/2011'),
('06000348','08/10/2011','08/12/2011'),
('06000348','08/20/2011','08/22/2011');
GO

MEMBID ADMDATE DSCHDATE DaysOut
06000248 2010-08-02 2010-08-04 0
06000248 2010-08-10 2010-08-12 6
06000248 2010-08-10 2010-08-12 -359
06000248 2010-08-20 2010-08-22 8
06000248 2010-08-20 2010-08-22 -357
06000348 2011-08-02 2011-08-04 0
06000348 2011-08-10 2011-08-12 371
06000348 2011-08-10 2011-08-12 6
06000348 2011-08-20 2011-08-22 373
06000348 2011-08-20 2011-08-22 8
Post #1345029
Posted Tuesday, August 14, 2012 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 23,397, Visits: 32,240
adonetok (8/14/2012)
Thank you. I almost get there.
But, if I inserted more values, the duplicate result will displays.

How to fix it?

INSERT INTO dbo.PatientInfo
VALUES ('06000248','08/02/2010','08/04/2010'),
('06000248','08/10/2010','08/12/2010'),
('06000248','08/20/2010','08/22/2010'),
('06000348','08/02/2011','08/04/2011'),
('06000348','08/10/2011','08/12/2011'),
('06000348','08/20/2011','08/22/2011');
GO

MEMBID ADMDATE DSCHDATE DaysOut
06000248 2010-08-02 2010-08-04 0
06000248 2010-08-10 2010-08-12 6
06000248 2010-08-10 2010-08-12 -359
06000248 2010-08-20 2010-08-22 8
06000248 2010-08-20 2010-08-22 -357
06000348 2011-08-02 2011-08-04 0
06000348 2011-08-10 2011-08-12 371
06000348 2011-08-10 2011-08-12 6
06000348 2011-08-20 2011-08-22 373
06000348 2011-08-20 2011-08-22 8


You tell me, are duplicate records allowed? Your original sample data set did not have duplicates. If duplicates can exist, what are your business rules regarding them, can you eliminate them prior to processing or do you have work them and if so what are the rules for dealing with duplicates.

Nevermind the above, small font on a small screen (laptop) didn't realize the account numbers were different. Yes, to the below, we need to add a bit more to the join criteria.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1345057
Posted Tuesday, August 14, 2012 3:36 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 706, Visits: 6,009
I think just doing the self join (the left join) on MEMBID and the row number to row number + 1 will solve the problem in the second set of sample data posted.
Post #1345059
Posted Tuesday, August 14, 2012 3:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 23,397, Visits: 32,240
Here is the updated code:


CREATE TABLE dbo.PatientInfo (
MEMBID CHAR(8),
ADMDATE DATE,
DSCHDATE DATE
);
go

INSERT INTO dbo.PatientInfo
VALUES ('06000248','08/02/2010','08/04/2010'),
('06000248','08/10/2010','08/12/2010'),
('06000248','08/20/2010','08/22/2010'),
('06000348','08/02/2011','08/04/2011'),
('06000348','08/10/2011','08/12/2011'),
('06000348','08/20/2011','08/22/2011');
GO



WITH BaseData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY MEMBID ORDER BY DSCHDATE) rn,
MEMBID,
ADMDATE,
DSCHDATE
FROM
dbo.PatientInfo
)
SELECT
bd1.MEMBID,
bd1.ADMDATE,
bd1.DSCHDATE,
ISNULL(DATEDIFF(dd, bd2.DSCHDATE, bd1.ADMDATE),0) DaysOut
FROM
BaseData bd1
LEFT OUTER JOIN BaseData bd2
ON (bd1.rn = bd2.rn + 1
AND bd1.MEMBID = bd2.MEMBID)
;
GO

DROP TABLE dbo.PatientInfo;
GO





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1345061
Posted Wednesday, August 15, 2012 9:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:42 AM
Points: 309, Visits: 585
It works great.
Thank you again
Post #1345343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse