looping or curson in stp - 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 2008 Forums

Transact-SQL (2008)

looping or curson in stp

Author

Topic

artistlover
Starting Member

4 Posts

Posted - 2014-03-26 : 14:09:03

I have a table that has records in it. One of the fields is quatity field. for that row i have to put the same line however many times is in the quatity field into another table. I also have to get from a third table the number assigment.

so

tblrecnumassignments

claimstoexplode

claims

this is my three tables.

The code below gets one recnumassignment and enters it the number of quatity. UGH..and it doesn't loop back for the next two in claims to explode.

I've never writen cursor or done any looping and i have no one to ask to help.

Can anyone help me

SET ARITHABORT OFF SET ANSI_WARNINGS OFF

declare @recnum as varchar(100),
@lngrecnum as varchar(100),
@vwclaimscount as int,
@count as int

select @vwclaimscount = COUNT([dlr num]) from claimstoexplode --outer loop number
select @lngrecnum = MAX([rec num] + 1) from tmpClaims_RecNumAssignment

print @lngrecnum

select @count = claimstoexplode.qty from dbo.claimstoexplode --inner loop

begin

while @vwclaimscount > 0

Begin
insert into tmpClaims_RecNumAssignment
( operator, dtmadded)
values ('explode5', getdate())

insert into tmpclaims1
([Dlr Num]
,[Promo]
,[Trans Code]
,[Proc Date]
,[Control Num]
,[Invoice Num]
,[Clm Date]
,[Amt Claimed]
,[Amt Appr]
,[Amt Paid]
,[Ad From]
,[Ad To]
,[Media]
,[Media Desc]
,[Audit 1]
,[Audit 2]
,[Audit 3]
,[Audit 4]
,[Audit 5]
,[Audit 6]
,[Prod Code 1]
,[Prod Code 1 %]
,[Prod Code 1 Amt]
,[Prod Code 1 Adv Amt]
,[Prod Code 2]
,[Prod Code 2 %]
,[Prod Code 2 Amt]
,[Prod Code 2 Adv Amt]
,[Comment]
,[Received Date]
,[Last Modified]
,[Ratio]
,[Number Of Ads]
,[Amt Applied]
,[UM]
,[Audited Amt]
,[Rated Ads]
,[Branch Number]
,[Operator]
,[Bal Pending]
,[Paid From Pending]
,[Store Num]
,[Clm Spec 1]
,[Clm Spec 2]
,[Status]
,[Rec Num]
,[Doc Type]
,locked

)

select
[dlr num]
,prognum
,'X'
,GETDATE()
,'0'
,''
,[received date]
,round([claimed amt]/qty,2)
,'0'
,'0'
,dtmfrom
,dtmto
,'9'
, dbo.Promos.[Promo Misc 1]
,'01'
,''
,''
,''
,''
,''
,[Product]

,'0'
,'0'
,'0'
,bundlemodel
,'0'
,'0'
,'0'
,''
,[received date]
,''

,'100'
,'1'
,'0'
,'0'
,'0'
,'0'
,'0'
,claimstoexplode.operator
,'0'
,'0'
,'0'
,LEFT(reference,10)
,[record number]
,'1'
, @lngrecnum
,'L'
,'1'
from claimstoexplode inner join
dbo.promos ON dbo.Promos.Promo = dbo.claimstoexplode.prognum

set @vwclaimscount = @vwclaimscount - 1
set @lngrecnum = @lngrecnum + 1
set @count = @count - 1
End
End

The above enters the number of records in claims to export it doesn't enter the same row x amount of times and it isn't getting new recnumassignment.

artistlover

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-26 : 16:45:34

Can you post your sample data and expected output? I suspect that there is no need for a cursor/iterative solution here, but hard to tell.

Here are a couple of links that can help you when posting your question(s):
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

artistlover
Starting Member

4 Posts

Posted - 2014-03-26 : 16:59:24

Sample Data
Dlr Num Received Date Reference Proc Date Record Number Claimed Amt product Qty dtmFrom dtmTo bundleModel bulletin prognum Value Operator
124248 2014-01-27 12:31:53.000 1-27-14-T2 NULL 10500004 975 LC70LE745U 5 2013-09-11 00:00:00.000 2013-09-24 00:00:00.000 NULL AV-13F-05REV 13F05BR 9 203784

expected output
Dlr Num Promo Trans Code Proc Date Control Num Invoice Num Clm Date Amt Claimed Amt Appr Amt Paid Ad From Ad To Media Media Desc Audit 1 Audit 2 Audit 3 Audit 4 Audit 5 Audit 6 Prod Code 1 Prod Code 1 % Prod Code 1 Amt Prod Code 1 Adv Amt Prod Code 2 Prod Code 2 % Prod Code 2 Amt Prod Code 2 Adv Amt Prod Code 3 Prod Code 3 % Prod Code 3 Amt Prod Code 3 Adv Amt Prod Code 4 Prod Code 4 % Prod Code 4 Amt Prod Code 4 Adv Amt Prod Code 5 Prod Code 5 % Prod Code 5 Amt Prod Code 5 Adv Amt Prod Code 6 Prod Code 6 % Prod Code 6 Amt Prod Code 6 Adv Amt Prod Code 7 Prod Code 7 % Prod Code 7 Amt Prod Code 7 Adv Amt Prod Code 8 Prod Code 8 % Prod Code 8 Amt Prod Code 8 Adv Amt Prod Code 9 Prod Code 9 % Prod Code 9 Amt Prod Code 9 Adv Amt Prod Code 10 Prod Code 10 % Prod Code 10 Amt Prod Code 10 Adv Amt Comment Received Date Last Modified Prior Appr Num AltPayee Flag AltPayee Number Ratio Number Of Ads Amt Applied UM Audited Amt Rated Ads Branch Number Operator Bal Pending Paid From Pending Store Num Clm Spec 1 Clm Spec 2 Dept Num Doc Type Det Spec 1 Det Spec 2 Det Spec 3 Det Spec 4 Det Spec 5 Det Spec 6 Size Claimed Size Appr Media Zip Color Check Num Check Date Rate Appr Void Date Void Comment Print Date Batch Num Status Org Control Num Locked Audited Rec Num systimestamp
124248 13F05BR X 2014-03-26 11:10:39.340 0 2014-01-27 12:31:53.000 195.00 0.00 0.00 2013-09-11 00:00:00.000 2013-09-24 00:00:00.000 9 AV13F05 01 LC70LE745U 0 0.00 0.00 NULL 0 0.00 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2014-01-27 12:31:53.000 1900-01-01 00:00:00.000 NULL NULL NULL 100 1 0.00 0 0.00 0 0 203784 0.00 0 0 1-27-14-T2 1.05e+007 NULL L NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 NULL 1 NULL 10500156 0x0000000006EDE5B7

That is hard to read. Sorry Thank you in advance for your help

artistlover

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-26 : 18:01:21

Can you please read the links I posted above for how to include you data in a consumable format (insert statement(s)) and include those?

In your original post you said something about three tables, yet I only see one above. Which is correct? Also, can you describe the logic for how you got form your input data to your results?

artistlover
Starting Member

4 Posts

Posted - 2014-03-26 : 19:30:22

I will get on this as soon as I get to work. Thank you for the links. Those are fantastic. Also, yes it is three tables. I did not realize you would want the third. What I posted was the first is the claimstoexport table i.e sample data. The second is the claims1 table i.e expected output. I appreciate all the information. I will be more clear.

The sample data table versus the results table is vastly different. So i am only inserting what is needed even though there are so many additional fields. Does that answer your last question? I also had to link to an additional table promos because the result table requires that field.

Please bear with me i'm trying. I also really want to learn.

artistlover

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources