October 9, 2017 at 6:30 am
adisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 9, 2017 at 11:47 am
sgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
October 9, 2017 at 4:04 pm
adisql - Monday, October 9, 2017 11:47 AMsgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
Hi Steve,
i am facing some issue with data comaparison with application selection query.
Below application query result gives the Transaction_count is 35 , but our query result gives Transaction_count as 36 for the same record.
I need to rearrange our query also should give the correct results like Transaction_count=36 .
Can you please help me on this.
Below is the application query selection.
select count(*) WJXBFS1,
(sum((a11.LOANAMOUNT)*(a11.STR_WEIGHT)) ) WJXBFS2,
(sum((a11.TERM)*(a11.STR_WEIGHT)) ) WJXBFS3,
(sum((a11.RESIDUAL)*(a11.STR_WEIGHT)) ) WJXBFS4,
(sum((a11.SUBPRBANKFEE)*(a11.STR_WEIGHT)) ) WJXBFS5,
(sum(Case when a11.RESIDUAL is not null then a11.STR_WEIGHT else 0 end)) WJXBFS6,
(sum(CASE WHEN a11.RESERVEDOLLAR > 0 THEN a11.RESERVEDOLLAR*a11.STR_WEIGHT ELSE NULL END)) WJXBFS7,
((sum(Case when a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS8,
sum(case when a11.CREDITSCORE is not null then a11.STR_WEIGHT else 0 end) WJXBFS9,
sum(case when a11.MONTHLYPAYMENT is not null then a11.STR_WEIGHT else 0 end) WJXBFSa,
(sum(Case when a11.TERM is not null then a11.STR_WEIGHT else 0 end)) WJXBFSb,
((sum(Case when a11.APR is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSc,
(sum(Case when (a11.LMF * 1000) is not null then a11.STR_WEIGHT else 0 end)) WJXBFSd,
(sum(((a11.LMF * 1000))*(a11.STR_WEIGHT)) ) WJXBFSe,
((sum(Case when a11.LOANTOTRANSACTIONPRICE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSf,
(sum(Case WHEN a11.SUBPRBANKFEE >0 then a11.STR_WEIGHT else 0 end)) WJXBFS10,
(sum(Case when a11.LOANAMOUNT is not null then a11.STR_WEIGHT else 0 end)) WJXBFS11,
(sum(Case when a11.TOTALDOWN is not null then a11.STR_WEIGHT else 0 end)) WJXBFS12,
(sum(Case when a11.MSRP is not null then a11.STR_WEIGHT else 0 end)) WJXBFS13,
(sum((a11.TOTALDOWN)*(a11.STR_WEIGHT)) ) WJXBFS14,
(sum(Case when a11.BASEPRICE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS15,
(sum((a11.MONTHLYPAYMENT)*(a11.STR_WEIGHT))) WJXBFS16,
(sum(((a11.APR - a11.BUYRATE))*(a11.STR_WEIGHT))) WJXBFS17,
(sum((a11.LOANTOTRANSACTIONPRICE)*(a11.STR_WEIGHT)) ) WJXBFS18,
(sum(case when a11.RESERVEDOLLAR > 0 then a11.STR_WEIGHT else NULL end)) WJXBFS19,
(sum((a11.APR)*(a11.STR_WEIGHT)) ) WJXBFS1a,
(sum(Case when a11.MANUFACTURERREBATE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS1b,
(sum((a11.MSRP)*(a11.STR_WEIGHT)) ) WJXBFS1c,
(sum((a11.BASEPRICE)*(a11.STR_WEIGHT)) ) WJXBFS1d,
(sum(a11.CREDITSCORE*a11.STR_WEIGHT)) WJXBFS1e,
(sum((a11.BUYRATE)*(a11.STR_WEIGHT)) ) WJXBFS1f,
((sum(case when a11.APR is not null and a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS20,
(sum((a11.MANUFACTURERREBATE)*(a11.STR_WEIGHT)) ) WJXBFS21
from TABLEA a11
join DIM_DATE a12
on (a11.PURCHASEDATE_SID = a12.DATE_SID)
where (a12.MONTH_FK in (305)
and a11.LOANAMOUNT between 25000 and 30000
and a11.NEWATPURCHASE in (1)
and a11.LEASEDPURCHASE in ('0')
and cast(a11.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and a11.TERM between 64 and 75
and a11.CREDITSCORE >= 780
and a11.LENDER_ID in (101430)
and a11.STATE_RETAILER_ID in (109)
and a11.MODELYEAR >= '2017')
October 9, 2017 at 4:22 pm
adisql - Monday, October 9, 2017 4:04 PMadisql - Monday, October 9, 2017 11:47 AMsgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
Hi Steve,
i am facing some issue with data comaparison with application selection query.
Below application query result gives the Transaction_count is 35 , but our query result gives Transaction_count as 36 for the same record.
I need to rearrange our query also should give the correct results like Transaction_count=36 .
Can you please help me on this.
Below is the application query selection.
select count(*) WJXBFS1,
(sum((a11.LOANAMOUNT)*(a11.STR_WEIGHT)) ) WJXBFS2,
(sum((a11.TERM)*(a11.STR_WEIGHT)) ) WJXBFS3,
(sum((a11.RESIDUAL)*(a11.STR_WEIGHT)) ) WJXBFS4,
(sum((a11.SUBPRBANKFEE)*(a11.STR_WEIGHT)) ) WJXBFS5,
(sum(Case when a11.RESIDUAL is not null then a11.STR_WEIGHT else 0 end)) WJXBFS6,
(sum(CASE WHEN a11.RESERVEDOLLAR > 0 THEN a11.RESERVEDOLLAR*a11.STR_WEIGHT ELSE NULL END)) WJXBFS7,
((sum(Case when a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS8,
sum(case when a11.CREDITSCORE is not null then a11.STR_WEIGHT else 0 end) WJXBFS9,
sum(case when a11.MONTHLYPAYMENT is not null then a11.STR_WEIGHT else 0 end) WJXBFSa,
(sum(Case when a11.TERM is not null then a11.STR_WEIGHT else 0 end)) WJXBFSb,
((sum(Case when a11.APR is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSc,
(sum(Case when (a11.LMF * 1000) is not null then a11.STR_WEIGHT else 0 end)) WJXBFSd,
(sum(((a11.LMF * 1000))*(a11.STR_WEIGHT)) ) WJXBFSe,
((sum(Case when a11.LOANTOTRANSACTIONPRICE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSf,
(sum(Case WHEN a11.SUBPRBANKFEE >0 then a11.STR_WEIGHT else 0 end)) WJXBFS10,
(sum(Case when a11.LOANAMOUNT is not null then a11.STR_WEIGHT else 0 end)) WJXBFS11,
(sum(Case when a11.TOTALDOWN is not null then a11.STR_WEIGHT else 0 end)) WJXBFS12,
(sum(Case when a11.MSRP is not null then a11.STR_WEIGHT else 0 end)) WJXBFS13,
(sum((a11.TOTALDOWN)*(a11.STR_WEIGHT)) ) WJXBFS14,
(sum(Case when a11.BASEPRICE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS15,
(sum((a11.MONTHLYPAYMENT)*(a11.STR_WEIGHT))) WJXBFS16,
(sum(((a11.APR - a11.BUYRATE))*(a11.STR_WEIGHT))) WJXBFS17,
(sum((a11.LOANTOTRANSACTIONPRICE)*(a11.STR_WEIGHT)) ) WJXBFS18,
(sum(case when a11.RESERVEDOLLAR > 0 then a11.STR_WEIGHT else NULL end)) WJXBFS19,
(sum((a11.APR)*(a11.STR_WEIGHT)) ) WJXBFS1a,
(sum(Case when a11.MANUFACTURERREBATE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS1b,
(sum((a11.MSRP)*(a11.STR_WEIGHT)) ) WJXBFS1c,
(sum((a11.BASEPRICE)*(a11.STR_WEIGHT)) ) WJXBFS1d,
(sum(a11.CREDITSCORE*a11.STR_WEIGHT)) WJXBFS1e,
(sum((a11.BUYRATE)*(a11.STR_WEIGHT)) ) WJXBFS1f,
((sum(case when a11.APR is not null and a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS20,
(sum((a11.MANUFACTURERREBATE)*(a11.STR_WEIGHT)) ) WJXBFS21
from TABLEA a11
join DIM_DATE a12
on (a11.PURCHASEDATE_SID = a12.DATE_SID)
where (a12.MONTH_FK in (305)
and a11.LOANAMOUNT between 25000 and 30000
and a11.NEWATPURCHASE in (1)
and a11.LEASEDPURCHASE in ('0')
and cast(a11.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and a11.TERM between 64 and 75
and a11.CREDITSCORE >= 780
and a11.LENDER_ID in (101430)
and a11.STATE_RETAILER_ID in (109)
and a11.MODELYEAR >= '2017')
what i mean , in our query if i use below filter then the TRANSACTION_COUNT is 36 , but it should be 35.
where
B.MONTH_FK in (305)
and A.LOANAMOUNT between 25000 and 30000
and A.NEWATPURCHASE in (1)
and A.LEASEDPURCHASE in ('0')
and cast(a.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and A.TERM between 64 and 75
and A.CREDITSCORE >= 780
and A.LENDER_ID in (101430)
and A.STATE_RETAILER_ID in (109)
and A.MODELYEAR >= '2017'
October 9, 2017 at 4:49 pm
adisql - Monday, October 9, 2017 4:22 PMadisql - Monday, October 9, 2017 4:04 PMadisql - Monday, October 9, 2017 11:47 AMsgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
Hi Steve,
i am facing some issue with data comaparison with application selection query.
Below application query result gives the Transaction_count is 35 , but our query result gives Transaction_count as 36 for the same record.
I need to rearrange our query also should give the correct results like Transaction_count=36 .
Can you please help me on this.
Below is the application query selection.
select count(*) WJXBFS1,
(sum((a11.LOANAMOUNT)*(a11.STR_WEIGHT)) ) WJXBFS2,
(sum((a11.TERM)*(a11.STR_WEIGHT)) ) WJXBFS3,
(sum((a11.RESIDUAL)*(a11.STR_WEIGHT)) ) WJXBFS4,
(sum((a11.SUBPRBANKFEE)*(a11.STR_WEIGHT)) ) WJXBFS5,
(sum(Case when a11.RESIDUAL is not null then a11.STR_WEIGHT else 0 end)) WJXBFS6,
(sum(CASE WHEN a11.RESERVEDOLLAR > 0 THEN a11.RESERVEDOLLAR*a11.STR_WEIGHT ELSE NULL END)) WJXBFS7,
((sum(Case when a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS8,
sum(case when a11.CREDITSCORE is not null then a11.STR_WEIGHT else 0 end) WJXBFS9,
sum(case when a11.MONTHLYPAYMENT is not null then a11.STR_WEIGHT else 0 end) WJXBFSa,
(sum(Case when a11.TERM is not null then a11.STR_WEIGHT else 0 end)) WJXBFSb,
((sum(Case when a11.APR is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSc,
(sum(Case when (a11.LMF * 1000) is not null then a11.STR_WEIGHT else 0 end)) WJXBFSd,
(sum(((a11.LMF * 1000))*(a11.STR_WEIGHT)) ) WJXBFSe,
((sum(Case when a11.LOANTOTRANSACTIONPRICE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSf,
(sum(Case WHEN a11.SUBPRBANKFEE >0 then a11.STR_WEIGHT else 0 end)) WJXBFS10,
(sum(Case when a11.LOANAMOUNT is not null then a11.STR_WEIGHT else 0 end)) WJXBFS11,
(sum(Case when a11.TOTALDOWN is not null then a11.STR_WEIGHT else 0 end)) WJXBFS12,
(sum(Case when a11.MSRP is not null then a11.STR_WEIGHT else 0 end)) WJXBFS13,
(sum((a11.TOTALDOWN)*(a11.STR_WEIGHT)) ) WJXBFS14,
(sum(Case when a11.BASEPRICE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS15,
(sum((a11.MONTHLYPAYMENT)*(a11.STR_WEIGHT))) WJXBFS16,
(sum(((a11.APR - a11.BUYRATE))*(a11.STR_WEIGHT))) WJXBFS17,
(sum((a11.LOANTOTRANSACTIONPRICE)*(a11.STR_WEIGHT)) ) WJXBFS18,
(sum(case when a11.RESERVEDOLLAR > 0 then a11.STR_WEIGHT else NULL end)) WJXBFS19,
(sum((a11.APR)*(a11.STR_WEIGHT)) ) WJXBFS1a,
(sum(Case when a11.MANUFACTURERREBATE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS1b,
(sum((a11.MSRP)*(a11.STR_WEIGHT)) ) WJXBFS1c,
(sum((a11.BASEPRICE)*(a11.STR_WEIGHT)) ) WJXBFS1d,
(sum(a11.CREDITSCORE*a11.STR_WEIGHT)) WJXBFS1e,
(sum((a11.BUYRATE)*(a11.STR_WEIGHT)) ) WJXBFS1f,
((sum(case when a11.APR is not null and a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS20,
(sum((a11.MANUFACTURERREBATE)*(a11.STR_WEIGHT)) ) WJXBFS21
from TABLEA a11
join DIM_DATE a12
on (a11.PURCHASEDATE_SID = a12.DATE_SID)
where (a12.MONTH_FK in (305)
and a11.LOANAMOUNT between 25000 and 30000
and a11.NEWATPURCHASE in (1)
and a11.LEASEDPURCHASE in ('0')
and cast(a11.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and a11.TERM between 64 and 75
and a11.CREDITSCORE >= 780
and a11.LENDER_ID in (101430)
and a11.STATE_RETAILER_ID in (109)
and a11.MODELYEAR >= '2017')what i mean , in our query if i use below filter then the TRANSACTION_COUNT is 36 , but it should be 35.
where
B.MONTH_FK in (305)
and A.LOANAMOUNT between 25000 and 30000
and A.NEWATPURCHASE in (1)
and A.LEASEDPURCHASE in ('0')
and cast(a.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and A.TERM between 64 and 75
and A.CREDITSCORE >= 780
and A.LENDER_ID in (101430)
and A.STATE_RETAILER_ID in (109)
and A.MODELYEAR >= '2017'
I am thinking what other columns should be added to Groupby ?
October 10, 2017 at 7:59 am
adisql - Monday, October 9, 2017 4:04 PMadisql - Monday, October 9, 2017 11:47 AMsgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
Hi Steve,
i am facing some issue with data comaparison with application selection query.
Below application query result gives the Transaction_count is 35 , but our query result gives Transaction_count as 36 for the same record.
I need to rearrange our query also should give the correct results like Transaction_count=36 .
Can you please help me on this.
Below is the application query selection.
select count(*) WJXBFS1,
(sum((a11.LOANAMOUNT)*(a11.STR_WEIGHT)) ) WJXBFS2,
(sum((a11.TERM)*(a11.STR_WEIGHT)) ) WJXBFS3,
(sum((a11.RESIDUAL)*(a11.STR_WEIGHT)) ) WJXBFS4,
(sum((a11.SUBPRBANKFEE)*(a11.STR_WEIGHT)) ) WJXBFS5,
(sum(Case when a11.RESIDUAL is not null then a11.STR_WEIGHT else 0 end)) WJXBFS6,
(sum(CASE WHEN a11.RESERVEDOLLAR > 0 THEN a11.RESERVEDOLLAR*a11.STR_WEIGHT ELSE NULL END)) WJXBFS7,
((sum(Case when a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS8,
sum(case when a11.CREDITSCORE is not null then a11.STR_WEIGHT else 0 end) WJXBFS9,
sum(case when a11.MONTHLYPAYMENT is not null then a11.STR_WEIGHT else 0 end) WJXBFSa,
(sum(Case when a11.TERM is not null then a11.STR_WEIGHT else 0 end)) WJXBFSb,
((sum(Case when a11.APR is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSc,
(sum(Case when (a11.LMF * 1000) is not null then a11.STR_WEIGHT else 0 end)) WJXBFSd,
(sum(((a11.LMF * 1000))*(a11.STR_WEIGHT)) ) WJXBFSe,
((sum(Case when a11.LOANTOTRANSACTIONPRICE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSf,
(sum(Case WHEN a11.SUBPRBANKFEE >0 then a11.STR_WEIGHT else 0 end)) WJXBFS10,
(sum(Case when a11.LOANAMOUNT is not null then a11.STR_WEIGHT else 0 end)) WJXBFS11,
(sum(Case when a11.TOTALDOWN is not null then a11.STR_WEIGHT else 0 end)) WJXBFS12,
(sum(Case when a11.MSRP is not null then a11.STR_WEIGHT else 0 end)) WJXBFS13,
(sum((a11.TOTALDOWN)*(a11.STR_WEIGHT)) ) WJXBFS14,
(sum(Case when a11.BASEPRICE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS15,
(sum((a11.MONTHLYPAYMENT)*(a11.STR_WEIGHT))) WJXBFS16,
(sum(((a11.APR - a11.BUYRATE))*(a11.STR_WEIGHT))) WJXBFS17,
(sum((a11.LOANTOTRANSACTIONPRICE)*(a11.STR_WEIGHT)) ) WJXBFS18,
(sum(case when a11.RESERVEDOLLAR > 0 then a11.STR_WEIGHT else NULL end)) WJXBFS19,
(sum((a11.APR)*(a11.STR_WEIGHT)) ) WJXBFS1a,
(sum(Case when a11.MANUFACTURERREBATE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS1b,
(sum((a11.MSRP)*(a11.STR_WEIGHT)) ) WJXBFS1c,
(sum((a11.BASEPRICE)*(a11.STR_WEIGHT)) ) WJXBFS1d,
(sum(a11.CREDITSCORE*a11.STR_WEIGHT)) WJXBFS1e,
(sum((a11.BUYRATE)*(a11.STR_WEIGHT)) ) WJXBFS1f,
((sum(case when a11.APR is not null and a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS20,
(sum((a11.MANUFACTURERREBATE)*(a11.STR_WEIGHT)) ) WJXBFS21
from TABLEA a11
join DIM_DATE a12
on (a11.PURCHASEDATE_SID = a12.DATE_SID)
where (a12.MONTH_FK in (305)
and a11.LOANAMOUNT between 25000 and 30000
and a11.NEWATPURCHASE in (1)
and a11.LEASEDPURCHASE in ('0')
and cast(a11.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and a11.TERM between 64 and 75
and a11.CREDITSCORE >= 780
and a11.LENDER_ID in (101430)
and a11.STATE_RETAILER_ID in (109)
and a11.MODELYEAR >= '2017')
Have you given any thought as to how I'm supposed to determine that 35 rows or 36 rows should come out of this query? I don't have your data. However, given your previous question about overlapping ranges, I'm noticing that you are using BETWEEN and specifying in one case 110 and 119.99999, and in another 25000 and 30000. As you saw in the previous solution, I did not use the BETWEEN keyword, and instead used >= LOW value and < HIGH value. This query doesn't appear to be following that standard. I might suggest that instead of BETWEEN 110 and 119.99999 that you instead compare that cast to be >= 110 and to be < 120, and then you don't need to use the 119.99999, which could be problematic. Similarly, you may need to re-examine your other BETWEEN ranges. Then again, you might have some other criteria incorrect. I have no way to know. Please be as specific with this problem as was needed for the previous one, as we can't see your data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 10, 2017 at 10:59 am
sgmunson - Tuesday, October 10, 2017 7:59 AMadisql - Monday, October 9, 2017 4:04 PMadisql - Monday, October 9, 2017 11:47 AMsgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
Hi Steve,
i am facing some issue with data comaparison with application selection query.
Below application query result gives the Transaction_count is 35 , but our query result gives Transaction_count as 36 for the same record.
I need to rearrange our query also should give the correct results like Transaction_count=36 .
Can you please help me on this.
Below is the application query selection.
select count(*) WJXBFS1,
(sum((a11.LOANAMOUNT)*(a11.STR_WEIGHT)) ) WJXBFS2,
(sum((a11.TERM)*(a11.STR_WEIGHT)) ) WJXBFS3,
(sum((a11.RESIDUAL)*(a11.STR_WEIGHT)) ) WJXBFS4,
(sum((a11.SUBPRBANKFEE)*(a11.STR_WEIGHT)) ) WJXBFS5,
(sum(Case when a11.RESIDUAL is not null then a11.STR_WEIGHT else 0 end)) WJXBFS6,
(sum(CASE WHEN a11.RESERVEDOLLAR > 0 THEN a11.RESERVEDOLLAR*a11.STR_WEIGHT ELSE NULL END)) WJXBFS7,
((sum(Case when a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS8,
sum(case when a11.CREDITSCORE is not null then a11.STR_WEIGHT else 0 end) WJXBFS9,
sum(case when a11.MONTHLYPAYMENT is not null then a11.STR_WEIGHT else 0 end) WJXBFSa,
(sum(Case when a11.TERM is not null then a11.STR_WEIGHT else 0 end)) WJXBFSb,
((sum(Case when a11.APR is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSc,
(sum(Case when (a11.LMF * 1000) is not null then a11.STR_WEIGHT else 0 end)) WJXBFSd,
(sum(((a11.LMF * 1000))*(a11.STR_WEIGHT)) ) WJXBFSe,
((sum(Case when a11.LOANTOTRANSACTIONPRICE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSf,
(sum(Case WHEN a11.SUBPRBANKFEE >0 then a11.STR_WEIGHT else 0 end)) WJXBFS10,
(sum(Case when a11.LOANAMOUNT is not null then a11.STR_WEIGHT else 0 end)) WJXBFS11,
(sum(Case when a11.TOTALDOWN is not null then a11.STR_WEIGHT else 0 end)) WJXBFS12,
(sum(Case when a11.MSRP is not null then a11.STR_WEIGHT else 0 end)) WJXBFS13,
(sum((a11.TOTALDOWN)*(a11.STR_WEIGHT)) ) WJXBFS14,
(sum(Case when a11.BASEPRICE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS15,
(sum((a11.MONTHLYPAYMENT)*(a11.STR_WEIGHT))) WJXBFS16,
(sum(((a11.APR - a11.BUYRATE))*(a11.STR_WEIGHT))) WJXBFS17,
(sum((a11.LOANTOTRANSACTIONPRICE)*(a11.STR_WEIGHT)) ) WJXBFS18,
(sum(case when a11.RESERVEDOLLAR > 0 then a11.STR_WEIGHT else NULL end)) WJXBFS19,
(sum((a11.APR)*(a11.STR_WEIGHT)) ) WJXBFS1a,
(sum(Case when a11.MANUFACTURERREBATE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS1b,
(sum((a11.MSRP)*(a11.STR_WEIGHT)) ) WJXBFS1c,
(sum((a11.BASEPRICE)*(a11.STR_WEIGHT)) ) WJXBFS1d,
(sum(a11.CREDITSCORE*a11.STR_WEIGHT)) WJXBFS1e,
(sum((a11.BUYRATE)*(a11.STR_WEIGHT)) ) WJXBFS1f,
((sum(case when a11.APR is not null and a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS20,
(sum((a11.MANUFACTURERREBATE)*(a11.STR_WEIGHT)) ) WJXBFS21
from TABLEA a11
join DIM_DATE a12
on (a11.PURCHASEDATE_SID = a12.DATE_SID)
where (a12.MONTH_FK in (305)
and a11.LOANAMOUNT between 25000 and 30000
and a11.NEWATPURCHASE in (1)
and a11.LEASEDPURCHASE in ('0')
and cast(a11.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and a11.TERM between 64 and 75
and a11.CREDITSCORE >= 780
and a11.LENDER_ID in (101430)
and a11.STATE_RETAILER_ID in (109)
and a11.MODELYEAR >= '2017')Have you given any thought as to how I'm supposed to determine that 35 rows or 36 rows should come out of this query? I don't have your data. However, given your previous question about overlapping ranges, I'm noticing that you are using BETWEEN and specifying in one case 110 and 119.99999, and in another 25000 and 30000. As you saw in the previous solution, I did not use the BETWEEN keyword, and instead used >= LOW value and < HIGH value. This query doesn't appear to be following that standard. I might suggest that instead of BETWEEN 110 and 119.99999 that you instead compare that cast to be >= 110 and to be < 120, and then you don't need to use the 119.99999, which could be problematic. Similarly, you may need to re-examine your other BETWEEN ranges. Then again, you might have some other criteria incorrect. I have no way to know. Please be as specific with this problem as was needed for the previous one, as we can't see your data.
sorry for confuse.
Thanks Steve.
October 10, 2017 at 3:13 pm
adisql - Tuesday, October 10, 2017 10:59 AMsgmunson - Tuesday, October 10, 2017 7:59 AMadisql - Monday, October 9, 2017 4:04 PMadisql - Monday, October 9, 2017 11:47 AMsgmunson - Monday, October 9, 2017 6:30 AMadisql - Friday, October 6, 2017 11:19 AMsgmunson - 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.
Well, ... unless you have a typo that got accidentally left in the code,ask yourself whether or not any given record can ever meet both join conditions for more than one range? Let's take 90 for example. If that value is >= to the LOW value, then the only range that can be true on is labelled 90 to 100, where the low is 90 and the high is 100. Having eliminated any other possible range solely on the LOW condition, you can still check the HIGH condition of the join, but that will be true as well. Now check that value against the next range lower (80 to 90). Does it meet the LOW conditon (>= 80) ? Yes, but it fails the < 90 condition, and thus there is no overlap. You can do this same exercise for ANY of the boundary values, and find that only one range applies.
Thank you Steve .
Hi Steve,
i am facing some issue with data comaparison with application selection query.
Below application query result gives the Transaction_count is 35 , but our query result gives Transaction_count as 36 for the same record.
I need to rearrange our query also should give the correct results like Transaction_count=36 .
Can you please help me on this.
Below is the application query selection.
select count(*) WJXBFS1,
(sum((a11.LOANAMOUNT)*(a11.STR_WEIGHT)) ) WJXBFS2,
(sum((a11.TERM)*(a11.STR_WEIGHT)) ) WJXBFS3,
(sum((a11.RESIDUAL)*(a11.STR_WEIGHT)) ) WJXBFS4,
(sum((a11.SUBPRBANKFEE)*(a11.STR_WEIGHT)) ) WJXBFS5,
(sum(Case when a11.RESIDUAL is not null then a11.STR_WEIGHT else 0 end)) WJXBFS6,
(sum(CASE WHEN a11.RESERVEDOLLAR > 0 THEN a11.RESERVEDOLLAR*a11.STR_WEIGHT ELSE NULL END)) WJXBFS7,
((sum(Case when a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS8,
sum(case when a11.CREDITSCORE is not null then a11.STR_WEIGHT else 0 end) WJXBFS9,
sum(case when a11.MONTHLYPAYMENT is not null then a11.STR_WEIGHT else 0 end) WJXBFSa,
(sum(Case when a11.TERM is not null then a11.STR_WEIGHT else 0 end)) WJXBFSb,
((sum(Case when a11.APR is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSc,
(sum(Case when (a11.LMF * 1000) is not null then a11.STR_WEIGHT else 0 end)) WJXBFSd,
(sum(((a11.LMF * 1000))*(a11.STR_WEIGHT)) ) WJXBFSe,
((sum(Case when a11.LOANTOTRANSACTIONPRICE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFSf,
(sum(Case WHEN a11.SUBPRBANKFEE >0 then a11.STR_WEIGHT else 0 end)) WJXBFS10,
(sum(Case when a11.LOANAMOUNT is not null then a11.STR_WEIGHT else 0 end)) WJXBFS11,
(sum(Case when a11.TOTALDOWN is not null then a11.STR_WEIGHT else 0 end)) WJXBFS12,
(sum(Case when a11.MSRP is not null then a11.STR_WEIGHT else 0 end)) WJXBFS13,
(sum((a11.TOTALDOWN)*(a11.STR_WEIGHT)) ) WJXBFS14,
(sum(Case when a11.BASEPRICE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS15,
(sum((a11.MONTHLYPAYMENT)*(a11.STR_WEIGHT))) WJXBFS16,
(sum(((a11.APR - a11.BUYRATE))*(a11.STR_WEIGHT))) WJXBFS17,
(sum((a11.LOANTOTRANSACTIONPRICE)*(a11.STR_WEIGHT)) ) WJXBFS18,
(sum(case when a11.RESERVEDOLLAR > 0 then a11.STR_WEIGHT else NULL end)) WJXBFS19,
(sum((a11.APR)*(a11.STR_WEIGHT)) ) WJXBFS1a,
(sum(Case when a11.MANUFACTURERREBATE is not null then a11.STR_WEIGHT else 0 end)) WJXBFS1b,
(sum((a11.MSRP)*(a11.STR_WEIGHT)) ) WJXBFS1c,
(sum((a11.BASEPRICE)*(a11.STR_WEIGHT)) ) WJXBFS1d,
(sum(a11.CREDITSCORE*a11.STR_WEIGHT)) WJXBFS1e,
(sum((a11.BUYRATE)*(a11.STR_WEIGHT)) ) WJXBFS1f,
((sum(case when a11.APR is not null and a11.BUYRATE is not null then a11.STR_WEIGHT else 0 end)) * 100.0) WJXBFS20,
(sum((a11.MANUFACTURERREBATE)*(a11.STR_WEIGHT)) ) WJXBFS21
from TABLEA a11
join DIM_DATE a12
on (a11.PURCHASEDATE_SID = a12.DATE_SID)
where (a12.MONTH_FK in (305)
and a11.LOANAMOUNT between 25000 and 30000
and a11.NEWATPURCHASE in (1)
and a11.LEASEDPURCHASE in ('0')
and cast(a11.LOANTOTRANSACTIONPRICE as decimal( 8, 2)) between 110 and 119.99999
and a11.TERM between 64 and 75
and a11.CREDITSCORE >= 780
and a11.LENDER_ID in (101430)
and a11.STATE_RETAILER_ID in (109)
and a11.MODELYEAR >= '2017')Have you given any thought as to how I'm supposed to determine that 35 rows or 36 rows should come out of this query? I don't have your data. However, given your previous question about overlapping ranges, I'm noticing that you are using BETWEEN and specifying in one case 110 and 119.99999, and in another 25000 and 30000. As you saw in the previous solution, I did not use the BETWEEN keyword, and instead used >= LOW value and < HIGH value. This query doesn't appear to be following that standard. I might suggest that instead of BETWEEN 110 and 119.99999 that you instead compare that cast to be >= 110 and to be < 120, and then you don't need to use the 119.99999, which could be problematic. Similarly, you may need to re-examine your other BETWEEN ranges. Then again, you might have some other criteria incorrect. I have no way to know. Please be as specific with this problem as was needed for the previous one, as we can't see your data.
sorry for confuse.
Thanks Steve.
Steve,
thanks a lot for your help.
desperately i need help on this.
query is fine but i think the calculation seems wrong with replace/format/foor .
here are the requirement and sample data.
any help.
thanks.
October 11, 2017 at 6:55 am
I appreciate where you're coming from, but do you understand what the FLOOR function or ROUND function do? Do a Google search on ROUND() and then another on FLOOR() and chances are, one of the top search results will be Microsoft's documentation on the function you just searched for. Read those pages and understand what they do. They will affect your resulting data. However, formatting your results should be done in a presentation layer and NOT in your query, as that kind of thing turns the data into character strings, which are then impossible to further control in a reporting tool, such as SSRS or Crystal Reports. Similarly, web pages would have difficulty with those formatted character strings. Let the presentation layer do it's job and "present" the data with formatting done there instead of using SQL to do that job. Check out the two functions and post back with any questions.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply