Cursor Loop - 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)

Cursor Loop

Author

Topic

SaanjhDn86
Starting Member

5 Posts

Posted - 2011-10-30 : 02:15:27

WHILE @@FETCH_STATUS = 0
Begin

SET @currbatch = @BATCHNUM
SET @curritem = @ITEM

If ((@prevbatch = '') or (@previtem =''))

SET @OBAL =(Select SUM(quantity) -
ISNULL((Select SUM(quantity)from IBT1 where Direction = '1' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt), 0)
from IBT1 where Direction = '0' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt)
SET @Stock = @OBAL

if ((@currbatch <> @prevbatch) or (@curritem <> @previtem))

SET @OBAL =(Select SUM(quantity) -
ISNULL((Select SUM(quantity)from IBT1 where Direction = '1' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt), 0)
from IBT1 where Direction = '0' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt)
SET @Stock = @OBAL

SET @Stock = @Stock + @QRECD - @QTYSOLD
SET @prevbatch = @BATCHNUM
SET @previtem = @ITEM

For this code of cursor, I am getting NULL value for the first row of OBAL and STOCK. Please Help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 02:21:51

looks like you're calculating a running value quantity by above cursor. You can easily dispense with cursor. if you can post some sample data and then explain what you want, we might be able to give you set based solution

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

SaanjhDn86
Starting Member

5 Posts

Posted - 2011-10-30 : 02:27:10

Actually I want to calculate Opening Balance item wise and batch wise and, means whenever item or batch will change, OBAL will change and parameter is date range,

Problem is

(Select SUM(quantity) -
ISNULL((Select SUM(quantity)from IBT1 where Direction = '1'
and BatchNum = '1112-300023' and ItemName = 'CONNECTOR' and DocDate <'20110401'), 0)
from IBT1 where Direction = '0' and BatchNum = '1112-300023' and ItemName = 'CONNECTOR' and DocDate <'20110401')

is query is giving "NULL" so data not coming correct.

I am pasting whole Stored Procedure

/* start*/

USE [MIPL_PROD_17 10 2011]
GO
/****** Object: StoredProcedure [dbo].[Micronova] Script Date: 10/30/2011 10:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Micronova] @FrDt as Date,@ToDt as Date
AS
BEGIN
create table #temp1 (
ITEM nvarchar(100),
BATCHNUM nvarchar(100),
CHAPTER nvarchar(100),
PREF nvarchar(100),
SUPP nvarchar (100),
DUTYP nvarchar (100),
PDUTY nvarchar (100),
QRECD int ,
INVNO nvarchar(100),
INVDATE Date,
CUSTOMER nvarchar (100),
ECCNO nvarchar (100),
QTYSOLD int,
DUTY numeric (19,4),
EDU_CESS numeric (19, 4),
SEC_CESS numeric (19, 4),
ADC numeric (19, 4),
TotalDutyPassed numeric (19, 4),
EntryNo int
)

Create Table #temp
(
ITEM nvarchar(100),
BATCHNUM nvarchar(100),
CHAPTER nvarchar(100),
PREF nvarchar(100),
SUPP nvarchar (100),
DUTYP nvarchar (100),
PDUTY nvarchar (100),
QRECD int ,
INVNO nvarchar(100),
INVDATE nvarchar(100),
CUSTOMER nvarchar (100),
ECCNO nvarchar (100),
QTYSOLD int,
DUTY numeric (19,4),
EDU_CESS numeric (19, 4),
SEC_CESS numeric (19, 4),
ADC numeric (19, 4),
TotalDutyPassed numeric (19, 4),
EntryNo int,
OBAL int,
Stock int,

)

Declare
@ITEM nvarchar(100),
@BATCHNUM nvarchar(100),
@CHAPTER nvarchar(100),
@PREF nvarchar(100),
@SUPP nvarchar (100),
@DUTYP nvarchar (100),
@PDUTY nvarchar (100),
@QRECD int ,
@TQTY int,
@INVNO nvarchar(100),
@INVDATE Date,
@CUSTOMER nvarchar (100),
@ECCNO nvarchar (100),
@QTYSOLD int,
@DUTY numeric (19,4),
@EDU_CESS numeric (19, 4),
@SEC_CESS numeric (19, 4),
@ADC numeric (19, 4),
@TotalDutyPassed numeric (19, 4),
@EntryNo int,
@Stock int,
@opstock int,
@OBAL int,
@currbatch nvarchar(100),
@prevbatch nvarchar (100),
@curritem nvarchar(100),
@previtem nvarchar(100)

insert into #temp1
(
ITEM,
BATCHNUM,
CHAPTER,
PREF,
SUPP,
DUTYP,
PDUTY,
QRECD,
INVNO,
INVDATE,
CUSTOMER,
ECCNO,
QTYSOLD,
DUTY,
EDU_CESS,
SEC_CESS,
ADC,
TotalDutyPassed,
EntryNo
)
select T0.ItemName 'ITEM',
T0.BatchNum,

ISNULL((Select T3.SuppCatNum from OITM T3 where T3.ItemCode = T0.ItemCode ), '')'CHAPTER',

ISNULL(CASE
When T0.BaseType = '15'
Then (select max(T2.U_Att1+'/'+T2.U_Att2) from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum group by T2.U_batch )
When T0.BaseType = '13'
Then (Select MAX(T2.U_Att1+'/'+T2.U_Att2)from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum group by T2.U_batch)

When T0.BaseType = '20'
Then (select max(T2.U_Att1+'/'+T2.U_Att2) from PCH1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum group by T2.U_batch )
When T0.BaseType = '18'
Then (Select MAX(T2.U_Att1+'/'+T2.U_Att2)from PCH1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum group by T2.U_batch)
End, '') 'PREF',

ISNULL(CASE
When T0.BaseType IN ('18','20', '59') Then T0.CardName
End, '') 'Supplier',

ISNULL(CASE
When T0.BaseType = '20'
Then (select max(convert(varchar,T2.U_CVDP)+'%'+convert(varchar,T2.U_UNEP) + '%'+convert(varchar,T2.U_USHP)+'%'+convert(varchar,T2.U_ADCP)+'%') from PCH1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum group by T2.U_batch )
When T0.BaseType = '18'
Then (Select max(convert(varchar,T2.U_CVDP)+'%'+convert(varchar,T2.U_UNEP))from PCH1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum group by T2.U_batch)
End, '') 'DUTYP',

ISNULL(CASE When T0.BaseType = '20'
Then (Select sum(T2.U_CVD * T2.Quantity) + SUM(T2.U_UNE * T2.Quantity) +SUM(T2.U_USH * T2.Quantity)+SUM(T2.U_ADC * T2.Quantity)
from PCH1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode group by T2.U_batch)
When T0.BaseType = '18'
Then (Select sum(T2.U_CVD * T2.Quantity) + SUM(T2.U_UNE * T2.Quantity) +SUM(T2.U_USH * T2.Quantity)+SUM(T2.U_ADC * T2.Quantity)
from PCH1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode group by T2.U_batch)
End, 0) 'PDUTY',

ISNULL(CASE When T0.Direction = '0'
Then sum(T0.Quantity)
End, 0) 'QRECD',

T0.BaseNum 'INV No',
T0.DocDate 'INV DATE',

ISNULL(CASE
When T0.BaseType IN ('13', '15', '60') Then T0.CardName
End, '') 'Customer',

ISNULL((Select MAX(T4.ECCNo) from CRD7 T4 where T4.CardCode = T0.CardCode), '') 'ECC No',

ISNULL(CASE When T0.Direction = '1'
Then sum(T0.Quantity)
End, 0) 'QTY SOLD',

ISNULL(CASE When T0.BaseType = '15' Then

(Select sum(T2.U_CVD * T2.Quantity)
from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
When T0.BaseType = '13' Then
(Select sum(T2.U_CVD * T2.Quantity)
from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
End, 0) 'DUTY',

ISNULL(CASE When T0.BaseType = '15' Then

(Select sum(T2.U_UNE * T2.Quantity)
from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
When T0.BaseType = '13' Then
(Select sum(T2.U_UNE * T2.Quantity)
from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
End, 0) 'EDU Cess',

ISNULL(CASE When T0.BaseType = '15' Then

(Select sum(T2.U_USH * T2.Quantity)
from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
When T0.BaseType = '13' Then
(Select sum(T2.U_USH * T2.Quantity)
from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
End, 0) 'SEC Cess',

ISNULL(CASE When T0.BaseType = '15' Then

(Select sum(T2.U_ADC * T2.Quantity)
from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
When T0.BaseType = '13' Then
(Select sum(T2.U_ADC * T2.Quantity)
from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
End, 0) 'ADC',

ISNULL(CASE When T0.BaseType = '15' Then

(Select sum(T2.U_CVD * T2.Quantity)+SUM(T2.U_UNE * T2.Quantity)+SUM(T2.U_USH * T2.Quantity)+SUM(T2.U_ADC * T2.Quantity)
from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
When T0.BaseType = '13' Then
(Select sum(T2.U_CVD * T2.Quantity)+SUM(T2.U_UNE * T2.Quantity)+SUM(T2.U_USH * T2.Quantity)+SUM(T2.U_ADC * T2.Quantity)
from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.U_batch = T0.BatchNum and T2.ItemCode = T0.ItemCode
group by T2.U_batch)
End, 0) 'TOTAL DUTY PASSED',

ISNULL(CASE
When T0.BaseType = '20'
Then (select max(T2.U_RG23D) from PCH1 T2 where T2.BaseEntry = T0.BaseEntry and T2.ItemCode = T0.ItemCode )
When T0.BaseType = '18'
Then (select max(T2.U_RG23D) from PCH1 T2 where T2.DocEntry = T0.BaseEntry and T2.ItemCode = T0.ItemCode )

When T0.BaseType = '15'
Then (select max(T2.U_RG23D) from INV1 T2 where T2.BaseEntry = T0.BaseEntry and T2.ItemCode = T0.ItemCode )
When T0.BaseType = '13'
Then (select max(T2.U_RG23D) from INV1 T2 where T2.DocEntry = T0.BaseEntry and T2.ItemCode = T0.ItemCode )
End, '') 'Entry No'

from IBT1 T0
where T0.BaseType in ('13','60','18','59', '15', '20')
and T0.DocDate between @FrDt and @ToDt
group by
T0.BatchNum, T0.ItemCode,T0.ItemName, T0.BatchNum,T0.BaseType,T0.CardCode,T0.CardName,T0.Direction,T0.DocDate,
T0.BaseEntry,T0.BaseNum, T0.LineNum
order by T0.DocDate

DECLARE rt_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
select * from #temp1
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO
@ITEM,
@BATCHNUM,
@CHAPTER,
@PREF ,
@SUPP ,
@DUTYP ,
@PDUTY ,
@QRECD,
@INVNO,
@INVDATE,
@CUSTOMER,
@ECCNO,
@QTYSOLD,
@DUTY,
@EDU_CESS,
@SEC_CESS,
@ADC,
@TotalDutyPassed,
@EntryNo

WHILE @@FETCH_STATUS = 0
Begin

SET @currbatch = @BATCHNUM
SET @curritem = @ITEM

If ((@prevbatch = '') or (@previtem =''))

SET @OBAL =(Select SUM(quantity) -
ISNULL((Select SUM(quantity)from IBT1 where Direction = '1' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt), 0)
from IBT1 where Direction = '0' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt)
SET @Stock = @OBAL

if ((@currbatch <> @prevbatch) or (@curritem <> @previtem))

SET @OBAL =(Select SUM(quantity) -
ISNULL((Select SUM(quantity)from IBT1 where Direction = '1' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt), 0)
from IBT1 where Direction = '0' and BatchNum = @currbatch and ItemName = @curritem and DocDate <@FrDt)
SET @Stock = @OBAL

SET @Stock = @Stock + @QRECD - @QTYSOLD
SET @prevbatch = @BATCHNUM
SET @previtem = @ITEM

insert into #temp Values
(

@ITEM,
@BATCHNUM,
@CHAPTER,
@PREF,
@SUPP,
@DUTYP,
@PDUTY,
@QRECD,
@INVNO,
@INVDATE,
@CUSTOMER,
@ECCNO,
@QTYSOLD,
@DUTY,
@EDU_CESS,
@SEC_CESS,
@ADC,
@TotalDutyPassed,
@EntryNo,
@OBAL,
@Stock

)
FETCH NEXT FROM rt_cursor INTO
@ITEM,
@BATCHNUM,
@CHAPTER,
@PREF ,
@SUPP ,
@DUTYP ,
@PDUTY ,
@QRECD,
@INVNO,
@INVDATE,
@CUSTOMER,
@ECCNO,
@QTYSOLD,
@DUTY,
@EDU_CESS,
@SEC_CESS,
@ADC,
@TotalDutyPassed,
@EntryNo

End

CLOSE rt_cursor
DEALLOCATE rt_cursor
select * from #temp
drop table #temp
Drop Table #temp1
End

/*End*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 02:36:23

please give some sample data and then explain what you want. that will really help rather than posting whole code

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

SaanjhDn86
Starting Member

5 Posts

Posted - 2011-10-30 : 02:41:32

(Select SUM(quantity) -
(Select SUM(quantity)from IBT1 where Direction = '1'
and BatchNum = '1112-300023' and ItemName = 'CONNECTOR' and DocDate <'20110401')
from IBT1 where Direction = '0' and BatchNum = '1112-300023' and ItemName = 'CONNECTOR'
and DocDate <'20110401')

This query is giving "NULL" value, which is wrong.

If i run

select sum(Quantity) from IBT1
where BatchNum = '1112-300023' and ItemName = 'CONNECTOR'
and Direction = '1' group by ItemName

it gives "109"

Similarly,

select sum(Quantity) from IBT1
where BatchNum = '1112-300023' and ItemName = 'CONNECTOR'
and Direction = '0' group by ItemName

gives "244"

So

(Select SUM(quantity) -
(Select SUM(quantity)from IBT1 where Direction = '1'
and BatchNum = '1112-300023' and ItemName = 'CONNECTOR' and DocDate <'20110401')
from IBT1 where Direction = '0' and BatchNum = '1112-300023' and ItemName = 'CONNECTOR'
and DocDate <'20110401')

Should give "244-109" = 135

but it gives NULL, that is why in cursor loop also data is not coming correct. Please help

SaanjhDn86
Starting Member

5 Posts

Posted - 2011-10-30 : 03:03:34

(Select SUM(quantity) -
(Select SUM(quantity)from IBT1 where Direction = '1'
and BatchNum = '1011-200362' and ItemName = 'TONER IR LOW END PRINTER' and DocDate <'20110401' )
from IBT1 where Direction = '0' and BatchNum = '1011-200362' and ItemName = 'TONER IR LOW END PRINTER'
and DocDate <'20110401')

This query is giving "NULL" if there is no transaction before

'20110401' Date, How to use ISNULL function to convert it to "0"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 03:30:25

where's sample data as requested? no point in posting code again

see how to post a question below and give data in required format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

SaanjhDn86
Starting Member

5 Posts

Posted - 2011-10-30 : 03:40:06

I have the following Table namely IBT1 with following details

ItemCode BatchNum Quantity DocDate Direction
I001 22 100 2010-10-25 00:00:00.000 0
I002 22 50 2010-10-27 00:00:00.000 1
Now i want a query where sum quantity for Direction 0 - sum of quantity for direction 1 for a particular batch and item and till a particular date, and there should not be any NULL value if the date is not there in docdate table.

I have written

(Select SUM(quantity) -
(Select SUM(quantity)from IBT1 where (Direction = '1'
and BatchNum = '1011-200362' and ItemName = 'TONER IR LOW END PRINTER') and DocDate <'20110401' )
from IBT1 where (Direction = '0' and BatchNum = '1011-200362' and ItemName = 'TONER IR LOW END PRINTER')
and DocDate <'20110401')

But it is giving NULL value as the date '20110401' is not there in Database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 05:28:03

do you mean this?


SELECT ItemCode,BatchNum,SUM(CASE WHEN Direction=0 THEN Quantity WHEN Direction = 1 THEN -1*Quantity ELSE 0 END) 
FROM table
WHERE DocDate< = yourdatevaluehere
GROUP BY ItemCode,BatchNum

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources