'As at Month End' reporting on history tables 1

[CdnRissa]

I have a database that tracks inventory movement. The main tables are Orders, Shipments, ShippedItems and Items. The relationships are: a Shipment may or may not have an Order, Shipments will have 1 or more Items and Items can be attached to 1 or more Shipments. The ShippedItems table is a link table and the PK is ShipmentId, ItemId giving the many to many relation.

Each of these 4 tables have associated history tables into which a row with the current (new) values inserted via triggers whenever a row is inserted, updated or deleted in the main table. Each of the history tables has a CreateDt and a uniquely named HistId (e.g. ShipmentsHistId) field. The main tables hold only the current values.

I now need to report on the state of the Items as of a particular point in time - specifically as of a given month end. Obviously, I need to pull the data from the history tables, but I'm stumbling on how to determine the latest values across all the history tables at any given point in time.

Using the same relationships as the main tables, results in multiple rows for each Item. I need a flattened row that contains fields from all four tables with the values as of a month end.

One thought I had was to create a FlatHistory table that would be updated with all the fields I need to report on whenever any of the main tables were changed. That seemed somewhat redundant, though it would make the reports run quickly since there wouldn't need to be any joins.

Another thought was that since I already have all the history, I just need to connect the history Ids to one another to get the complete picture. The Flat History table would then become pointers into the other history tables with a Create Date to determine which rows are the last ones for a month. Again, I'm stumped on how to get the latest history id in all of the tables when only one main table is changing.

If anyone has any suggestions as to how I can do this (and keep some hair on my head!), I would be ever so grateful!

Cheers, May

 

[CdnRissa]

BTW running SQL Server 2008 R2

 

[fredericofonseca]

not enough information to give a better example but you will most likely need to do something along these lines is probably what you looking for

select *
from orders mt
outer apply (select top 1 *
             from hist_item hi
             on hi.itemid = mt.itemid
             and hi.CreateDt  <= @reportdate
             order by hi.CreateDt desc
            ) item
outer apply (select top 1 *
             from hist_Shipments hs
             on hs.itemid = mt.itemid
             and hs.movement_date <= @repordate
             order by hs.CreateDt desc
            ) Shipments
where item.itemid is not null
   or Shipments.itemid is not null

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CdnRissa]

Thanks, Frederico. I will try it when I'm back in the office on Monday.

Cheers, May

 

[CdnRissa]

Frederico,

That didn't work because ItemId does not exist in the Orders table. Perhaps this will clarify the situation:

[bold]Shipments:[/bold] ShipId (PK), ShipType, OrderId (FK to Orders), ShipDt, and other fields
[bold]ShippedItems:[/bold] ShipId (FK to Shipments), ItemId (FK to Items), CreateDt, and other fields (note: PK is ShipId, ItemId)
[bold]Items:[/bold] ItemId (PK), LastShipId, AssetTag, and other fields
[bold]Orders:[/bold] OrderId (PK), OrderNum, CustomerNum, and other fields

[bold]ShipmentsHistory:[/bold] ShipmentsHistId (PK), HistCreateDt, Action, ShipId, ShipType, OrderId, ShipDt, and other fields
[bold]ShippedItemsHistory:[/bold] ShippedItemsHistId (PK), HistCreateDt, Action, ShipId, ItemId, CreateDt, and other fields
[bold]ItemsHistory:[/bold] ItemsHistId (PK), HistCreateDt, Action, ItemId, LastShipId, AssetTag, and other fields
[bold]OrdersHistory:[/bold] OrdersHistId (PK), HistCreateDt, Action, OrderId), OrderNum, CustomerNum, and other fields

For reporting purposes, the main tables are flattened like this:

SELECT top 5
Items.ItemId
, Items.AssetTag
, Items.LastShipId
, Shipments.ShipType
, Shipments.ShipDt
, Orders.OrderNum
FROM Orders 
INNER JOIN Shipments ON Orders.OrderId = Shipments.OrderId 
INNER JOIN Items 
INNER JOIN ShippedItems ON Items.ItemId = ShippedItems.ItemId 
[tab]AND Items.LastShipmentId = ShippedItems.ShipmentId 
ON Shipments.ShipmentId = Items.LastShipmentId
order by Items.ItemId desc, LastShipmentId desc

Result:
ItemId[tab]AssetTag[tab]LastShipId[tab]ShipType[tab]ShipDt[tab]OrderNum
48942[tab]073628[tab][tab]54766[tab][tab]12[tab]2014-11-17[tab]256593
48941[tab]073865[tab][tab]54764[tab][tab]12[tab]2014-11-17[tab]256324
48920[tab]073626[tab][tab]54745[tab][tab]12[tab]2014-11-14[tab]256240
48919[tab]073863[tab][tab]54741[tab][tab]12[tab]2014-11-13[tab]256111
48918[tab]073862[tab][tab]54740[tab][tab]12[tab]2014-11-13[tab]256110

Now I need to do the same thing but from the history tables and as of a month end. The new values are inserted into the history table when a row in the main table is changed, so the latest row in the history tables is the same as the rows in the main tables. I need to get the most recent row from each of the 4 history tables for each Item that was attached to a Shipment as of the month end.

Does that make any sense at all? I confess, I've been looking at this so long that I'm not sure I'm explaining it very well.

Cheers, May

BTW This will eventually have to run on SQL Server 2005 (no SP) - It's my development system that's running 2008 R2 SP3 - don't ask...

 

[gk53]

try something like that

;with Orders as
(
select ROW_NUMBER() over(partition by OrderId group by HistCreateDt desc) row, OrderNum 
from OrdersHistory
),
Shipments as
(
select ROW_NUMBER() over(partition by ShipId group by HistCreateDt desc) row, ShipType, ShipDt 
from ShipmentsHistory
),
ShippedItems as
(
select ROW_NUMBER() over(partition by ShipId, ItemId group by HistCreateDt desc) row, ShipId, ItemId 
from ShippedItemsHistory
)
SELECT top 5
Items.ItemId
, Items.AssetTag
, Items.LastShipId
, Shipments.ShipType
, Shipments.ShipDt
, Orders.OrderNum
FROM Orders 
INNER JOIN Shipments ON Orders.OrderId = Shipments.OrderId 
INNER JOIN Items 
INNER JOIN ShippedItems ON Items.ItemId = ShippedItems.ItemId 
    AND Items.LastShipmentId = ShippedItems.ShipmentId 
ON Shipments.ShipmentId = Items.LastShipmentId
order by Items.ItemId desc, LastShipmentId desc

 

[gk53]

sorry on previous post conflict of the alias and table names and everything should be filtered to row = 1

;with O as
(
select ROW_NUMBER() over(partition by OrderId group by HistCreateDt desc) row, OrderNum 
from OrdersHistory
),
S as
(
select ROW_NUMBER() over(partition by ShipId group by HistCreateDt desc) row, ShipType, ShipDt 
from ShipmentsHistory
),
SI as
(
select ROW_NUMBER() over(partition by ShipId, ItemId group by HistCreateDt desc) row, ShipId, ItemId 
from ShippedItemsHistory
)
SELECT top 5
Items.ItemId
, Items.AssetTag
, Items.LastShipId
, S.ShipType
, S.ShipDt
, O.OrderNum
FROM O 
INNER JOIN S ON O.OrderId = S.OrderId 
	and S.row = 1
INNER JOIN Items 
	ON S.ShipmentId = Items.LastShipmentId
	and S.row = 1
INNER JOIN SI ON Items.ItemId = SI.ItemId 
    AND Items.LastShipmentId = SI.ShipmentId 
    and SI.row = 1
where o.row = 1
order by Items.ItemId desc, Items.LastShipmentId desc

 

[CdnRissa]

Thank you, gk53, but this isn't working. I'm getting a "Incorrect syntax near the keyword 'group'" error on the row_number() function. I pretty sure it's because the database is running on SQL Server 2005 (no SP).

Any other ideas?

Cheers, May

 

[gmmastros]

sql server 2005 supports the row_number() function. The problem with gk53's code is that it should be order by instead of group by.

;with O as
(
select ROW_NUMBER() over(partition by OrderId [!]order[/!] by HistCreateDt desc) row, OrderNum 
from OrdersHistory
),
S as
(
select ROW_NUMBER() over(partition by ShipId [!]order[/!] by HistCreateDt desc) row, ShipType, ShipDt 
from ShipmentsHistory
),
SI as
(
select ROW_NUMBER() over(partition by ShipId, ItemId [!]order[/!] by HistCreateDt desc) row, ShipId, ItemId 
from ShippedItemsHistory
)
SELECT top 5
Items.ItemId
, Items.AssetTag
, Items.LastShipId
, S.ShipType
, S.ShipDt
, O.OrderNum
FROM O 
INNER JOIN S ON O.OrderId = S.OrderId 
	and S.row = 1
INNER JOIN Items 
	ON S.ShipmentId = Items.LastShipmentId
	and S.row = 1
INNER JOIN SI ON Items.ItemId = SI.ItemId 
    AND Items.LastShipmentId = SI.ShipmentId 
    and SI.row = 1
where o.row = 1
order by Items.ItemId desc, Items.LastShipmentId desc

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CdnRissa]

Thank you, one and all. I did a bit of tweaking and finally have a view that gives the state of things as of the end of the month. I still need to do a bit of testing around the dates, but it is very close. For anyone who is interested, here is the code I ended up with:

;with 
--Orders
O as
(
select ROW_NUMBER() over(partition by OrderId, substring(convert(char(8), HistCreateDt, 112),1,6) 
order by HistCreateDt desc) row
, OrdersHistId, HistCreateDt, substring(convert(char(8), HistCreateDt, 112),1,6) as Mth
, OrderId, OrderNum, CustomerNum, BillDt, AbandDt, SiteDesc 
from OrdersHistory
)
--Shipments
, S as
(
select ROW_NUMBER() over(partition by ShipmentId
				, substring(convert(char(8), ShipmentsHistCreateDt, 112),1,6) 
				order by ShipmentsHistCreateDt desc) row
, ShipmentsHistId, ShipmentsHistCreateDt
, substring(convert(char(8), ShipmentsHistCreateDt, 112),1,6) as Mth
, ShipmentId, ShipType, ShipDt, OrderId, Location, ShipperRecvr, Comment
from ShipmentsHistory
where ShipType in (12,16)
)
--ShippedItems
, si as
(
select ROW_NUMBER() over(partition by ShipmentId, ItemId
			, substring(convert(char(8), ShippedItemHistCreateDt, 112),1,6) 
			order by ShippedItemHistCreateDt desc) row
, ShippedItemHistId, ShippedItemHistCreateDt
, substring(convert(char(8), ShippedItemHistCreateDt, 112),1,6) as Mth
, ShipmentId, ItemId, Replacement 
from ShippedItemsHistory
where Replacement = 0
)
-- Items
, i as
(
select ROW_NUMBER() over(partition by itemid
			, substring(convert(char(8), HistCreateDt, 112),1,6) 
				order by HistCreateDt desc) row
, ItemHistId, HistCreateDt, substring(convert(char(8), HistCreateDt, 112),1,6) as Mth
, ItemId, LastShipmentId, AssetTag, ItemNum, ItemCat, CustOwned, HoldOnBehalfDt, CapitalizeDt
from ItemsHistory
)
SELECT i.ItemId, i.AssetTag, i.LastShipmentId, i.ItemNum, i.ItemCat, s.ShipmentId, S.ShipType
, S.ShipDt, s.Location as LocCd, l.Loc, s.ShipperRecvr, si.Replacement as Repl, o.OrderNum
, o.BillDt, o.AbandDt, o.customerNum, o.SiteDesc, s.Comment
FROM o 
INNER JOIN S ON o.OrderId = S.OrderId 
	and S.row = 1
INNER JOIN i ON S.ShipmentId = i.LastShipmentId
	and S.row = 1
INNER JOIN SI ON i.ItemId = SI.ItemId 
    AND i.LastShipmentId = SI.ShipmentId 
    and SI.row = 1
INNER JOIN vw_LocDesc l ON s.Location = l.LocCd
where o.row = 1

Your help was much appreciated!

Cheers, May