Summary Record
2 posters
Page 1 of 1
Summary Record
Hi Experts-
Please provide the possible solution for the below query-
Master_Table-
ID_Col Ref_Num Status
222 A123 P
222 A123 A
222 A123 F
222 A123 K
222 A123 T
111 B123 P
111 B123 T
111 B123 K
111 B123 F
Records with value A in the Status column (Master_Table) will only consider as Active records rest will be consider Inactive records.
From the above table, our script should give a single record per ID_col/Ref_Num based on the Status columns as below,
Subset_Master-
ID_Col Ref_Num No_Active_Status No_Inactive_Status Total_Status
222 A123 1 4 5
111 B123 0 4 4
For ID_Col 222 the No_Active_Status is 1 because this ID contains only 1 A, the rest will be consider No_Inactive_Status and the Total_Status is No_Active_Status+No_Inactive_Status.
For ID_Col 111 the No_Active_Status is 0 because there is no A status, the rest will be consider No_Inactive_Status and the Total_Status is No_Active_Status+No_Inactive_Status.
Thanks In advance.
-Balas
Please provide the possible solution for the below query-
Master_Table-
ID_Col Ref_Num Status
222 A123 P
222 A123 A
222 A123 F
222 A123 K
222 A123 T
111 B123 P
111 B123 T
111 B123 K
111 B123 F
Records with value A in the Status column (Master_Table) will only consider as Active records rest will be consider Inactive records.
From the above table, our script should give a single record per ID_col/Ref_Num based on the Status columns as below,
Subset_Master-
ID_Col Ref_Num No_Active_Status No_Inactive_Status Total_Status
222 A123 1 4 5
111 B123 0 4 4
For ID_Col 222 the No_Active_Status is 1 because this ID contains only 1 A, the rest will be consider No_Inactive_Status and the Total_Status is No_Active_Status+No_Inactive_Status.
For ID_Col 111 the No_Active_Status is 0 because there is no A status, the rest will be consider No_Inactive_Status and the Total_Status is No_Active_Status+No_Inactive_Status.
Thanks In advance.
-Balas
Balas- Posts : 11
Join date : 2012-04-26
Re: Summary Record
Check your database manual to see if it supports a CASE, IF or DECODE function in a column expression. Use it to check the status value and return either 0 or 1 in each column. Enclose each expression with a sum(...).
Similar topics
» Summary Tables
» SNAPSHOT SUMMARY FACT -
» summary and detail fact
» When is a record "current"?
» Planning vs Operational System - Dimension Source
» SNAPSHOT SUMMARY FACT -
» summary and detail fact
» When is a record "current"?
» Planning vs Operational System - Dimension Source
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|