locked
What am I doing wrong? RRS feed

  • Question

  • Hi folks, I have been troubling with the following SQL Statement. I don’t know what am I doing wrong. Please assist.

    SCRIPT 1:   SELECTING RECORDS WHICH SATISFY THE BELOW FILTERS FROM OBJECT DM_CRM..ExtDimCustomerSCD. (COUNT: 33062)ExtCustomerID IS PRIMARY KEY, MEANS NO DUPLICATES.

    SELECT Cust.ExtCustomerID
    FROM DM_CRM..ExtDimCustomerSCD Cust
           JOIN ( SELECT DISTINCT CustomerID
                         FROM CAMPAIGNMGMT..TBL_CUSTOMER_COMPANY_SEGMENT ComS WITH(NOLOCK)
                         WHERE ComS.IsApproved = 1
                  )ComSeg on ComSeg.CustomerID = Cust.InternalCustomerID
    WHERE CASE    WHEN   ExtCustomerCity IN ('DELHI','MUMBAI') 
                         AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=80000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                         WHEN   ExtCustomerCity IN ('BANGALORE','CHENNAI') 
                         AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=60000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                         WHEN   ExtCustomerCity NOT IN ('BANGALORE','CHENNAI','MUMBAI','DELHI') 
                         AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=40000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                         ELSE 'Failed'
           END = 'Passed'
           AND (ExtAge BETWEEN 27 AND 58 OR ExtAge>100)
           AND ExtCustomerCity IN ('AHMEDABAD','BANGALORE','CHENNAI','DELHI','FARIDABAD','GHAZIABAD','GURGAON','HYDERABAD','KOLKATA',
                                'MUMBAI','NOIDA','PUNE','THANE','BARODA','CHANDIGARH','COCHIN','COIMBATORE','INDORE','JAIPUR',
                                'JALANDHAR','LUDHIANA','NAGPUR','SURAT','VAIZAG','VIJAYAWADA','AURANGABAD','BHOPAL','MADURAI',
                                'MYSORE','NASIK','RAJKOT','JODHPUR','KARNAL','KOLHAPUR','PANIPAT','RAIPUR','BHUBANESWAR','GOA',
                               'MANGALORE','SALEM','JAGADHARI','JAMNAGAR')
           AND (CONVERT(INT,ExtCustomerCIBILScore) >= 700 
                         OR CONVERT(INT,ExtCustomerCIBILScore) = 0 
                         OR CONVERT(INT,ExtCustomerCIBILScore) = -1
                         OR CONVERT(INT,ExtCustomerCIBILScore) = 1
                         OR ExtCustomerCIBILScore IS NULL 
                  )
           AND DATEDIFF(DD, ExtDTEntryInBase, CONVERT(DATE, GETDATE())) > 60
    

    SCRIPT 2:   NOW IF I JOIN THE OUTPUT OF ABOVE SCRIPT WITH DM_CRM..ExtDimCustomerSCD ITSELF ON PRIMARY KEY. THEN THE COUNT SHOULD REMAIN SAME. BUT THIS AINT HAPPENING, COUNT IS INCREASING DRASTICALLY TO 2.6 Millions

    SELECT * 
    FROM DM_CRM..ExtDimCustomerSCD CustO 
    JOIN
           (SELECT Cust.ExtCustomerID
           FROM DM_CRM..ExtDimCustomerSCD Cust
                         JOIN ( SELECT DISTINCT CustomerID
                                      FROM CAMPAIGNMGMT..TBL_CUSTOMER_COMPANY_SEGMENT ComS WITH(NOLOCK)
                                      WHERE ComS.IsApproved = 1
                               )ComSeg on ComSeg.CustomerID = Cust.InternalCustomerID
           WHERE CASE    WHEN   ExtCustomerCity IN ('DELHI','MUMBAI') 
                               AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=80000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                               WHEN   ExtCustomerCity IN ('BANGALORE','CHENNAI') 
                               AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=60000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                               WHEN   ExtCustomerCity NOT IN ('BANGALORE','CHENNAI','MUMBAI','DELHI') 
                               AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=40000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                               ELSE 'Failed'
                         END = 'Passed'
                         AND (ExtAge BETWEEN 27 AND 58 OR ExtAge>100)
                         AND ExtCustomerCity IN ('AHMEDABAD','BANGALORE','CHENNAI','DELHI','FARIDABAD','GHAZIABAD','GURGAON','HYDERABAD','KOLKATA',
                                             'MUMBAI','NOIDA','PUNE','THANE','BARODA','CHANDIGARH','COCHIN','COIMBATORE','INDORE','JAIPUR',
                                             'JALANDHAR','LUDHIANA','NAGPUR','SURAT','VAIZAG','VIJAYAWADA','AURANGABAD','BHOPAL','MADURAI',
                                             'MYSORE','NASIK','RAJKOT','JODHPUR','KARNAL','KOLHAPUR','PANIPAT','RAIPUR','BHUBANESWAR','GOA',
                                             'MANGALORE','SALEM','JAGADHARI','JAMNAGAR')
                         AND (CONVERT(INT,ExtCustomerCIBILScore) >= 700 
                                      OR CONVERT(INT,ExtCustomerCIBILScore) = 0 
                                      OR CONVERT(INT,ExtCustomerCIBILScore) = -1
                                      OR CONVERT(INT,ExtCustomerCIBILScore) = 1
                                      OR ExtCustomerCIBILScore IS NULL 
                               )
                         AND DATEDIFF(DD, ExtDTEntryInBase, CONVERT(DATE, GETDATE())) > 60) Interim
    ON CustO.ExtCustomerID = Interim.ExtCustomerID
    

    Please tell me where is the flaw in my logic.




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

    Thursday, February 6, 2014 7:18 AM

Answers

  • Thanks Latheesh NK. I appreciate your quick response.

    I found what was the issue. An extremely stupid mistake I was doing by creating Primary key on a computed column. It was behaving abruptly. Checked the constraint and fixed it.

    Thanks again.


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

    • Proposed as answer by Johnny Bell Jnr Thursday, February 6, 2014 12:52 PM
    • Marked as answer by HimanshuSharma Saturday, February 8, 2014 6:10 AM
    Thursday, February 6, 2014 7:46 AM

All replies

  • Can you try the below and see if that duplicates:

    ;with cte
    as
    (SELECT Cust.ExtCustomerID
           FROM DM_CRM..ExtDimCustomerSCD Cust
                         JOIN ( SELECT DISTINCT CustomerID
                                      FROM CAMPAIGNMGMT..TBL_CUSTOMER_COMPANY_SEGMENT ComS WITH(NOLOCK)
                                      WHERE ComS.IsApproved = 1
                               )ComSeg on ComSeg.CustomerID = Cust.InternalCustomerID
           WHERE CASE    WHEN   ExtCustomerCity IN ('DELHI','MUMBAI') 
                               AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=80000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                               WHEN   ExtCustomerCity IN ('BANGALORE','CHENNAI') 
                               AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=60000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                               WHEN   ExtCustomerCity NOT IN ('BANGALORE','CHENNAI','MUMBAI','DELHI') 
                               AND (CAST(REPLACE(REPLACE(ExtAverageSalary,' ',''),',','') AS FLOAT)>=40000.00 OR ExtAverageSalary IS NULL) THEN 'Passed'
                               ELSE 'Failed'
                         END = 'Passed'
                         AND (ExtAge BETWEEN 27 AND 58 OR ExtAge>100)
                         AND ExtCustomerCity IN ('AHMEDABAD','BANGALORE','CHENNAI','DELHI','FARIDABAD','GHAZIABAD','GURGAON','HYDERABAD','KOLKATA',
                                             'MUMBAI','NOIDA','PUNE','THANE','BARODA','CHANDIGARH','COCHIN','COIMBATORE','INDORE','JAIPUR',
                                             'JALANDHAR','LUDHIANA','NAGPUR','SURAT','VAIZAG','VIJAYAWADA','AURANGABAD','BHOPAL','MADURAI',
                                             'MYSORE','NASIK','RAJKOT','JODHPUR','KARNAL','KOLHAPUR','PANIPAT','RAIPUR','BHUBANESWAR','GOA',
                                             'MANGALORE','SALEM','JAGADHARI','JAMNAGAR')
                         AND (CONVERT(INT,ExtCustomerCIBILScore) >= 700 
                                      OR CONVERT(INT,ExtCustomerCIBILScore) = 0 
                                      OR CONVERT(INT,ExtCustomerCIBILScore) = -1
                                      OR CONVERT(INT,ExtCustomerCIBILScore) = 1
                                      OR ExtCustomerCIBILScore IS NULL 
                               )
                         AND DATEDIFF(DD, ExtDTEntryInBase, CONVERT(DATE, GETDATE())) > 60)
    SELECT * 
    FROM DM_CRM..ExtDimCustomerSCD CustO 
    Where exists(Select 1 From cte A where CustO.ExtCustomerID = A.ExtCustomerID)
                          

    Thursday, February 6, 2014 7:27 AM
  • Thanks Latheesh NK. I appreciate your quick response.

    I found what was the issue. An extremely stupid mistake I was doing by creating Primary key on a computed column. It was behaving abruptly. Checked the constraint and fixed it.

    Thanks again.


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

    • Proposed as answer by Johnny Bell Jnr Thursday, February 6, 2014 12:52 PM
    • Marked as answer by HimanshuSharma Saturday, February 8, 2014 6:10 AM
    Thursday, February 6, 2014 7:46 AM
  • Thanks Latheesh NK. I appreciate your quick response.

    I found what was the issue. An extremely stupid mistake I was doing by creating Primary key on a computed column. It was behaving abruptly. Checked the constraint and fixed it.

    Thanks again.


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

    Sometimes that happens!!! cool.
    Thursday, February 6, 2014 7:48 AM