Time Span Help

Question

Post reply

Time Span Help

Brian Dilks

Grasshopper

Points: 20
More actions
September 17, 2006 at 10:10 pm

#114575

CREATE TABLE [dbo].[Inp_Stay] (
[MemberID] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Provider] [int] NOT NULL ,
[Admit] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Discharge] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Data Set in order from above table
123                   1              1                          20050401 20050405
123                   1              1                          20050407 20050410
123                   1              1                          20050415 20050417
123                   1              1                          20050418 20050422
123                   1              1                          20050424 20050428
123                   1              1                          20050429 20050430
123                   1              1                          20060101 20060104
4                     4              4                          20040101 20040105
4                     4              4                          20040106 20040108
4                     4              4                          20040110 20040114
4                     4              4                          20040122 20040124
456                   2              2                          20040501 20040510
The Problem: A stay is defined as a time span in which the min(admit) date and max(Discharge) date where the member id, product and provider match, however... if the Dischage and admit dates are within 2 days they are considered 1 stay (need to be grouped), and need to be represented as one row. This can happen multiple times. In the above table:
lets just look at member 123
123                   1              1                          20050401 20050410
123                   1              1                          20050415 20050430
123                   1              1                          20060101 20060104
I need to acomplish this absolutely not using cursors. The amount of data is roughly around 53 million rows.
Thanks in advance for anyone taking this on!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Start here:

CREATE TABLE [dbo].[Inp_Stay] (

[MemberID] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Provider] [int] NOT NULL ,

[Admit] SMALLDATETIME NOT NULL ,

[Discharge] SMALLDATETIME NOT NULL

) ON [PRIMARY]

SELECT T1.MemberID, T1.Product, T1.Provider, T1.Admit, T2.Discharge

FROM [dbo].[Inp_Stay] T1

INNER JOIN [dbo].[Inp_Stay] T2 ON T1.MemberID = T2.MemberID AND T1.Product = T2.Product AND T1.Provider = T2.Provider AND T1.Discharge < T2.Admit AND T1.Discharge >= T2.Admit - 2

Hope it will help

_____________
Code for TallyGenerator

Brian Dilks Grasshopper Points: 20 More actions · Answer 2

Thank you for your post. That is really close...

results as follows:

123 1 1 20050401 20050410

123 1 1 20050415 20050422

123 1 1 20050424 20050430

123 1 1 20050418 20050428

4 4 4 20040101 20040108

4 4 4 20040106 20040114

However, member 123, has a span 20050415 to 20050422, and 20050422 to 20050430 which need to be combined to: 20050415 to 20040430.

Maybe this could be solved using your query but having maybe a final temptable that contains all the results. WHILE looping in to another temp table until there are no more results maybe?

Thanks Again for your help.

Brian

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 3

Brian, I think all you need to do to Serqiy's solution is change the "<" to "<="... haven't tried it, though...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)