March 14, 2019 at 3:06 pm
Hello
I am working on a complex problem
There is a problem with the query
DECLARE @AMTTOMARK DECIMAL(38,2),@CVIDDR VARCHAR(11),@CVIDCR VARCHAR(11),@MCVID VARCHAR(11),@CURRMARKAMT DECIMAL(38,2),@skipdr INT,@skipcr INT
DECLARE @Balamtdr DECIMAL(38,2)
DECLARE @amtdr DECIMAL(38,2)
DECLARE @balamtcr DECIMAL(38,2)
DECLARE @amtcr DECIMAL(38,2)
DECLARE @CVID VARCHAR(11)
;
IF EXISTS (SELECT * FROM sys.tables WHERE name= 'Cashvchr') BEGIN
DROP TABLE Cashvchr
END
;
CREATE TABLE [dbo].[Cashvchr](
[TRANSACTIONID] INT NOT NULL,
[VCHRDATE] [datetime] NULL,
[CustomerID] [varchar](5) NOT NULL,
[DRCR] [varchar](1) NOT NULL,
[Amount] DECIMAL(38,2) NOT NULL
) ON [PRIMARY]
;
INSERT INTO [dbo].[Cashvchr](TRANSACTIONID,VCHRDATE,CustomerID,DRCR,Amount)
SELECT '1','2018-01-01 00:00:00.000','RAS12','D','2000' UNION ALL
SELECT '2','2018-01-02 00:00:00.000','RAS12','D','3000' UNION ALL
SELECT '3','2018-01-03 00:00:00.000','RAS12','C','4000' UNION ALL
SELECT '4','2018-01-04 00:00:00.000','RAS12','D','5000' UNION ALL
SELECT '5','2018-01-05 00:00:00.000','RAS12','C','1000' UNION ALL
SELECT '6','2018-01-06 00:00:00.000','RAS12','C','6000' UNION ALL
SELECT '7','2018-01-07 00:00:00.000','RAS12','D','3000'
;
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name= 'TargetTable') BEGIN
CREATE TABLE TargetTable (CASHVCHRID INT,AMOUNT DECIMAL(38,2),MARKEDCASHVCHRID INT)
END
;
declare @ıd varchar(15)
DECLARE custerr CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
select cv.CustomerID
From Cashvchr cv
Group by cv.CustomerID
having sum(case when cv.drcr='D' then cv.Amount else 0 end) <> 0
and sum(case when cv.drcr='C' then cv.Amount else 0 end) <> 0
OPEN custerr
FETCH NEXT FROM custerr
INTO @ıd
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE CVDR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
select TRANSACTIONID, cv.Amount
From Cashvchr cv
where cv.CustomerID=@ıd
AND CV.DRCR = 'D'
ORDER BY VCHRDATE,TRANSACTIONID
OPEN CVDR
FETCH NEXT FROM CVDR
INTO @CVIDDR, @AMTDR
DECLARE CVCR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
select TRANSACTIONID, cv.Amount
From Cashvchr cv
where cv.CustomerID=@ıd
AND CV.DRCR = 'C'
ORDER BY VCHRDATE,TRANSACTIONID
OPEN CVCR
FETCH NEXT FROM CVCR
INTO @CVIDCR, @AMTCR
BEGIN
set @Balamtdr = @amtdr
set @balamtcr = @amtcr
-- WHILE @AMTTOMARK > 0
-- BEGIN
SET @CVID = @CVIDDR
SET @MCVID = @CVIDCR
SET @CURRMARKAMT=0
set @skipdr = 0
set @skipcr = 0
IF @BALAMTDR > @BALAMTCR -- i.e. balance debit amount to be marked is bigger from bal.credit amt THEN skip CREDIT
begin
SET @CURRMARKAMT = @balAMTCR
set @skipCr = 1
set @balamtdr = @balamtdr - @balamtcr
end
ELSE IF @balAMTDR < @balAMTCR -- i.e. balance Credit amount is bigger THEN skip Debit
begin
SET @CURRMARKAMT = @balAMTDR
set @skipdr = 1
set @balamtcr = @balamtCr - @balamtdr
end
ELSE -- i.e. balance Credit & Debit amount is same, mark and skip both
begin
SET @CURRMARKAMT = @balamtdr
set @balamtcr = @balamtCr - @balamtdr
set @skipdr = 1
set @skipcr = 1
end
----
INSERT INTO TargetTable
(CASHVCHRID,AMOUNT,MARKEDCASHVCHRID)
VALUES
(@CVIDDR,@CURRMARKAMT,@CVIDCR)
set @amttomark = @amttomark - @currmarkamt
if @skipdr = 1 and @amttomark > 0
begin
FETCH next from CVDR INTO @CVIDDR, @AMTDR
if @@fetch_status <> 0
begin
set @amttomark = 0
end
else
begin
set @balamtdr = @amtdr
end
-- end if
end
-- end if @skipdr = 1
if @skipcr = 1 and @amttomark > 0
begin
FETCH next from CVCR INTO @CVIDCR, @AMTCR
if @@fetch_status <> 0
begin
set @amttomark = 0
end
else
begin
set @balamtcr = @amtcr
end
-- end if
end
END
CLOSE CVCR
DEALLOCATE CVCR
CLOSE CVDR
DEALLOCATE CVDR
FETCH NEXT FROM custerr
INTO @ıd
end
CLOSE custerr
DEALLOCATE custerr
----WHILE @AMTTOMARK > 0 FOR DR/CR skip FOR ONE CUSTOMER
---END
SELECT * FROM TargetTable
March 14, 2019 at 9:02 pm
meryemkurs072 - Thursday, March 14, 2019 3:06 PMHello
I am working on a complex problem
There is a problem with the query
DECLARE @AMTTOMARK DECIMAL(38,2),@CVIDDR VARCHAR(11),@CVIDCR VARCHAR(11),@MCVID VARCHAR(11),@CURRMARKAMT DECIMAL(38,2),@skipdr INT,@skipcr INT
DECLARE @Balamtdr DECIMAL(38,2)
DECLARE @amtdr DECIMAL(38,2)
DECLARE @balamtcr DECIMAL(38,2)
DECLARE @amtcr DECIMAL(38,2)
DECLARE @CVID VARCHAR(11)
;
IF EXISTS (SELECT * FROM sys.tables WHERE name= 'Cashvchr') BEGIN
DROP TABLE Cashvchr
END
;CREATE TABLE [dbo].[Cashvchr](
[TRANSACTIONID] INT NOT NULL,
[VCHRDATE] [datetime] NULL,
[CustomerID] [varchar](5) NOT NULL,
[DRCR] [varchar](1) NOT NULL,
[Amount] DECIMAL(38,2) NOT NULL
) ON [PRIMARY]
;INSERT INTO [dbo].[Cashvchr](TRANSACTIONID,VCHRDATE,CustomerID,DRCR,Amount)
SELECT '1','2018-01-01 00:00:00.000','RAS12','D','2000' UNION ALL
SELECT '2','2018-01-02 00:00:00.000','RAS12','D','3000' UNION ALL
SELECT '3','2018-01-03 00:00:00.000','RAS12','C','4000' UNION ALL
SELECT '4','2018-01-04 00:00:00.000','RAS12','D','5000' UNION ALL
SELECT '5','2018-01-05 00:00:00.000','RAS12','C','1000' UNION ALL
SELECT '6','2018-01-06 00:00:00.000','RAS12','C','6000' UNION ALL
SELECT '7','2018-01-07 00:00:00.000','RAS12','D','3000'
;IF NOT EXISTS (SELECT * FROM sys.tables WHERE name= 'TargetTable') BEGIN
CREATE TABLE TargetTable (CASHVCHRID INT,AMOUNT DECIMAL(38,2),MARKEDCASHVCHRID INT)
END;
TRUNCATE TABLE TargetTable
;
declare @ıd varchar(15)DECLARE custerr CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
select cv.CustomerID
From Cashvchr cv
Group by cv.CustomerID
having sum(case when cv.drcr='D' then cv.Amount else 0 end) <> 0
and sum(case when cv.drcr='C' then cv.Amount else 0 end) <> 0
OPEN custerrFETCH NEXT FROM custerr
INTO @ıdwhile @@FETCH_STATUS=0
BEGIN
DECLARE CVDR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
select TRANSACTIONID, cv.Amount
From Cashvchr cv
where cv.CustomerID=@ıd
AND CV.DRCR = 'D'
ORDER BY VCHRDATE,TRANSACTIONID
OPEN CVDRFETCH NEXT FROM CVDR
INTO @CVIDDR, @AMTDRDECLARE CVCR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
select TRANSACTIONID, cv.Amount
From Cashvchr cv
where cv.CustomerID=@ıd
AND CV.DRCR = 'C'
ORDER BY VCHRDATE,TRANSACTIONID
OPEN CVCRFETCH NEXT FROM CVCR
INTO @CVIDCR, @AMTCR
while @@FETCH_STATUS=0
BEGINset @Balamtdr = @amtdr
set @balamtcr = @amtcr
SET @CVID = @CVIDDR
SET @MCVID = @CVIDCR
SET @CURRMARKAMT=0
set @skipdr = 0
set @skipcr = 0
IF @BALAMTDR > @BALAMTCR -- i.e. balance debit amount to be marked is bigger from bal.credit amt THEN skip CREDIT
begin
SET @CURRMARKAMT = @balAMTCR
set @skipCr = 1
set @balamtdr = @balamtdr - @balamtcrend
ELSE IF @balAMTDR < @balAMTCR -- i.e. balance Credit amount is bigger THEN skip Debit
begin
SET @CURRMARKAMT = @balAMTDR
set @skipdr = 1
set @balamtcr = @balamtCr - @balamtdr
end
ELSE -- i.e. balance Credit & Debit amount is same, mark and skip both
begin
SET @CURRMARKAMT = @balamtdr
set @balamtcr = @balamtCr - @balamtdr
set @skipdr = 1
set @skipcr = 1
END
----
INSERT INTO TargetTable
(CASHVCHRID,AMOUNT,MARKEDCASHVCHRID)
VALUES
(@CVIDDR,@CURRMARKAMT,@CVIDCR)--- set @amttomark = @amttomark - @currmarkamt
if @skipdr = 1 and @currmarkamt > 0
begin
FETCH next from CVDR INTO @CVIDDR, @AMTDR
if @@fetch_status <> 0
begin
set @currmarkamt = 0
end
else
begin
set @balamtdr = @amtdr
end
-- end if
end
-- end if @skipdr = 1
if @skipcr = 1 and @currmarkamt > 0
begin
FETCH next from CVCR INTO @CVIDCR, @AMTCR
if @@fetch_status <> 0
begin
set @currmarkamt = 0
end
else
begin
set @balamtcr = @amtcr
end
-- end ifend
-- end if @skipcr = 1
-- END
--- END
----WHILE @AMTTOMARK > 0 FOR DR/CR skip FOR ONE CUSTOMER
---END
end
FETCH next from custerr INTO @ıd
endSELECT * FROM TargetTable
You should add a couple of comments to the spreadsheet to identify what is expected and what is not. At least identify how the run results of the code you posted aren't what you want and provide a hint as to what the code is supposed to do in your currently uncomented code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2019 at 1:26 am
I am working on a complex problem - Bill Marking for Ageing Analysis. The data is like this :
Source Table :
TrxnID, Date, CustomerID, DebitCredit, Amount
-----------------------------------------------
1 01-Apr, RAS12, D, 2000
2 01-Apr, RAS12, D, 3000
3 02-Apr, RAS12, C, 4000
4 03-Apr, RAS12, D, 5000
5 04-Apr, RAS12, C, 1000
6 10-Apr, RAS12, C, 6000
7 25-Apr, RAS12, D, 3000
So, Total Debit : 13,000 and Total Credit : 11,000 and Total Balance is 2,000 Debit.
So the objective is to mark all Credit records with the respective debit records based on the date (FIFO), and store the marking scheme for each record in a separate table as shown below.
The target table has marking detail of each record
Target Table
TrxnID, MarkedTrxnID, MarkedAmount
------------------------------------
1,c3,2000 **Rem : 1 balance=0, 3 balance= 1000
2,c3,2000 **Rem : 2 balance=1000, 3 balance= 0
2,c5,1000 **Rem : 2 balance=0, 5 balance= 0
4,c6,5000 **Rem : 4 balance=0, 6 balance= 1000
7,c6,1000 **Rem : 7 balance=2000, 6 balance= 0
I have been using old fashioned cursor based approach and that too becomes quite complex, but believe that there must be a set based mechanism to handle this.
Any help would be greatly appreciated. We are using SQL Server 2008 R2.
Thanks
March 15, 2019 at 7:36 am
Based on what you've posted, I'm confused as to the nature of the marking scheme.
Two consecutive debits appear, putting the balance at what I would consider a negative 5000.
Then you see a credit for just 4000 leaving a negative 1000 balance. This continues with yet
another debit for 5000, leaving a negative 6000 balance, then there's a credit for 1000, followed
by a credit for 6000, leaving balances of negative 5000 and positive 1000, respectively. Finally,
there's a debit for 3000, leaving a final balance of negative 2000. The idea that you can tie any
given debit record to only certain specific credit records doesn't really make any logical sense
whatsoever, nor does it likely provide any benefit of any kind for a business to do such a thing,
unless there's something in play that we're not privy to. Please explain what the benefit of this
is, because I can't come up with one that uniquely makes this more beneficial than just having
the record of all the transactions and the initial balance. If you want a balance after each transaction,
or even a balance for every given day, that I get. But tying one transaction to any other? Really?
What is it that we don't know?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2019 at 9:16 am
Thank you for your interest.
The result of the query will be as follows. I want to close the debt.
TrxnID MarkedAmount MarkedTrxnID
1 2000.00 3
2 2000.00 3
2 1000.00 5
4 5000.00 6
7 1000.00 6
March 20, 2019 at 10:19 am
You keep using the term "close the debt", as if it were something you could make mean whatever it needed to, when the well-known and generally accepted meaning is for a debt to be paid in full (a 0 balance). This data doesn't bring the debt to closure, and you have not explained any benefit of any kind to what you are doing. Clearly, each transaction that pays down the debt may well do so against more than one of the debt increases, so again, I fail to see the benefit of any of this. Unless you have some kind of FIFO or LIFO queue to deal with here, I'm not going anywhere with this until it's fully explained.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply