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
Avatar of slightwv (䄆 Netminder)
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?
Avatar of Vishal Jaiswal
Vishal Jaiswal
Flag of United States of America image

ASKER

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.
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

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')
Avatar of johnsone
johnsone
Flag of United States of America image

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
Avatar of Vishal Jaiswal
Vishal Jaiswal
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo