I am fairly new to crystal reports and need help with the following problem. I am writing a dead stock write off report. The parameters and SQL are below.
I am having trouble with the section regarding stock not sold in the last 12 months. Does anyone have any tips on how I would do this part in crystal? I can use the second half of this statement : DATEDIFF(MONTH, apply_date, GETDATE()) <= 12 to get a true/false value for each apply date (each transaction date).
Now I need to find the MAXIMUM apply_date for each product to apply this filter, and only select those that return a true value (i.e. those products that have the most recent transaction date within the last 12 months).
Any ideas how to return the max transaction date for each product based on the part no and apply date?
-- --
-- Positive Quantity In Stock --
-- Not Sold in the last 12 Months --
-- Excludes Voided & NQB Items --
-- Includes Obsolete Items --
-------------------------------------
SELECT c.[description] AS 'Group'
, i.[description] AS 'Description'
, i.part_no AS 'Part No'
, dbo.PBS_fnQuantityInStock(il.part_no, il.location) AS 'In Stock'
, il.avg_cost AS 'Average Cost'
, dbo.PBS_fnQuantityInStock(il.part_no, il.location) * il.avg_cost AS 'Total Cost'
FROM inv_master i
INNER JOIN inv_list il ON il.part_no = i.part_no
LEFT JOIN category c ON c.kys = i.category
WHERE i.void <> 'V'
AND i.status <> 'V' -- NQB
AND dbo.PBS_fnQuantityInStock(il.part_no, il.location) > 0
AND (SELECT COUNT(*) FROM inv_tran WHERE part_no = i.part_no AND tran_type = 'S' AND DATEDIFF(MONTH, apply_date, GETDATE()) <= 12) = 0
ORDER BY c.[description], i.[description]
IP Logged
