CODEWITHSUNDEEP
sql-servert-sqlcountdistinctone-to-many
rvphx
rvphx

Reputation: 2402

Resolving duplicates in 1 to many relationship in SQL Server

I have a table which has data like:

case_id    person_id     provider_group_ID       PROVIDER_GROUP_FUNCTION   case_date
 12421      1111             SLS_D2                  BPSC_Service          2014-10-07
 12422      1111             SLS_D2                  BPSC Service          2014-10-07
 12423      1111             SLS_D2                  BPSC Service          2014-10-07

And I have another table called TeleComData like:

PersonID   provider_group_id                    provider_group_function     case_date    NUM_CALLS
1111         SLS_D2                                  BPSC_Service           2014-10-07     8
1111         BPSC                                    BPSC_Service           2014-10-07     0

What I want is

CaseCount  PROVIDER_GROUP_ID       PROVIDER_GROUP_FUNCTION            case_date    num_calls
  3              SLS_D2                  BPSC_Service                  2014-10-07    8
  0              BPSC                    BPSC_Service                  2014-10-07    0

But as of now, I get the following

 CaseCount  PROVIDER_GROUP_ID       PROVIDER_GROUP_FUNCTION            case_date    num_calls
      3              SLS_D2                  BPSC_Service                  2014-10-07    8
      3              BPSC                    BPSC_Service                  2014-10-07    0

I did a left outer join between the two tables and instead of counting the Cases based on a particular PROVIDER_GROUP_ID, it duplicates it. I cant seem to figure out whats going on with my code. Here is the piece that I have got so far:

SELECT COUNT(DISTINCT CASE_ID)  FROM 
      dbo.DW_TELECOM_DATA B 
      LEFT OUTER JOIN Clear.T_CLEAR_SERVICE A ON 
      A.Case_date = B.Case_date
      AND A.PERSON_ID = B.PERSON_ID

Upvotes: 2

Views: 74

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Try this. Add one more ONcondition in your Left Outer Join

CREATE TABLE #e
  (
     case_id                 INT,
     person_id               INT,
     provider_group_ID       VARCHAR(100),
     PROVIDER_GROUP_FUNCTION VARCHAR(100),
     case_date               DATE
  )

INSERT #e
VALUES (12421,1111,'SLS_D2','BPSC_Service','2014-10-07'),
       (12422,1111,'SLS_D2','BPSC Service','2014-10-07'),
       (12423,1111,'SLS_D2','BPSC Service','2014-10-07')

CREATE TABLE #f
  (
     PersonID                INT,
     provider_group_ID       VARCHAR(100),
     PROVIDER_GROUP_FUNCTION VARCHAR(100),
     case_date               DATE,
     NUM_CALLS               INT
  )

INSERT #f
VALUES (1111,'SLS_D2','BPSC_Service','2014-10-07',8),
       (1111,'BPSC','PSC_Service','2014-10-07',0) 



SELECT COUNT(CASE_ID) CaseCount, b.provider_group_ID,  b.PROVIDER_GROUP_FUNCTION,  
       b.case_date,b.NUM_CALLS
FROM   #f B
       LEFT OUTER JOIN #e A
                    ON A.CASE_DATE = B.CASE_DATE
                       AND A.PERSON_ID = B.PERSONID
                       AND A.PROVIDER_GROUP_ID = B.PROVIDER_GROUP_ID 
                       group by b.provider_group_ID,  
                                b.PROVIDER_GROUP_FUNCTION,b.case_date,b.NUM_CALLS

Or 

SELECT DISTINCT Count(CASE_ID)OVER (partition BY b.provider_group_ID) CaseCount,
                b.provider_group_ID,
                b.PROVIDER_GROUP_FUNCTION,
                b.case_date,b.NUM_CALLS
FROM   #f B
       LEFT OUTER JOIN #e A
                    ON A.CASE_DATE = B.CASE_DATE
                       AND A.PERSON_ID = B.PERSONID
                       AND A.PROVIDER_GROUP_ID = B.PROVIDER_GROUP_ID

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

SELECT B.PROVIDER_GROUP_ID, B.PROVIDER_GROUP_FUNCTION, B.case_date 
     , COUNT(DISTINCT A.CASE_ID)  
  FROM dbo.DW_TELECOM_DATA B 
  LEFT OUTER JOIN Clear.T_CLEAR_SERVICE A 
    ON A.Case_date = B.Case_date
   AND A.PERSON_ID = B.PERSON_ID
   AND A.provider_group_ID = A.provider_group_ID 
 group by B.PROVIDER_GROUP_ID, B.PROVIDER_GROUP_FUNCTION, B.case_date

Upvotes: 0

Related Questions