troubleshooting Question

how to apply lead and lag function to a group of records in oracle

Avatar of mahjag
mahjag asked on
Oracle Database
6 Comments2 Solutions1088 ViewsLast Modified:

I have a set of data with the following results - how do I evaluate the previous record data for bunch of columns based on a group value.

Group_id    customer     flag1    flag2       flag3
1                 Netflix          Y         Auto       Template
1                  Netflix         N        Auto       Template
2                Amazon         Y        Auto        Template
2                Amazon         Y        Manual    Template
3              N       Auto        NonStandard
3              N       Auto        Template
4                Blockbuster    Y       Auto         Template
4                Blockbuster    Y       Auto         Template
4                 Blockbuster   N      Auto          NonStandard
5              Y      Auto          Template
5              Y      Auto          Template

From the results above I want to write a sql to give me the group ids that do not have the flag values same  - for example easy case will be group id value = 5 wher the flag1, flag2 and flag3 values are same then I want to categorize them as "PERFECT" but if there are any changes to flag1 of first record to flag1 of second record or third record then I want to categorize them as flag1value change - from my example above group id value = 1, similarly to categorize as flag2 change for group id value = 2 and flag3 change for group id value = 3.
More complex case is group id value = 4 where both flag1 and flag3 value change when compared to 3 records -

group id value can be same for 2 or more records - I dont know how I can write a sql to categorize them based on flag value comparing the first record to second , third and so on -

any help will be greatly appreciated
Information Technology Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros