Reporting first instance within date selction
I have a dataset which includes every items we have ever invoiced, which includes the part number(dbo.vSalesWorksOrders.[Part
No]), order number (dbo.vSalesWorksOrders.[Works
Order No]) & date of invoice (dbo.SCH_DATES.Date_SQL).
What I am wanting to do is be able to select a date range and then only show any new part numbers and the date of invoice, that appear for the 1st time in the dataset. So for example if we have invoiced part ABCD on 1st Jan 2010 for the 1st
time & again on 1st Feb 2010, if i selected dates 01/01/10 to 31/01/10 Part ABCD would appear, but if i select dates 01/02/10 to 28/02/10 it would not appear as it has already been invoiced previously.
I hope thats understandable? My current SQL is below:
SELECT dbo.INT_WO_SALES.Works_Order,
dbo.vSalesWorksOrders.[Works Order No], dbo.vSalesWorksOrders.Description,
dbo.vSalesWorksOrders.[Part
No], dbo.SCH_DATES.Date_SQL, dbo.INT_WO_SALES.Delivery_Note
FROM
dbo.vSalesWorksOrders INNER JOIN
dbo.INT_WO_SALES ON dbo.vSalesWorksOrders.[Works Order No] = dbo.INT_WO_SALES.Works_Order INNER JOIN
dbo.SCH_DATES ON dbo.INT_WO_SALES.Delivery_Date = dbo.SCH_DATES.Date_FM
ORDER BY dbo.vSalesWorksOrders.[Part No],
dbo.SCH_DATES.Date_SQL
Mark
April 27th, 2011 9:25am