August 19, 2010 at 2:16 pm
I am trying to create a report with a listing of parts with a minimum safety stock > zero along with other information and, provide a date or indicator that will show last activity of the part.
The last activity part is the part that is not working as the table I am looking at has many instances of the part, I need the MAX date but not sure how to get it.
here is the code before....
select
a.Itemid 'Item',
a.MinInventOnHand 'Min onHand',
a.MaxInventOnHand 'Max onHand',
b.InventLocationId 'WareHouse',
f.name 'WarehouseName',
c.ItemBuyerGroupId 'Buyer Group',
c.ItemType 'Item Type',
c.PrimaryVendorId 'Primary Vendor',
c.ItemCategory 'Category',
d.Price 'Price',
e.availPhysical 'Available',
e.onOrder 'Requirements',
e.ordered 'Ordered',
E.availOrdered 'Net available'
from
ReqItemTable A
inner join
InventDim B
on (b.inventdimid = a.covInventDimId)
inner join
Inventtable C
on (a.itemid = c.itemid)
inner join
InventTableModule D
on (a.itemid = d.itemid and
d.ModuleType = 0)
inner join
InventSum E
on (a.itemid = e.itemid and
e.InventDimId = a.covInventDimId)
inner join
InventLocation F
on (b.inventLocationId = f.InventLocationId)
where a.MinInventOnHand > 0
order by a.itemid
I want to add on a part like this....
inner join
InventTrans G
on (a.itemid = g.itemid)
and
(g.dateexpected > getdate() -90)
just before the 'where', but I'm unable to get it working, any attempt to
put in a MAX fetch delivers a different error, so I assume I'm lacking a good direction.
August 19, 2010 at 2:29 pm
I am not 100% sure what you are trying to do but I'll take a stab as saying I think you mean to do this:
JOIN InventTrans G
on a.itemid = g.itemid
and
g.dateexpected > dateadd(day,-90,getdate())
The probability of survival is inversely proportional to the angle of arrival.
August 20, 2010 at 10:45 am
Thanks for looking, sorry I wasn't clearer.
The InventTrans table contains many dates. I am looking for the most recent date (indicating activity, i.e MAX date) for the itemid within 3 months, or to simplify, just the max activity date for it.
August 20, 2010 at 11:09 am
I only want a single record, with the new and old code I get every date within that date window.
August 20, 2010 at 12:28 pm
How about....
INNER JOIN (
SELECT ItemID, MAX(DateExpected)
FROM InventTrans
GROUP BY ItemID
) G ON a.ItemID = g.ItemID
August 20, 2010 at 12:30 pm
And if you just want the last three months....
INNER JOIN (
SELECT ItemID, MAX(DateExpected)
FROM InventTrans
WHERE DateExpected > GETDATE() -90 -- or however you want to form the WHERE clause
GROUP BY ItemID
) G ON a.ItemID = g.ItemID
August 20, 2010 at 1:17 pm
It might be fun to try out the HAVING clause...
INNER JOIN InventTrans G
on (a.itemid = g.itemid)
GROUP BY <selected columns>
HAVING MAX(G.DateExpected) > GETDATE() - 90
Just be aware of the time values returned for DateExpted and GETDATE() as they may leave out some records.
August 20, 2010 at 1:27 pm
I got an error unfortunately. I've marked where the compiler objected.
Msg 8155, Level 16, State 2, Line 43
No column name was specified for column 2 of 'G'.
Msg 207, Level 16, State 1, Line 62
Invalid column name 'dateExpected'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'dateExpected'.
select
a.Itemid 'Item',
a.MinInventOnHand 'Min onHand',
a.MaxInventOnHand 'Max onHand',
b.InventLocationId 'WareHouse',
f.name 'WarehouseName',
c.ItemBuyerGroupId 'Buyer Group',
c.ItemType 'Item Type',
c.PrimaryVendorId 'Primary Vendor',
c.ItemCategory 'Category',
d.Price 'Price',
e.availPhysical 'Available',
e.onOrder 'Requirements',
e.ordered 'Ordered',
e.availOrdered 'Net available',
g.dateExpected 'Date Active' <-----------------red lined
from
ReqItemTable A
inner join
InventDim B
on (b.inventdimid = a.covInventDimId)
inner join
Inventtable C
on (a.itemid = c.itemid)
inner join
InventTableModule D
on (a.itemid = d.itemid and
d.ModuleType = 0)
inner join
InventSum E
on (a.itemid = e.itemid and
e.InventDimId = a.covInventDimId)
inner join
InventLocation F
on (b.inventLocationId = f.InventLocationId)
inner join (
select ItemId,Max(dateExpected)
from InventTrans
where DATEEXPECTED > GETDATE()-90
group by ITEMID
)
G on a.itemid = g.itemid <------------red lined
where a.MinInventOnHand > 0
and a.itemid = '030'
group by
a.Itemid ,
a.MinInventOnHand ,
a.MaxInventOnHand ,
b.InventLocationId ,
f.name ,
c.ItemBuyerGroupId ,
c.ItemType ,
c.PrimaryVendorId ,
c.ItemCategory ,
d.Price ,
e.availPhysical ,
e.onOrder ,
e.ordered ,
e.availOrdered ,
g.dateExpected <---------------------red lined
order by a.itemid
August 20, 2010 at 1:29 pm
Oh, DUH! My bad.
Try this...
INNER JOIN (
SELECT ItemID, MAX(DateExpected) AS DateExpected
FROM InventTrans
GROUP BY ItemID
) G ON a.ItemID = g.ItemID
Sorry about that.
August 20, 2010 at 1:44 pm
Hi S,
Got an error on this one
Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'where'.
select
a.Itemid 'Item',
a.MinInventOnHand 'Min onHand',
a.MaxInventOnHand 'Max onHand',
b.InventLocationId 'WareHouse',
f.name 'WarehouseName',
c.ItemBuyerGroupId 'Buyer Group',
c.ItemType 'Item Type',
c.PrimaryVendorId 'Primary Vendor',
c.ItemCategory 'Category',
d.Price 'Price',
e.availPhysical 'Available',
e.onOrder 'Requirements',
e.ordered 'Ordered',
e.availOrdered 'Net available',
g.dateExpected 'Date Active'
from
ReqItemTable A
inner join
InventDim B
on (b.inventdimid = a.covInventDimId)
inner join
Inventtable C
on (a.itemid = c.itemid)
inner join
InventTableModule D
on (a.itemid = d.itemid and
d.ModuleType = 0)
inner join
InventSum E
on (a.itemid = e.itemid and
e.InventDimId = a.covInventDimId)
inner join
InventLocation F
on (b.inventLocationId = f.InventLocationId)
inner join
INVENTTRANS G
on (a.itemid = g.itemid)
group by g.itemid
having MAX(g.dateExpected) > GETDATE() - 90
where a.MinInventOnHand > 0 <----------red lined
and a.itemid = '030'
group by
a.Itemid ,
a.MinInventOnHand ,
a.MaxInventOnHand ,
b.InventLocationId ,
f.name ,
c.ItemBuyerGroupId ,
c.ItemType ,
c.PrimaryVendorId ,
c.ItemCategory ,
d.Price ,
e.availPhysical ,
e.onOrder ,
e.ordered ,
e.availOrdered ,
g.dateExpected
order by a.itemid
August 20, 2010 at 1:48 pm
That's because the syntax as the other poster wrote it won't compile if used as provided. I assume he meant that to be implemented differently than wrote it.
Did the solution I suggested work?
August 20, 2010 at 1:52 pm
This one worked as I had wanted, thanks!
August 20, 2010 at 1:53 pm
I wasn't skilled enough to interpret.
August 20, 2010 at 2:11 pm
August 24, 2010 at 8:34 am
I appreciate it very much..I guess I need it. Thanks!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply