counting total items sold - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

counting total items sold

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-31 : 08:15:54

luvcloud writes "I need to count the number of items (grouped by model no) sold. THe table to count on has fields like the modelno, and quantity. But the problem is, this statement does not take into account the quantity field, which means that the number sold might not be accurate. This statement is ok if the data in the quantity field is always 1. But if the quantity is more than 1, then the total count of items sold would not be accurate anymore. HOw should i modify it?

right now, my sql statement looks like this:

SELECT SALES_INVOICE.RetailID, RETAIL_SHOP.RetailName, ITEM.ItemName,
ITEM.ItemDesc,
SALES.ModelNo, count(SALES.ModelNo) as TotalNumber

FROM SALES_INVOICE, RETAIL_SHOP, SALES, ITEM

WHERE SALES_INVOICE.RetailID = RETAIL_SHOP.RetailID and
SALES_INVOICE.InvoiceNo = SALES.InvoiceNo and
SALES.ModelNo = ITEM.ModelNo
AND
DATEPART(month,GETDATE()) - DATEPART(month,Sales_Invoice.SalesDate) <= 3
AND
DATEPART(year,GETDATE()) - DATEPART(year,Sales_Invoice.SalesDate) = 0

GROUP BY SALES_INVOICE.RetailID, RETAIL_SHOP.RetailName, SALES.ModelNo,
ITEM.ItemName, ITEM.ItemDesc"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-31 : 09:19:25

Is this what you mean?
Caveat: If you have rows in SALES where ModelNo is NULL, but Quantity is neither 0 nor NULL then it won't quite work.

SELECT SALES_INVOICE.RetailID, RETAIL_SHOP.RetailName, ITEM.ItemName,
ITEM.ItemDesc,
SALES.ModelNo, sum(SALES.Quantity) as TotalNumber

FROM SALES_INVOICE, RETAIL_SHOP, SALES, ITEM

WHERE SALES_INVOICE.RetailID = RETAIL_SHOP.RetailID and
SALES_INVOICE.InvoiceNo = SALES.InvoiceNo and
SALES.ModelNo = ITEM.ModelNo
AND
DATEPART(month,GETDATE()) - DATEPART(month,Sales_Invoice.SalesDate) <= 3
AND
DATEPART(year,GETDATE()) - DATEPART(year,Sales_Invoice.SalesDate) = 0

GROUP BY SALES_INVOICE.RetailID, RETAIL_SHOP.RetailName, SALES.ModelNo,
ITEM.ItemName, ITEM.ItemDesc"

BTW, do you really mean this part:
DATEPART(month,GETDATE()) - DATEPART(month,Sales_Invoice.SalesDate) <= 3
AND
DATEPART(year,GETDATE()) - DATEPART(year,Sales_Invoice.SalesDate) = 0
? Or did you mean something more like:
DATEDIFF(month, Sales_Invoice.SalesDate, GETDATE()) <= 3
?

Edited by - Arnold Fribble on 07/31/2002 09:25:43

Billpl
Yak Posting Veteran

71 Posts

Posted - 2002-08-01 : 12:39:40

Ultimately (make it easy on the index)

Declare @StartDate as DateTime
Set @StartDate = DateAdd( mm, -3, getdate() )

... and Sales_Invoice.SalesDate >= @Startdate ...

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-01 : 13:29:31

That's not quite the same since DATEDIFF counts boundaries crossings. But yes, I was being lazy. This would be the start date if you want whole months:

SET @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources