Urgent assistance requested

Database
Enthusiast

Urgent assistance requested

Hi Friends,

It's very urgent reqirement,could you please help us....

Thanks in advance...



1. GROUP records FROM MISSION_CASE_HIST based ON the same MISSION_CASE_ID , MCID AND MBR_CASE_ID ORDER BY MISSION_CASE_ENTRY_DTM

a. EACH record would have TO be examined IN this ORDER:

b. Episode/record 1 :The very FIRST record WHERE MISSION_CASE_HIST .CP_STTS_CD = 'OPEN' would be the FIRST episode FOR that GROUPING (USE MISSION_CASE_HIST.MISSION_CASE_ENTRY_DTM FOR PM_STRT_DT)

c. NEXT Episode / record FOR the same GROUPING :

EACH subsequent record FOR the same GROUP WITH MISSION_CASE_HIST .CP_STTS_CD = 'OPEN'

(AND ONLY IF this record FOLLOWING the record WITH MISSION_CASE_HIST .CP_STTS_CD <> 'OPEN' ) would also CREATE an episode(USE CAST(MISSION_CASE_HIST.MISSION_CASE_ENTRY_DTM AS DATE) FOR PM_STRT_DT)
10 REPLIES
Junior Contributor

Re: Urgent assistance requested

It's hard to understand your requirements without some example data and expected result, but looks like you want to assign the same value to a series of rows based on some condition:

sum(case when MISSION_CASE_HIST.CP_STTS_CD  = 'OPEN' then 1 else 0 end) -- same value for all rows following an 'OPEN' row
over (partition by MISSION_CASE_ID , MCID , MBR_CASE_ID
      order by MISSION_CASE_ENTRY_DTM
      rows unbounded preceding) 

Re: Urgent assistance requested

I am new to teradata and i am trying to learn via the blogs here. i have a scenario which i am unable to create.

 

below is the table-

COL ACOL BCOL C (Expected)
1ABCVALUE1
1DEFVALUE1
2ABCABC
3ABCABC
4DEFDEF
5ABCVALUE 2
5DEFVALUE 2
5GHIVALUE 2
6ABCVALUE 1
6DEFVALUE 1

 

if the count of values in COL B is 2, then COL C should have VALUE 1, if the count is more than 3, then key in Value 2. 

however if the count is 1, then put in the value from COL B.

 

I would really appreciate if you can help me with this question.

 

Enthusiast

Re: Urgent assistance requested

-- Dnoeth

Please find the sample data....

mission_CASE_ID                           mbr-case_id                                                      CP_STTS_CD       ENTRY_DTM   

                6454496                     A777CF03DD6AC74B5E7D448B5E595EC0           OPEN                 3/5/14 8:41 AM

               6454496                     A777CF03DD6AC74B5E7D448B5E595EC0           CLOSED              4/4/14 11:53 AM

               6454496                     A777CF03DD6AC74B5E7D448B5E595EC0           OPEN                 6/2/14 4:54 PM

               6454496                     A777CF03DD6AC74B5E7D448B5E595EC0           CLOSED              6/11/14 10:32 PM

               6454496                     A777CF03DD6AC74B5E7D448B5E595EC0           OPEN                       1/8/15 2:05 PM

               6454496                     A777CF03DD6AC74B5E7D448B5E595EC0           CLOSED            1/23/15 10:35 PM

 

Enthusiast

Re: Urgent assistance requested

hi doneth we needed to populate start date for that record by the above logic could u please help us

Junior Contributor

Re: Urgent assistance requested

What is the expected result & why?

Are there always alternating OPEN & CLOSED rows?

Enthusiast

Re: Urgent assistance requested

Source data :

MCID                     MBR_ID          STATUS               ENTRY_DTM   

9031824                307067877           OPEN                    9/12/16 10:28

9031824                307067877           OPEN                    9/13/16 10:28

9031824                307067877           CLOSED                9/15/16 14:33

9031824                307067877           OPEN                    10/17/16 12:32

9031824                307067877           CLOSED                10/24/16 15:10

 

 Expected Result:

 MCID                     MBR_ID                 STATUS                    ENTRY_dtm  START_DATE         END_DATE

9031824                307067877           OPEN                    9/12/16 10:28     9/12/16 10:28     9/15/16 14:33

9031824                307067877           CLOSED                9/15/16 14:33     9/12/16 10:28     9/15/16 14:33

9031824                307067877           OPEN                    10/17/16 12:32  10/17/16 12:32  10/24/16 15:10

9031824                307067877           CLOSED                10/24/16 15:10  10/17/16 12:32  10/24/16 15:10

 

If you got multiple open status with diffrent entry dates ,we have to get start_date with corresponding ENtry dtm as Start date.

 

 

Junior Contributor

Re: Urgent assistance requested

Some more questions:

 

Are there multiple non-OPEN rows in each group?

If yes, how to process them, return the first/last/all?

Enthusiast

Re: Urgent assistance requested

Yes we need all...

Please do the needful

Junior Contributor

Re: Urgent assistance requested

And what start/end date should be returned for those rows?