Join based on date

Question

(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?

Can you provide expected results so we have something to double check out work against? It's not really clear what you want from the question. — Erik Reasonable Rates Darling, Commented Sep 25, 2017 at 17:39
So, even if the BOM Date is 2016-12-08, you want that to match to the invoice on 2016-12-09 ? — John, Commented Sep 25, 2017 at 18:15
@AndresAngel alright, I'm gonna eject here. You really need to list restrictions like that when you write a question. Best of luck. — Erik Reasonable Rates Darling, Commented Sep 25, 2017 at 19:13

Artashes Khachatryan · Accepted Answer · 2017-09-25 19:47:27Z

1

Try this

select 
t1.Year, 
t1.Month, 
m.materialid,
t1.[RowCount]
from (
select Item, YEAR(Invoicedate) as [Year]
, MONTH(Invoicedate) as [MonthNum]
, DateName( month , DateAdd( month , MONTH(Invoicedate) , 0 ) - 1 ) as [Month], COUNT(*) as [RowCount]
from [dbo].[Invoices]
group by YEAR(Invoicedate), MONTH(Invoicedate), Item
) T1
join [dbo].[Materials] m 
on 
T1.Year = year(m.[BOMDate]) 
--and t1.[MonthNum] = month(m.[BOMDate])
and t1.item = m.item

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.

answered Sep 25, 2017 at 19:47

Artashes Khachatryan

1,5331 gold badge12 silver badges23 bronze badges

I mean let's suppose you have in BOM tree different dates for a material. then for Material A is on Sept 20, Sep 21 and Sep 24. Now you have in invoice products that require materials. But these materials must be linked to closest date to invoice. So for invoices generated on Sep 20 then we use Bom date in 20, invoices in sept 21 we use bom in 21 and invoices sep 24 and later we need bom date 24 always,
– Andres Angel
Commented Sep 25, 2017 at 19:58
And what about count? How the count must be calculated? Based on the year?
– Artashes Khachatryan
Commented Sep 25, 2017 at 20:08
So let' talk about day before go beyond. Then on Sep 24 we sold 4 items B that required material A then my count is going to be product I sold 4 and I required 4 material A. Now let's supose that in sep 24 the materials required for product B were A and Z then the count in sep 20 wil be I sold 4 product B and I needed 4 material A and 4 material Z and so on.
– Andres Angel
Commented Sep 25, 2017 at 20:11

Add a comment |

Stack Exchange Network

Join based on date

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
join
or ask your own question.

Hot Network Questions

Join based on date

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008-r2join or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
join
or ask your own question.