Avatar of Vishal Jaiswal
Vishal JaiswalFlag for United States of America asked on

need a sql script

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.
Oracle Database* PL/SQLSQL

Avatar of undefined
Last Comment
Vishal Jaiswal

8/22/2022 - Mon
slightwv (䄆 Netminder)

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?
ASKER
Vishal Jaiswal

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 Geerlings

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')
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
johnsone

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.
ASKER CERTIFIED SOLUTION
Vishal Jaiswal

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question