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 10:39
Using SQL 2005

Have a table that we'll call types. Type A thru E. In a second table we have
data that includes types, date_of, and cust_ID. I need to show totals for
types A thru E for each month even if the totals are zero or null. I can't
seem to get be able to show this.

Types Jun Jul Aug Sep Oct Nov Dec
A 10 10 10 10 10 10 10
B 10 10 11 12 13 14 15
C 10 11 12 13 14 15 16
D 12 13 14 15 16 17
E 12 10 11 12 11 11 12

If Type D doesn't have any data for Jun then it doesn't show the row D in
order. The Types column need to remain constant whether there is data for the
month or not. I have used NULLIF as an attempt to show blanks or zeros.

Select Type, Count(type) AS total
From types_table, data_table
Where data_table.date_of = DATEADD(mm, - 6, '12/01/2009') AND cust_ID =
'123'
GROUP By Type
I'd also like to know if there is a way to use Pivot to do this so I don't
have to run six separate queries?

Any help is appreciated.

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

From: Plamen Ratchev on 23 Feb 2010 12:45
Seems like your query is missing join on the type column. Here is how you can get counts for all types:

SELECT T.[type], COUNT(D.date_of) AS total
FROM types_table AS T
LEFT OUTER JOIN data_table AS D
ON T.[type] = D.[type]
AND D.date_of = DATEADD(mm, - 6, '20091201')
AND cust_ID = '123'
GROUP BY T.[type];

Here is example to pivot 3 months, you can expand to 6:

SELECT T.[type],
COUNT(CASE WHEN D.date_of >= '20090601' AND D.date_of < '20090701' THEN 1 END) AS Jun,
COUNT(CASE WHEN D.date_of >= '20090701' AND D.date_of < '20090801' THEN 1 END) AS Jul,
COUNT(CASE WHEN D.date_of >= '20090801' AND D.date_of < '20090901' THEN 1 END) AS Aug
FROM types_table AS T
LEFT OUTER JOIN data_table AS D
ON T.[type] = D.[type]
AND cust_ID = '123'
GROUP BY T.[type];



--
Plamen Ratchev
http://www.SQLStudio.com
From: Chamark via SQLMonster.com on 23 Feb 2010 14:55
Thanks Plamen I appreciate your help. This certainly worked.You have helped
me before. I need to start sending you $$$ You are the best!!!

Plamen Ratchev wrote:
>Seems like your query is missing join on the type column. Here is how you can get counts for all types:
>
>SELECT T.[type], COUNT(D.date_of) AS total
>FROM types_table AS T
>LEFT OUTER JOIN data_table AS D
> ON T.[type] = D.[type]
> AND D.date_of = DATEADD(mm, - 6, '20091201')
> AND cust_ID = '123'
>GROUP BY T.[type];
>
>Here is example to pivot 3 months, you can expand to 6:
>
>SELECT T.[type],
> COUNT(CASE WHEN D.date_of >= '20090601' AND D.date_of < '20090701' THEN 1 END) AS Jun,
> COUNT(CASE WHEN D.date_of >= '20090701' AND D.date_of < '20090801' THEN 1 END) AS Jul,
> COUNT(CASE WHEN D.date_of >= '20090801' AND D.date_of < '20090901' THEN 1 END) AS Aug
>FROM types_table AS T
>LEFT OUTER JOIN data_table AS D
> ON T.[type] = D.[type]
> AND cust_ID = '123'
>GROUP BY T.[type];
>

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

From: Chamark via SQLMonster.com on 23 Feb 2010 15:42
One more question. I would like to get the % of each call type from the
totals of all five call types. Doing with a calculator I add all calls and
divide the total into each call type. How would I do that in this statement.

SELECT Call_type,

Count(CASE WHEN Month = DATEADD(mm, - 5, '20091201')THEN 1 END) 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 cl_client_id_C = '725618'
WHERE Product = 'DC'
GROUP BY call_type
___________________________________________________________
Call_type Jul Aug Sep Oct Nov Dec
Indicative Data 39 32 21 48 43 28
Investment Education 6 7 2 5 5 8
Money In 28 33 20 36 34 30
Money Out 170 127 104 251 185 136
Money Reallocation (within plan) 0 2 1 0 1 2
Web/VRU Assistance 6 4 5 2 4 3








Plamen Ratchev wrote:
>Seems like your query is missing join on the type column. Here is how you can get counts for all types:
>
>SELECT T.[type], COUNT(D.date_of) AS total
>FROM types_table AS T
>LEFT OUTER JOIN data_table AS D
> ON T.[type] = D.[type]
> AND D.date_of = DATEADD(mm, - 6, '20091201')
> AND cust_ID = '123'
>GROUP BY T.[type];
>
>Here is example to pivot 3 months, you can expand to 6:
>
>SELECT T.[type],
> COUNT(CASE WHEN D.date_of >= '20090601' AND D.date_of < '20090701' THEN 1 END) AS Jun,
> COUNT(CASE WHEN D.date_of >= '20090701' AND D.date_of < '20090801' THEN 1 END) AS Jul,
> COUNT(CASE WHEN D.date_of >= '20090801' AND D.date_of < '20090901' THEN 1 END) AS Aug
>FROM types_table AS T
>LEFT OUTER JOIN data_table AS D
> ON T.[type] = D.[type]
> AND cust_ID = '123'
>GROUP BY T.[type];
>

--
Message posted via http://www.sqlmonster.com

From: Plamen Ratchev on 23 Feb 2010 16:59
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'

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: How to increment a column with varchar data type
Next: Check Contraint in Stored procedure