February 9, 2009 at 2:34 am
I have 2 tables TableA (Id,PrName, Qty), TableB(PrName, Qty, Loc)
I need to insert in 3rd table records from 2 tables matching the qty values till either of the 2 table quantity is completely inserted. I have tried the below by declaring cursors for both tables but not getting proper result.
The values in TableA look like
Id, PrName, Qty
1, A, 100
2, A, 20
3, B, 100
4, B, 10
5, B, 20
Values of Table B are
A, 110, LocA
A, 10, LocB
B, 40, LocC
B, 60, LocD
B, 30, LocE
Require a query to insert in Table C that should get values as:
1, A, 100, LocA
2, A, 10, LocA
2, A, 10, LocB
3, B, 40, LocC
3, B, 60, LocD
4, B, 10, LocE
5, B, 20, LocE
February 9, 2009 at 6:46 am
Could you explain more about the requirement & the logic behind it? Also the script of the cursor you have tried so for. And also, the table creation & sample data script (in form of DDL & DML scripts)...
--Ramesh
February 9, 2009 at 9:20 am
I have a solution (using cursors) which produces the required results, but I don't know how robust it is. I haven't come up with a set-based solution (yet).
create table #a (id int, prname char(1), qty int)
create table #b (prname char(1), qty int, loc char(4))
create table #c (id int, prname char(1), qty int, loc char(4))
insert #a values(1,'A',100)
insert #a values(2,'A',20)
insert #a values(3,'B',100)
insert #a values(4,'B',10)
insert #a values(5,'B',20)
insert #b values('A',110,'LocA')
insert #b values('A',10,'LocB')
insert #b values('B',40,'LocC')
insert #b values('B',60,'LocD')
insert #b values('B',30,'LocE')
if exists (select * from (
select prname,sum(qty) as 'qty' from #a group by prname
) a join (
select prname,sum(qty) as 'qty' from #b group by prname
) b on a.prname=b.prname and a.qty <> b.qty
)
print 'Warning: numbers don''t add up' -- should probably raise an error
declare @id int, @pra char(1), @qta int, @prb char(1), @qtb int, @loc char(4)
declare l1 cursor for select id,prname,qty from #a order by prname,id
declare l2 cursor for select prname,qty,loc from #b order by prname,loc
open l1
fetch next from l1 into @id, @pra, @qta
open l2
fetch next from l2 into @prb, @qtb, @loc
while (@@fetch_status=0)
begin
if @qta > @qtb
begin
insert #c values (@id, @pra, @qtb, @loc)
set @qta=@qta-@qtb
fetch next from l2 into @prb, @qtb, @loc
end
else
begin
insert #c values (@id, @pra, @qta, @loc)
set @qtb=@qtb-@qta
fetch next from l1 into @id, @pra, @qta
end
if @qta=0
fetch next from l1 into @id, @pra, @qta
if @qtb=0
fetch next from l2 into @prb, @qtb, @loc
end
close l1
deallocate l1
close l2
deallocate l2
select * from #c
drop table #a
drop table #b
drop table #c
Where did your solution go wrong?
[Edit: Added ORDER BY clauses to cursors]
Derek
February 9, 2009 at 10:16 am
Do you realize that a table is an UNORDERED set?
As long as you are prepared to order TableA by Id and TableB By Loc,
which may not make sense with real data, then something like the following
should work:
-- *** Test Data ***
CREATE TABLE #a
(
    [Id] int NOT NULL
    ,PrName varchar(20) NOT NULL
    ,Qty int NOT NULL
)
INSERT INTO #a
SELECT 1, 'A', 100 UNION ALL
SELECT 2, 'A', 20 UNION ALL
SELECT 3, 'B', 100 UNION ALL
SELECT 4, 'B', 10 UNION ALL
SELECT 5, 'B', 20
CREATE TABLE #b
(
    PrName varchar(20) NOT NULL
    ,Qty int NOT NULL
    ,Loc varchar(20) NOT NULL
)
INSERT INTO #b
SELECT 'A', 110, 'LocA' UNION ALL
SELECT 'A', 10, 'LocB' UNION ALL
SELECT 'B', 40, 'LocC' UNION ALL
SELECT 'B', 60, 'LocD' UNION ALL
SELECT 'B', 30, 'LocE'
-- *** End Test Data ***
;WITH A (RowId, [Id], PrName, Qty)
AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY PrName ORDER BY [Id])
        ,[Id], PrName, Qty
    FROM #a
)
,B (RowId, PrName, Qty, Loc)
AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY PrName ORDER BY Loc)
        ,PrName, Qty, Loc
    FROM #b
)
,QtyLocs ([Id], PrName, Qty, Loc, AQty, BQty, ARow, BRow)
AS
(
    SELECT A.[Id]
        ,A.PrName
        ,CASE
            WHEN B.Qty < A.Qty
            THEN B.Qty
            ELSE A.Qty
        END
        ,B.Loc
        ,CASE
            WHEN B.Qty < A.Qty
            THEN A.Qty - B.Qty
            ELSE CAST(NULL AS int)
        END
        ,CASE
            WHEN A.Qty < B.Qty
            THEN B.Qty - A.Qty
            ELSE CAST(NULL AS int)
        END
        ,A.RowId
        ,B.RowId
    FROM A
        JOIN B
            ON A.PrName = B.PrName
    WHERE A.RowId = 1
        AND B.RowId = 1
    UNION ALL
    
    SELECT A.[Id]
        ,A.PrName
        ,CASE
            WHEN ISNULL(Q.BQty, B.Qty) < ISNULL(Q.AQty, A.Qty)
            THEN ISNULL(Q.BQty, B.Qty)
            ELSE ISNULL(Q.AQty, A.Qty)
        END
        ,B.Loc
        ,CASE
            WHEN ISNULL(Q.BQty, B.Qty) < ISNULL(Q.AQty, A.Qty)
            THEN ISNULL(Q.AQty, A.Qty) - ISNULL(Q.BQty, B.Qty)
            ELSE CAST(NULL AS int)
        END
        ,CASE
            WHEN ISNULL(Q.AQty, A.Qty) < ISNULL(Q.BQty, B.Qty)
            THEN ISNULL(Q.BQty, B.Qty) - ISNULL(Q.AQty, A.Qty)
            ELSE CAST(NULL AS int)
        END
        ,A.RowId
        ,B.RowId
    FROM A
        JOIN B
            ON A.PrName = B.PrName
        JOIN QtyLocs Q
            ON A.PrName = Q.PrName
    WHERE A.RowId =
        CASE
            WHEN Q.AQty IS NULL
            THEN Q.ARow + 1
            ELSE Q.ARow
        END
        AND B.RowId =
            CASE
                WHEN Q.BQty IS NULL
                THEN Q.BRow + 1
                ELSE Q.BRow
            END
)
SELECT [Id], PrName, Qty, Loc
FROM QtyLocs
ORDER BY PrName, [Id]
February 9, 2009 at 11:45 pm
Thanks ken,
your solution worked perfectly. I was earlier trying with cursors which I feel I should stop using after looking at such a simpler query from you. Thanks again.
Jannaty
February 10, 2009 at 3:18 am
Ken McKelvey (2/9/2009)
Do you realize that a table is an UNORDERED set?[/code]
Yes, i do.
After I posted (and was at home) it occurred to me that I needed to update my solution to add ORDER BY clauses to the cursor definitions, since I was relying on the order the data was loaded. I was also aware that my solution was possibly not very robust. It was more a proof-of-concept that a solution was possible with cursors as the OP was trying to do.
I suspected a set-based solution would be possible but didn't have the time to work it out.
I think you still need a separate consistency check that the total quantities for each PrName match in the 2 tables, since Ken's solution doesn't give any warning if the numbers don't add up. For example, if you change the data for 4,B,10 to 4,B,30 then the output for Id=5 simply isn't reported. Of course, this possibly isn't a problem for the real data.
Derek
February 10, 2009 at 3:38 am
Derek – sorry for the confusion but my question was aimed at the OP as he seemed to be relying on the order the data was placed in the tables.
My understanding of the OP’s question was that he wanted the output to be until either table had been completely processed. Maybe I was wrong.
I suspect that temp tables and iteration would be faster than CTEs with recursion but CTEs are easier to write.
February 10, 2009 at 4:13 am
Ken McKelvey (2/10/2009)
Derek – sorry for the confusion but my question was aimed at the OP as he seemed to be relying on the order the data was placed in the tables.
Fine, but, as mentioned, I realised I was guilty of the same fault. Of course, this could be why his cursor solutions didn't work; mine was OK because the data had just been loaded in a known order - the real data may be randomised.
My understanding of the OP’s question was that he wanted the output to be until either table had been completely processed. Maybe I was wrong.
I didn't think this was clear from the sample data as, in this case, the totals do add up. I assumed that this is a normalization/rationalization exercise where the 2 input tables come from 2 applications/databases where, for example, one is the quantities of product assigned to customers and the other is quantities stored at given locations. Since they both refer to the same physical product, a rationalization would be to merge the 2 tables into 1 and then use views for the old information.
I suspect that temp tables and iteration would be faster than CTEs with recursion but CTEs are easier to write.
Your solution doesn't use recursion. I think someone would have to test to see if temp tables were faster. Often, the time taken to test wouldn't be worth it, especially in a one-off situation.
Derek
February 10, 2009 at 6:44 am
After some efforts my cursor solution did work as you mentioned correctly I faced issues in the real data with the solution. Here is the solution that worked:
Few changes in this as per real data. There is actually a date_ field as integer in place of id which has to be taken from tableA and bin (location) from TableB.
declare @aq int
declare @bq int
declare @d int
declare @b-2 varchar(60)
declare @p varchar(60)
declare @p2 varchar(60)
declare @id int
declare @id2 int
declare A1 cursor for select PrName, date, Qty, id from TableA order by PrName
Open A1
take1:
fetch next from a1 into @p, @d, @aq, @id2
while @@fetch_status<>0 goto endofprogram
take2:
begin
select top 1 @p2=Prname, @bq=Qty, @b-2=bin, @id=id from TableB where PrName=@p
if @aq>=@bq
begin
insert into TableC (date_, PrName, Qty, bin)
values
(@d, @p, @bq, @b-2 )
set @aq=@aq-@bq
delete from TableB where id=@id
if @aq>0
begin
goto take2
end
if @aq=0
begin
goto take1
end
end
if @aq<@bq
begin
insert into TableC (date_, PrName, Qty, bin)
values
(@d, @p, @aq, @b-2)
set @bq=@bq-@aq
update TableB set Qty=@bq where Id=@id
goto take1
end
end
endofprogram:
close A1
deallocate A1
February 10, 2009 at 6:59 am
Forgot mentioning that I have inserted Identity column in both tables Id to use them for update and deleting the original record. I didnt want to keep the original table.
February 11, 2009 at 5:32 am
A few comments.
Firstly, I try to avoid using GOTO as it can make the code difficult to figure out when someone comes back to it months later.
I also try to avoid destructive solutions as it means you can't just repeat the test if something goes wrong. Hence my use of 2 cursors.
Many people here would recommend avoiding cursors as they are often inefficent. If somethings only going to be run once, as here, it's probably OK as time spent looking for a set-based solution won't be recovered.
Your use of "WHILE @@fetch_status<>0 GOTO endofprogam" is unusual in that WHILE usually starts a loop. In this case, as soon as the loop is entered, it exits via GOTO. I'd have used "IF ... GOTO ..." or rather, because I avoid GOTOs, "WHILE @@fetch_status=0 BEGIN ... END endofprogram:".
Anyway, I'm glad you found a solutuon which worked.
Derek
February 11, 2009 at 7:38 am
Thanks for the comment. As you can derive from the code I have not done much of SQL programming and am carrying the concept of basic programming methods I had learnt few years back while learning SQL functions. Nevertheless it was great to get all the help from this forum and thankful to all of you.
Jannaty
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply