Checking sequence of numbers and populate a flag if number is out of sequence

Database
Enthusiast

Checking sequence of numbers and populate a flag if number is out of sequence

Hi All,

First of all, I wish you all a HAPPY NEW YEAR.

I have a requirement which I have explained below by using data:

Data in source table:

Serial_Number

Send_Date

File_Number

876543890

1-Jan-13

345

876543890

1-Jan-13

346

876543890

1-Jan-13

347

876543890

1-Jan-13

348

876543890

2-Jan-13

569

876543890

2-Jan-13

571

876543890

2-Jan-13

572

876543891

1-Jan-13

234

876543891

1-Jan-13

237

876543891

1-Jan-13

238

876543891

1-Jan-13

239

876543891

1-Jan-13

241

876543891

2-Jan-13

123

876543891

2-Jan-13

124

876543891

2-Jan-13

125

876543891

2-Jan-13

126

Expected output which will eventually be stored in the target table:-

Serial_Number

Send_Date

File_Count

Non_Seq_Flag

Min_Seq

Max_Seq

876543890

1-Jan-13

4

No

345

348

876543890

2-Jan-13

3

Yes

569

572

876543891

1-Jan-13

5

Yes

234

241

876543891

2-Jan-13

4

No

123

126

Extracting all details in the target table is simple except Non_Seq_Flag.

Below is transformation rule for the same:-

Serial_Number:- Direct Move

Send_Date:- Direct Move

File_Count:- Total number of records for the given serial number and send date.

Non_Seq_Flag:- Should be set to “Yes” if file number is out of sequence for given serial number and send date.

Min_Seq and Max_Seq:- MIN and MAX file numbers for given serial number and send date.

Can anyone help to derive the logic for Non_Seq_Flag.

Thanks a lot in advance.

Regards,

Sagar.

9 REPLIES
Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

Hi..

Hope below query helps you out..

sel

Serial_Number,

Send_Date ,

Count(*)  as File_Count ,

Case When ( Max_Seq - Min_Seq ) + 1 = File_Count Then ' No'  Else 'YES' END as Non_Seq_Flag,

Min (File_Number) as  Min_Seq,

max( File_Number) as Max_Seq

from

<<Source_Table>>

group by

Serial_Number,

Send_Date

regards,

Rupesh

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

just ensure the table has not duplicate records like..

Either define the table as SET which will have CPU-I/O overhead for duplicate row check

Or before executing above query check duplicates by

sel

Serial_Number,

Send_Date ,

File_Number

from

<<Source_Table>>

group by

Serial_Number,

Send_Date ,

File_Number

Having count(*) > 1 ;






Serial_Number Send_Date File_Number
876543890 1-Jan-13 345
876543890 1-Jan-13 345
876543890 1-Jan-13 347

if there are duplicates then abobe query will not give proper answers.

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

just ensure the table has not duplicate records like..

Either define the table as SET which will have CPU-I/O overhead for duplicate row check

Or before executing above query check duplicates by

sel

Serial_Number,

Send_Date ,

File_Number

from

<<Source_Table>>

group by

Serial_Number,

Send_Date ,

File_Number

Having count(*) > 1 ;






Serial_Number Send_Date File_Number
876543890 1-Jan-13 345
876543890 1-Jan-13 345
876543890 1-Jan-13 347

if there are duplicates then above query will not give proper answers.

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

Hi Sagar,

You can try the following query as well, this should work with duplicates also

It will just increase the filecount

Here tab id the table which contains the data

Please try and let me know if this works

sel

Serial_Number

,Send_Date

,max(cnt) file_count

,case when max(diff) > 1 then 'Yes' else 'No' end as Non_Seq_Flag

,max(file_number) as Min_Seq

,min(file_number) as Max_Seq

from

(

sel

Serial_Number

,Send_Date

,File_Number

,count(*) over (partition by Serial_Number,Send_Date  order by file_number)as cnt

,coalesce(mdiff(file_number,1,file_number),0) as diff

from

tab

group by 1,2    

)a

group by 1,2

order by 1,2;

Regards

Rajeev

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

Hi Rajeev,

It worked well and thanks a lot for your reply.

By any chance, do you have any internet link where I can get detailed explanation of OLAP and advanced OLAP functions like MDIFF etc.

Regards,

Sagar.

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

Hi Rajeev,

I have some new requirement extention to above mentioned one.

I want to use above output as input to this. 

Input (Above Output):-







Serial_Number Send_Date File_Count Non_Seq_Flag
76543890 1-Jan-13 4 No
76543891 1-Jan-13 5 Yes
76543891 2-Jan-13 4 No
76543890 2-Jan-13 3 Yes
76543891 3-Jan-13 2 No
76543890 3-Jan-13 1 No

Output:- I want to aggregate by date by summing the File_Count and Non_Seq_Flag is set to Yes if atleast one Yes for that particular date else No.






Send_Date File_Count Non_Seq_Flag
1-Jan-13 9 Yes
2-Jan-13 7 Yes
3-Jan-13 3 No

Thanks a lot in advance.

Regards,

Sagar

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

The below query will work with duplicates along with null values if we have it in File number too..

sel

   Serial_Number,

   Send_Date ,

   Count(*)  as File_Count ,

   Case When ( Max_Seq - Min_Seq ) = File_Count-1 Then ' No'  Else 'YES' END as Non_Seq_Flag,

   Min (File_Number) as  Min_Seq,

   max( File_Number) as Max_Seq

from

 ( sel distinct serial_number,send_date,zeroifnull(File_number) as File_number from <<sourcetable>>)dt

group by

  Serial_Number,

  Send_Date

Regards,

Mohan K

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

Hi Sagar

Pls try the following query for your additional requirement

Sel

Send_Date

,sum(file_count)

,max(Non_Seq_Flag)

from

(sel

Serial_Number

,Send_Date

,max(cnt) file_count

,case when max(diff) > 1 then 'Yes' else 'No' end as Non_Seq_Flag

,max(file_number) as Min_Seq

,min(file_number) as Max_Seq

from

(

sel

Serial_Number

,Send_Date

,File_Number

,count(*) over (partition by Serial_Number,Send_Date  order by file_number)as cnt

,coalesce(mdiff(file_number,1,file_number),0) as diff

from

tab

group by 1,2   

)a

group by 1,2

)b

group by 1

order by 1;

let me know if this works

Also you can learn about the analytical functions here -

http://www.coffingdw.com/sql/tdsql.htm

refer to chapter 10

Regards

Rajeev 

Enthusiast

Re: Checking sequence of numbers and populate a flag if number is out of sequence

Hi Rajeev,

Yes, the query is working and thanks for the link and it is very much useful.

Regards,

Sagar