I am attempting to compare and then perform calculations on sets of values that have multiple entries in Crystal Reports or T-SQL (MS SQL Server) and then do a calculation.
Example Data:
Table A (Millions of Rows)
Entry ID 1, Vendor, Product ID, Inventory Count, Inventory Value
Entry ID 2, Vendor, Product ID, Inventory Count, Inventory Value
...
Entry ID 99, Vendor, Product ID, Inventory Count, Inventory Value
Table B: (Millions of Rows)
Entry ID 1, DateTime, EntryType
Entry ID 2, DateTime, EntryType
...
Entry ID 99, DateTime, EntryType
An entry is created in both tables for every transaction instance and both tables are 5 million plus rows, as well as an entry for several batch operations that occur on schedules and with certain maintenance activities.
I need to compare two values in Table A and calculate the difference in the values of Inventory Count & Inventory Value, but only between the most recent date time or Entry ID update in Table B (Max) regardless of table B entry type and the last Table B value of type "10" for the Vendor & Product ID in Table A.
So simply if Entry 1 was of type '10' and entry 99 was of type 62, I would subtract the values of entry 99 from the values of entry 1 to come up with the current inventory counts and values.
This is just how the vendor choose to implement it in their software and I am looking for a way to get this information outside of their application.
A Crystal Report way to do it would be highly preferred, but a T-SQL solution I would probably also work for me as long as it returns all products or doesn't require hard coding the product IDs and by default return a complete inventory unless further filtered in the WHERE statement or Crystal Reports filter.
I am running Crystal Reports 2011 with MS SQL Server 2008 SP3
Give this query a try. It runs in a few seconds on 3 million rows of random test data (having the right indexes on your tables will be important). I called Table A 'Inventory' and Table B 'Entry'.
select x.Vendor, x.ProductId, i2.InventoryCount - i.InventoryCount InventoryCountDiff, i2.InventoryValue - i.InventoryValue InventoryValueDiff
from
(
select i.Vendor, i.ProductId, Max(e.EntryDate) MaxEntryDate, (select MAX(e2.EntryId) from Entry e2 join Inventory i2 on i2.EntryId = e2.EntryId and i2.Vendor = i.Vendor and i2.ProductId = i.ProductId where e2.EntryType = 10) MaxEntryId
from Inventory i
join Entry e on e.EntryId = i.EntryId
group by i.Vendor, i.ProductId
) x
join Inventory i on x.Vendor = i.Vendor and x.ProductId = i.ProductId
join Entry e on i.EntryId = e.EntryId
join Inventory i2 on x.Vendor = i2.Vendor and x.ProductId = i2.ProductId
join Entry e2 on i2.EntryId = e2.EntryId
where e.EntryDate = x.MaxEntryDate
and e2.EntryId = x.MaxEntryId
Whatever query you end up using, do yourself a favor and put it in a stored procedure and then use that procedure as the source for your report. Crystal Reports is painful enough to use without trying to compose a complex query in their designer.