Insert records based on parameter

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)

Insert records based on parameter

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-02 : 10:56:41

I have a cursor that is inserting records while a count has not reached the total count for insurance filing purposes. Here is the background.

A patient started treatment on 5/1/08 for 28 months. The first available filing will be 9 months from the date and then every 3 months after until the treatment months is met. So the billing dates will be 2/1/09 (9mo), 5/1/09 (12mo), 8/1/09 (15mo), 11/1/09 (18mo), 2/1/10 (21mo), 5/1/10 (24mo), 8/1/10 (27mo). How can I work the remainder of the month into my syntax. Note that the treatment months varies from patient to patient...

Here is part of my query:

...
FETCH NEXT FROM myCursor INTO @OfficeNumber, @PatsNumber, @CntrNumber, @BillingDate, @TxMonths, @CntrAmount

declare @i as int

WHILE @@FETCH_STATUS = 0 BEGIN
set @i = 9 --Determines the 1st continuation

while @i <= @TxMonths begin
insert into DeptPlayGround.dbo.MedicaidBillingContinuations (OfficeNumber, PatsNumber, CntrNumber, BillingDate, CntrMonths, CntrAmount)
values (@OfficeNumber, @PatsNumber, @CntrNumber, dateadd(m,@i,@BillingDate ), @TxMonths, @CntrAmount)
set @i = @i + 3
end

FETCH NEXT FROM myCursor INTO @OfficeNumber, @PatsNumber, @CntrNumber, @BillingDate, @TxMonths, @CntrAmount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 14:03:53

[code]insert into DeptPlayGround.dbo.MedicaidBillingContinuations (OfficeNumber, PatsNumber, CntrNumber, BillingDate, CntrMonths, CntrAmount)
SELECT t.OfficeNumber,
t.PatsNumber,
t.CntrNumber,
CASE WHEN v.number>0 THEN DATEADD(m,v.number*3,t.BillingDate) ELSE DATEADD(mm,5,t.BillingDate) END AS BillingDate,
t.TxMonths,
t.CntrAmount
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number*3 <=TxMonths[/code]

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-02 : 14:36:04

quote:

Originally posted by visakh16

insert into DeptPlayGround.dbo.MedicaidBillingContinuations (OfficeNumber, PatsNumber, CntrNumber, BillingDate, CntrMonths, CntrAmount)
SELECT t.OfficeNumber, 
t.PatsNumber, 
t.CntrNumber, 
CASE WHEN v.number>0 THEN DATEADD(m,v.number*3,t.BillingDate) ELSE DATEADD(mm,5,t.BillingDate) END AS BillingDate, 
t.TxMonths, 
t.CntrAmount
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number*3 <=TxMonths

Can you help me better understand some of the terms you used:
v.number (what is number in this case?)
v.type = 'p' (I don't understand what this is?)

Also wouldn't this cause errors in the context of my cursor?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 14:42:49

quote:
Originally posted by andros30

quote:
Originally posted by visakh16

insert into DeptPlayGround.dbo.MedicaidBillingContinuations (OfficeNumber, PatsNumber, CntrNumber, BillingDate, CntrMonths, CntrAmount)
SELECT t.OfficeNumber,
t.PatsNumber,
t.CntrNumber,
CASE WHEN v.number>0 THEN DATEADD(m,v.number*3,t.BillingDate) ELSE DATEADD(mm,5,t.BillingDate) END AS BillingDate,
t.TxMonths,
t.CntrAmount
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number*3 <=TxMonths

Can you help me better understand some of the terms you used:
v.number (what is number in this case?)
v.type = 'p' (I don't understand what this is?)

Also wouldn't this cause errors in the context of my cursor?

Thanks

you dont need cursor at all
master..spt_values will do looping for you (its an internal table.
just put select statement of cursor definition as first part (blue part above except CASE). number and type are columns of internal count table.

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-06 : 12:43:18

Hi Visakh, thanks for the suggestion. I am tweaking the data a bit to get the results but I have to ask you how you came up with the Case When logic. I googled everything about the spt_values table and found very little information. I see the table that you used but the data does not make sense to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 12:59:34

quote:
Originally posted by andros30

Hi Visakh, thanks for the suggestion. I am tweaking the data a bit to get the results but I have to ask you how you came up with the Case When logic. I googled everything about the spt_values table and found very little information. I see the table that you used but the data does not make sense to me.

spt_values is a count table internally present in sql server. i've used it to iterate your table data for future dates. each time except first i will add datefield + 3 * number value of months and for first time (0) i add 5. so you get info for table for dates t.BillingDate+5,t.BillingDate+5+3,t.BillingDate+5+2*3.... until TxMonths.Actually it needed a small tweak try like below

insert into DeptPlayGround.dbo.MedicaidBillingContinuations (OfficeNumber, PatsNumber, CntrNumber, BillingDate, CntrMonths, CntrAmount)
SELECT t.OfficeNumber, 
t.PatsNumber, 
t.CntrNumber, 
CASE WHEN v.number>0 THEN DATEADD(m,5+(v.number*3),t.BillingDate) ELSE DATEADD(mm,5,t.BillingDate) END AS BillingDate, 
t.TxMonths, 
t.CntrAmount
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND 5+(v.number*3) <=TxMonths

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources