October 3, 2017 at 10:21 am
Hi,
Need to add having clause as tansaction_count>1 after group by clause in below query .
Transaction_count is exist in table dbo.TABLEW
Any help here please.
=======================================================================================================
with
TABLEA
AS(
SELECT A.* FROM dbo.TABLEA A with(nolock)
JOIN dbo.TABLEC C with(nolock) ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND (A.CREDITSCORE IS NOT NULL AND A.LOANTOTRANSACTIONPRICE IS NOT NULL AND TERM IS NOT NULL AND MODELYEAR IS NOT NULL AND LOANAMOUNT IS NOT NULL AND MODELYEAR>=2008)
AND C.NAME IN (
'AL'
,'FL'
,'GA'
,'NC'
,'SC'
)
),
TABLEW
AS
(
SELECT TRANSACTIONTYPE,MONTH_YYYYMM_NBR AS SALES_MONTH
,CASE
WHEN CREDITSCORE <= 549
THEN '0-549'
END AS CREDIT_SCORE
,COUNT(1) AS TRANSACTION_COUNT
,SUM(STR_WEIGHT) AS STR_WEIGHT
FROM dbo.TABLEA A
JOIN dbo.TABLEB B with(nolock) ON A.PURCHASEDATE_SID = B.DATE_SID
JOIN dbo.TABLEC C with(nolock) ON C.ID = A.TABLEC_ID
JOIN dbo.TABLED D with(nolock) ON D.ID = A.TABLED_ID
JOIN dbo.TABLEE E on E.ID=A.TABLEE_ID
GROUP BY MONTH_YYYYMM_NBR,TRANSACTIONTYPE
,CASE
WHEN CREDITSCORE <= 549
THEN '0-549'
WHEN CREDITSCORE BETWEEN 550
AND 579
THEN '550-579'
END
)
select SALES_MONTH,
TRANSACTION_COUNT,
FORMAT((STR_WEIGHT/TOTAL_WEIGHT),'P') AS MARKET_SHARE
from dbo.TABLEW W
CROSS APPLY (SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT FROM dbo.TABLEW) T
===================================================================================
October 3, 2017 at 11:01 am
There are a couple of references in your query to dbo.TABLEW, but your second CTE is TABLEW. No amount of adding a HAVING clause is going to make the query reference your CTE. When you use a CTE, you do NOT use a dbo prefix, as that will attempt to find an actual table of that name in the dbo schema. Additionally, you reference dbo.TABLEA, which will also not reference your TABLEA CTE. I can't come up with any logic that says there's a need for a HAVING clause, but then, I don't have your data so I can't know what you know. It would appear that you first need to remove your references to dbo wherever TABLEA and TABLEW are referenced. Given the way your query is written, you will only succeed at running it if an actual dbo.TABLEA and dbo.TABLEW actually exist. Your query, however, would then not make any use of your two CTEs, as they have not been referenced at all, so their existence would be pointless, and I suspect there IS a reason.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 11:18 am
thanks for your reply steve.
here is the query.
with
TABLEA
AS(
SELECT A.* FROM dbo.TABLEA A with(nolock)
JOIN dbo.TABLEC C with(nolock) ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND (A.CREDITSCORE IS NOT NULL AND A.LOANTOTRANSACTIONPRICE IS NOT NULL AND TERM IS NOT NULL AND MODELYEAR IS NOT NULL AND LOANAMOUNT IS NOT NULL AND MODELYEAR>=2008)
AND C.NAME IN (
'AL'
,'FL'
,'GA'
,'NC'
,'SC'
)
),
TABLEW
AS
(
SELECT TRANSACTIONTYPE,MONTH_YYYYMM_NBR AS SALES_MONTH
,CASE
WHEN CREDITSCORE <= 549
THEN '0-549'
WHEN CREDITSCORE BETWEEN 550
AND 579
THEN '550-579'
WHEN CREDITSCORE BETWEEN 580
AND 599
THEN '580-599'
WHEN CREDITSCORE BETWEEN 600
AND 619
THEN '600-619'
WHEN CREDITSCORE BETWEEN 620
AND 639
THEN '620-639'
WHEN CREDITSCORE BETWEEN 640
AND 659
THEN '640-659'
WHEN CREDITSCORE BETWEEN 660
AND 679
THEN '660-679'
WHEN CREDITSCORE BETWEEN 680
AND 699
THEN '680-699'
WHEN CREDITSCORE BETWEEN 700
AND 719
THEN '700-719'
WHEN CREDITSCORE BETWEEN 720
AND 739
THEN '720-739'
WHEN CREDITSCORE BETWEEN 740
AND 759
THEN '740-759'
WHEN CREDITSCORE BETWEEN 760
AND 779
THEN '760-779'
WHEN CREDITSCORE >= 780
THEN '780+'
END AS CREDIT_SCORE
,CASE
WHEN LOANTOTRANSACTIONPRICE <= 79.999999999999
THEN '0-79'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 80
AND 89.999999999999
THEN '80-89'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 90
AND 99.999999999999
THEN '90-99'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 100
AND 109.999999999999
THEN '100-109'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 110
AND 119.999999999999
THEN '110-119'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 120
AND 129.999999999999
THEN '120-129'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 130
AND 139.999999999999
THEN '130-139'
WHEN LOANTOTRANSACTIONPRICE >= 140
THEN '140+'
END AS LOAN_TO_VALUE
,CASE
WHEN TERM <= 48
THEN '0-48'
WHEN TERM BETWEEN 49
AND 63
THEN '49-63'
WHEN TERM BETWEEN 64
AND 75
THEN '64-75'
WHEN TERM >= 76
THEN '76+'
END AS TERM
,CASE
WHEN MODELYEAR BETWEEN 2008
AND 2010
THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011
AND 2013
THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014
AND 2016
THEN '2014-2016'
WHEN MODELYEAR >= 2017
THEN '2017+'
END AS MODEL_YEAR
,C.NAME AS STATES
,CASE
WHEN LOANAMOUNT <= 14999.99
THEN '<15K'
WHEN LOANAMOUNT BETWEEN 15000
AND 19999.99
THEN '15-20K'
WHEN LOANAMOUNT BETWEEN 20000
AND 24999.99
THEN '20-25K'
WHEN LOANAMOUNT BETWEEN 25000
AND 29999.99
THEN '25-30K'
WHEN LOANAMOUNT BETWEEN 30000
AND 34999.99
THEN '30-35K'
WHEN LOANAMOUNT BETWEEN 35000
AND 39999.99
THEN '35-40K'
WHEN LOANAMOUNT BETWEEN 40000
AND 44999.99
THEN '40-45K'
WHEN LOANAMOUNT BETWEEN 45000
AND 49999.99
THEN '45-50K'
WHEN LOANAMOUNT BETWEEN 50000
AND 74999.99
THEN '50-75K'
WHEN LOANAMOUNT >= 75000
THEN '75K+'
END AS AMOUNTFINANCED
,CASE
WHEN D.ID = 101430
THEN 'World Omni'
ELSE 'Non-World Omni'
END AS PROVIDER
,CASE WHEN E.NAME LIKE 'To%'
THEN 'Toyota'
ELSE 'Non-Toyota'
END AS MAKE
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.LOANAMOUNT IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.LOANAMOUNT IS NOT NULL
THEN a.LOANAMOUNT * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.LOANAMOUNT IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS AMOUNT_FINANCED
,FORMAT(CASE
WHEN sum(CASE
WHEN a.APR IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.APR IS NOT NULL
THEN a.APR * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.APR IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END*0.01, 'P') AS [APR/IRR]
,FORMAT(CASE
WHEN sum(CASE
WHEN a.BUYRATE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.BUYRATE IS NOT NULL
THEN a.BUYRATE * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.BUYRATE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END*0.01,'P') AS BUY_RATE
,LEFT(FORMAT(CASE
WHEN sum(CASE
WHEN a.CREDITSCORE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.CREDITSCORE IS NOT NULL
THEN a.CREDITSCORE * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.CREDITSCORE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END,'N'),3) AS CREDITSCORE
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.BASEPRICE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.BASEPRICE IS NOT NULL
THEN a.BASEPRICE * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.BASEPRICE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS CONTRACT_PRICE
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.MANUFACTURERREBATE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.MANUFACTURERREBATE IS NOT NULL
THEN a.MANUFACTURERREBATE * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.MANUFACTURERREBATE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS CUSTOMER_CASH_REBATE
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.SUBPRBANKFEE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.SUBPRBANKFEE IS NOT NULL
THEN a.SUBPRBANKFEE * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.SUBPRBANKFEE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS DISCOUNT_FEE
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.RESERVEDOLLAR IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.RESERVEDOLLAR IS NOT NULL
THEN a.RESERVEDOLLAR * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.RESERVEDOLLAR IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS FINANCE_RESERVE
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.MONTHLYPAYMENT IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.MONTHLYPAYMENT IS NOT NULL
THEN a.MONTHLYPAYMENT * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.MONTHLYPAYMENT IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS MONTHLY_PAYMENT
,FORMAT(CASE
WHEN sum(CASE
WHEN a.SPREAD IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.SPREAD IS NOT NULL
THEN a.SPREAD * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.SPREAD IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END*.01,'P') AS SPREAD
,FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.TERM IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.TERM IS NOT NULL
THEN a.TERM * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.TERM IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'N') AS TERM1
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.TOTALDOWN IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.TOTALDOWN IS NOT NULL
THEN a.TOTALDOWN * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.TOTALDOWN IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS TOTAL_DOWN
,FORMAT(CASE
WHEN sum(CASE
WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL
THEN a.LOANTOTRANSACTIONPRICE * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END*0.01,'P') AS LTV
,FORMAT(CASE
WHEN sum(CASE
WHEN a.LMF IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.LMF IS NOT NULL
THEN a.LMF * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.LMF IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END,'N') AS LMF
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.RESIDUAL IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.RESIDUAL IS NOT NULL
THEN a.RESIDUAL * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.RESIDUAL IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS LEASE_RESIDUAL
,REPLACE(FORMAT(FLOOR(CASE
WHEN sum(CASE
WHEN a.MSRP IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END) = 0
THEN 0
ELSE sum(CASE
WHEN a.MSRP IS NOT NULL
THEN a.MSRP * a.STR_WEIGHT
ELSE 0
END) / sum(CASE
WHEN a.MSRP IS NOT NULL
THEN a.STR_WEIGHT
ELSE 0
END)
END),'C'),'.00','') AS MSRP
,COUNT(1) AS TRANSACTION_COUNT
,SUM(STR_WEIGHT) AS STR_WEIGHT
FROM TABLEA A
JOIN dbo.TABLEB B with(nolock) ON A.PURCHASEDATE_SID = B.DATE_SID
JOIN dbo.TABLEC C with(nolock) ON C.ID = A.TABLEC_ID
JOIN dbo.TABLED D with(nolock) ON D.ID = A.TABLED_ID
JOIN dbo.TABLEE E on E.ID=A.TABLEE_ID
GROUP BY MONTH_YYYYMM_NBR,TRANSACTIONTYPE
,CASE
WHEN CREDITSCORE <= 549
THEN '0-549'
WHEN CREDITSCORE BETWEEN 550
AND 579
THEN '550-579'
WHEN CREDITSCORE BETWEEN 580
AND 599
THEN '580-599'
WHEN CREDITSCORE BETWEEN 600
AND 619
THEN '600-619'
WHEN CREDITSCORE BETWEEN 620
AND 639
THEN '620-639'
WHEN CREDITSCORE BETWEEN 640
AND 659
THEN '640-659'
WHEN CREDITSCORE BETWEEN 660
AND 679
THEN '660-679'
WHEN CREDITSCORE BETWEEN 680
AND 699
THEN '680-699'
WHEN CREDITSCORE BETWEEN 700
AND 719
THEN '700-719'
WHEN CREDITSCORE BETWEEN 720
AND 739
THEN '720-739'
WHEN CREDITSCORE BETWEEN 740
AND 759
THEN '740-759'
WHEN CREDITSCORE BETWEEN 760
AND 779
THEN '760-779'
WHEN CREDITSCORE >= 780
THEN '780+'
END
,CASE
WHEN LOANTOTRANSACTIONPRICE <= 79.999999999999
THEN '0-79'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 80
AND 89.999999999999
THEN '80-89'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 90
AND 99.999999999999
THEN '90-99'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 100
AND 109.999999999999
THEN '100-109'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 110
AND 119.999999999999
THEN '110-119'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 120
AND 129.999999999999
THEN '120-129'
WHEN LOANTOTRANSACTIONPRICE BETWEEN 130
AND 139.999999999999
THEN '130-139'
WHEN LOANTOTRANSACTIONPRICE >= 140
THEN '140+'
END
,CASE
WHEN TERM <= 48
THEN '0-48'
WHEN TERM BETWEEN 49
AND 63
THEN '49-63'
WHEN TERM BETWEEN 64
AND 75
THEN '64-75'
WHEN TERM >= 76
THEN '76+'
END
,CASE
WHEN MODELYEAR BETWEEN 2008
AND 2010
THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011
AND 2013
THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014
AND 2016
THEN '2014-2016'
WHEN MODELYEAR >= 2017
THEN '2017+'
END
,C.NAME
,CASE
WHEN LOANAMOUNT <= 14999.99
THEN '<15K'
WHEN LOANAMOUNT BETWEEN 15000
AND 19999.99
THEN '15-20K'
WHEN LOANAMOUNT BETWEEN 20000
AND 24999.99
THEN '20-25K'
WHEN LOANAMOUNT BETWEEN 25000
AND 29999.99
THEN '25-30K'
WHEN LOANAMOUNT BETWEEN 30000
AND 34999.99
THEN '30-35K'
WHEN LOANAMOUNT BETWEEN 35000
AND 39999.99
THEN '35-40K'
WHEN LOANAMOUNT BETWEEN 40000
AND 44999.99
THEN '40-45K'
WHEN LOANAMOUNT BETWEEN 45000
AND 49999.99
THEN '45-50K'
WHEN LOANAMOUNT BETWEEN 50000
AND 74999.99
THEN '50-75K'
WHEN LOANAMOUNT >= 75000
THEN '75K+'
END
,CASE
WHEN D.ID = 101430
THEN 'World Omni'
ELSE 'Non-World Omni'
END
,CASE WHEN E.NAME LIKE 'To%'
THEN 'Toyota'
ELSE 'Non-Toyota'
END
)
select SALES_MONTH,
CASE WHEN TRANSACTIONTYPE ='D' THEN 'LOAN' WHEN TRANSACTIONTYPE ='L' THEN 'LEASE' END AS TRANSACTIONTYPE ,
CREDIT_SCORE,
LOAN_TO_VALUE,
TERM,
MODEL_YEAR,
STATES,
AMOUNTFINANCED,
PROVIDER,
MAKE,
AMOUNT_FINANCED,
[APR/IRR],
BUY_RATE,
CREDITSCORE,
CONTRACT_PRICE,
CUSTOMER_CASH_REBATE,
DISCOUNT_FEE,
FINANCE_RESERVE,
MONTHLY_PAYMENT,
SPREAD,
TERM1,
TOTAL_DOWN,
LTV,
LMF*100.00 AS LMF,
LEASE_RESIDUAL,
MSRP,
TRANSACTION_COUNT,
FORMAT((STR_WEIGHT/TOTAL_WEIGHT),'P') AS MARKET_SHARE
from TABLEW W
CROSS APPLY (SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT FROM TABLEW) T
October 3, 2017 at 12:11 pm
sgmunson - Tuesday, October 3, 2017 11:01 AMThere are a couple of references in your query to dbo.TABLEW, but your second CTE is TABLEW. No amount of adding a HAVING clause is going to make the query reference your CTE. When you use a CTE, you do NOT use a dbo prefix, as that will attempt to find an actual table of that name in the dbo schema. Additionally, you reference dbo.TABLEA, which will also not reference your TABLEA CTE. I can't come up with any logic that says there's a need for a HAVING clause, but then, I don't have your data so I can't know what you know. It would appear that you first need to remove your references to dbo wherever TABLEA and TABLEW are referenced. Given the way your query is written, you will only succeed at running it if an actual dbo.TABLEA and dbo.TABLEW actually exist. Your query, however, would then not make any use of your two CTEs, as they have not been referenced at all, so their existence would be pointless, and I suspect there IS a reason.
Steve,
I just post the query for your reference .
My requirement is to to add Having clause after group by clause .
Having Transaction_Count > 1 .
Transaction_Count column is from TABLEW .
can i get help here ???
October 3, 2017 at 12:48 pm
Not sure if this will perform better, but given the number of CASE statements, it may. Also, do you still have a need for a HAVING clause?WITH TABLEA AS (
SELECT A.*
FROM dbo.TABLEA AS A with(nolock)
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND A.CREDITSCORE IS NOT NULL
AND A.LOANTOTRANSACTIONPRICE IS NOT NULL
AND TERM IS NOT NULL
--AND MODELYEAR IS NOT NULL -- not needed as >= 2008 will force a non null value
AND LOANAMOUNT IS NOT NULL
AND MODELYEAR >= 2008
AND C.NAME IN ('AL','FL','GA','NC','SC')
),
CREDIT_SCORE_RANGES AS (
SELECT 0 AS LOW, 549 AS HIGH, '0-549' AS CREDIT__SCORE UNION ALL
SELECT 550, 579, '550-579' UNION ALL
SELECT 580, 599, '580-599' UNION ALL
SELECT 600, 619, '600-619' UNION ALL
SELECT 620, 639, '620-639' UNION ALL
SELECT 640, 659, '640-659' UNION ALL
SELECT 660, 679, '660-679' UNION ALL
SELECT 680, 699, '680-699' UNION ALL
SELECT 700, 719, '700-719' UNION ALL
SELECT 720, 739, '720-739' UNION ALL
SELECT 740, 759, '740-759' UNION ALL
SELECT 760, 779, '760-779' UNION ALL
SELECT 780, 9999, '780+'
),
LTV_RANGES AS (
SELECT 0 AS LOW, 80 AS HIGH, '0-79' AS LOAN_TO_VALUE UNION ALL
SELECT 80, 90, '80-89' UNION ALL
SELECT 90, 100, '90-99' UNION ALL
SELECT 100, 110, '100-109' UNION ALL
SELECT 110, 120, '110-119' UNION ALL
SELECT 120, 130, '120-129' UNION ALL
SELECT 130, 140, '130-139' UNION ALL
SELECT 140, 9999, '140+'
),
LOAN_AMOUNT_RANGES AS (
SELECT 0 AS LOW, 15000 AS HIGH, '<15K' AS AMOUNTFINANCED UNION ALL
SELECT 15000, 20000, '15-20K' UNION ALL
SELECT 20000, 25000, '20-25K' UNION ALL
SELECT 25000, 30000, '25-30K' UNION ALL
SELECT 30000, 35000, '30-35K' UNION ALL
SELECT 35000, 40000, '35-40K' UNION ALL
SELECT 40000, 45000, '40-45K' UNION ALL
SELECT 45000, 50000, '45-50K' UNION ALL
SELECT 50000, 75000, '50-75K' UNION ALL
SELECT 75000, 999999999, '75K+'
),
TABLEW AS (
SELECT
TRANSACTIONTYPE,
MONTH_YYYYMM_NBR AS SALES_MONTH,
CSR.CREDIT__SCORE AS CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END AS TERM,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END AS MODEL_YEAR,
C.NAME AS STATES,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END AS PROVIDER,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END AS MAKE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.LOANAMOUNT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END)
, 'C'), '.00', '') AS AMOUNT_FINANCED,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.APR IS NOT NULL THEN a.APR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS [APR/IRR],
FORMAT(
CASE
WHEN SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.BUYRATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS BUY_RATE,
LEFT(FORMAT(
CASE
WHEN SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.CREDITSCORE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N'), 3) AS CREDITSCORE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.BASEPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CONTRACT_PRICE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.MANUFACTURERREBATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CUSTOMER_CASH_REBATE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.SUBPRBANKFEE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS DISCOUNT_FEE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.RESERVEDOLLAR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS FINANCE_RESERVE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.MONTHLYPAYMENT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MONTHLY_PAYMENT,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.SPREAD * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END *.01, 'P') AS SPREAD,
FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TERM IS NOT NULL THEN a.TERM * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'N') AS TERM1,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.TOTALDOWN * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS TOTAL_DOWN,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.LOANTOTRANSACTIONPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS LTV,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LMF IS NOT NULL THEN a.LMF * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N') AS LMF,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.RESIDUAL * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS LEASE_RESIDUAL,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.MSRP * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MSRP,
COUNT(1) AS TRANSACTION_COUNT,
SUM(STR_WEIGHT) AS STR_WEIGHT
FROM TABLEA AS A
INNER JOIN dbo.TABLEB AS B with(nolock)
ON A.PURCHASEDATE_SID = B.DATE_SID
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
INNER JOIN dbo.TABLED AS D with(nolock)
ON D.ID = A.TABLED_ID
INNER JOIN dbo.TABLEE AS E
ON E.ID=A.TABLEE_ID
INNER JOIN CREDIT_SCORE_RANGES AS CSR
ON CREDIT_SCORE BETWEEN CSR.LOW AND CSR.HIGH
INNER JOIN LTV_RANGES AS LTVR
ON LOANTOTRANSACTIONPRICE >- LTVR.LOW
AND LOANTOTRANSACTIONPRICE < LTVR.HIGH
INNER JOIN LOAN_AMOUNT_RANGES AS LAR
ON LOANAMOUNT >= LAR.LOW
AND LOANAMOUNT < LAR.HIGH
GROUP BY
MONTH_YYYYMM_NBR,
TRANSACTIONTYPE,
CSR.CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END,
C.NAME,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END
HAVING COUNT(1) > 1
)
SELECT SALES_MONTH,
CASE WHEN TRANSACTIONTYPE ='D' THEN 'LOAN' WHEN TRANSACTIONTYPE ='L' THEN 'LEASE' END AS TRANSACTIONTYPE,
CREDIT_SCORE,
LOAN_TO_VALUE,
TERM,
MODEL_YEAR,
STATES,
AMOUNTFINANCED,
PROVIDER,
MAKE,
AMOUNT_FINANCED,
[APR/IRR],
BUY_RATE,
CREDITSCORE,
CONTRACT_PRICE,
CUSTOMER_CASH_REBATE,
DISCOUNT_FEE,
FINANCE_RESERVE,
MONTHLY_PAYMENT,
SPREAD,
TERM1,
TOTAL_DOWN,
LTV,
LMF*100.00 AS LMF,
LEASE_RESIDUAL,
MSRP,
TRANSACTION_COUNT,
FORMAT((W.STR_WEIGHT / T.TOTAL_WEIGHT), 'P') AS MARKET_SHARE
FROM TABLEW AS W
CROSS APPLY (
SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT
FROM TABLEW
) AS T;
You may need to adjust the LOW or HIGH values on the additional CTEs that I added in, to accommodate if certain values in your data exceed the values I supplied at either the lowest or highest end of each range.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 12:56 pm
Note that I added the having clause to the posted query above, rather than repost the whole shootin' match.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 1:06 pm
sgmunson - Tuesday, October 3, 2017 12:48 PMNot sure if this will perform better, but given the number of CASE statements, it may. Also, do you still have a need for a HAVING clause?WITH TABLEA AS (SELECT A.*
FROM dbo.TABLEA AS A with(nolock)
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND A.CREDITSCORE IS NOT NULL
AND A.LOANTOTRANSACTIONPRICE IS NOT NULL
AND TERM IS NOT NULL
--AND MODELYEAR IS NOT NULL -- not needed as >= 2008 will force a non null value
AND LOANAMOUNT IS NOT NULL
AND MODELYEAR >= 2008
AND C.NAME IN ('AL','FL','GA','NC','SC')
),
CREDIT_SCORE_RANGES AS (SELECT 0 AS LOW, 549 AS HIGH, '0-549' AS CREDIT__SCORE UNION ALL
SELECT 550, 579, '550-579' UNION ALL
SELECT 580, 599, '580-599' UNION ALL
SELECT 600, 619, '600-619' UNION ALL
SELECT 620, 639, '620-639' UNION ALL
SELECT 640, 659, '640-659' UNION ALL
SELECT 660, 679, '660-679' UNION ALL
SELECT 680, 699, '680-699' UNION ALL
SELECT 700, 719, '700-719' UNION ALL
SELECT 720, 739, '720-739' UNION ALL
SELECT 740, 759, '740-759' UNION ALL
SELECT 760, 779, '760-779' UNION ALL
SELECT 780, 9999, '780+'
),
LTV_RANGES AS (SELECT 0 AS LOW, 80 AS HIGH, '0-79' AS LOAN_TO_VALUE UNION ALL
SELECT 80, 90, '80-89' UNION ALL
SELECT 90, 100, '90-99' UNION ALL
SELECT 100, 110, '100-109' UNION ALL
SELECT 110, 120, '110-119' UNION ALL
SELECT 120, 130, '120-129' UNION ALL
SELECT 130, 140, '130-139' UNION ALL
SELECT 140, 9999, '140+'
),
LOAN_AMOUNT_RANGES AS (SELECT 0 AS LOW, 15000 AS HIGH, '<15K' AS AMOUNTFINANCED UNION ALL
SELECT 15000, 20000, '15-20K' UNION ALL
SELECT 20000, 25000, '20-25K' UNION ALL
SELECT 25000, 30000, '25-30K' UNION ALL
SELECT 30000, 35000, '30-35K' UNION ALL
SELECT 35000, 40000, '35-40K' UNION ALL
SELECT 40000, 45000, '40-45K' UNION ALL
SELECT 45000, 50000, '45-50K' UNION ALL
SELECT 50000, 75000, '50-75K' UNION ALL
SELECT 75000, 999999999, '75K+'
),
TABLEW AS (SELECT
TRANSACTIONTYPE,
MONTH_YYYYMM_NBR AS SALES_MONTH,
CSR.CREDIT__SCORE AS CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END AS TERM,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END AS MODEL_YEAR,
C.NAME AS STATES,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END AS PROVIDER,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END AS MAKE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.LOANAMOUNT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END)
, 'C'), '.00', '') AS AMOUNT_FINANCED,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.APR IS NOT NULL THEN a.APR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS [APR/IRR],
FORMAT(
CASE
WHEN SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.BUYRATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS BUY_RATE,
LEFT(FORMAT(
CASE
WHEN SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.CREDITSCORE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N'), 3) AS CREDITSCORE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.BASEPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CONTRACT_PRICE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.MANUFACTURERREBATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CUSTOMER_CASH_REBATE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.SUBPRBANKFEE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS DISCOUNT_FEE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.RESERVEDOLLAR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS FINANCE_RESERVE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.MONTHLYPAYMENT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MONTHLY_PAYMENT,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.SPREAD * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END *.01, 'P') AS SPREAD,
FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TERM IS NOT NULL THEN a.TERM * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'N') AS TERM1,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.TOTALDOWN * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS TOTAL_DOWN,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.LOANTOTRANSACTIONPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS LTV,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LMF IS NOT NULL THEN a.LMF * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N') AS LMF,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.RESIDUAL * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS LEASE_RESIDUAL,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.MSRP * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MSRP,
COUNT(1) AS TRANSACTION_COUNT,
SUM(STR_WEIGHT) AS STR_WEIGHT
FROM TABLEA AS A
INNER JOIN dbo.TABLEB AS B with(nolock)
ON A.PURCHASEDATE_SID = B.DATE_SID
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
INNER JOIN dbo.TABLED AS D with(nolock)
ON D.ID = A.TABLED_ID
INNER JOIN dbo.TABLEE AS E
ON E.ID=A.TABLEE_ID
INNER JOIN CREDIT_SCORE_RANGES AS CSR
ON CREDIT_SCORE BETWEEN CSR.LOW AND CSR.HIGH
INNER JOIN LTV_RANGES AS LTVR
ON LOANTOTRANSACTIONPRICE >- LTVR.LOW
AND LOANTOTRANSACTIONPRICE < LTVR.HIGH
INNER JOIN LOAN_AMOUNT_RANGES AS LAR
ON LOANAMOUNT >= LAR.LOW
AND LOANAMOUNT < LAR.HIGH
GROUP BY
MONTH_YYYYMM_NBR,
TRANSACTIONTYPE,
CSR.CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END,
C.NAME,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END
HAVING COUNT(1) > 1
)
SELECT SALES_MONTH,
CASE WHEN TRANSACTIONTYPE ='D' THEN 'LOAN' WHEN TRANSACTIONTYPE ='L' THEN 'LEASE' END AS TRANSACTIONTYPE,
CREDIT_SCORE,
LOAN_TO_VALUE,
TERM,
MODEL_YEAR,
STATES,
AMOUNTFINANCED,
PROVIDER,
MAKE,
AMOUNT_FINANCED,
[APR/IRR],
BUY_RATE,
CREDITSCORE,
CONTRACT_PRICE,
CUSTOMER_CASH_REBATE,
DISCOUNT_FEE,
FINANCE_RESERVE,
MONTHLY_PAYMENT,
SPREAD,
TERM1,
TOTAL_DOWN,
LTV,
LMF*100.00 AS LMF,
LEASE_RESIDUAL,
MSRP,
TRANSACTION_COUNT,
FORMAT((W.STR_WEIGHT / T.TOTAL_WEIGHT), 'P') AS MARKET_SHARE
FROM TABLEW AS W
CROSS APPLY (
SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT
FROM TABLEW
) AS T;You may need to adjust the LOW or HIGH values on the additional CTEs that I added in, to accommodate if certain values in your data exceed the values I supplied at either the lowest or highest end of each range.
Thank you Steve .
But i still need the having clause .
is that possible to include in the query ?
October 3, 2017 at 1:12 pm
adisql - Tuesday, October 3, 2017 1:06 PMsgmunson - Tuesday, October 3, 2017 12:48 PMNot sure if this will perform better, but given the number of CASE statements, it may. Also, do you still have a need for a HAVING clause?WITH TABLEA AS (SELECT A.*
FROM dbo.TABLEA AS A with(nolock)
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND A.CREDITSCORE IS NOT NULL
AND A.LOANTOTRANSACTIONPRICE IS NOT NULL
AND TERM IS NOT NULL
--AND MODELYEAR IS NOT NULL -- not needed as >= 2008 will force a non null value
AND LOANAMOUNT IS NOT NULL
AND MODELYEAR >= 2008
AND C.NAME IN ('AL','FL','GA','NC','SC')
),
CREDIT_SCORE_RANGES AS (SELECT 0 AS LOW, 549 AS HIGH, '0-549' AS CREDIT__SCORE UNION ALL
SELECT 550, 579, '550-579' UNION ALL
SELECT 580, 599, '580-599' UNION ALL
SELECT 600, 619, '600-619' UNION ALL
SELECT 620, 639, '620-639' UNION ALL
SELECT 640, 659, '640-659' UNION ALL
SELECT 660, 679, '660-679' UNION ALL
SELECT 680, 699, '680-699' UNION ALL
SELECT 700, 719, '700-719' UNION ALL
SELECT 720, 739, '720-739' UNION ALL
SELECT 740, 759, '740-759' UNION ALL
SELECT 760, 779, '760-779' UNION ALL
SELECT 780, 9999, '780+'
),
LTV_RANGES AS (SELECT 0 AS LOW, 80 AS HIGH, '0-79' AS LOAN_TO_VALUE UNION ALL
SELECT 80, 90, '80-89' UNION ALL
SELECT 90, 100, '90-99' UNION ALL
SELECT 100, 110, '100-109' UNION ALL
SELECT 110, 120, '110-119' UNION ALL
SELECT 120, 130, '120-129' UNION ALL
SELECT 130, 140, '130-139' UNION ALL
SELECT 140, 9999, '140+'
),
LOAN_AMOUNT_RANGES AS (SELECT 0 AS LOW, 15000 AS HIGH, '<15K' AS AMOUNTFINANCED UNION ALL
SELECT 15000, 20000, '15-20K' UNION ALL
SELECT 20000, 25000, '20-25K' UNION ALL
SELECT 25000, 30000, '25-30K' UNION ALL
SELECT 30000, 35000, '30-35K' UNION ALL
SELECT 35000, 40000, '35-40K' UNION ALL
SELECT 40000, 45000, '40-45K' UNION ALL
SELECT 45000, 50000, '45-50K' UNION ALL
SELECT 50000, 75000, '50-75K' UNION ALL
SELECT 75000, 999999999, '75K+'
),
TABLEW AS (SELECT
TRANSACTIONTYPE,
MONTH_YYYYMM_NBR AS SALES_MONTH,
CSR.CREDIT__SCORE AS CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END AS TERM,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END AS MODEL_YEAR,
C.NAME AS STATES,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END AS PROVIDER,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END AS MAKE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.LOANAMOUNT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END)
, 'C'), '.00', '') AS AMOUNT_FINANCED,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.APR IS NOT NULL THEN a.APR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS [APR/IRR],
FORMAT(
CASE
WHEN SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.BUYRATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS BUY_RATE,
LEFT(FORMAT(
CASE
WHEN SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.CREDITSCORE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N'), 3) AS CREDITSCORE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.BASEPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CONTRACT_PRICE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.MANUFACTURERREBATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CUSTOMER_CASH_REBATE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.SUBPRBANKFEE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS DISCOUNT_FEE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.RESERVEDOLLAR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS FINANCE_RESERVE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.MONTHLYPAYMENT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MONTHLY_PAYMENT,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.SPREAD * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END *.01, 'P') AS SPREAD,
FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TERM IS NOT NULL THEN a.TERM * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'N') AS TERM1,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.TOTALDOWN * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS TOTAL_DOWN,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.LOANTOTRANSACTIONPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS LTV,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LMF IS NOT NULL THEN a.LMF * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N') AS LMF,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.RESIDUAL * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS LEASE_RESIDUAL,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.MSRP * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MSRP,
COUNT(1) AS TRANSACTION_COUNT,
SUM(STR_WEIGHT) AS STR_WEIGHT
FROM TABLEA AS A
INNER JOIN dbo.TABLEB AS B with(nolock)
ON A.PURCHASEDATE_SID = B.DATE_SID
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
INNER JOIN dbo.TABLED AS D with(nolock)
ON D.ID = A.TABLED_ID
INNER JOIN dbo.TABLEE AS E
ON E.ID=A.TABLEE_ID
INNER JOIN CREDIT_SCORE_RANGES AS CSR
ON CREDIT_SCORE BETWEEN CSR.LOW AND CSR.HIGH
INNER JOIN LTV_RANGES AS LTVR
ON LOANTOTRANSACTIONPRICE >- LTVR.LOW
AND LOANTOTRANSACTIONPRICE < LTVR.HIGH
INNER JOIN LOAN_AMOUNT_RANGES AS LAR
ON LOANAMOUNT >= LAR.LOW
AND LOANAMOUNT < LAR.HIGH
GROUP BY
MONTH_YYYYMM_NBR,
TRANSACTIONTYPE,
CSR.CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END,
C.NAME,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END
HAVING COUNT(1) > 1
)
SELECT SALES_MONTH,
CASE WHEN TRANSACTIONTYPE ='D' THEN 'LOAN' WHEN TRANSACTIONTYPE ='L' THEN 'LEASE' END AS TRANSACTIONTYPE,
CREDIT_SCORE,
LOAN_TO_VALUE,
TERM,
MODEL_YEAR,
STATES,
AMOUNTFINANCED,
PROVIDER,
MAKE,
AMOUNT_FINANCED,
[APR/IRR],
BUY_RATE,
CREDITSCORE,
CONTRACT_PRICE,
CUSTOMER_CASH_REBATE,
DISCOUNT_FEE,
FINANCE_RESERVE,
MONTHLY_PAYMENT,
SPREAD,
TERM1,
TOTAL_DOWN,
LTV,
LMF*100.00 AS LMF,
LEASE_RESIDUAL,
MSRP,
TRANSACTION_COUNT,
FORMAT((W.STR_WEIGHT / T.TOTAL_WEIGHT), 'P') AS MARKET_SHARE
FROM TABLEW AS W
CROSS APPLY (
SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT
FROM TABLEW
) AS T;You may need to adjust the LOW or HIGH values on the additional CTEs that I added in, to accommodate if certain values in your data exceed the values I supplied at either the lowest or highest end of each range.
Thank you Steve .
But i still need the having clause .
is that possible to include in the query ?
It's in the query already, as I edited the post to include it rather than re-post that rather long query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 1:36 pm
sgmunson - Tuesday, October 3, 2017 1:12 PMadisql - Tuesday, October 3, 2017 1:06 PMsgmunson - Tuesday, October 3, 2017 12:48 PMNot sure if this will perform better, but given the number of CASE statements, it may. Also, do you still have a need for a HAVING clause?WITH TABLEA AS (SELECT A.*
FROM dbo.TABLEA AS A with(nolock)
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND A.CREDITSCORE IS NOT NULL
AND A.LOANTOTRANSACTIONPRICE IS NOT NULL
AND TERM IS NOT NULL
--AND MODELYEAR IS NOT NULL -- not needed as >= 2008 will force a non null value
AND LOANAMOUNT IS NOT NULL
AND MODELYEAR >= 2008
AND C.NAME IN ('AL','FL','GA','NC','SC')
),
CREDIT_SCORE_RANGES AS (SELECT 0 AS LOW, 549 AS HIGH, '0-549' AS CREDIT__SCORE UNION ALL
SELECT 550, 579, '550-579' UNION ALL
SELECT 580, 599, '580-599' UNION ALL
SELECT 600, 619, '600-619' UNION ALL
SELECT 620, 639, '620-639' UNION ALL
SELECT 640, 659, '640-659' UNION ALL
SELECT 660, 679, '660-679' UNION ALL
SELECT 680, 699, '680-699' UNION ALL
SELECT 700, 719, '700-719' UNION ALL
SELECT 720, 739, '720-739' UNION ALL
SELECT 740, 759, '740-759' UNION ALL
SELECT 760, 779, '760-779' UNION ALL
SELECT 780, 9999, '780+'
),
LTV_RANGES AS (SELECT 0 AS LOW, 80 AS HIGH, '0-79' AS LOAN_TO_VALUE UNION ALL
SELECT 80, 90, '80-89' UNION ALL
SELECT 90, 100, '90-99' UNION ALL
SELECT 100, 110, '100-109' UNION ALL
SELECT 110, 120, '110-119' UNION ALL
SELECT 120, 130, '120-129' UNION ALL
SELECT 130, 140, '130-139' UNION ALL
SELECT 140, 9999, '140+'
),
LOAN_AMOUNT_RANGES AS (SELECT 0 AS LOW, 15000 AS HIGH, '<15K' AS AMOUNTFINANCED UNION ALL
SELECT 15000, 20000, '15-20K' UNION ALL
SELECT 20000, 25000, '20-25K' UNION ALL
SELECT 25000, 30000, '25-30K' UNION ALL
SELECT 30000, 35000, '30-35K' UNION ALL
SELECT 35000, 40000, '35-40K' UNION ALL
SELECT 40000, 45000, '40-45K' UNION ALL
SELECT 45000, 50000, '45-50K' UNION ALL
SELECT 50000, 75000, '50-75K' UNION ALL
SELECT 75000, 999999999, '75K+'
),
TABLEW AS (SELECT
TRANSACTIONTYPE,
MONTH_YYYYMM_NBR AS SALES_MONTH,
CSR.CREDIT__SCORE AS CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END AS TERM,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END AS MODEL_YEAR,
C.NAME AS STATES,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END AS PROVIDER,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END AS MAKE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.LOANAMOUNT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END)
, 'C'), '.00', '') AS AMOUNT_FINANCED,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.APR IS NOT NULL THEN a.APR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS [APR/IRR],
FORMAT(
CASE
WHEN SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.BUYRATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS BUY_RATE,
LEFT(FORMAT(
CASE
WHEN SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.CREDITSCORE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N'), 3) AS CREDITSCORE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.BASEPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CONTRACT_PRICE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.MANUFACTURERREBATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CUSTOMER_CASH_REBATE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.SUBPRBANKFEE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS DISCOUNT_FEE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.RESERVEDOLLAR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS FINANCE_RESERVE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.MONTHLYPAYMENT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MONTHLY_PAYMENT,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.SPREAD * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END *.01, 'P') AS SPREAD,
FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TERM IS NOT NULL THEN a.TERM * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'N') AS TERM1,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.TOTALDOWN * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS TOTAL_DOWN,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.LOANTOTRANSACTIONPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS LTV,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LMF IS NOT NULL THEN a.LMF * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N') AS LMF,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.RESIDUAL * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS LEASE_RESIDUAL,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.MSRP * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MSRP,
COUNT(1) AS TRANSACTION_COUNT,
SUM(STR_WEIGHT) AS STR_WEIGHT
FROM TABLEA AS A
INNER JOIN dbo.TABLEB AS B with(nolock)
ON A.PURCHASEDATE_SID = B.DATE_SID
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
INNER JOIN dbo.TABLED AS D with(nolock)
ON D.ID = A.TABLED_ID
INNER JOIN dbo.TABLEE AS E
ON E.ID=A.TABLEE_ID
INNER JOIN CREDIT_SCORE_RANGES AS CSR
ON CREDIT_SCORE BETWEEN CSR.LOW AND CSR.HIGH
INNER JOIN LTV_RANGES AS LTVR
ON LOANTOTRANSACTIONPRICE >- LTVR.LOW
AND LOANTOTRANSACTIONPRICE < LTVR.HIGH
INNER JOIN LOAN_AMOUNT_RANGES AS LAR
ON LOANAMOUNT >= LAR.LOW
AND LOANAMOUNT < LAR.HIGH
GROUP BY
MONTH_YYYYMM_NBR,
TRANSACTIONTYPE,
CSR.CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END,
C.NAME,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END
HAVING COUNT(1) > 1
)
SELECT SALES_MONTH,
CASE WHEN TRANSACTIONTYPE ='D' THEN 'LOAN' WHEN TRANSACTIONTYPE ='L' THEN 'LEASE' END AS TRANSACTIONTYPE,
CREDIT_SCORE,
LOAN_TO_VALUE,
TERM,
MODEL_YEAR,
STATES,
AMOUNTFINANCED,
PROVIDER,
MAKE,
AMOUNT_FINANCED,
[APR/IRR],
BUY_RATE,
CREDITSCORE,
CONTRACT_PRICE,
CUSTOMER_CASH_REBATE,
DISCOUNT_FEE,
FINANCE_RESERVE,
MONTHLY_PAYMENT,
SPREAD,
TERM1,
TOTAL_DOWN,
LTV,
LMF*100.00 AS LMF,
LEASE_RESIDUAL,
MSRP,
TRANSACTION_COUNT,
FORMAT((W.STR_WEIGHT / T.TOTAL_WEIGHT), 'P') AS MARKET_SHARE
FROM TABLEW AS W
CROSS APPLY (
SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT
FROM TABLEW
) AS T;You may need to adjust the LOW or HIGH values on the additional CTEs that I added in, to accommodate if certain values in your data exceed the values I supplied at either the lowest or highest end of each range.
Thank you Steve .
But i still need the having clause .
is that possible to include in the query ?It's in the query already, as I edited the post to include it rather than re-post that rather long query.
Thanks a lot Steve.
October 3, 2017 at 2:11 pm
Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 12:01 pm
Hi Steve,
Thanks a lot for your help.
i want to create a VIEW with this query .
is it possible ?
Thank you.
October 5, 2017 at 12:38 pm
Steve,
i am able to create view.
Thank you ..
October 6, 2017 at 10:30 am
sgmunson - Tuesday, October 3, 2017 12:48 PMNot sure if this will perform better, but given the number of CASE statements, it may. Also, do you still have a need for a HAVING clause?WITH TABLEA AS (SELECT A.*
FROM dbo.TABLEA AS A with(nolock)
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
AND A.DATEOFPURCHASE BETWEEN '05/01/2017' AND '05/31/2017'
AND A.CREDITSCORE IS NOT NULL
AND A.LOANTOTRANSACTIONPRICE IS NOT NULL
AND TERM IS NOT NULL
--AND MODELYEAR IS NOT NULL -- not needed as >= 2008 will force a non null value
AND LOANAMOUNT IS NOT NULL
AND MODELYEAR >= 2008
AND C.NAME IN ('AL','FL','GA','NC','SC')
),
CREDIT_SCORE_RANGES AS (SELECT 0 AS LOW, 549 AS HIGH, '0-549' AS CREDIT__SCORE UNION ALL
SELECT 550, 579, '550-579' UNION ALL
SELECT 580, 599, '580-599' UNION ALL
SELECT 600, 619, '600-619' UNION ALL
SELECT 620, 639, '620-639' UNION ALL
SELECT 640, 659, '640-659' UNION ALL
SELECT 660, 679, '660-679' UNION ALL
SELECT 680, 699, '680-699' UNION ALL
SELECT 700, 719, '700-719' UNION ALL
SELECT 720, 739, '720-739' UNION ALL
SELECT 740, 759, '740-759' UNION ALL
SELECT 760, 779, '760-779' UNION ALL
SELECT 780, 9999, '780+'
),
LTV_RANGES AS (SELECT 0 AS LOW, 80 AS HIGH, '0-79' AS LOAN_TO_VALUE UNION ALL
SELECT 80, 90, '80-89' UNION ALL
SELECT 90, 100, '90-99' UNION ALL
SELECT 100, 110, '100-109' UNION ALL
SELECT 110, 120, '110-119' UNION ALL
SELECT 120, 130, '120-129' UNION ALL
SELECT 130, 140, '130-139' UNION ALL
SELECT 140, 9999, '140+'
),
LOAN_AMOUNT_RANGES AS (SELECT 0 AS LOW, 15000 AS HIGH, '<15K' AS AMOUNTFINANCED UNION ALL
SELECT 15000, 20000, '15-20K' UNION ALL
SELECT 20000, 25000, '20-25K' UNION ALL
SELECT 25000, 30000, '25-30K' UNION ALL
SELECT 30000, 35000, '30-35K' UNION ALL
SELECT 35000, 40000, '35-40K' UNION ALL
SELECT 40000, 45000, '40-45K' UNION ALL
SELECT 45000, 50000, '45-50K' UNION ALL
SELECT 50000, 75000, '50-75K' UNION ALL
SELECT 75000, 999999999, '75K+'
),
TABLEW AS (SELECT
TRANSACTIONTYPE,
MONTH_YYYYMM_NBR AS SALES_MONTH,
CSR.CREDIT__SCORE AS CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END AS TERM,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END AS MODEL_YEAR,
C.NAME AS STATES,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END AS PROVIDER,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END AS MAKE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.LOANAMOUNT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END)
, 'C'), '.00', '') AS AMOUNT_FINANCED,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.APR IS NOT NULL THEN a.APR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.APR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS [APR/IRR],
FORMAT(
CASE
WHEN SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.BUYRATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BUYRATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS BUY_RATE,
LEFT(FORMAT(
CASE
WHEN SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.CREDITSCORE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.CREDITSCORE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N'), 3) AS CREDITSCORE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.BASEPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.BASEPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CONTRACT_PRICE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.MANUFACTURERREBATE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MANUFACTURERREBATE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS CUSTOMER_CASH_REBATE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.SUBPRBANKFEE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SUBPRBANKFEE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS DISCOUNT_FEE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.RESERVEDOLLAR * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESERVEDOLLAR IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS FINANCE_RESERVE,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.MONTHLYPAYMENT * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MONTHLYPAYMENT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MONTHLY_PAYMENT,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.SPREAD * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.SPREAD IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END *.01, 'P') AS SPREAD,
FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TERM IS NOT NULL THEN a.TERM * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TERM IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'N') AS TERM1,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.TOTALDOWN * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.TOTALDOWN IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS TOTAL_DOWN,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.LOANTOTRANSACTIONPRICE * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LOANTOTRANSACTIONPRICE IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END * 0.01, 'P') AS LTV,
FORMAT(
CASE
WHEN SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.LMF IS NOT NULL THEN a.LMF * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.LMF IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END, 'N') AS LMF,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.RESIDUAL * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.RESIDUAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS LEASE_RESIDUAL,
REPLACE(FORMAT(FLOOR(
CASE
WHEN SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.MSRP * a.STR_WEIGHT ELSE 0 END) /
SUM(CASE WHEN a.MSRP IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END)
END), 'C'), '.00', '') AS MSRP,
COUNT(1) AS TRANSACTION_COUNT,
SUM(STR_WEIGHT) AS STR_WEIGHT
FROM TABLEA AS A
INNER JOIN dbo.TABLEB AS B with(nolock)
ON A.PURCHASEDATE_SID = B.DATE_SID
INNER JOIN dbo.TABLEC AS C with(nolock)
ON C.ID = A.TABLEC_ID
INNER JOIN dbo.TABLED AS D with(nolock)
ON D.ID = A.TABLED_ID
INNER JOIN dbo.TABLEE AS E
ON E.ID=A.TABLEE_ID
INNER JOIN CREDIT_SCORE_RANGES AS CSR
ON CREDIT_SCORE BETWEEN CSR.LOW AND CSR.HIGH
INNER JOIN LTV_RANGES AS LTVR
ON LOANTOTRANSACTIONPRICE >- LTVR.LOW
AND LOANTOTRANSACTIONPRICE < LTVR.HIGH
INNER JOIN LOAN_AMOUNT_RANGES AS LAR
ON LOANAMOUNT >= LAR.LOW
AND LOANAMOUNT < LAR.HIGH
GROUP BY
MONTH_YYYYMM_NBR,
TRANSACTIONTYPE,
CSR.CREDIT_SCORE,
LTVR.LOAN_TO_VALUE,
CASE
WHEN TERM <= 48 THEN '0-48'
WHEN TERM BETWEEN 49 AND 63 THEN '49-63'
WHEN TERM BETWEEN 64 AND 75 THEN '64-75'
WHEN TERM >= 76 THEN '76+'
END,
CASE
WHEN MODELYEAR BETWEEN 2008 AND 2010 THEN '2008-2010'
WHEN MODELYEAR BETWEEN 2011 AND 2013 THEN '2011-2013'
WHEN MODELYEAR BETWEEN 2014 AND 2016 THEN '2014-2016'
WHEN MODELYEAR >= 2017 THEN '2017+'
END,
C.NAME,
LAR.AMOUNTFINANCED,
CASE WHEN D.ID = 101430 THEN 'World Omni' ELSE 'Non-World Omni' END,
CASE WHEN E.NAME LIKE 'To%' THEN 'Toyota' ELSE 'Non-Toyota' END
HAVING COUNT(1) > 1
)
SELECT SALES_MONTH,
CASE WHEN TRANSACTIONTYPE ='D' THEN 'LOAN' WHEN TRANSACTIONTYPE ='L' THEN 'LEASE' END AS TRANSACTIONTYPE,
CREDIT_SCORE,
LOAN_TO_VALUE,
TERM,
MODEL_YEAR,
STATES,
AMOUNTFINANCED,
PROVIDER,
MAKE,
AMOUNT_FINANCED,
[APR/IRR],
BUY_RATE,
CREDITSCORE,
CONTRACT_PRICE,
CUSTOMER_CASH_REBATE,
DISCOUNT_FEE,
FINANCE_RESERVE,
MONTHLY_PAYMENT,
SPREAD,
TERM1,
TOTAL_DOWN,
LTV,
LMF*100.00 AS LMF,
LEASE_RESIDUAL,
MSRP,
TRANSACTION_COUNT,
FORMAT((W.STR_WEIGHT / T.TOTAL_WEIGHT), 'P') AS MARKET_SHARE
FROM TABLEW AS W
CROSS APPLY (
SELECT SUM(STR_WEIGHT) AS TOTAL_WEIGHT
FROM TABLEW
) AS T;You may need to adjust the LOW or HIGH values on the additional CTEs that I added in, to accommodate if certain values in your data exceed the values I supplied at either the lowest or highest end of each range.
Steve,
Thanks a lot for your help.
Actually as per my result data verification , It looks to me like a record with a LTV of 90 would be represented in two groups and also a loan amount of 20000 to be counted twice.
Any help to overcome this issue ?
LTV_RANGES AS (
SELECT 0 AS LOW, 80 AS HIGH, '0-79' AS LOAN_TO_VALUE UNION ALL
SELECT 80, 90, '80-89' UNION ALL
SELECT 90, 100, '90-99' UNION ALL
SELECT 100, 110, '100-109' UNION ALL
SELECT 110, 120, '110-119' UNION ALL
SELECT 120, 130, '120-129' UNION ALL
SELECT 130, 140, '130-139' UNION ALL
SELECT 140, 9999, '140+'
),
LOAN_AMOUNT_RANGES AS (
SELECT 0 AS LOW, 15000 AS HIGH, '<15K' AS AMOUNTFINANCED UNION ALL
SELECT 15000, 20000, '15-20K' UNION ALL
SELECT 20000, 25000, '20-25K' UNION ALL
SELECT 25000, 30000, '25-30K' UNION ALL
SELECT 30000, 35000, '30-35K' UNION ALL
SELECT 35000, 40000, '35-40K' UNION ALL
SELECT 40000, 45000, '40-45K' UNION ALL
SELECT 45000, 50000, '45-50K' UNION ALL
SELECT 50000, 75000, '50-75K' UNION ALL
SELECT 75000, 999999999, '75K+'
October 6, 2017 at 10:44 am
Take a look at the query. Note that the joins do >= LOW and < HIGH. There should not be any way for a value like 90 to appear in two different categories with a JOIN like that.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 11:19 am
sgmunson - Friday, October 6, 2017 10:44 AMTake a look at the query. Note that the joins do >= LOW and < HIGH. There should not be any way for a value like 90 to appear in two different categories with a JOIN like that.
yes that the joins do >= LOW and < HIGH.
But I am looking at grouping and it appears we have overlap.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply