March 18, 2008 at 2:25 pm
I have the following CASE statement:
,CASE
WHEN SUM(v.PurchaseAmount) /15000 <=0 THEN 0
ELSE SUM(v.PurchaseAmount)/15000
END as 'TotalPersonalSalesCalculatedPoints'
v.PurchaseAmount is a Decimal(19,2).
What is suppose to happen is that if the PurchaseAMount is < the 0 then I just want to display a 0 in the column but it keeps giving me 0.02343 and so on. Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 2:35 pm
Not from the code snippet. How about all the code, that may help in understanding what the query is doing.
😎
March 18, 2008 at 2:37 pm
Plus, it looks like the value you have being returned is not <= 0.
March 18, 2008 at 2:43 pm
This is the code:
DECLARE @consultantID VARCHAR(20)
DECLARE @StartDt DateTime
DECLARE @EndDt DateTime
--SET @ConsultantID = @COnsultantID
--SET @StartDt = @StartDt
--SET @EndDt = @EndDt
--
SET @ConsultantID = '0000344'
SET @StartDt = '11/01/2007'
SET @EndDt = '10/31/2008'
--
select v.ConsultantID
,(Select c.firstname + ' ' + Lastname) AS ConsultantName
,v.SaleAmountLevelOne AS Mthly1stLinePORSales
,v.SaleAmountLevelTwo AS Mthly2ndLinePORSales
,v.SaleAmountLevelThree AS Mthly3rdLinePORSales
,CASE
WHEN v.PurchaseAmount <=0 THen 0
ELSE v.PurchaseAmount
END AS MthlyPORSalesLessCredits
,(v.SaleAmountLevelOne+v.SaleAmountLevelTwo+v.SaleAmountLevelThree) AS YTDTeamSalesTotal
,CASE
WHEN SUM(v.PurchaseAmount)/15000 <=0 THEN 0
ELSE SUM(v.PurchaseAmount)/15000
END as 'TotalPersonalSalesCalculatedPoints'
,v.PeriodEndDate
From Volume v
LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID
WHERE v.ConsultantID = @ConsultantID
AND v.PeriodEndDate >= @StartDt
AND v.PeriodEndDate <@EndDt
GROUP BY v.ConsultantID
,FirstName
,LastName
,v.SaleAMountLevelONe
,v.SaleAmountLevelTwo
,v.SaleAmountLevelThree
,v.PurchaseAmount
,v.PeriodEndDate
ORDER BY v.PeriodENdDate Asc
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 2:57 pm
What is suppose to happen is that if the PurchaseAMount is < the 0 then I just want to display a 0 in the column but it keeps giving me 0.02343 and so on. Any ideas
You are saying when purchaseamt / 15000 <= 0, not purchaseamt. Also, 0.02343 is greater than 0.
March 18, 2008 at 2:59 pm
I don't mean to sound condescending but...
Based on your sample data, 0.02343 , that values is NOT less than zero. Values such as -1, -3.1415 are less than zero. Typically summed values such as purchase amount (a positive quantity) will produce a positive quantity. So what exactly are you attempting? Do you want a 0 to display if the quotient is not a whole number > 1?
DAB
March 18, 2008 at 3:00 pm
I don't mean to sound condescending but...
Based on your sample data, 0.02343 , that values is NOT less than zero. Values such as -1, -3.1415 are less than zero. Typically summed values such as purchase amount (a positive quantity) will produce a positive quantity. So what exactly are you attempting? Do you want a 0 to display if the quotient is not a whole number > 1?
Yep, I agree that he probably needs to use round(v.PurchaseAmount,0).
March 18, 2008 at 3:02 pm
Exactly. If the Purchase amount is less than a whole number then I want to see a 0.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 3:05 pm
does the following work in SSMS, and if so what is the result, if not the error message.
SET @ConsultantID = '0000344'
SET @StartDt = '11/01/2007'
SET @EndDt = '10/31/2008'
--
SELECT
v.ConsultantID
-- ,c.FirstName + ' ' + LastName AS ConsultantName
,v.SaleAmountLevelOne AS Mthly1stLinePORSales
,v.SaleAmountLevelTwo AS Mthly2ndLinePORSales
,v.SaleAmountLevelThree AS Mthly3rdLinePORSales
,v.PurchaseAmount
,(v.SaleAmountLevelOne + v.SaleAmountLevelTwo + v.SaleAmountLevelThree) AS YTDTeamSalesTotal
,v.PeriodEndDate
FROM
Volume v
WHERE
v.ConsultantID = @ConsultantID
AND v.PeriodEndDate >= @StartDt
AND v.PeriodEndDate < @EndDt
GROUP BY
v.ConsultantID
-- ,c.FirstName
-- ,c.LastName
,v.SaleAMountLevelOne
,v.SaleAmountLevelTwo
,v.SaleAmountLevelThree
,v.PurchaseAmount
,v.PeriodEndDate
ORDER BY
v.PeriodEndDate Asc
March 18, 2008 at 3:12 pm
Yes it worked. Kinda of hard to read the output.
0000344 37127.3341821.8629992.62129.65108941.812007-11-30 00:00:00.000
0000344 25964.2330203.9325797.920.0081966.082007-12-31 00:00:00.000
0000344 14517.5418325.423914.60106.8436757.562008-01-31 00:00:00.000
0000344 14969.8533732.3812847.07322.5461549.302008-02-29 00:00:00.000
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 3:14 pm
Now, take out the group by clause and rerun it. does it still work, does it return the same result set?
March 18, 2008 at 3:18 pm
Still works. same resultset.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 3:20 pm
How about:
CASE
WHEN SUM(v.PurchaseAmount) /15000 <=0 THEN 0
WHEN SUM(v.PurchaseAmount) /15000 > 0 AND SUM(v.PurchaseAmount) /15000 < 1 THEN 0
ELSE SUM(v.PurchaseAmount)/15000
END as 'TotalPersonalSalesCalculatedPoints'
March 18, 2008 at 3:20 pm
Please explain this code snippet:
,CASE WHEN SUM(v.PurchaseAmount)/15000 <= 0
THEN 0
ELSE SUM(v.PurchaseAmount)/15000
END AS 'TotalPersonalSalesCalculatedPoints'
what is it supposed to accomplish?
March 18, 2008 at 3:23 pm
I think the issue resolved it self when perople reminded me that 0.23220 is not equivlent to 0.
THis is my solution:
,CASE
WHEN v.PurchaseAmount/15000 <=1 THEN 0
ELSE v.PurchaseAmount/15000
END as 'TotalPersonalSalesCalculatedPoints'
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply