ALTER PROCEDURE [dbo].[rptstockholdinglevel] 
	-- Add the parameters for the stored procedure here
@datefrom datetime,
@dateto datetime,
@periodname varchar(50),
@percentage int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT     
products.productname, 
@periodname AS periodname, 
isnull(SUM(invoiceline.qty),0) AS QtySold, 
products.qtyonhand AS OnHand, 
nominals.nominalname, 
productcategories.categoryname, 
productmanufacturers.manufacturername
FROM         
productmanufacturers RIGHT OUTER JOIN
invoices INNER JOIN
invoiceline ON invoices.invoiceid = invoiceline.invoiceid RIGHT OUTER JOIN
products ON invoiceline.productid = products.productid ON productmanufacturers.manufacturerid = products.manufacturerid LEFT OUTER JOIN
nominals INNER JOIN
productcategories ON nominals.nominalid = productcategories.salesnominal ON products.categoryid = productcategories.productcategoryid
WHERE     
(invoices.invoicedate BETWEEN @datefrom AND @dateto) 
OR 
(invoices.invoicedate is null)
GROUP BY 
products.productname, 
products.qtyonhand, 
productcategories.categoryname, 
nominals.nominalname, 
productmanufacturers.manufacturername
HAVING (SUM(isnull(invoiceline.qty,0)) < products.qtyonhand)
ORDER BY 
nominals.nominalname, 
productcategories.categoryname, 
productmanufacturers.manufacturername
END

quote:

---

Originally posted by steve_c

Well probably not that complex for some of you out there!

I need to work out the amount of stock which was sold between @datefrom and @dateto and how much we currently have on hand (to work out if we are over ordering etc). That's the pretty easy part but I also need to include a column which works out how many items have been sold 3 months prior to @datefrom (from invoiceline). The proc I have so far works out the items sold between 2 dates so basically what I need is another column which is the amount sold (QtySold) in the 3 months prior to datefrom

This is the basic part I have so far:

ALTER PROCEDURE [dbo].[rptstockholdinglevel] 
	-- Add the parameters for the stored procedure here
@datefrom datetime,
@dateto datetime,
@periodname varchar(50),
@percentage int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT     
products.productname, 
@periodname AS periodname, 
isnull(SUM(invoiceline.qty),0) AS QtySold, 
products.qtyonhand AS OnHand, 
nominals.nominalname, 
productcategories.categoryname, 
productmanufacturers.manufacturername
FROM         
productmanufacturers RIGHT OUTER JOIN
invoices INNER JOIN
invoiceline ON invoices.invoiceid = invoiceline.invoiceid RIGHT OUTER JOIN
products ON invoiceline.productid = products.productid ON productmanufacturers.manufacturerid = products.manufacturerid LEFT OUTER JOIN
nominals INNER JOIN
productcategories ON nominals.nominalid = productcategories.salesnominal ON products.categoryid = productcategories.productcategoryid
WHERE     
(invoices.invoicedate BETWEEN @datefrom AND @dateto) 
OR 
(invoices.invoicedate is null)
GROUP BY 
products.productname, 
products.qtyonhand, 
productcategories.categoryname, 
nominals.nominalname, 
productmanufacturers.manufacturername
HAVING (SUM(isnull(invoiceline.qty,0)) < products.qtyonhand)
ORDER BY 
nominals.nominalname, 
productcategories.categoryname, 
productmanufacturers.manufacturername
END

I'd be really grateful for any pointers as I'm just going round in circles on this one.

Thanks in advance as usual all :)

Stephen.

---