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

gareth.ensor

SSC Enthusiast

Points: 101

January 19, 2010 at 8:46 am

#1105480

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