T-SQL - Find Client Market Code and Plan Code at time of initial span 1

[JcTon]

Have a sql server database that stores information about clients such as the date/month that the client initial became a client (initial client span) and the MktCode and PlanCode for the clients.

Sometimes the clients terminate service that is recorded in the field titled "ClientEndDt." If a client terminated and rejoins, the client will have another "elibility span" where there are additional values stored in the fields titled "ClientStartDt" and "ClientEndDt."

For example:

ClientID--ClientStartDt----MktCode---PlanCode
12345-----01/01/07---------LosAngel---Calif
12345-----06/16/07---------Seattle----Wash

So far, I am not able to extract the initial MktCode or PlanCode values for the clients using the sql script below.

What revisions do I need to make to the sql script so that the initial MktCode and PlanCode are in the query results?

Any assistance will be appreciated.

SELECT C.CLIENT_ID
, INIT_SPAN.ClientStartDt
, C.CLIENT_FIRST_NM
, CE.CLIENT_PLAN_CD PlanCode
, CE.CLIENT_MARKET_CD MktCode

FROM CLIENT C

INNER JOIN CLIENT_LOOKUP CL
ON C.CLIENT_ID = ML.CLIENT_ID

INNER JOIN CLIENT_ELIGIBILITY CE
ON C.CLIENT_ID = CE.CLIENT_ID

INNER JOIN
( --RETRIEVES INITIAL CLIENT SPAN
SELECT CLIENT_ID,
MIN(CLIENT_START_DT) AS InitialSpanDate
FROM CLIENT_ELIGIBILITY
WHERE CLIENT_PLAN_CD = ?
CLIENT_MARKET_CD = ?

GROUP BY CLIENT_ID
) INIT_SPAN

ON CE.CLIENT_ID = INIT_SPAN.CLIENT_ID
AND CE.CLIENT_START_DT = INIT_SPAN.InitialSpanDate

WHERE C.CLIENT_ID in ('458923','459826')

 

[bborissov]

Try:

[COLOR=blue]SELECT[/color] C.CLIENT_ID
     , INIT_SPAN.ClientStartDt
     , C.CLIENT_FIRST_NM
     , CE.CLIENT_PLAN_CD PlanCode
     , CE.CLIENT_MARKET_CD MktCode
[COLOR=blue]FROM[/color] CLIENT C
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLIENT_LOOKUP      CL [COLOR=blue]ON[/color] C.CLIENT_ID = ML.CLIENT_ID
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLIENT_ELIGIBILITY CE [COLOR=blue]ON[/color] C.CLIENT_ID = CE.CLIENT_ID
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] CLIENT_ID,  
                   [COLOR=#FF00FF]MIN[/color](CLIENT_START_DT) [COLOR=blue]AS[/color] InitialSpanDate
                   [COLOR=blue]FROM[/color] CLIENT_ELIGIBILITY
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] CLIENT_ID) INIT_SPAN
      [COLOR=blue]ON[/color] CE.CLIENT_ID       = INIT_SPAN.CLIENT_ID
     AND CE.CLIENT_START_DT = INIT_SPAN.InitialSpanDate
[COLOR=blue]WHERE[/color] C.CLIENT_ID in ([COLOR=red]'458923'[/color],[COLOR=red]'459826'[/color])

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JcTon]

Thanks.

Tested to see if the query is extracting the initial ClientStartDate and it appears that it is.

However, I am not certain that the associated "MktCode" and "PlanCode" are being extracted. Will conduct more tests of data to determine if the query is performing as planned.

So, by specifying that the initial ClientStartDate is extracted, does this always imply that the corresponding "MktCode" and "PlanCode" will be extracted as well?

 

[bborissov]

No,
Please could you post some example data from all tables and desired results from this data?
I am not sure what you have in
CLIENT_ELIGIBILITY
why you use CLIENT_LOOKUP if you don't have any reference to it in your field list.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JcTon]

Client Eligibility table fields include the following;

ClientID--ClientStartDt----MktCode---PlanCode
12345-----01/01/07---------LosAngel---Calif
12345-----06/16/07---------Seattle----Wash


"CL.SYS_CLIENT_NBR" should have been displayed in the SELECT clause of the sql script that was initially posted.

 

[bborissov]

try

[COLOR=blue]SELECT[/color] C.CLIENT_ID
     , CE.CLIENT_START_DT   [COLOR=blue]AS[/color] ClientStartDt
     , C.CLIENT_FIRST_NM
     , CE.CLIENT_PLAN_CD    [COLOR=blue]AS[/color] PlanCode
     , CE.CLIENT_MARKET_CD  [COLOR=blue]AS[/color] MktCode
[COLOR=blue]FROM[/color] CLIENT C
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLIENT_LOOKUP      CL [COLOR=blue]ON[/color] C.CLIENT_ID = ML.CLIENT_ID
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] CE.CLIENT_ID,
                   CLIENT_START_DT,
                   CLIENT_PLAN_CD,
                   CLIENT_MARKET_CD
            [COLOR=blue]FROM[/color] CLIENT_ELIGIBILITY   CE
            [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] CLIENT_ID,  
                               [COLOR=#FF00FF]MIN[/color](CLIENT_START_DT) [COLOR=blue]AS[/color] InitialSpanDate
                         [COLOR=blue]FROM[/color] CLIENT_ELIGIBILITY
                         [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] CLIENT_ID) INIT_SPAN
                  [COLOR=blue]ON[/color] CE.CLIENT_ID       = INIT_SPAN.CLIENT_ID
                 AND CE.CLIENT_START_DT = INIT_SPAN.InitialSpanDate) CE
       [COLOR=blue]ON[/color] CE.CLIENT_ID = C.CLIENT_ID
[COLOR=blue]WHERE[/color] C.CLIENT_ID in ([COLOR=red]'458923'[/color],[COLOR=red]'459826'[/color])

(not tested again)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JcTon]

Will test and post back.