none
TSQL- Help ( Scores)

    Question

  • Hi, 

    I have a Candidate table with the following fields :

    • CandidateId ( System Generated Id)
    • EmailAddress 
    • PurchaseId 
    • CreditScore
    • CreditScoreDate 

    There can be same email address specified multiple times in the Candidate table. The credit Score value can be NULL or Invalid Range ( >0 and <300) , 0 , Valid Range (>300 and <800)

    Samle SQL and Desired Results are below :

    /*SQL Script*/
    CREATE TABLE #Candidate( CandidateId INT NOT NULL,
                             EmailAddress VARCHAR(100) NOT NULL,
                             PurchaseId INT,
                             CreditScore INT,
                             CreditScoreDate datetime )
      /*a.	There were no credit scores associated with 
      this address (does that ever happen?)*/
      /*Assign -9990*/
      INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore ,
                             CreditScoreDate )
      VALUES (1,'drtwer@gmail.com',190,NULL,NULL)
      
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore ,
                             CreditScoreDate  )
      VALUES (2,'drtwer@gmail.com',908,NULL,NULL)
      
      /*b.	There was only one credit score pulled, and it was a 0*/
       /*Assign -9991 as */
      
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore ,
                             CreditScoreDate  )
     VALUES (3,'truered@gmail.com',346,0,'2013-01-10')
      
      /*c.	There was only one credit score pulled, 
      and it was in the valid range (300-850)*/
      /*Assign -9992*/
      
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate   )
     VALUES (4,'tampura@gmail.com',390,765,'2013-10-01')
      
      /*d.	There was only one credit score pulled, 
      and it was outside the valid range, and it was >0 and less than 300.*/
      /*Assign -9993*/
    INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate  )
     VALUES (5,'gtrew@gmail.com',9825,58,'2001-01-01')
      /*e.	There were two credit scores, but the EARLIER one was a 0, 
      and the LATER one was in the VALID range, so no number difference could be generated.*/
       /*Assign -9994*/
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate )
     VALUES (6,'hagty@gmail.com',9826,0,'2010-01-01')
     
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate)
     VALUES (7,'hagty@gmail.com',9878,678,'2011-01-01')
      
      /*f.	There were two credit scores, but the LATER one was a 0, 
      and the EARLIER one was in the VALID range.*/
       /*Assign -9995*/
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate)
     VALUES (8,'hagty678@gmail.com',9890,765,'2012-01-01')
     
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate)
     VALUES (9,'hagty678@gmail.com',9856,0,'2013-11-01')
      
    
      
      /*g.	There were two credit scores, and both returned a 0.*/
      /*Assign -9996*/
      INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate )
     VALUES (10,'mighty@gmail.com',9890,0,'2012-02-01')
     
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate )
     VALUES (11,'mighty@gmail.com',9856,0,'2013-03-01')
     
     /*h.	One of the credit scores was not in the valid range.*/
     /*Assign -9997*/
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate )
     VALUES (12,'stone@gmail.com',9859,778,'2013-03-01')
     
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate )
     VALUES (13,'stone@gmail.com',9082,678,'2013-07-01')
     
     INSERT #Candidate (CandidateId, 
                             EmailAddress ,
                             PurchaseId, 
                             CreditScore,
                             CreditScoreDate )
     VALUES (14,'stone@gmail.com',9081,276,'2002-07-01')


    The Desired Output :

    /* DESIRED SQL OUTPUT*/
     
     SELECT 'drtwer@gmail.com' AS EmailAddress 
             ,2 AS COUNT
             ,-9990 AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,NULL AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,NULL AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
             UNION ALL 
    SELECT 'truered@gmail.com' AS EmailAddress 
             ,1 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,-9991 AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,NULL AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,NULL AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
             UNION ALL 
     SELECT 'tampura@gmail.com' AS EmailAddress 
             ,1 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,-9992 AS OnlyOneScoreAndValidRange
             ,NULL AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,NULL AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
             UNION ALL 
     SELECT 'gtrew@gmail.com' AS EmailAddress 
             ,1 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,-9993 AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,NULL AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
             UNION ALL 
    SELECT 'hagty@gmail.com' AS EmailAddress 
             ,2 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,NULL  AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,-9994 AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,NULL AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
             UNION ALL 
    SELECT 'hagty678@gmail.com' AS EmailAddress 
             ,2 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,NULL  AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,-9995 AS TwoScoresLATER0andEarlierValid
             ,NULL AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
            UNION ALL 
     SELECT 'mighty@gmail.com' AS EmailAddress 
             ,2 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,NULL  AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,-9996 AS TwoScoresAndBoth0
             ,NULL AS OneScoreNotInValidRange
             UNION ALL 
     SELECT 'stone@gmail.com' AS EmailAddress 
             ,3 AS COUNT
             ,NULL AS NoCreditInfoAssciated
             ,NULL AS OnlyOneScoreAnd0
             ,NULL AS OnlyOneScoreAndValidRange
             ,NULL  AS OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
             ,NULL AS TwoScoresEARLIER0andLaterValid
             ,NULL AS TwoScoresLATER0andEarlierValid
             ,NULL  AS TwoScoresAndBoth0
             ,-9997 AS OneScoreNotInValidRange

    The output consist of 

    • EmailAddress
    • Count
    • NoCreditInfoAssciated
    • OnlyOneScoreAnd0
    • OnlyOneScoreAndValidRange
    • OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
    • TwoScoresEARLIER0andLaterValid
    • TwoScoresLATER0andEarlierValid
    • TwoScoresAndBoth0
    • OneScoreNotInValidRange

    The idea is to assign defaults ( the defaults are specified in the scripts ) when any of the condition is satisfied. Please guide as to how to achieve this. 

    Thanks


    EVA05

    Friday, November 15, 2013 12:05 AM

Answers

  • Try this.

    select EmailAddress
    ,Count(*)  AS COUNT
    ,Case When Count(CreditScore)=0 then -9990 
    		Else null End as NoCreditInfoAssciated
    ,Case When Count(CreditScore)=1 and Sum(CreditScore)=0  then -9991 
    		Else null End as OnlyOneScoreAnd0
    ,Case When Count(CreditScore)=1 and Sum(CreditScore)>300 and Sum(CreditScore)<800 then -9992 
    		Else null End as OnlyOneScoreAndValidRange
    ,Case When Count(CreditScore)=1 and Sum(CreditScore)>0 and Sum(CreditScore)<300 then -9993 
    		Else null End as OnlyOneScoreAndInValidRangeLess300ANDGreaterThan0
    ,Case When Count(CreditScore)=2 and Min(Dateadd(dd,CreditScore,CreditScoreDate))=Min(CreditScoreDate) 
    			and Sum(CreditScore)>300 and Sum(CreditScore)<800  then -9994 
    		Else null End as TwoScoresEARLIER0andLaterValid
    ,Case When Count(CreditScore)=2 and Max(Dateadd(dd,CreditScore,CreditScoreDate))=Max(CreditScoreDate) 
    			and Sum(CreditScore)>300 and Sum(CreditScore)<800  then -9995 
    		Else null End as TwoScoresLATER0andEarlierValid
    ,Case When Count(CreditScore)=2 and Sum(CreditScore)>=0  then -9996
    		Else null End as TwoScoresAndBoth0
    ,Case When Count(CreditScore)>2 and Min(CreditScore)<300 and Min(CreditScore)>0 then -9997 
    		Else null End as OneScoreNotInValidRange
    from #Candidate
    Group by EmailAddress


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    • Proposed as answer by RSingh() Friday, November 15, 2013 3:21 AM
    • Marked as answer by eva05 Friday, November 15, 2013 6:35 PM
    Friday, November 15, 2013 1:59 AM