additions and deletions

Hi
I have a table where it holds data by .....

Company      Division      Date         Manager

there are many companies each with different divisions and a Manager could be in any company or division on any date. They can change by date - that is they could be in Company A and Division Z in one month and not the next.
I would like to know how I can show the "additions" in one sql query and "deletions" in another sql query of manager in each Company - then Division by each month.
So, my output would read something like ..
additions.........
Date             Cpy        Manager           Divisions
Jan 2013      A            Barry J                Z
Jan 2013      A            Dave S                Z
Jan 2013      A            John D                Y

etc

Regards
PHIL SawyerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
To understand your requirement better, I think we need a little more clarification. Perhaps you can provide some sample data that spans more than the one company and month that you show in your example and what you would expect the results to look like.
0
PHIL SawyerAuthor Commented:
OK - see attached

Regards
Book1.xlsx
0
sdstuberCommented:
try these...

SELECT yourdate,
       cpy,
       division,
       manager
  FROM (SELECT t.*,
               LAG(yourdate) OVER(PARTITION BY cpy, division, manager ORDER BY yourdate) prevmonth,
               LEAD(yourdate) OVER(PARTITION BY cpy, division, manager ORDER BY yourdate) nextmonth,
               MIN(yourdate) OVER () mindate,
               MAX(yourdate) OVER () maxdate
          FROM yourtable t)
 WHERE yourdate > mindate AND prevmonth IS NULL -- additions

Open in new window


SELECT yourdate,
       cpy,
       division,
       manager
  FROM (SELECT t.*,
               LAG(yourdate) OVER(PARTITION BY cpy, division, manager ORDER BY yourdate) prevmonth,
               LEAD(yourdate) OVER(PARTITION BY cpy, division, manager ORDER BY yourdate) nextmonth,
               MIN(yourdate) OVER () mindate,
               MAX(yourdate) OVER () maxdate
          FROM yourtable t)
 WHERE yourdate < maxdate AND nextmonth IS NULL -- deletions

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PHIL SawyerAuthor Commented:
sdstuber

This looks good - many thanks.
One question is that if I want to select a 12 month period and see the additions deletions then will it work if I use the following on date selection ...
 yourdate BETWEEN TRUNC(ADD_MONTHS(:pi_end_date,-12),'MM') AND LAST_DAY(ADD_MONTHS(:pi_end_date,-0))

Regards
0
PHIL SawyerAuthor Commented:
sdstuber
I want to clarify re the date selection...
One question is that if I want to select a 12 month period and see the additions deletions WITHIN a given 12 month period then will it work if I use the following on date selection ...
 yourdate BETWEEN TRUNC(ADD_MONTHS(:pi_end_date,-12),'MM') AND LAST_DAY(ADD_MONTHS(:pi_end_date,-0))

Regards
0
PHIL SawyerAuthor Commented:
Just carried out a reconciliation on additions and that seems to work ok - but, the deletions seem to be showing the deletion in the month after it actually happened.

Regards
0
PHIL SawyerAuthor Commented:
Correction

The deletions are showing in the month BEFORE it actually happened.

Regards
0
sdstuberCommented:
better would be...

yourdate >= TRUNC(ADD_MONTHS(:pi_end_date,-12),'MM')
 AND yourdate < TRUNC(ADD_MONTHS(:pi_end_date,1))

if yourdate is already truncated then your original between would work
if your date is not truncated then you'll lose all values from 00:00:01 to 23:59:59 of the last day


The deletions are showing in the month BEFORE it actually happened.

can you post some sample data that shows this problem?
0
PHIL SawyerAuthor Commented:
In this sample data you will see that Phil, Siv was deleted in 01/09/2013 - the current deletions sql shows it in 01/08/2013
DATE
01/08/2012      Cpy A      Division 1      Dave
01/08/2012      Cpy A      Division 1      John
01/08/2012      Cpy A      Division 1      Ian
01/08/2012      Cpy A      Division 1      Phil, Siv
01/08/2012      Cpy A      Division 1      Brian

01/09/2012      Cpy A      Division 1      Dave
01/09/2012      Cpy A      Division 1      John
01/09/2012      Cpy A      Division 1      Ian
01/09/2012      Cpy A      Division 1      Brian
0
PHIL SawyerAuthor Commented:
Sorry - I meant the year 2012
0
sdstuberCommented:
oh I see ,  the problem is we have no way of knowing when a record was deleted.
all we know is the last time we did see a record.

I guess you could simply add 1 to the date because you know if the last day was 1/8/2012 then it was gone on 1/9/2012.

Doesn't mean it really was deleted on 1/9, only that you don't have a record after 1/8


SELECT yourdate+1 yourdate,
       cpy,
       division,
       manager
  FROM (SELECT t.*,
               LAG(yourdate) OVER(PARTITION BY cpy, division, manager ORDER BY yourdate) prevmonth,
               LEAD(yourdate) OVER(PARTITION BY cpy, division, manager ORDER BY yourdate) nextmonth,
               MIN(yourdate) OVER () mindate,
               MAX(yourdate) OVER () maxdate
          FROM yourtable t)
 WHERE yourdate < maxdate AND nextmonth IS NULL -- deletions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PHIL SawyerAuthor Commented:
Thanks - great work
0
PHIL SawyerAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.