| Author |
 Topic  |
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
 Posted - 04/23/2012 : 05:55:20
|
Good day,please help me to loop on this script.
i want to loop all the @CSHDRKey from a #temptable then use it as a parameter to run the following script. i want to create loop where
for every CSHRKEY from #temptable then pass it on the following script to get the results,here is my script.
notice the @CSHDRKey i set to static,the result is okie but i want
to basically loop the @CSHDRKEY from #TempTable(Temp tables contains many CSHDRKEY),really appreciate your help,thank you.
--------------Begin ----------------------------------
USE [TMPC]
GO
/****** Object: StoredProcedure [dbo].[RptADBRewardsTEST] Script Date: 04/23/2012 17:39:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-
*/
--exec [RptADBRewardsTEST] '3/1/2012','3/31/2012','2253'
ALTER Procedure [dbo].[RptADBRewardsTEST]
@Startdate datetime
,@Enddate datetime
,@CSHDRKey int
As SET NOCOUNT ON
BEGIN
Set @Startdate ='3/1/2012'
Set @Enddate ='3/31/2012'
DECLARE @StartFolder datetime, @EndFolder datetime, @DaysPassed int,
@CurrYear int, @NoDaysYr int, @MinDate datetime, @BegBal money, @IRate float,
@withTax float, @MinBalance money, @sumNoofDays int,@Month int,@FolderKey int
SELECT @MinBalance = IntOnSavingsMinBalance FROM parameters
SELECT CSHDRKey,TransDate, BeginBal = cast(0 as money),
Debit = SUM(CASE Type WHEN 'D' THEN Amount ELSE 0 END),
Credit = SUM(CASE Type WHEN 'C' THEN Amount ELSE 0 END),
EndBal = cast (0 as money), DaysInterval = cast(0 as money),
ADB = cast(0 as money)
INTO #TempSavings FROM ClientSavingsDTL WHERE CSHDrkey =@CSHDRKey AND TransDate >= @StartDate
AND TransDate < dateadd(day,1,@EndDate)
GROUP BY CSHDrKey, TransDate
SELECT @BegBal = SUM(CASE Type WHEN 'C' THEN Amount ELSE Amount * - 1 END)
FROM ClientSavingsDTL WHERE CSHDrkey = @CSHDRKey
AND TransDate < @EndDate
end
begin
SET @BegBal = ISNULL(@BegBal,0)
INSERT INTO #TempSavings VALUES (@CSHDRKey, dateadd(day,-1,@Startdate), @BegBal,0,0, @BegBal,0,0)
SELECT @MinDate = min(Transdate) FROM #TempSavings
DECLARE @Transdate datetime, @Debit money, @Credit money, @Endbal money, @LastTranDate datetime, @DaysInterval int
SET @LastTranDate = dateadd(day,-1,@StartDate)
DECLARE cur_savings CURSOR FOR SELECT TransDate, Debit, Credit FROM #TempSavings ORDER BY TransDate
OPEN cur_savings
FETCH NEXT FROM cur_savings INTO @TransDate,@Debit, @Credit
WHILE @@FETCH_STATUS = 0
BEGIN
SET @EndBal = @BegBal + @Credit - @Debit
SELECT @LastTranDate = min(TransDate) FROM #TempSavings WHERE CSHDRKey = @CSHDRKey AND TransDate > @TransDate
IF @LastTranDate IS NULL
SET @LastTranDate = @EndDate
SET @DaysInterval = datediff (day, @TransDate, @LastTranDate)
UPDATE #TempSavings SET BeginBal = @BegBal, EndBal = @EndBal,DaysInterval = @DaysInterval
WHERE CSHDrkey = @CSHDRKey AND TransDate = @TransDate
SET @BegBal = @EndBal
FETCH NEXT FROM cur_savings INTO @TransDate, @Debit, @Credit
END
CLOSE cur_savings
DEALLOCATE cur_savings
end
-- compute adb only to those above the minimum balance
UPDATE #TempSavings SET ADB = EndBal*CAST(DaysInterval AS MONEY)/(DateDiff(Day, @StartDate, @EndDate)+1)--/ @NoDaysYr
WHERE EndBal > @MinBalance
SELECT T.CSHDRKey, C.ClientCode, CE.LastName + ', ' + CE.FirstName + ' ' + CE.MiddleName ClientName, C.SavingsCode,
P.SVDesc SavingsName, T.TransDate TranDate, EndBal =
(select top 1 endbal from #Tempsavings ORDER BY TransDate desc),
DateDiff = 0, AveTransBalance = cast(0 as money),
AveDailyBalance = ADB,dateinterval = 0
INTO #FinalDest FROM (SELECT CSHDrkey, SUM(ADB) ADB, max(TransDate) TransDate
FROM #TempSavings GROUP BY CSHDrkey) T INNER JOIN ClientSavings C
ON C.CSHDRkey = T.CSHDRkey LEFT JOIN Clientele CE ON CE.ClientCode = C.ClientCode
LEFT JOIN PrSavings P ON P.Acctcode =C.SavingsCode
WHERE C.SavingsCode = '363'
--END
Select * from #TempSavings
Select * from #FinalDest
drop table #TempSavings
drop table #FinalDest
--------------End------------------------------------- |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 04/23/2012 : 11:53:45
|
why do you need to loop? what are you trying to calculate inside loop? is it running count? if yes, it can be implemented using set based logic which would be much more efficient
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
Posted - 04/24/2012 : 01:18:49
|
i need to loop the variable @CSHDRKEY i want to select it from table then pass it into my loop here is my example.
declare @cshdrkey varchar(10)
select cshdrkey into #temp from Client
set @cshdrkey = cshdrkey
DECLARE cur_TEST CURSOR FOR
SELECT CSHDRKEY FROM #temp
OPEN cur_TEST
FETCH NEXT FROM cur_savings INTO @CSHDRKEY
WHILE @@FETCH_STATUS = 0
BEGIN
-do somethine here using @CSHDRKEY
FETCH NEXT FROM cur_TEST INTO CSHDRKEY
END
CLOSE cur_TEST
DEALLOCATE cur_TEST
end |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 04/24/2012 : 01:24:49
|
you still didnt answer my question. what are you doing inside loop? why cant it be done in a set based manner?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
Posted - 04/24/2012 : 03:53:32
|
| i dont know how to create script on a set base approach. can you please help me,thank you. |
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 04/24/2012 : 07:13:59
|
If you can post the DDL of the tables being used, some readily consumable sample data and a precise explanation of the business requirement then we could give you a Set Based solution for it.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 04/24/2012 : 21:39:47
|
quote:
Originally posted by julius.delorino
i dont know how to create script on a set base approach. can you please help me,thank you.
first explain what exactly you're trying to do inside update
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
Posted - 04/25/2012 : 01:33:38
|
select @Variable from #temp --contains 3 record
-output return from querry above
@variable1
@variable2
@variable3
--------------------------------
create a loop
set to record 1 = @variable1
--do something here
then next record @Variable2
until last record
please help. thank you.
|
|
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 04/25/2012 : 14:22:55
|
I think what Visak is saying, is there is almost definitely a set-based approach that is the correct way to accomplish what you are looking to accomplish. However, the posts you put up are very difficult to see what you are looking to do since you are just illustrating a loop without showing how you obtain your data and how it is to be used.
Please post DDL for sample data and a sample of what you are doing with the data and desired end results after your LOOP.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
Edited by - Vinnie881 on 04/25/2012 15:45:26 |
|
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
Posted - 04/25/2012 : 21:43:47
|
here is my sp use to compute for average daily balance.
notice the parameter @CSHDRKey(contains only one record supplied by user).
i want to eliminate the parameter @CSHDRKey in order to have a result base from selection of (select CSHRDKEY from clientsavingsDTL)
after i get the CSHRDKEY then pass it to @CSHRDKEY then apply
the script below then (select CSHRDKEY from clientsavingsDTL) again
until to the last records from clientsavingsDTL.
----------------------------------------------------------------------
USE [TMPC]
GO
/****** Object: StoredProcedure [dbo].[RptADBRewardsTEST] Script Date: 04/26/2012 09:31:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-
*/
--exec [RptADBRewardsTEST] '3/1/2012','3/31/2012','3103'
ALTER Procedure [dbo].[RptADBRewardsTEST]
@Startdate datetime
,@Enddate datetime
,@CSHDRKey int
As SET NOCOUNT ON
BEGIN
DECLARE @DaysPassed int, @NoDaysYr int, @MinDate datetime, @BegBal money, @MinBalance money--,@CSHDRKey int ='2577'
SELECT @MinBalance = IntOnSavingsMinBalance FROM parameters
SELECT CSHDRKey,TransDate, BeginBal = cast(0 as money),
Debit = SUM(CASE Type WHEN 'D' THEN Amount ELSE 0 END),
Credit = SUM(CASE Type WHEN 'C' THEN Amount ELSE 0 END),
EndBal = cast (0 as money), DaysInterval = cast(0 as money),
ADB = cast(0 as money)
INTO #TempSavings FROM ClientSavingsDTL WHERE CSHDrkey =@CSHDRKey AND TransDate >= @StartDate
AND TransDate < dateadd(day,1,@EndDate)
GROUP BY CSHDrKey, TransDate
SELECT @BegBal = SUM(CASE Type WHEN 'C' THEN Amount ELSE Amount * - 1 END)
FROM ClientSavingsDTL WHERE CSHDrkey = @CSHDRKey
AND TransDate < @EndDate
end
begin
SET @BegBal = ISNULL(@BegBal,0)
INSERT INTO #TempSavings VALUES (@CSHDRKey, dateadd(day,-1,@Startdate), @BegBal,0,0, @BegBal,0,0)
SELECT @MinDate = min(Transdate) FROM #TempSavings
DECLARE @Transdate datetime, @Debit money, @Credit money, @Endbal money, @LastTranDate datetime, @DaysInterval int
SET @LastTranDate = dateadd(day,-1,@StartDate)
DECLARE cur_savings CURSOR FOR SELECT TransDate, Debit, Credit FROM #TempSavings ORDER BY TransDate
OPEN cur_savings
FETCH NEXT FROM cur_savings INTO @TransDate,@Debit, @Credit
WHILE @@FETCH_STATUS = 0
BEGIN
SET @EndBal = @BegBal + @Credit - @Debit
SELECT @LastTranDate = min(TransDate) FROM #TempSavings WHERE CSHDRKey = @CSHDRKey AND TransDate > @TransDate
IF @LastTranDate IS NULL
SET @LastTranDate = @EndDate
SET @DaysInterval = datediff (day, @TransDate, @LastTranDate)
UPDATE #TempSavings SET BeginBal = @BegBal, EndBal = @EndBal,DaysInterval = @DaysInterval
WHERE CSHDrkey = @CSHDRKey AND TransDate = @TransDate
SET @BegBal = @EndBal
FETCH NEXT FROM cur_savings INTO @TransDate, @Debit, @Credit
END
CLOSE cur_savings
DEALLOCATE cur_savings
end
-- compute adb only to those above the minimum balance
UPDATE #TempSavings SET ADB = EndBal*CAST(DaysInterval AS MONEY)/(DateDiff(Day, @StartDate, @EndDate)+1)--/ @NoDaysYr
WHERE EndBal > @MinBalance
SELECT T.CSHDRKey, C.ClientCode, CE.LastName + ', ' + CE.FirstName + ' ' + CE.MiddleName ClientName, C.SavingsCode,
P.SVDesc SavingsName, T.TransDate TranDate, EndBal =
(select top 1 endbal from #Tempsavings ORDER BY TransDate desc),
DateDiff = 0, AveTransBalance = cast(0 as money),
AveDailyBalance = ADB,dateinterval = 0
INTO #FinalDest FROM (SELECT CSHDrkey, SUM(ADB) ADB, max(TransDate) TransDate
FROM #TempSavings GROUP BY CSHDrkey) T INNER JOIN ClientSavings C
ON C.CSHDRkey = T.CSHDRkey LEFT JOIN Clientele CE ON CE.ClientCode = C.ClientCode
LEFT JOIN PrSavings P ON P.Acctcode =C.SavingsCode
WHERE C.SavingsCode = '363'
--END
Select CSHDRKey,ClientCode,ClientName,SavingsCode,SavingsName,TranDate,AveDailyBalance
from #FinalDest
drop table #TempSavings
drop table #FinalDest
----------------------------------------------------------------------
RESULT TABLE -OUTPUT
----------------------------------------------------------------------
Cshdrkey|Clientcode|Clientsname|Savingscode|Desc|TranDate|ADB|
3103 RM-001367 GACAYAN, JOYCE J. 363 PAID - UP SHARE CAPITAL - COMMON 2012-02-29 11600.00 |
|
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 04/25/2012 : 22:58:52
|
OK... That is not really what was requested, but I'll try to work with it.
Anyway, I took a look at your code. First the procedure doesn't need a cursor you can use a CTE for the running totals or a cluster table update for much better performance, but that is not the issue you were looking to resolve so I'll skip to the question.
You would want to create a table valued function instead of a procedure to bring all results into a table(CONVERT ALL YOUR TEMP TABLES to TABLE VARIABLES in the current procedure then return the table. SQL MANAGEMENT STUDIO has a template for how to create a table variable function, just use that and paste your code (Changing the #table's to table variables (i.e. tables that begin with @tablename) and it should be ready to go.
Then you can pass it to the function like so
select b.* from
#TEMPTABLE a
cross apply
(Select * from FN_RptADBRewardsTEST('1/1/2011','1/1/2012',a.CSHDRKEY) b
it will run and returned all results for all CSHDRKEY in #TEMPTABLE.
Since you didn't post the ddl and sample data, I don't really want to invest a lot of time in this to recreating what you are doing, but if you decide to post some sample data and DDL and desired results, I'll try to assist more if you get stuck, or someone else on here I am sure will assist as well.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
Edited by - Vinnie881 on 04/25/2012 23:00:31 |
|
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
Posted - 04/25/2012 : 23:27:20
|
| Thanks for your help sir. i'll try to use your solution. |
|
|
|
julius.delorino
Starting Member
Philippines
29 Posts |
Posted - 04/25/2012 : 23:31:53
|
here is my DDL of my tableclientsavingsdtl
USE [TMPC]
GO
/****** Object: Table [dbo].[ClientSavingsDTL] Script Date: 04/26/2012 11:31:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClientSavingsDTL](
[ID] [varchar](60) NOT NULL,
[RefNo] [varchar](15) NOT NULL,
[CSHDRKey] [numeric](9, 0) NOT NULL,
[TransNo] [varchar](10) NOT NULL,
[TransDate] [datetime] NOT NULL,
[Amount] [money] NOT NULL,
[Remarks] [varchar](100) NULL,
[Type] [char](1) NOT NULL,
[TransType] [char](1) NOT NULL,
[Teller] [varchar](16) NOT NULL,
[Workstation] [varchar](50) NOT NULL,
[PrnSlipYN] [bit] NOT NULL,
[PrnPassbookYN] [bit] NOT NULL,
[PrnLedgerYN] [bit] NOT NULL,
[Status] [char](10) NULL,
[Batch] [varchar](10) NULL,
[Time] [datetime] NULL,
[TransDesc] [varchar](4) NULL,
[Line] [smallint] NULL,
[Page] [smallint] NULL,
[Book] [smallint] NULL,
[CashAccnt] [varchar](12) NULL,
[LineLedger] [int] NOT NULL,
[PageLedger] [int] NOT NULL,
CONSTRAINT [PK_ClientSavingsDTL] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [ID]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [RefNo]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [CSHDRKey]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [TransNo]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [Amount]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [Type]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [TransType]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [Teller]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [Workstation]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PrnSlipYN]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PrnPassbookYN]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PrnLedgerYN]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [LineLedger]
GO
ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PageLedger]
GO
|
|
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 04/26/2012 : 14:03:41
|
Proper DDL for something like this would just include needed fields and also it is VERY IMPORTANT TO INCLUDE SAMPLE DATA, and your desired end results
ie.
Create table #ClientSavingsDTL (CSHDRKey int, id int,type as varchar(20),amount)
insert into #ClientSavingsDTL
select 1,10,'D',20
union all
select 2,30,'D',40
union all
select 3,40,'D',25
union all
select 140,10,'C',20
union all
select 170,30,'C',40
union all
select 180,40,'C',25
Then post what you want your results to be in this scenerio.
create table #Results(CSHDRKey int, id int,type as varchar(20),amount money,Debit money, Credit money, EndBal money, ADB money)
--WHAT DO YOU ULTIMATELY WANT YOUR RESULTS TO BE???
Please fill out/modify the tables/sample data so we can easily see exactly what your results should be, and then we can better assist.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
Edited by - Vinnie881 on 04/26/2012 14:05:27 |
|
|
| |
Topic |
|
|
|
LOOP ON VARIABLE
