Conditional Counting

Question

Conditional Counting

varunkum

Default port

Points: 1419
More actions
May 11, 2011 at 3:08 pm

#389505

In UNICA.SUBS table i have Account, TRANS_DATE, TRANS_TYPE, PROD_CODE and CODE columns.
SELECT count(B.TRANS_TYPE) STARTS, B.PROD_CODE pcode, B.CODE CScode
FROM UNICA.SUBS B
WHERE B.TRANS_DATE between @startdate AND @Enddate
AND (B.TRANS_TYPE IN ('3','2')) -------------------need a condition here i guess
GROUP BY B.PROD_CODE , B.CODE
So now in B.TRANS_TYPE IN ('3','2') i want all the 3's but for 2's i have a condition that i want to check before i count the trans_type 2 i.e
For a prior trans_date there is a transaction for the same account with trans_type 1 then we wont count that particular trans_type 2
For example there is an account =102, he has
account, trans_date, trans_type
102 , 3/30/2011 , 1
102, 4/11/2011, 2---------i don't want to count this 2 in (B.TRANS_TYPE IN ('3','2'))
For example there is an account =104, he has
account, trans_date, trans_type
104, 5/9/2011, 1
104 , 4/11/2011, 2---------i want to count this 2 in (B.TRANS_TYPE IN ('3','2'))
So basically i want the SQL to check this condition each time it counts the trans_type 2 for a particular account.
for example
Check and see account =102 his his trans_type =3 count it and trans_type =2 check if he has trans_type =1 and if that is a prior date.
Someone please help me with the SQL. Thanks.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

You might want to be a little more specific as to the results that you want to return.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

I think you want to use SUM with a CASE statement. Maybe something along these lines:

SELECT SUM(CASE WHEN B.TRANS_TYPE = '2' THEN 1

WHEN B.TRANS_TYPE = '3'

AND 1 = 0 /* change "1 = 0" to your special condition */ THEN 1

ELSE 0

END) AS STARTS,

B.PROD_CODE AS pcode,

B.CODE AS CScode

FROM UNICA.SUBS B

WHERE B.TRANS_DATE BETWEEN @startdate AND @Enddate

AND (B.TRANS_TYPE IN ('3', '2'))

GROUP BY B.PROD_CODE,

B.CODE ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

toddasd SSCertifiable Points: 6298 More actions · Answer 3

You can conditionally pick that value with a sub-select:

SELECT count(B.TRANS_TYPE) STARTS, B.PROD_CODE pcode, B.CODE CScode

FROM SUBS B

WHERE B.TRANS_DATE between '1/1/2011' AND '5/1/2011'

AND (B.TRANS_TYPE = '3'

OR

B.TRANS_TYPE = (select '2' from SUBS a where a.TRANS_TYPE = '1' and a.TRANS_DATE > b.TRANS_DATE)

)

GROUP BY B.PROD_CODE , B.CODE

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 4

One approach is to use SUM(CASE...) in place of COUNT().

The trick is to make the test for an earlier Trans_Type = 1 quick. Depending on the size of your table and the frequency of the counting jobs, you may want to precalculate the max type 1 transaction for each account number. There are a number of ways you can do this. Here's a sample.

declare @sample table (account int, trans_date date, trans_type smallint)

insert into @sample

select 102, '4/30/2011',1 union all

select 102, '4/11/2011',2

-- this could also be done with filtered index or indexed view

declare @ones table (account int primary key, trans_date date, trans_type smallint)

insert into @ones

select account,MAX(trans_date), max(trans_type)

from @sample

where trans_type=1

group by account

--select * from @sample

--select * from @ones

select S.account,

SUM(case

when S.trans_type = 3 then 1

when S.trans_type = 2 and S.trans_date > O.trans_date then 1

else 0

end) as tranCount

from @sample S

left join @ones O on O.account = S.account

where S.trans_type = 3

or S.trans_type = 2

group by S.account

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

The Dixie Flatline (5/11/2011)
One approach is to use SUM(CASE...) in place of COUNT().
The trick is to make the test for an earlier Trans_Type = 1 quick. Depending on the size of your table and the frequency of the counting jobs, you may want to precalculate the max type 1 transaction for each account number.

Agreed, excellent point, I should have mentioned that in my post. This type of problem, if implemented as a SUM...CASE or a sub-select as toddasd proposed, can bring a server to it's knees depending on the size of the table and the available indexes.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Here's a solution using CROSS APPLY that builds off Dixie's idea but does not require an aggregate table to be maintained. It seems to perform well compared to the other solutions but it's impossible to know without having the actual DDL.

varunkum, if you can provide the DDL for your table including all constraints and indexes we can work towards a performant solution with you.

SELECT S.account ,

SUM(CASE WHEN S.trans_type = 3

OR (

S.trans_type = 2

AND S.trans_date > trans_date_1.max_trans_date

) THEN 1

ELSE 0

END) AS tranCount

FROM #sample S

CROSS APPLY (

SELECT MAX(trans_date) AS max_trans_date

FROM #sample

WHERE trans_type = 1

AND account = S.account

) AS trans_date_1

WHERE S.trans_type = 3

OR S.trans_type = 2

GROUP BY S.account ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

varunkum (5/11/2011)
In UNICA.SUBS table i have Account, TRANS_DATE, TRANS_TYPE, PROD_CODE and CODE columns.
SELECT count(B.TRANS_TYPE) STARTS, B.PROD_CODE pcode, B.CODE CScode
FROM UNICA.SUBS B
WHERE B.TRANS_DATE between @startdate AND @Enddate
AND (B.TRANS_TYPE IN ('3','2')) -------------------need a condition here i guess
GROUP BY B.PROD_CODE , B.CODE
So now in B.TRANS_TYPE IN ('3','2') i want all the 3's but for 2's i have a condition that i want to check before i count the trans_type 2 i.e
For a prior trans_date there is a transaction for the same account with trans_type 1 then we wont count that particular trans_type 2
For example there is an account =102, he has
account, trans_date, trans_type
102 , 3/30/2011 , 1
102, 4/11/2011, 2---------i don't want to count this 2 in (B.TRANS_TYPE IN ('3','2'))
For example there is an account =104, he has
account, trans_date, trans_type
104, 5/9/2011, 1
104 , 4/11/2011, 2---------i want to count this 2 in (B.TRANS_TYPE IN ('3','2'))
So basically i want the SQL to check this condition each time it counts the trans_type 2 for a particular account.
for example
Check and see account =102 his his trans_type =3 count it and trans_type =2 check if he has trans_type =1 and if that is a prior date.
Someone please help me with the SQL. Thanks.

You CAN use a CASE statement in a COUNT...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

varunkum Default port Points: 1419 More actions · Answer 8

Thanks for the replies guyz..

CREATE TABLE DSI_SUBS_STOP_START_DRW

(

ACCOUNT NUMBER(12),

TRANS_DATE DATE,

TRANS_TYPE NUMBER,

TRANS_NAME VARCHAR2(7 BYTE),

CODE VARCHAR2(2 BYTE),

PROD_CODE VARCHAR2(4000 BYTE)

)