Combine 3 fields

[zcalebz]

I have three tables. Each table contains a field 'SKU'. Two of the tables only contain unique records for each 'SKU'. The third table field 'SKU' has many records for each 'SKU' (i.e. one record for 108976 in each of the first two tables, 49 records containing 108976 in the third table).

Currently there is a inner join on firsttable.SKU = secondtable.SKU which of course only returns one record for 108976....I need to add another join on thirdtable.SKU, but only return one of the records from the third table.

The third table contains a date with each record i.e. '108976' 04/29/2003, '108976' 06/07/2003, '108976' 02/23/2004, etc. I want to grab the record based on the corresponding date.

 

[donutman]

>>I want to grab the record based on the corresponding date.<<
What correspondence?
-Karl

 

[amorous]

Do you want the most recent record??

-VJ

 

[amorous]

If you want the recent record than try something like this:

SELECT t1.SKU,t2.field1,t3.field2 FROM Table1 t1
INNER JOIN Table2 t2 ON
t1.SKU=t2.SKU
INNER JOIN Table3 t3 ON
t1.SKU=t3.SKU
WHERE t3.Datefield=(SELECT TOP 1 (DateField) FROM Table3 ORDER By Datefield Desc)

Hope this helps.

VJ

 

[zcalebz]

Maybe this will explain the situation better.

Table one(INVMST) is a list of all our products containing fields such as SKU, Price, Cost Description, etc. (so there is only one SKU per record)

Table two (CONT_QTY) is a list of our products containing fields such as Quantity On Hand, Reorder Level, Stocking level etc. (so there is only one SKU per record)

Table three (JOURNAL) is a list of sales data so if SKU 108976 was sold a total of 235 times it will be in that table 235 times. Each record in this table will have SKU, Description, Price, Date Sold etc.

Right now an inner join is used on the first two tables. If the Quantity on Hand from table 2 is lower than the Reorder level it will return the number that are needed to order.

What I need to do is join in this third table and return the number of that particular SKU that were sold in the last seven days.

EXAMPLE: Right now..If SKU 108976 has a Quantity Of 4 and the reorder level is 2... it would return 2.

What I need is if SKU 108976 sold six in GetDate() > lastseven it returns the number sold.

Here is the code:
CREATE VIEW dbo.EntSKUonhand
AS
SELECT
VENDOR.VEND_NAME,
INVMST.SKU,
INVMST.PLU_DESC,
INVMST.PLUPRICE,

NOTE: I only want to go off of sales for one store cont_QTY.id '1'.....also 4 is just a place holder for when I figure out how to get the total sold the last seven days)

(case when (cont_qty.id_cont = 1) then 4 when (cont_qty.id_cont <> 1 and reorderLevel > sku_qty) then stockLevel - isnull(sku_qty,0) - isnull(onOrder,0) else null end) as QtyToOrder,

Cont_Qty.SKU_Qty, Cont_Qty.OnOrder,
Cont_Qty.ReorderLevel, -- AS MinQty,
Cont_Qty.StockLevel, -- AS MaxQty,
Cont_Qty.LastOrder,
Cont_Qty.LastReceived, Cont_Qty.LastSold,

Mdse_Hier.Mdse_Hier_Desc,
invmst.vendor,
cont_qty.id_cont,
Cont_Hier.Description,

Cont_Hier.Ancestors, Mdse_Hier.MdseUpLine

FROM INVMST INNER JOIN
Cont_Qty ON INVMST.SKU = Cont_Qty.SKU INNER JOIN
VENDOR ON
INVMST.VENDOR = VENDOR.VENDOR INNER JOIN
Cont_Hier ON
Cont_Qty.ID_Cont = Cont_Hier.ID_Cont INNER JOIN
Mdse_Hier ON
INVMST.Mdse_Hier_ID = Mdse_Hier.Mdse_Hier_ID
--INNER JOIN Journal ON INVMST.SKU = JOURNAL.JRITEM
-- WHERE JOURNAL.JRDATE = (SELECT TOP 1 (JRDATE) FROM JOURNAL)

 

[donutman]

You could replace the 4 with a subquery:
(Select sum(QtySoldField) from Journal J where J.SKU=Cont_Qty.SKU and J.JRDate+7>GetDate())
You may what the 7 to be 8 or maybe 7>=GetDate(). But you didn't mention how the Journal table needs to be filtered to represent sales for only store with ID_Cont=1, so you may have to adjust the subquery I gave you above.
You also have a problem with the 2nd case expression. I think it should be (cont_qty.id_cont <> 1 and reorderLevel > ISNull(sku_qty,0)+isnull(onOrder,0)). Otherwise (aside from the null issue) you might trigger a reorder just because a few more where sold yesterday when in fact you already reordered two days ago.
Frankly, I think this is query that should be broken into the union of two result sets. The first one for ID_Cont=1 and the other for all other stores. I doubt that there will much difference in speed, but it will be much clearer what you are doing. And what are you doing with that 4th table Mdse_Hier?
-Karl