Auto incrementing value in column per charge for each account number

Question

Auto incrementing value in column per charge for each account number

J M-314995

SSCrazy

Points: 2975
More actions
December 9, 2009 at 6:25 pm

#218628

I have 2 tables I'm pulling data from. One is a table that logs charges on accounts and the other is like a charge master table that keeps information about each charge. (if you need more information than that on these 2 tables let me know)
I'm trying to write a data pull to send off to a 3rd party vendor but I'm stuck on one of the specs they gave me.
This is a small example of the what the data needs to look like...
<sample-result>
acctnum charge_code itemnumber
1257 H345 1
1259 H122 1
1259 A125 2
1261 C1205 1
1265 J1205 1
1265 J4235 2
1265 A125 3
1265 15199 4
1265 12314 5
1283 A205 1
</sample-result>
(acct 1257 has 1 charge, 1259 has 2 charges etc)
My problem is with the itemnumber column. I can see that I'm going to need to group my data by acctnum but I'm not sure how to increase the itemnumber column by 1 when there's more than one charge per account number and then clear it back to 1 when the next line is a new account number.
Any help would be greatly appreciated.
TIA,
John

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Two questions. One, is itemnumber part of the table or just part of the output? Two, is the order of the charges per account important?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Answering my own questions (Only part of the output, order of charge code immaterial), here is some code:

create table dbo.Charges (

AcctNum int,

ChargeCode varchar(10)

);

insert into dbo.Charges

select 257 ,'H345' union all

select 1259 ,'H122' union all

select 1259 ,'A125' union all

select 1261 ,'C1205' union all

select 1265 ,'J1205' union all

select 1265 ,'J4235' union all

select 1265 ,'A125' union all

select 1265 ,'15199' union all

select 1265 ,'12314' union all

select 1283 ,'A205';

select

AcctNum,

ChargeCode,

ROW_NUMBER() over (partition by AcctNum order by ChargeCode) as ItemNum

from

dbo.Charges

;

drop table dbo.Charges;

J M-314995 SSCrazy Points: 2975 More actions · Answer 3

Lynn Pettis (12/9/2009)
Two questions. One, is itemnumber part of the table or just part of the output? Two, is the order of the charges per account important?

itemnumber is not part of either table. It's data I have to create in the query. Sorry, I should've specified that.

The charges should probably be in order according to the service date.

Here's a stripped down sample of the code I have so far...

select

patientaccnum = rtrim(ARCHRG.ACCT_ID)

,chg_cd = rtrim(ARCHRG.PROC_CODE)

,chg_desc = rtrim(PPPROC.PROC_DESC_SH_UC)

,itemnumber = '' --i'm not sure how to increment this column per charge on an acct

from

ARCHRG99 ARCHRG

,PPPROC99 PPPROC

where

ARCHRG.PROC_CODE = PPPROC.PROC_CODE

AND ARCHRG_D_POSTING IS NOT NULL

AND ARCHRG.D_SERVICEFROM >= '2009-11-01 00:00:00.000'

AND ARCHRG.D_SERVICEFROM < '2009-12-01 00:00:00.000'

ORDER BY

ARCHRG.ACCT_ID ASC, ARCHRG.D_SERVICEFROM ASC

J M-314995 SSCrazy Points: 2975 More actions · Answer 4

Lynn Pettis (12/9/2009)
Answering my own questions (Only part of the output, order of charge code immaterial), here is some code:
create table dbo.Charges (
AcctNum int,
ChargeCode varchar(10)
);
insert into dbo.Charges
select 257 ,'H345' union all
select 1259 ,'H122' union all
select 1259 ,'A125' union all
select 1261 ,'C1205' union all
select 1265 ,'J1205' union all
select 1265 ,'J4235' union all
select 1265 ,'A125' union all
select 1265 ,'15199' union all
select 1265 ,'12314' union all
select 1283 ,'A205';
select
AcctNum,
ChargeCode,
ROW_NUMBER() over (partition by AcctNum order by ChargeCode) as ItemNum
from
dbo.Charges
;
drop table dbo.Charges;

It looks like the line...

ROW_NUMBER() over (partition by AcctNum order by ChargeCode)

...after modifying the names worked beautifully.

THANK YOU!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

J M-314995 (12/9/2009)
Lynn Pettis (12/9/2009)
Two questions. One, is itemnumber part of the table or just part of the output? Two, is the order of the charges per account important?
itemnumber is not part of either table. It's data I have to create in the query. Sorry, I should've specified that.
The charges should probably be in order according to the service date.
Here's a stripped down sample of the code I have so far...
select
patientaccnum = rtrim(ARCHRG.ACCT_ID)
,chg_cd = rtrim(ARCHRG.PROC_CODE)
,chg_desc = rtrim(PPPROC.PROC_DESC_SH_UC)
,itemnumber = '' --i'm not sure how to increment this column per charge on an acct
from
ARCHRG99 ARCHRG
,PPPROC99 PPPROC
where
ARCHRG.PROC_CODE = PPPROC.PROC_CODE
AND ARCHRG_D_POSTING IS NOT NULL
AND ARCHRG.D_SERVICEFROM >= '2009-11-01 00:00:00.000'
AND ARCHRG.D_SERVICEFROM < '2009-12-01 00:00:00.000'
ORDER BY
ARCHRG.ACCT_ID ASC, ARCHRG.D_SERVICEFROM ASC

Adds a little, but it wasn't part of your original spec. hang on.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

New code, does this help?

create table dbo.Charges (

AcctNum int,

ChargeCode varchar(10),

ChargeDate datetime

);

insert into dbo.Charges

select 257 ,'H345', '2009-11-01' union all

select 1259 ,'H122', '2009-11-03' union all

select 1259 ,'A125', '2009-11-02' union all

select 1261 ,'C1205', '2009-11-05' union all

select 1265 ,'J1205', '2009-11-10' union all

select 1265 ,'J4235', '2009-11-11' union all

select 1265 ,'A125', '2009-11-12' union all

select 1265 ,'15199', '2009-11-13' union all

select 1265 ,'12314', '2009-11-14' union all

select 1283 ,'A205', '2009-11-20';

select

AcctNum,

ChargeCode,

ROW_NUMBER() over (partition by AcctNum order by ChargeDate) as ItemNum

from

dbo.Charges

;

drop table dbo.Charges;

J M-314995 SSCrazy Points: 2975 More actions · Answer 7

Lynn Pettis (12/9/2009)
New code, does this help?
select
AcctNum,
ChargeCode,
ROW_NUMBER() over (partition by AcctNum order by ChargeDate) as ItemNum
from
dbo.Charges
;
drop table dbo.Charges;
[/code]

Yes, all I needed was the line....

ROW_NUMBER() over (partition by AcctNum order by ChargeDate) as ItemNum

...and it worked beautifully. Thanks Lynn.

🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

J M-314995 (12/9/2009)
Lynn Pettis (12/9/2009)
New code, does this help?
select
AcctNum,
ChargeCode,
ROW_NUMBER() over (partition by AcctNum order by ChargeDate) as ItemNum
from
dbo.Charges
;
drop table dbo.Charges;
[/code]
Yes, all I needed was the line....
ROW_NUMBER() over (partition by AcctNum order by ChargeDate) as ItemNum
...and it worked beautifully. Thanks Lynn.
🙂

Glad to help!