(SQL Server 2008 R2) I need to get a result set the link between two tables linked by key items and a special link to date. I have a couple of troubles to know how to get this link and I can't use this a stored procedure, so it must be a view. Here is my case:
Table Materials:
You will see how an item is composed of materials but these materials were purchased in different dates in 2016 and 2017.
Item BOM Date Warehouse Material ID BOM #
CDIL-PTU208XXX-00 2016-12-08 00:00:00.000 MAIN SDCGF-208L-0250-001 B000001211
CDIL-PTU208XXX-00 2016-12-08 00:00:00.000 MAIN SDCGF-208L-0250-002 B000001211
CDIL-PTU208XXX-00 2016-12-08 00:00:00.000 MAIN SDCGF-208L-0300-001 B000001211
CDIL-PTU208XXX-00 2016-12-08 00:00:00.000 MAIN SDCXX-PTU-0208-001 B000001211
CDIL-PTU208XXX-00 2017-06-28 00:00:00.000 MAIN SDCGF-208L-0250-001 B000001358
CDIL-PTU208XXX-00 2017-06-28 00:00:00.000 MAIN SDCGF-208L-0250-002 B000001358
CDIL-PTU208XXX-00 2017-06-28 00:00:00.000 MAIN SDCGF-208L-0300-001 B000001358
CDIL-PTU208XXX-00 2017-06-28 00:00:00.000 MAIN SDCGF-208L-0300-003 B000001358
CDIL-PTU208XXX-00 2017-06-28 00:00:00.000 MAIN SDCXX-PTU-0208-001 B000001358
Table Invoices:
Invoice Date Item
2017-09-25 00:00:00.000 CDIL-PTU208XXX-00
2017-09-22 00:00:00.000 CDIL-PTU208XXX-00
2017-09-20 00:00:00.000 CDIL-PTU208XXX-00
2017-09-20 00:00:00.000 CDIL-PTU208XXX-00
2017-09-15 00:00:00.000 CDIL-PTU208XXX-00
2017-09-08 00:00:00.000 CDIL-PTU208XXX-00
2017-09-08 00:00:00.000 CDIL-PTU208XXX-00
2016-12-29 00:00:00.000 CDIL-PTU208XXX-00
2016-12-28 00:00:00.000 CDIL-PTU208XXX-00
2016-12-21 00:00:00.000 CDIL-PTU208XXX-00
2016-12-16 00:00:00.000 CDIL-PTU208XXX-00
2016-12-15 00:00:00.000 CDIL-PTU208XXX-00
2016-12-14 00:00:00.000 CDIL-PTU208XXX-00
2016-12-13 00:00:00.000 CDIL-PTU208XXX-00
2016-12-13 00:00:00.000 CDIL-PTU208XXX-00
2016-12-12 00:00:00.000 CDIL-PTU208XXX-00
2016-12-09 00:00:00.000 CDIL-PTU208XXX-00
I need a result set the materials that composed the item sold in the most closed date to the invoice date. so for invoices in 2017 get in the output items in 2017 and in 2016 just the components in 2016. I know the join must be in invoice date but I don't know how to create it.
My tries:
SELECT month([Invoice Date]),bom.[Material ID]--,bom.[BOM #]
FROM [dbo].[INVOICES] inv
LEFT JOIN (select Item, Warehouse,[Material ID],[BOM Date]
from [dbo].[BOM]) bom
ON inv.Item = bom.Item
AND [Invoice Date]>= MAX([BOM Date])
required output
We see how the components for the Item in 2016 were 4 but for 2017 are 5, So my count must be based in the closest date coming from materials with invoice date to get something like:
In Sept 2017 we sold 7 products so that means that I needed 7 materials per id material and the same logic for 2016.
year month material_id qty
2017 Sept SDCGF-208L-0250-001 7
2017 Sept SDCGF-208L-0250-002 7
2017 Sept SDCGF-208L-0300-001 7
2017 Sept SDCGF-208L-0300-003 7
2017 Sept SDCXX-PTU-0208-001 7
2016 Dec SDCGF-208L-0250-001 10
2016 Dec SDCGF-208L-0250-002 10
2016 Dec SDCGF-208L-0300-001 10
2016 Dec SDCXX-PTU-0208-001 10
Editing to add insert scripts:
USE tempdb
SET NOCOUNT ON
CREATE TABLE #Materials
(
Item VARCHAR(20),
BOMDate DATETIME,
Warehouse VARCHAR(5),
MaterialId VARCHAR(30),
BomNo VARCHAR(20)
);
INSERT #Materials ( Item, BOMDate, Warehouse, MaterialId, BomNo )
SELECT *
FROM ( VALUES ( 'CDIL-PTU208XXX-00', '2016-12-08 00:00:00.000', 'MAIN', 'SDCGF-208L-0250-001', 'B000001211' ),
( 'CDIL-PTU208XXX-00', '2016-12-08 00:00:00.000', 'MAIN', 'SDCGF-208L-0250-002', 'B000001211' ),
( 'CDIL-PTU208XXX-00', '2016-12-08 00:00:00.000', 'MAIN', 'SDCGF-208L-0300-001', 'B000001211' ),
( 'CDIL-PTU208XXX-00', '2016-12-08 00:00:00.000', 'MAIN', 'SDCXX-PTU-0208-001 ', 'B000001211' ),
( 'CDIL-PTU208XXX-00', '2017-06-28 00:00:00.000', 'MAIN', 'SDCGF-208L-0250-001', 'B000001358' ),
( 'CDIL-PTU208XXX-00', '2017-06-28 00:00:00.000', 'MAIN', 'SDCGF-208L-0250-002', 'B000001358' ),
( 'CDIL-PTU208XXX-00', '2017-06-28 00:00:00.000', 'MAIN', 'SDCGF-208L-0300-001', 'B000001358' ),
( 'CDIL-PTU208XXX-00', '2017-06-28 00:00:00.000', 'MAIN', 'SDCGF-208L-0300-003', 'B000001358' ),
( 'CDIL-PTU208XXX-00', '2017-06-28 00:00:00.000', 'MAIN', 'SDCXX-PTU-0208-001 ', 'B000001358' )) AS x (
Item, BOMDate, Warehouse, MaterialId, BomNo );
CREATE TABLE #Invoices
(
InvoiceDate DATETIME,
Item VARCHAR(20)
);
INSERT #Invoices ( InvoiceDate, Item )
SELECT *
FROM ( VALUES ( '2017-09-25 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2017-09-22 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2017-09-20 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2017-09-20 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2017-09-15 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2017-09-08 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2017-09-08 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-29 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-28 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-21 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-16 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-15 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-14 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-13 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-13 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-12 00:00:00.000', 'CDIL-PTU208XXX-00' ),
( '2016-12-09 00:00:00.000', 'CDIL-PTU208XXX-00' )) AS X ( InvoiceDate, Item );
Update
I have done something like that:
SELECT month([Invoice Date]),bom.[Material ID],count(*)
FROM [dbo].[INVOICES_VIEW] inv
LEFT JOIN (select Division, Item,[Material ID],[BOM Date]
from [dbo].[BOM_VIEW]
where [Work Center]= 'PREPA') bom
ON inv.Item = bom.Item
AND inv.division = bom.division
AND bom.[BOM Date] = (SELECT MAX(bom2.[BOM Date])
FROM [dbo].[BOM_VIEW] bom2
WHERE inv.Item = bom2.Item
AND inv.division = bom2.division
AND bom2.[BOM Date] <= inv.[Invoice Date])
WHERE inv.Item='CDIL-PTU208XXX-00'
AND year([Invoice Date])=2017 and month([Invoice Date])=9
GROUP BY month([Invoice Date]),bom.[Material ID]
I cant use CTE. Insights?
Best Answer
Try this
This query returns exactly the same result that you are expecting, but I am not sure about "So my count must be based in the closest date coming from materials ".
Please check it out.