sum(),Cummulative addition,Case statement

Question

Post reply

sum(),Cummulative addition,Case statement

karthik M

One Orange Chip

Points: 29139
More actions
May 16, 2008 at 4:23 am

#380579

All,
I have faced one scenario yesterday.
Table Structure:
----------------
Create table t1
(
CompanyId int,
TransactionId int,
RestrictedId bit
)
Insert into #t1
select 101,1001,0
union all
select 101,1002,0
union all
select 101,1004,1
union all
select 101,1020,1
union all
select 103,1007,0
union all
select 103,1201,0
union all
select 104,1430,0
union all
select 102,1320,1
union all
select 102,1321,0
------------------------------------
CompanyId TransactionId RestrictedId
------------------------------------
101 1001 0
101 1002 0
101 1004 1
101 1020 1
103 1007 0
103 1201 0
104 1430 0
102 1320 1
102 1321 0
Conditions:
1) If any company contain 'zero' in its all TransactionId ,then we need to display it as 'Grey'
2)If any company doesn't contain 'Zero' in its all TransactionId, and if it is '0' then we need to display it as 'Black Restrcted'
3)If any company doesn't contain 'Zero' in its all TransactionId, and if it is '1' then we need to display it as 'Red Restrcted'
Expected Result:
CompanyId TransactionId RestrictedId Status
------------------------------------
101 1001 0 BR
101 1002 0 BR
101 1004 1 RR
101 1020 1 RR
103 1007 0 Grey
103 1201 0 Grey
104 1430 0 Grey
102 1320 1 RR
102 1321 0 BR
what i did was, i have created a function and called it in SELECT clause.
Create function dbo.f_Restrictedstatus
(
@CompanyId int,
@TransactionId int
)
Return varchar(15)
as
Begin
Declare @Cnt int,@Result varchar(15)
select @Cnt = sum(convert(int,RestrictedId))
from t1
where CompanyId = @CompanyId
If @Cnt = 0
Begin
select @Result = 'Grey'
End
Else
Begin
select @Result = case when RestricedId = 0 then 'BR' else 'RR' end
from t1
where CompanyId = @CompanyId
and TransactionId = @TransactionId
End
Return @Result
End
----
My final select statement is
select CompanyId,TransactionId,RestrictedId,f_restrictedstatus(CompanyId,TransactionId)
from t1
My Questions:
1) is there any other way to achive the same task ?
2) was i went in the right direction to achieve the task ? i hope so.
Inputs are welcome !
karthik

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Here is another solution.

SELECT

CompanyId

,TransactionId

,RestrictedId

,CASE WHEN (SUM(CAST(RestrictedId as INT)) OVER (PARTITION BY CompanyId) = 0)

THEN 'Grey'

ELSE CASE WHEN RestrictedId = 0

THEN 'BR'

ELSE 'RR'

END

END as [STATUS]

FROM @t1

GROUP BY

CompanyId

,TransactionId

,RestrictedId

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

May 16, 2008 at 6:00 am

#816391

I am using sql2000. Will it work ?

karthik

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Nope...

--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)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

... but this will...

SELECT o.CompanyID,o.TransactionID,o.RestrictedID,

CASE

WHEN d.HasRestriction = 0 THEN 'Grey'

WHEN d.HasRestriction > 0 AND RestrictedID = 0 THEN 'Black Restricted'

WHEN d.HasRestriction > 0 AND RestrictedID = 1 THEN 'Red Restricted'

END AS Status

FROM t1 o

INNER JOIN

(--==== Sum restrictions for each company (SIGN converts bit for SUM)

SELECT CompanyID,SUM(SIGN(RestrictedID)) AS HasRestriction

FROM t1

GROUP BY CompanyID) d

ON o.CompanyID = d.CompanyID

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff, couldn't you use MAX(CAST(RestrictedID as int)) instead of SUM(SIGN(..))?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

rbarryyoung (5/17/2008)
Jeff, couldn't you use MAX(CAST(RestrictedID as int)) instead of SUM(SIGN(..))?

Absolutely... just didn't want to type that much 😛

Karthik, no function is required. The function is inherently RBAR in this case. Think "columns"... that's the basis of thinking "set based". 🙂

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Thanks Jeff for leading me in the right direction to lesson me about SET BASED concept and giving new ideas to me.Also my Thanks to rbarryyoung and Christopher for giving their new ideas to me.

I never think my function leads to RBAR. How to find it ?

The function is inherently RBAR in this case

How to know it ?

Also i have one doubt.

which one is the best and correct way to Add BIT column ? why ?

1) Sum(convert(int,RestrictedId))

2) SUM(SIGN(RestrictedID))

3) MAX(CAST(RestrictedID as int))

All are giving the same result.But what is the internal difference between among those 3 statements ?

Please explain it with example.

karthik

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

I never think my function leads to RBAR. How to find it ?

The function is executed once for every row that is returned in the SELECT list... since the function references a table, that makes it no better than a correlated sub-query. Correlated sub-queries execute one select per row. One select per row is RBAR. Virtually any function that references a table is RBAR for the reasons stated above.

which one is the best and correct way to Add BIT column ? why ?
1) Sum(convert(int,RestrictedId))
2) SUM(SIGN(RestrictedID))
3) MAX(CAST(RestrictedID as int))

As you've found, you cannot do an aggragate function on the BIT datatype, so some type of conversion is necessary to determine if a given company has 1 or more restrictions.

I selected #2 above just because I was being a bit lazy... I didn't want to type much. In reality, over a million rows, it takes the longest because SIGN returns FLOAT and floating point operations take the longest.

If I had my druthers, I'd select #1 just because of experience... I know that someone will ask me to later put in how many restrictions they have. #1 is the second fastest of the 3 because SUM is just a little more expensive an aggragate than MAX.

#3 is the fastest of the 3.

#1 takes about 1953 ms across a million rows

#2 takes about 2281 ms across a million rows

#3 takes about 1766 ms across a million rows

Here's the test code...

DROP TABLE JBMTest

GO

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 100,000 unique numbers

-- Column "RestrictedID" returns a BIT datatype (0 or 1)

-- Column "CompanyID " has a range of 1 to 500

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

RestrictedID = CAST(ABS(CHECKSUM(NEWID()))%2 AS BIT),

CompanyID = ABS(CHECKSUM(NEWID()))%500+1

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

--===== Do the performance comparisons

PRINT 'Formula #1...'

SET STATISTICS TIME ON

SELECT Sum(convert(int,RestrictedID))

FROM dbo.JBMTest

GROUP BY CompanyID

SET STATISTICS TIME OFF

PRINT REPLICATE('=',78)

PRINT 'Formula #2...'

SET STATISTICS TIME ON

SELECT SUM(SIGN(RestrictedID))

FROM dbo.JBMTest

GROUP BY CompanyID

SET STATISTICS TIME OFF

PRINT REPLICATE('=',78)

PRINT 'Formula #3...'

SET STATISTICS TIME ON

SELECT MAX(CAST(RestrictedID as int))

FROM dbo.JBMTest

GROUP BY CompanyID

SET STATISTICS TIME OFF

PRINT REPLICATE('=',78)

Please explain it with example.

You've seen me do test tables like this before... next time, you can "explain it with example". 😛

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 9

You've seen me do test tables like this before... next time, you can "explain it with example".

Sure.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Jeff,

I have tested the below script.

create table #test

(

Sample Bit

)

insert into #test

select 1

union all

select 0

union all

select 1

union all

select 0

union all

select 1

Way :#1

output - 3

Way : #2

output :3

Way : #3

Output:1

it is not giving the expected output.

karthik

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 11

You've obviously had to change the code to accomodate the new column name and the new table name... I'm pretty good, but without seeing the code, there's no way I can tell.

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 12

select sum(sign(Sample)) from #test

select sum(convert(int,Sample) from #test

select max(cast( Sample as int)) from #test

karthik

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 13

Once I repair the error in the #2 code you provided, it gives me the following...

[font="Courier New"]-----------------------------------------------------

3.0

(1 row(s) affected)

-----------

3

(1 row(s) affected)

-----------

1

(1 row(s) affected)[/font]

What's not expected in that?

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 14

Once I repair the error in the #2 code you provided, it gives me the following...
-----------------------------------------------------
3.0
(1 row(s) affected)
-----------
3
(1 row(s) affected)
-----------
1
(1 row(s) affected)
What's not expected in that?

I want the cummulative added value ( i.e 3). But #3 just displaying the max(i.e 1) value.

karthik