August 29, 2021 at 6:07 am
Good Morning,
Trying to find out something here with this sample data below.
ISFUNDINGCUSTOMER = if a customer id has any of 4 cost fields has anynumber >= 0.01 then ISFUNDINGCUSTOMER = Y, for example Customer ID 100 has 3 records only one has 4.25 it means Custid100 is funding
ActiveFunding = IF any of CustomerStatus= " Active" and funding then Y esle N. for example cust100 Active recordes shows no payment so outcome is "N" where as custid 300 is "Y"
Can you please help me with this
Create Table Customers (CustomerID varchar(10), Subid varchar(10), cityid varchar(10), workid varchar(10), SubscriptionYear varchar(5), Cost1 varchar(10), Cost2 varchar(10), Cost3 varchar(10), NewCost4 varchar(10), CustomerStatus varchar(6))
INSERT INTO CUSTOMERS VALUES (100, 11,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (100, 11,12, NULL, '20-21','4.25','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (100, 11,12, 8762, '20-21','0.0','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (200, 21,40, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (200, 21,40, NULL, '20-21','','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (300, 251,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (300, 251,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (300, 145,12, 12 , '20-21','','3','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (400, 95,12, NULL, '20-21','','','20','','CANCEL')
INSERT INTO CUSTOMERS VALUES (400, 95,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (400, 95,12, 1030, '20-21',','10','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (400, 95,40, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (400, 95,40, 2040, '20-21','10','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (500, 105,12, NULL, '20-21','','0.0','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (500, 105,13, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (500, 105,14, 12, '20-21',','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (500, 105,4, NULL, '20-21','','','','0.0','CANCEL')
INSERT INTO CUSTOMERS VALUES (500, 105,30, 20, '20-21','0.0','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (600, 87,32, 138, '20-21','400','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (700, 17,12, 18, '20-21','110','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (800, 317,324, 66, '20-21','','','','','CANCEL')
Expecting Outcome is
CREATE TABLE OUTCOME (CUSTOMERID varchar(10), ISFUNDINGCUSTOMER bit, FundingLevel int, ActiveFunding bit)
INSERT INTO OUTCOME VALUES (100,'Y',NULL,'N')
INSERT INTO OUTCOME VALUES (200,'N',NULL,'N')
INSERT INTO OUTCOME VALUES (300,'Y',NULL,'Y')
INSERT INTO OUTCOME VALUES (400,'Y',NULL,'Y')
INSERT INTO OUTCOME VALUES (500,'N',NULL,'N')
INSERT INTO OUTCOME VALUES (600,'Y',NULL,'Y')
INSERT INTO OUTCOME VALUES (700,'Y',NULL,'N')
INSERT INTO OUTCOME VALUES (800,'N',NULL,'N')
Thank you in advance
ASiti
August 29, 2021 at 1:00 pm
The obligatory comment is regarding the problems arising from storing numeric data as varchar instead of decimal(#, #). Assuming you're stuck with the DDL you could try something like this. Also, the data had some typo issues so it's included here too
drop Table if exists #Customers;
go
Create Table #Customers (
CustomerID varchar(10),
Subid varchar(10),
cityid varchar(10),
workid varchar(10),
SubscriptionYear varchar(5),
Cost1 varchar(10),
Cost2 varchar(10),
Cost3 varchar(10),
NewCost4 varchar(10),
CustomerStatus varchar(6));
INSERT INTO #Customers VALUES
(100, 11,12, NULL, '20-21','','','','','CANCEL'),
(100, 11,12, NULL, '20-21','4.25','','','','CANCEL'),
(100, 11,12, 8762, '20-21','0.0','','','','ACTIVE'),
(200, 21,40, NULL, '20-21','','','','','CANCEL'),
(200, 21,40, NULL, '20-21','','','','','ACTIVE'),
(300, 251,12, NULL, '20-21','','','','','CANCEL'),
(300, 251,12, NULL, '20-21','','','','','CANCEL'),
(300, 145,12, 12 , '20-21','','3','','','ACTIVE'),
(400, 95,12, NULL, '20-21','','','20','','CANCEL'),
(400, 95,12, NULL, '20-21','','','','','CANCEL'),
(400, 95,12, 1030, '20-21','','10','','','ACTIVE'),
(400, 95,40, NULL, '20-21','','','','','CANCEL'),
(400, 95,40, 2040, '20-21','10','','','','ACTIVE'),
(500, 105,12, NULL, '20-21','','0.0','','','CANCEL'),
(500, 105,13, NULL, '20-21','','','','','CANCEL'),
(500, 105,14, 12, '20-21','','','','','ACTIVE'),
(500, 105,4, NULL, '20-21','','','','0.0','CANCEL'),
(500, 105,30, 20, '20-21','0.0','','','','ACTIVE'),
(600, 87,32, 138, '20-21','400','','','','ACTIVE'),
(700, 17,12, 18, '20-21','110','','','','CANCEL'),
(800, 317,324, 66, '20-21','','','','','CANCEL');
with
unq_cust_cte(CustomerID) as (
select distinct CustomerID
from #Customers),
dec_cte as (
select *
from #Customers c
cross apply (values (case when isnumeric(Cost1)=1
then cast(Cost1 as decimal(14,2))
else 0 end,
case when isnumeric(Cost2)=1
then cast(Cost2 as decimal(14,2))
else 0 end,
case when isnumeric(Cost3)=1
then cast(Cost3 as decimal(14,2))
else 0 end,
case when isnumeric(NewCost4)=1
then cast(NewCost4 as decimal(14,2))
else 0 end)) conv(dCost1, dCost2, dCost3, dCost4))
select uc.*,
case when fund.cnt>0 then 'Y' else 'N' end ISFUNDINGCUSTOMER,
null FundingLevel,
case when active.cnt>0 then 'Y' else 'N' end ActiveFunding
from unq_cust_cte uc
cross apply (select count(*)
from dec_cte cc
where uc.CustomerID=cc.CustomerID
and (cc.dCost1>=0.01
or cc.dCost2>=0.01
or cc.dCost3>=0.01
or cc.dCost4>=0.01)) fund(cnt)
cross apply (select count(*)
from dec_cte cc
where uc.CustomerID=cc.CustomerID
and cc.CustomerStatus='ACTIVE'
and (cc.dCost1>=0.01
or cc.dCost2>=0.01
or cc.dCost3>=0.01
or cc.dCost4>=0.01)) active(cnt)
order by uc.CustomerID;
CustomerID ISFUNDINGCUSTOMER FundingLevel ActiveFunding
100 Y NULL N
200 N NULL N
300 Y NULL Y
400 Y NULL Y
500 N NULL N
600 Y NULL Y
700 Y NULL N
800 N NULL N
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 29, 2021 at 2:44 pm
Thank you very much Steve. I am working on it.
Best Regards
ASiti
August 29, 2021 at 8:22 pm
On second thought, instead of individually calculating the decimal costs they could be added all together into one value 'dCost'. Also, a combination of TRY_CAST and ISNULL could be used instead of the CASE expressions to convert the cost data.
with
unq_cust_cte(CustomerID) as (
select distinct CustomerID
from #Customers),
dec_cte as (
select *
from #Customers c
cross apply (values (isnull(try_cast(Cost1 as decimal(14,2)), 0)+
isnull(try_cast(Cost2 as decimal(14,2)), 0)+
isnull(try_cast(Cost3 as decimal(14,2)), 0)+
isnull(try_cast(NewCost4 as decimal(14,2)), 0)))
conv(dCost))
select uc.*,
case when fund.cnt>0 then 'Y' else 'N' end ISFUNDINGCUSTOMER,
null FundingLevel,
case when active.cnt>0 then 'Y' else 'N' end ActiveFunding
from unq_cust_cte uc
cross apply (select count(*)
from dec_cte cc
where uc.CustomerID=cc.CustomerID
and cc.dCost>=0.01) fund(cnt)
cross apply (select count(*)
from dec_cte cc
where uc.CustomerID=cc.CustomerID
and cc.CustomerStatus='ACTIVE'
and cc.dCost>=0.01) active(cnt)
order by uc.CustomerID;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 30, 2021 at 4:39 am
This seems to be awfully over-complicated.
This is much shorter:
SELECT CustomerID,
CASE WHEN SUM(Cost1 + Cost2 + Cost3 + NewCost4) > 0 THEN 'Y' ELSE 'N' END AS Funding,
CASE WHEN SUM(
CASE WHEN CustomerStatus = 'ACTIVE' THEN Cost1 + Cost2 + Cost3 + NewCost4
ELSE 0 END
) > 0 THEN 'Y' ELSE 'N' END AS ActiveFunding
FROM (
SELECT CustomerID, CAST(Cost1 as money) Cost1, CAST(Cost2 as money) Cost2, CAST(Cost3 as money) Cost3, CAST(NewCost4 as money) NewCost4, CustomerStatus
FROM CUSTOMERS ) C
GROUP BY CustomerID
And it contains the derived table part only to correct for inappropriate data type chosen for Cost columns.
_____________
Code for TallyGenerator
August 30, 2021 at 11:16 am
All right Sergiy's correct about the CROSS APPLY's not being necessary. What I don't agree with is using MONEY (VARCHAR converts to FLOAT too fwiw) and comparing the sum to 0. Also, I never use nested subqueries; instead, it's either a common table expression or the VALUES constructor.
select c.CustomerID,
iif(sum(conv.dCost)>0.01, 'Y', 'N') Funding,
null FundingLevel,
iif(sum(iif(c.CustomerStatus='ACTIVE', conv.dCost, 0))>0.01, 'Y', 'N') ActiveFunding
from #Customers c
cross apply (values (isnull(try_cast(c.Cost1 as decimal(14,2)), 0)+
isnull(try_cast(c.Cost2 as decimal(14,2)), 0)+
isnull(try_cast(c.Cost3 as decimal(14,2)), 0)+
isnull(try_cast(c.NewCost4 as decimal(14,2)), 0)))
conv(dCost)
group by c.CustomerID;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 30, 2021 at 12:23 pm
Steve, can you please elaborate on potential issues with converting VARCHAR to money, and what role float could play in it?
_____________
Code for TallyGenerator
August 30, 2021 at 2:44 pm
Imo a type conversion to an approximate data type and a comparison to 0 are not strictly what the question asked. As you know well float and money data types are both approximations. In this particular case possibly the issues are not relevant. Flipped around, in what circumstance do you recommend to store values of US currency using the MONEY data type?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 30, 2021 at 10:23 pm
I don’t know anything about MONEY being an approximation.
can you give me a link to a source of such information, or a real life example?
as far as I know, and as it’s name suggests, MONEY data type was introduced to be uses specifically with monetary values. It happened in the US, so it was all about US currency. As far as I know, there was even a legislation mandating the use of MONEY (with 4 digits after the decimal point) in banking software to avoid certain types of rounding errors associated with DECIMAL(?,2) data type.
so, in the US it’s kinda a legal requirement to use MONEY data type in every case when working with monetary values, especially when expressed in US currency.
_____________
Code for TallyGenerator
August 30, 2021 at 10:43 pm
Here's one by Phil Factor on red-gate.com
Avoid use of the MONEY and SMALLMONEY datatypes (BP022)
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 30, 2021 at 11:31 pm
Phil is clearly wrong there.
Can you figure out his crucial mistake?
_____________
Code for TallyGenerator
August 31, 2021 at 1:51 am
He didn't check with you first before writing the article? Is it because money and smallmoney are really bigint and int? I'm pretty sure major payment API's like Stripe uses integers and Authorize.NET uses decimal
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 31, 2021 at 3:45 am
No, not because of that.
It's because of the following factors:
There are 2 rational data types in SQL Server - REAL and FLOAT. Which one to pick depend on the precision required.
Correct calculation should look like that:
DECLARE @MoneyTable TABLE (Total MONEY, Portion MONEY);
INSERT INTO @MoneyTable (Total, Portion)
VALUES
($271.0000, $199.5000), ($4639.00, $4316.00), ($8031.00, $7862.00), ($7558.00, $7081.00),
($9912.00, $9547.00), ($389.00, $179.00), ($4495.00, $4214.00), ($2844.00, $2398.00),
($265.67, $124.33), ($4936.56, $967.54);
SELECT Portion, Total,
(convert(real, Portion) / total)*100 AS Percentage
FROM @MoneyTable?
Now, you may round it to whatever scale you want, but it would be mathematically incorrect to reduce the accuracy of rationales by removing significant digits. Rational numbers must remain in rational data types.
You might see why if you modify Phil's query a bit:
DECLARE @MoneyTable TABLE (Total DECIMAL(19, 4), Portion DECIMAL(19, 4));
INSERT INTO @MoneyTable (Total, Portion)
VALUES
(271.00, 199.50), (4639.00, 4316.00), (8031.00, 7862.00), (7558.00, 7081.00),
(9912.00, 9547.00), (389.00, 179.00), (4495.00, 4214.00), (2844.00, 2398.00),
(265.67, 124.33), (4936.56, 967.54);
SELECT Portion,Total,
(Portion / Total) * 100 AS percentage,
SQL_VARIANT_PROPERTY(( Portion / total), 'basetype') AS [Base Type],
SQL_VARIANT_PROPERTY(( Portion / total), 'precision') AS [precision],
SQL_VARIANT_PROPERTY(( Portion / total), 'scale') AS [scale]
FROM @MoneyTable
?
SQL Server has to double the precision of the result comparing to the original values, just to keep the same level of accuracy.
DECLARE @MoneyTable TABLE (Total MONEY, Portion MONEY);
INSERT INTO @MoneyTable (Total, Portion)
VALUES
($271.0000, $199.5000), ($4639.00, $4316.00), ($8031.00, $7862.00), ($7558.00, $7081.00),
($9912.00, $9547.00), ($389.00, $179.00), ($4495.00, $4214.00), ($2844.00, $2398.00),
($265.67, $124.33), ($4936.56, $967.54);
SELECT Portion, Total,
SQL_VARIANT_PROPERTY(( Portion / total), 'basetype') AS [Base Type],
CONVERT(money, (convert(float, Portion) / total))*100 AS PercentageM
FROM @MoneyTable
?
As you can see, when converted from FLOAT to MONEY explicitly, the numbers are amazingly similar to the DECIMALs outcome.
According to accounting rules in the USA, floating point numbers are converted to money by truncating the excessive scale, not by rounding. There is some history behind it, but it does not really matter. That's how it is. And that's how it's implemented in SQL Server.
And it's not really a problem - it's still 4th digit of a monetary value - it must be rounded anyway before showing up in any report.
Conclusion: use correct data types for appropriate use cases.
Money for monetary values, Float for percentages, Decimal for more or less accurate representation of all sorts of values in a decimal form with specific scale and precision.
_____________
Code for TallyGenerator
September 1, 2021 at 2:05 pm
Well, more could be said about all of this. I'm sticking by what I wrote tho. Phil Factor article aside, in this particular question it seems reasonable to infer the values stored in the 'Cost1', etc. columns are US currency. US currency is of a specific scale and precision. The operation to be performed is addition (not mult/div) so the scale and precision are unaffected
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 2, 2021 at 1:20 am
Yeah, I know, flat-earth believers keep to their views no matter what proof you put in front of them.
The main point is that others who read this thread could see that your view is not supported by the facts.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply