We help IT Professionals succeed at work.

need a sql script

82 Views
Last Modified: 2018-10-17
Hi,

I have an employee_ref_id column and can have multiple entry in address table.  When employee do any chnage a new row insert in address table and new row in employee table. I have to count how many times he changed his address.

Example: employee : JOHN
employee_ref_id in year 2017 is : 100
with this id, he have an address in address table suppose "Newyork"

after that in 2018 he changed address 4 times
for same employee john , 4 ref_id created 101, 102,103,104
address : washington, newyork, newyork, washington

basically he chnaged 3 times.since 2017. first newyork to washington and then washinton to newyork and in last again newyork to washington.. so I have to give count 3.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I do not understand what you are asking.

It sounds like a simple count in the address table:
select employee_id,count(*) from address_table group by employee_id;

Since you asked the question, I'm guessing it isn't that simple.

Can you please provide sample data from both tables and the expected results?
Vishal JaiswalDatabase Architect

Author

Commented:
The below query might help you understand my situation:

WITH N1 AS
  (SELECT /*+ parallel (4) */ E.Id Eligibility_2018,E.User_Profile_Id FROM Eligibility E, User_Profile UP
  WHERE UP.Deleted                = 0
  AND UP.Id                       = E.User_Profile_Id
  AND E.Eligibility_Status       IN ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED','OBSOLETE')
  AND TRUNC(E.Determination_Date) > '31-DEC-2017'
  AND E.Determination_Date        =
    (SELECT MIN(E2.Determination_Date)
    FROM Eligibility E2
    WHERE E2.User_profile_Id   = E.User_Profile_Id
    AND E2.Eligibility_Status IN ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED','OBSOLETE')
    )
  ),
  N2 AS
  (SELECT N1.*, SLM.Old_Eligibility_Id Eligibility_2017
  FROM N1, SSAP_Lsc_Mapping SLM
  WHERE N1.Eligibility_2018 = SLM.Eligibility_Id
  ),
  N3 AS
  (SELECT N2.User_Profile_Id, N2.Eligibility_2018, N2.Eligibility_2017, EM.Member_Reference_Id,Trim(Ad.Street_Addressline1||Ad.Street_Addressline2||Ad.City||Ad.State_Code||Ad.Zip||Ad.County) Base_Address
  FROM N2,
    Elg_Member_Rpt_VW EM,
    Contact_Info_Address_Mpng CIAM,
    Address Ad
  WHERE N2.Eligibility_2017 = EM.Eligibility_Id
  AND EM.Contact_Info_Id    = CIAM.Contact_Info_Id
  AND Ad.Id                 = CIAM.Address_Id
  AND Ad.Type               = 'Primary'
  ) Select * From N3;

If you see In N1 WITH N1 AS
  (SELECT /*+ parallel (4) */ E.Id Eligibility_2018,E.User_Profile_Id FROM Eligibility E, User_Profile UP
  WHERE UP.Deleted                = 0
  AND UP.Id                       = E.User_Profile_Id
  AND E.Eligibility_Status       IN ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED','OBSOLETE')
  AND TRUNC(E.Determination_Date) > '31-DEC-2017'
  AND E.Determination_Date        =
    (SELECT MIN(E2.Determination_Date)
    FROM Eligibility E2
    WHERE E2.User_profile_Id   = E.User_Profile_Id
    AND E2.Eligibility_Status IN ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED','OBSOLETE')
    )
  ),
  N2 AS
  (SELECT N1.*, SLM.Old_Eligibility_Id Eligibility_2017
  FROM N1, SSAP_Lsc_Mapping SLM
  WHERE N1.Eligibility_2018 = SLM.Eligibility_Id
  ),
  N3 AS
  (SELECT N2.User_Profile_Id, N2.Eligibility_2018, N2.Eligibility_2017, EM.Member_Reference_Id,Trim(Ad.Street_Addressline1||Ad.Street_Addressline2||Ad.City||Ad.State_Code||Ad.Zip||Ad.County) Base_Address
  FROM N2,
    Elg_Member_Rpt_VW EM,
    Contact_Info_Address_Mpng CIAM,
    Address Ad
  WHERE N2.Eligibility_2017 = EM.Eligibility_Id
  AND EM.Contact_Info_Id    = CIAM.Contact_Info_Id
  AND Ad.Id                 = CIAM.Address_Id
  AND Ad.Type               = 'Primary'
  ),
 
  In N1 block, I am picking the min(id) which is created after 31-dec-2107, means 1st record in 2018 year. Now we have a mapping table which gives me the last record of 2017. So after N2, I have last record of 2017 and 1st record of 2018. In N3, I will get the address which is associated with 2017 record.
  Now the problem is: in 2018, suppose user creates 5 records, so i have to check address for all 5 and compare with 1st base_address.. if chnages then give the count how many times he changed.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I'm not sure that you need as much complexity as you have in the query you posted.  
1. For example, why do you need the "1st record of 2018" to help determine "the last record of 2017"? Isn't the "the last record of 2017" the same, whether there is a record for that user in 2018, or not?  
2. Do you really want to hard-code the values for 2017 and 2018?  Isn't it better to just use relative values for the last day of last year (and the first date of this year, if you actually need that also)?

There are two problems with this line in your query: "TRUNC(E.Determination_Date) > '31-DEC-2017'
3. You should include "to_date" like this: to_date ('31-DEC-2017','DD-MON-YYYY').  Otherwise, you force Oracle to do an "implicit datatype conversion", because you have a "DATE" datatype: E.Determination_Date on one side of the comparison operator (>) but a CHARACTER value: '31-DEC-2017' on the other side.  (Yes, that looks to a human like a "date", but to Oracle, anything in single quotes is a CHARACTER.)  And Oracle cannot directly compare "DATE" values to "CHARACTER" (or to "NUMBER") values.  Oracle can only compare: DATES to DATES, CHARACTERS to CHARACTERS and NUMBERS to NUMBERS.  If you explicitly convert this string: '31-DEC-2017' to a date, by using "to_date" like this: to_date ('31-DEC-2017','DD-MON-YYYY'), you allow Oracle to compare a DATE value to a DATE value.

4. Are you sure that you want the "TRUNC" here? That will prevent the use of an index on this column (and it will force Oracle to convert every Determination_Date to its truncated form, before the query can do the comparison.  I think you want that comparison to look like this instead:
E.Determination_Date >= to_date('01-JAN-2018','DD-MON-YYYY')
or like this, so it is dynamic for this year and any future year:
E.Determination_Date >= to_date('0101'||to_char(sysdate,'YYYY'),'MMDDYYYY')
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I find this very confusing.  First you say:

I have an employee_ref_id column and can have multiple entry in address table.

Then you say:

When employee do any chnage a new row insert in address table and new row in employee table.

This means a one to one relationship.  You wouldn't have multiple entries in the address table for the same id if you insert a new id every time you insert a new address.  This is a very odd design to me, as you are duplicating a row in a table with a new id and all the same information.  Doesn't seem like a good design.
Database Architect
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.