Prev: How to increment a column with varchar data type
Next: Check Contraint in Stored procedure
From: Chamark via SQLMonster.com on 23 Feb 2010 17:35
You are quite a teacher - thanks. I tried your suggestion and my numbers were
off for some reason - I then did it the hard way (see below line) and it
worked - Also is there a way to pass the month/YR in the AS instead of hard
coding it? I really did't mean to take up this much of your resources - you
really need to send me your address so I can send you $$$ for all your help.
Do you contract or tutor for a fee? Thanks again!!!

SELECT Call_type,

Count(CASE WHEN Month = DATEADD(mm, - 5, '20091201')THEN 1 END)*1.0 /
NULLIF(COUNT([Month]), 0) AS Jul,
Count(CASE WHEN Month = DATEADD(mm, - 4, '20091201')THEN 1 END) AS Aug,
Count(CASE WHEN Month = DATEADD(mm, - 3, '20091201')THEN 1 END) AS Sep,
Count(CASE WHEN Month = DATEADD(mm, - 2, '20091201')THEN 1 END) AS Oct,
Count(CASE WHEN Month = DATEADD(mm, - 1, '20091201')THEN 1 END) AS Nov,
Count(CASE WHEN Month = '20091201' THEN 1 END) AS Dec


FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month Between DATEADD(mm, - 5, '20091201') AND '20091201'
AND cl_client_id_C = '725618'
WHERE Product = 'DC'
GROUP BY call_type
________________________________________________________________

SELECT Call_type,

ROUND(Count(CASE WHEN Month = DATEADD(mm, - 5, '20091201')THEN 1 END)* 1.0 *
100 /
(SELECT Count(Cl_Call_Type)
FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month = DATEADD(mm, - 5, '20091201')
AND cl_client_id_C = '725618'
WHERE Product = 'DC'),2,2) AS Jul,


ROUND(Count(CASE WHEN Month = DATEADD(mm, - 4, '20091201')THEN 1 END) * 1.0 *
100 /
(SELECT Count(Cl_Call_Type)
FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month = DATEADD(mm, - 4, '20091201')
AND cl_client_id_C = '725618'
WHERE Product = 'DC'),2,2) AS Aug,

ROUND(Count(CASE WHEN Month = DATEADD(mm, - 3, '20091201')THEN 1 END)* 1.0 *
100 /
(SELECT Count(Cl_Call_Type)
FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month = DATEADD(mm, - 3, '20091201')
AND cl_client_id_C = '725618'
WHERE Product = 'DC'),2,2) AS Sep,

ROUND(Count(CASE WHEN Month = DATEADD(mm, - 2, '20091201')THEN 1 END) * 1.0 *
100 /
(SELECT Count(Cl_Call_Type)
FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month = DATEADD(mm, - 2, '20091201')
AND cl_client_id_C = '725618'
WHERE Product = 'DC'),2,2) AS Oct,

ROUND(Count(CASE WHEN Month = DATEADD(mm, - 1, '20091201')THEN 1 END) * 1.0 *
100 /
(SELECT Count(Cl_Call_Type)
FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month = DATEADD(mm, - 1, '20091201')
AND cl_client_id_C = '725618'
WHERE Product = 'DC'),2,2) AS Nov,

ROUND(Count(CASE WHEN Month = '20091201' THEN 1 END) * 1.0 * 100 /
(SELECT Count(Cl_Call_Type)
FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type
AND Month = '20091201'
AND cl_client_id_C = '725618'
WHERE Product = 'DC'),2,2) AS Dec

FROM Client_Call_types AS T
LEFT OUTER JOIN [CSSAuto].[dbo].[T_Andrew_Call_Logger_Clean] AS D
ON T.Call_Type = D.Cl_call_type

AND cl_client_id_C = '725618'
WHERE Product = 'DC'
GROUP BY call_type












Plamen Ratchev wrote:
>Simply divide each count by the total count (example for Jun):
>
>COUNT(CASE WHEN [Month] = DATEADD(mm, - 5, '20091201') THEN 1 END) /
>NULLIF(COUNT([Month]), 0)
>
>You may need to multiply this expression by 1.0 or cast the count to DECIMAL, depending on what precision you need.
>
>To make sure the total count is correct add predicate to filter only dates in the range (in the join ON clause):
>... AND [Month] >= '20090601' AND [Month] < '20100101'
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201002/1

First  |  Prev  | 
Pages: 1 2
Prev: How to increment a column with varchar data type
Next: Check Contraint in Stored procedure