Counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Counts

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 09:35:37

How would I write a stored procedure to get a count of the 5W, 7W and 9W's in each column. If I make a column called Count for the first column... I would have 2 in the Count Column since a 9W and 5W are listed on the same row. Does that make sense? Do I need an occurrence?


DryCd1	DryDte1	DryFu1	DryCd2	DryDte2	DryFu2	DryCd3	DryDte3
2E	20080914  1	9W	20081023  0	 5W	        
7W	20110823  0	9W	20080924  1	          
5W	20110715  0	9W	20080920  1	  	        
5W	20090501  0	9W	20081024  0	  	        
9W	20080413  1     MR	20100326  0	 9W  20081031
MR	20100729  0	5W	20081012  0	 G7  20081004
MR	20110821  0	7W	20080928  1	 9W  20080917

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 09:41:57

on what basis you want count? by date?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 09:46:58

Yes by date.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-12-09 : 09:48:57

I assume you mean a count of 5W,7W,9W for a row checking DryCd1 and DryCDD2 and DryCd3

declare @tmp table (
DryCd1 varchar(20), DryDte1 varchar(20), DryFu1 varchar(20), DryCd2 varchar(20), DryDte2 varchar(20), DryFu2 varchar(20), DryCd3 varchar(20), DryDte3 varchar(20)
)

insert into @tmp
select '2E', '20080914', '1', '9W', '20081023', '0', '5W', '' union all
select '7W', '20110823', '0', '9W', '20080924', '1', '', '' union all
select '5W', '20110715', '0', '9W', '20080920', '1', '', '' union all
select '5W', '20090501', '0', '9W', '20081024', '0', '', '' union all
select '9W', '20080413', '1', 'MR', '20100326', '0', '9W', '20081031' union all
select 'MR', '20100729', '0', '5W', '20081012', '0', 'G7', '20081004' union all
select 'MR', '20110821', '0', '7W', '20080928', '1', '9W', '20080917'

select
*,
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end

as DryCd1_DryCd2_DryCd3_5w_7W_9W
from @tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 10:00:30

Thanks jhocutt that worked!

Can I add this to a conditional split in SSIS or should I just put this in an Execute SQL Task?

select
*,
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end

as DryCd1_DryCd2_DryCd3_5w_7W_9W
from @tmp

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-12-09 : 10:08:04

What are you trying to accomplish?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 10:24:52

I have the counts which works out find that you have given me.

If the count is 0 those are cleared records and should go into Cleared table. If the count is 1 or more then it's pending and should go into Pending Table.

Is this possible to do with a conditonal split or an Execute SQL Task and just have two destinations?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:43:59

didnt you got solution here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112991

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 10:53:31

No it wasn't giving me the correct count. It would find the first 5W, 7W, or 9W and then it would stop and not count the other one's on that row. What Jhocutt sent gives me the proper count I want to now make that case statement into a conditional split. I will try it with the Execute SQL Task and two destinations. Whew this is making my hair come out!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-12-09 : 10:56:43

[code]

declare @tmp table (
DryCd1 varchar(20), DryDte1 varchar(20), DryFu1 varchar(20), DryCd2 varchar(20), DryDte2 varchar(20), DryFu2 varchar(20), DryCd3 varchar(20), DryDte3 varchar(20)
)

declare @cleared table (
DryCd1 varchar(20), DryDte1 varchar(20), DryFu1 varchar(20), DryCd2 varchar(20), DryDte2 varchar(20), DryFu2 varchar(20), DryCd3 varchar(20), DryDte3 varchar(20), DryCd1_DryCd2_DryCd3_5w_7W_9W int
)

declare @Pending table (
DryCd1 varchar(20), DryDte1 varchar(20), DryFu1 varchar(20), DryCd2 varchar(20), DryDte2 varchar(20), DryFu2 varchar(20), DryCd3 varchar(20), DryDte3 varchar(20), DryCd1_DryCd2_DryCd3_5w_7W_9W int
)

insert into @tmp
select '2E', '20080914', '1', '9W', '20081023', '0', '5W', '' union all
select '7W', '20110823', '0', '9W', '20080924', '1', '', '' union all
select '5W', '20110715', '0', '9W', '20080920', '1', '', '' union all
select '5W', '20090501', '0', '9W', '20081024', '0', '', '' union all
select '9W', '20080413', '1', 'MR', '20100326', '0', '9W', '20081031' union all
select 'MR', '20100729', '0', '5W', '20081012', '0', 'G7', '20081004' union all
select 'MR', '20110821', '0', '7W', '20080928', '1', '9W', '20080917'

insert into @cleared
select
*,
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end

as DryCd1_DryCd2_DryCd3_5w_7W_9W
from @tmp
WHERE
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end
= 0

insert into @Pending
select
*,
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end

as DryCd1_DryCd2_DryCd3_5w_7W_9W
from @tmp
WHERE
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end
!= 0

select * from @Cleared
select * from @Pending

select
*,
case
when DryCd1 = '5W' then 1
when DryCd1 = '7W' then 1
when DryCd1 = '9W' then 1
else 0
end
+
case
when DryCd2 = '5W' then 1
when DryCd2 = '7W' then 1
when DryCd2 = '9W' then 1
else 0
end
+
case
when DryCd3 = '5W' then 1
when DryCd3 = '7W' then 1
when DryCd3 = '9W' then 1
else 0
end

as DryCd1_DryCd2_DryCd3_5w_7W_9W
from @tmp

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 10:58:14

How would I put this in a conditional split (the expression section)

CASE WHEN DryCd1 = '5W' THEN 1 WHEN DryCd1 = '7W' THEN 1 WHEN DryCd1 = '9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd2 = '5W' THEN 1 WHEN
DryCd2 = '7W' THEN 1 WHEN DryCd2 = '9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd3 = '5W' THEN 1 WHEN DryCd3 = '7W' THEN 1 WHEN DryCd3 ='9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd4 = '5W' THEN 1 WHEN DryCd4 = '7W' THEN 1 WHEN DryCd4 = '9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd5 = '5W' THEN 1 WHEN DryCd5 = '7W' THEN 1 WHEN DryCd5 = '9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd6 = '5W' THEN 1 WHEN DryCd6 ='7W' THEN 1 WHEN DryCd6 = '9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd7 = '5W' THEN 1 WHEN DryCd7 = '7W' THEN 1 WHEN DryCd7 = '9W' THEN 1 ELSE 0 END +

CASE WHEN DryCd8 = '5W' THEN 1 WHEN DryCd8 = '7W' THEN 1 WHEN DryCd8 = '9W' THEN 1 ELSE 0 END

AS DryCd1_DryCd2_DryCd3_DryCd4_DryCd5_DryCd6_DryCd7_DryCd8_5W_7W_9W

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-09 : 11:31:51

Thanks Johuctt that's it! Thanks for all your help everyone else too!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources