January 14, 2008 at 9:10 am
I'm getting wacky results, so obviously I do not know what I am doing.
I have a query which counts and sums up the count of records from one table, pretty much the same way for two fields:
Sum(CASE [MailDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [FirstLetters],
Sum(CASE [ScanDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [Scanned],
As you can see, for each field, if the field is null or zero it counts as 0, but if it has contents, it counts as 1, which is then summed up.
In the same query, I want a varchar returned that shows [Scanned]/[FirstLetters] as a percentage.
If I merely try to put [Scanned]/[FirstLetters] I always get zeroes, even if I make my whole query a subquery and do the calc in the calling query.
Here's what I have now:
CAST(CAST(((Sum(CASE [ScanDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END )
/ Sum(CASE [MailDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END)
)) AS NUMERIC(10,1)) AS VarChar(10)) +'%' AS PerCentScanned
This gives me zeroes but if I multiply everything by 100 then divide back, I get correct answers except for some rows which is totally weird.
There must be a better way????
Phil
entire SQL follows
-- =============================================
-- Author:PFM
-- Create date: 2008-1-14
-- REports Summary query (totals)
-- =============================================
ALTER PROCEDURE [dbo].[ReportSummarizeLettersbyRenewalDatewithPercent]
@brand-2 varchar(50)
AS
BEGIN
DECLARE @CHKLEN int
SET @CHKLEN = 6
SET NOCOUNT ON;
SELECT
CAST(DATEPART("yyyy", [RenewalDate]) as varchar(4)) + RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART("mm", [RenewalDate])),2)
+
CASE [MailDate1]
WHEN 0 THEN '999999'
ELSE CAST(DATEPART("yyyy", [MailDate1]) as varchar(4)) + RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART("mm", [MailDate1])),2)
END
AS RenewalDateSort,
DATENAME(Month, [RenewalDate]) + ' ' + CAST(DATEPART("yyyy",[RenewalDate]) AS VARCHAR(4)) AS [Renewal Date],
CASE [MailDate1]
WHEN NULL THEN '--not yet mailed--'
WHEN 0 THEN '--not yet mailed--'
ELSE DATENAME(Month, [MailDate1]) + ' ' + CAST(DATEPART("yyyy", [MailDate1]) AS VARCHAR(4))
END AS [Process Date],
Count(*) AS Total,
Sum(CASE [MailDate1]
WHEN NULL Then 1
WHEN 0 THEN 1
ELSE 0
END) AS [NotMailed],
Sum(CASE [MailDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [FirstLetters],
Sum(CASE [MailDate2]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [SecondLetters],
Sum(CASE [ScanDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [Scanned],
CAST(CAST(((Sum(CASE [ScanDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 100
END )
/ Sum(CASE [MailDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 100
END)
)) AS NUMERIC(10,1)) AS VarChar(10)) +'%' AS PerCentScanned,
Sum(CASE PolicyHolders.Q_PremiumChange WHEN 1 THEN 1 ELSE 0 END) AS PremiumChange
FROM PolicyHolders WHERE LEFT(BRAND, @CHKLEN) = LEFT(@Brand, @CHKLEN)
GROUP BY
CAST(DATEPART("yyyy", [RenewalDate]) as varchar(4)) + RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART("mm", [RenewalDate])),2)
+
CASE [MailDate1]
WHEN 0 THEN '999999'
ELSE CAST(DATEPART("yyyy", [MailDate1]) as varchar(4)) + RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART("mm", [MailDate1])),2)
END,
DATENAME(Month, [RenewalDate]) + ' ' + CAST(DATEPART("yyyy",[RenewalDate]) AS VARCHAR(4)) ,
CASE [MailDate1]
WHEN NULL THEN '--not yet mailed--'
WHEN 0 THEN '--not yet mailed--'
ELSE DATENAME(Month, [MailDate1]) + ' ' + CAST(DATEPART("yyyy", [MailDate1]) AS VARCHAR(4))
END
UNION
SELECT
'9999999999999999' AS RenewalDateSort,
'' AS [Renewal Date],
'Total' [Process Date],
Count(*) AS Total,
Sum(CASE [MailDate1]
WHEN NULL Then 1
WHEN 0 THEN 1
ELSE 0
END) AS [NotMailed],
Sum(CASE [MailDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [FirstLetters],
Sum(CASE [MailDate2]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [SecondLetters],
Sum(CASE [ScanDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 1
END) AS [Scanned],
CAST(CAST(((Sum(CASE [ScanDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 100
END )
/ Sum(CASE [MailDate1]
WHEN NULL Then 0
WHEN 0 THEN 0
ELSE 100
END)
)/100) AS NUMERIC(10,1)) AS VarChar(10)) +'%' AS PerCentScanned,
Sum(CASE PolicyHolders.Q_PremiumChange WHEN 1 THEN 1 ELSE 0 END) AS PremiumChange
FROM PolicyHolders WHERE LEFT(BRAND, @CHKLEN) = LEFT(@Brand, @CHKLEN)
ORDER BY RenewalDateSort DESC
END
January 14, 2008 at 9:23 am
You have to remember SQL Behavior as to mathematical operations: it will try to retain the data type it started with. So in your case - you're taking an integer, dividing it by another integer, so SQL Server is going to return an integer. By truncating your values
Try doing:
select cast(
sum(
case when isnull([MailDate1],0)=0 then 0
else 1
end
)
as decimal(18,6)) /
Sum(
CASE
WHEN isnull([ScanDate1],0)=0 Then 0
ELSE 1
END
)
By casting the numerator to something that can house a decimal value - I can now get results.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 12:54 pm
Thanks, that was exactly the clue I needed!! I couldn't figure out why it was not doing what I wanted. You also cleaned up my CASE statement as well, so here's the final result:
CAST(
ROUND(
CAST(
100*CAST((Sum(CASE WHEN ISNULL([ScanDate1], 0) = 0 THEN 0 ELSE 1 END)) AS DECIMAL(18,6))
/ (Sum(CASE WHEN ISNULL([MailDate1], 0) = 0 THEN 0 ELSE 1 END))
AS Decimal(10,1))
,1)
AS varchar(15)) + '%' AS PerCentScanned,
Thanks, Grateful Phil
January 14, 2008 at 4:57 pm
You'll get a "Divide by Zero" error if the following ever resolves to zero... plan on it...
(Sum(CASE WHEN ISNULL([MailDate1], 0) = 0 THEN 0 ELSE 1 END))
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply