none
data validation and query

    Question

  • HI Friends,

    I have a scenario like below,

    Above is the type of data I'm getting from a flat file, If you see above, a single provid is holding multiple groupid's, but in my database it can hold only one groupid for single provider and I know the validations like below :

    1.for single provider id if there are different Group ID’s, select the active begin/end date  (affiliation_enddate=9999999, it means that provider is active, active begin date means latestdate)

    2..for single provider id if there are different Group ID’s  and have identical begin/end dates - select the first one.

    I'm just trying but missing some logic to code, any help is highly appreciated.

    Thanks.

    Thursday, November 14, 2013 2:07 AM

Answers

  • Are you looking for the below query,

    SELECT 
    ProvID,
    GroupID,
    Max(Affiliation_startdate)
    FROM (
    	SELECT  ProvID,GroupID,
    	cast(left(Affiliation_startdate,4) + '/' + 
    	substring(Affiliation_startdate,5,2) + '/' + 
    	substring(Affiliation_startdate,7,2) as datetime) Affiliation_startdate,
    	Affiliation_enddate FROM tablename 
    	WHERE Affiliation_startdate = Affiliation_enddate
    )
    GROUP BY ProvID, GroupID


    Regards, RSingh

    Thursday, November 14, 2013 4:04 AM

All replies

  • In that case first push the data to another staging table , where multiple group ids can be inserted and then apply the logic on that table to get unique groupid and provider combination.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, November 14, 2013 2:45 AM
  • Are you looking for the below query,

    SELECT 
    ProvID,
    GroupID,
    Max(Affiliation_startdate)
    FROM (
    	SELECT  ProvID,GroupID,
    	cast(left(Affiliation_startdate,4) + '/' + 
    	substring(Affiliation_startdate,5,2) + '/' + 
    	substring(Affiliation_startdate,7,2) as datetime) Affiliation_startdate,
    	Affiliation_enddate FROM tablename 
    	WHERE Affiliation_startdate = Affiliation_enddate
    )
    GROUP BY ProvID, GroupID


    Regards, RSingh

    Thursday, November 14, 2013 4:04 AM
  • SELECT ProvID, GroupID, AffiliationBegindate, AffiliationEnddate FROM (SELECT 
    ProvID,GroupID,AffiliationBegindate,AffiliationEnddate,
    Row_number() Over(Partition by ProvID,AffiliationBeginDate Order By GroupID ) rn
    FROM stagingTable
    WHERE AffiliationEndDate='99999999') t
    WHERE rn=1

    Thursday, November 14, 2013 4:46 AM