How could this be completed without a cursor

Question

Post reply

How could this be completed without a cursor

gareth.ensor

SSC Enthusiast

Points: 101
More actions
January 19, 2010 at 3:31 am

#144362

Consider the following scenario.
First table contains a list of requirements for bins to be replenished.
CREATE TABLE #wms_requirements
(
[warehouse] [char](2) NOT NULL,
[product] [char](20) NOT NULL,
[bin_number] [char](10) NOT NULL,
[req_quantity] [float] NULL
)
INSERT INTO
#wms_requirements
SELECT
'10',
'ABCD',
'KBAN33',
'2000'
I then have a second table which contains the batches of products I have in stock
CREATE TABLE #wms_batch_queue
(
[warehouse] [char](2) NOT NULL,
[product] [char](20) NOT NULL,
[bin_number] [char](10) NOT NULL,
[pallet_number] [char](10) NOT NULL,
[batch_number] [char](10) NOT NULL,
[fifo_date] [datetime] NOT NULL,
[quantity_free] [float] NOT NULL
)
INSERT INTO
#wms_batch_queue
SELECT
'10',
'ABCD',
'R0102D3',
'P00085461',
'Q8745',
'11/01/2010 00:00:00',
'300'
UNION ALL
SELECT
'10',
'ABCD',
'R0108D1',
'P00054161',
'Q7842',
'12/20/2009 00:00:00',
'1100'
UNION ALL
SELECT
'10',
'ABCD',
'R0702D1',
'P00054761',
'Q7246',
'11/20/2009 00:00:00',
'900'
What I need to be able to do for each requirement is to select the oldest batches from the batch_queue table and populate into a new table until the req_quantity is equaled or exceeded by the total of the batches selected.
The only way I can think of doing this would be with a cursor.
Can you give me some hints on how it would be possible to do this without using a cursor?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

WITH cte(warehouse, product,req_quantity, batch_number,RowNumber) AS

(

SELECT a1.warehouse, a1.product, a1.req_quantity, a2.batch_number,

ROW_NUMBER() OVER(PARTITION BY a1.warehouse, a1.product ORDER BY fifo_date ASC) AS 'RowNumber'

FROM #wms_requirements a1

INNER JOIN #wms_batch_queue a2 ON a1.warehouse = a2.warehouse AND a1.product = a2.product

)

select *

--into #newtable

from cte where rownumber <= req_quantity

🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

CREATE TABLE #wms_requirements

(

[warehouse] [char](2) NOT NULL,

[product] [char](20) NOT NULL,

[bin_number] [char](10) NOT NULL,

[req_quantity] [float] NULL

)

INSERT INTO #wms_requirements

SELECT

'10',

'ABCD',

'KBAN33',

'2000'

--------------------------------------------------------

CREATE TABLE #wms_batch_queue

(

[warehouse] [char](2) NOT NULL,

[product] [char](20) NOT NULL,

[bin_number] [char](10) NOT NULL,

[pallet_number] [char](10) NOT NULL,

[batch_number] [char](10) NOT NULL,

[fifo_date] [datetime] NOT NULL,

[quantity_free] [float] NOT NULL

)

INSERT INTO #wms_batch_queue

SELECT '10', 'ABCD', 'R0102D3', 'P00085461', 'Q8745', '11/01/2010 00:00:00', '300' UNION ALL

SELECT '10', 'ABCD', 'R0108D1', 'P00054161', 'Q7842', '12/20/2009 00:00:00', '1100' UNION ALL

SELECT '10', 'ABCD', 'R0702D1', 'P00054761', 'Q7246', '11/20/2009 00:00:00', '900'

DECLARE @Maxbins INT -- the maximum number of rows in #wms_batch_queue per parent in #wms_requirements

SET @Maxbins = 3

;WITH q AS (SELECT RowID = ROW_NUMBER() OVER (ORDER BY fifo_date), *

FROM #wms_batch_queue)

SELECT r.*, '#' AS '#',

q.[bin_number], q.[pallet_number], q.[batch_number], q.[fifo_date], q.[quantity_free],

its.TotalFree AS RunningTotalFree

FROM #wms_requirements r

INNER JOIN q ON q.[warehouse] = r.warehouse AND q.product = r.product

INNER JOIN (

SELECT ca.*

FROM (SELECT TOP (@Maxbins) n = ROW_NUMBER() OVER (ORDER BY [name]) FROM master.dbo.syscolumns) n

CROSS APPLY (

SELECT n.n, [warehouse], [product], SUM([quantity_free]) AS TotalFree

FROM q

WHERE RowID BETWEEN 1 AND n.n

GROUP BY [warehouse], [product]

) ca

) its ON its.n = q.RowID AND its.TotalFree <= r.[req_quantity]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gareth.ensor SSC Enthusiast Points: 101 More actions · Answer 3

Many Thanks to both. I will have a play and I'm sure I will be back with questions!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hey Gareth

The solution I've posted above works, but looking at it I can't help thinking of one of Jeff Moden's mantras - make it work, make it fast, make it pretty. It's at stage 1, there's room for improvement. Give it a try by all means, but you can bet your a$$ that within a few hours one of the regulars will have posted up a huge improvement.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Here's version 2 Gareth, with an extended sample data set.

-- test data --

DROP TABLE #wms_requirements

DROP TABLE #wms_batch_queue

CREATE TABLE #wms_requirements

(

[warehouse] [char](2) NOT NULL,

[product] [char](20) NOT NULL,

[bin_number] [char](10) NOT NULL,

[req_quantity] [float] NULL

)

INSERT INTO #wms_requirements ([warehouse], [product], [bin_number], [req_quantity])

SELECT '10', 'ABCD', 'KBAN33', '2000' UNION ALL

SELECT '10', 'ABCE', 'KBAN34', '1600' UNION ALL

SELECT '10', 'ABCF', 'KBAN35', '2250'

--------------------------------------------------------

CREATE TABLE #wms_batch_queue

(

[warehouse] [char](2) NOT NULL,

[product] [char](20) NOT NULL,

[bin_number] [char](10) NOT NULL,

[pallet_number] [char](10) NOT NULL,

[batch_number] [char](10) NOT NULL,

[fifo_date] [datetime] NOT NULL,

[quantity_free] [float] NOT NULL

)

INSERT INTO #wms_batch_queue

SELECT '10', 'ABCD', 'R0102D3', 'P00000001', 'Q8745', '11/01/2010 00:00:00', '300' UNION ALL

SELECT '10', 'ABCD', 'R0108D1', 'P00000002', 'Q7842', '12/20/2009 00:00:00', '1100' UNION ALL

SELECT '10', 'ABCD', 'R0702D1', 'P00000003', 'Q7246', '11/20/2009 00:00:00', '900' UNION ALL

SELECT '10', 'ABCE', 'R0102D3', 'P00000004', 'Q8745', '11/01/2010 00:00:00', '200' UNION ALL

SELECT '10', 'ABCE', 'R0108D1', 'P00000005', 'Q7842', '12/20/2009 00:00:00', '700' UNION ALL

SELECT '10', 'ABCE', 'R0702D1', 'P00000006', 'Q7246', '11/20/2009 00:00:00', '600' UNION ALL

SELECT '10', 'ABCF', 'R0102D3', 'P00000007', 'Q8745', '11/01/2010 00:00:00', '1300' UNION ALL

SELECT '10', 'ABCF', 'R0108D1', 'P00000008', 'Q7842', '12/20/2009 00:00:00', '100' UNION ALL

SELECT '10', 'ABCF', 'R0702D1', 'P00000009', 'Q7246', '11/20/2009 00:00:00', '800'

-- /test data --

-- Solution --

DECLARE @Maxbins INT -- the maximum number of rows in #wms_batch_queue per parent in #wms_requirements

SET @Maxbins = 3

;WITH q AS (SELECT RowID = ROW_NUMBER() OVER (PARTITION BY [warehouse], [product] ORDER BY fifo_date), *

FROM #wms_batch_queue)

SELECT r.*, '#' AS '#',

q.[bin_number], q.[pallet_number], q.[batch_number], q.[fifo_date], q.[quantity_free],

its.TotalFree AS RunningTotalFree

FROM #wms_requirements r

INNER JOIN q ON q.[warehouse] = r.warehouse AND q.product = r.product

INNER JOIN (

SELECT ca.*

FROM (SELECT TOP (@Maxbins) n = ROW_NUMBER() OVER (ORDER BY [name]) FROM master.dbo.syscolumns) n

CROSS APPLY (

SELECT n.n, [warehouse], [product], SUM([quantity_free]) AS TotalFree

FROM q

WHERE RowID BETWEEN 1 AND n.n

GROUP BY [warehouse], [product]

) ca

) its ON its.[warehouse] = q.warehouse AND its.product = q.product

AND its.n = q.RowID AND its.TotalFree <= r.[req_quantity]

ORDER BY r.[warehouse], r.[product], r.[bin_number]

-- /Solution --

Results:

warehouse product bin_number req_quantity # bin_number pallet_number batch_number fifo_date quantity_free RunningTotalFree

--------- -------------------- ---------- ---------------------- ---- ---------- ------------- ------------ ----------------------- ---------------------- ----------------------

10 ABCD KBAN33 2000 # R0702D1 P00000003 Q7246 2009-11-20 00:00:00.000 900 900

10 ABCD KBAN33 2000 # R0108D1 P00000002 Q7842 2009-12-20 00:00:00.000 1100 2000

10 ABCE KBAN34 1600 # R0702D1 P00000006 Q7246 2009-11-20 00:00:00.000 600 600

10 ABCE KBAN34 1600 # R0108D1 P00000005 Q7842 2009-12-20 00:00:00.000 700 1300

10 ABCE KBAN34 1600 # R0102D3 P00000004 Q8745 2010-11-01 00:00:00.000 200 1500

10 ABCF KBAN35 2250 # R0702D1 P00000009 Q7246 2009-11-20 00:00:00.000 800 800

10 ABCF KBAN35 2250 # R0108D1 P00000008 Q7842 2009-12-20 00:00:00.000 100 900

10 ABCF KBAN35 2250 # R0102D3 P00000007 Q8745 2010-11-01 00:00:00.000 1300 2200

(8 row(s) affected)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden