Counting Instances - 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

General SQL Server Forums

New to SQL Server Programming

Counting Instances

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-07-22 : 17:08:58

Hi All - I have the following table:

RECORDID----TELNUM
1234 4165551234
1235 4165551234
1236 4165551234
1237 4165559988
1238 4165559988
1239 4165559988
1240 4165554444

I am trying to write a query that calculates a $6.50 credit only for only first (and second) instances of each telephone number. The output would be as follows:

RECORDID || TELNUM || SPIFF
1234 4165551234 $6.50
1235 4165551234 $6.50
1236 4165551234
1237 4165559988 $6.50
1238 4165559988 $6.50
1239 4165559988
1240 4165554444 $6.50

SELECT RECORDID, TELNUM, 6.5 AS 'SPIFF'
FROM ORDERS

How can I modify my query to accomodate this?
Thanks!

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-07-22 : 18:07:40

Weird way but it works.
--copy from your table to a temp table as create new field in table --for credit and order by telnum
select recordid,telnum,cast(0.00 as decimal(10,2)) as Spiff
into #YOURTEMPTABLE
from #YOURTABLE
order by telnum

declare @telnum bigint
set @telnum = 0

declare @pos int
set @pos = 1

-- updates your temp table if first 2 orders for customer
update #YOURTEMPTABLE
set @pos = case when @telnum = telnum then @pos + 1 else 1 end,
@telnum = telnum,Spiff = case when @pos < 3 then 6.5 else 0 end

select *
from #YOURTEMPTABLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-23 : 04:02:00

[code]
update t
set t.SPIFF=$6.50
from
(
select SPIFF,ROW_NUMBER() OVER (PARTITION BY TELNUM ORDER BY RECID) AS rn
FROM Table
)t
WHERE rn <=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources