Best approach? Loop through records, create a temptable, use a view?

Question

SQL Server newbie question here! I have 3 tables, tbl_Sales, tbl_SalesItems, tbl_Products. tbl_SalesItems stores the products added to each sale, and is joined to tbl_Products by product code. Products in tbl_Products are categorised by a ProductGroup field. Environment-wise, there are at any time about 200 sales, 4-5 items per sale and 3000 products. Although the record numbers are quite small, the Sales and SalesItems data is continually changing, the calculation will be done hundreds of times a day in a live environment, with response time being critical.

I want to classify each sale based on the numbers of items for that sale by certain product groups, specifically:

Sale is Type1 if has 1 item of product group 13 and 0 items of product group 14 and 0 items of product group 16.

Sale is Type2 if has 0 item of product group 13 and 1 items of product group 14 and 0 items of product group 16.

Else Sale is Type0

If I was using Access/VBA I would create one recordset of 3 records, being the count of items within that sale for each of the 3 product groups and then loop through the records to get my values and determine the type.

I'm not sure if this is possible in a sql server function or stored procedure? At the moment I am running three separate SELECT statements and then evaluating the results like this:

ALTER FUNCTION [dbo].[fSaleATCType] ( @SaleID int)
RETURNS tinyint
BEGIN

declare @InOutWashCount int
declare @OutWashCount int
declare @ExtraCount int
declare @ATCType tinyint

SET @InOutWashCount = 
    (
    SELECT COUNT(dbo.tbl_SalesItems.SaleItemCode) AS CountItems
    FROM        dbo.tbl_SalesItems LEFT OUTER JOIN
                dbo.tbl_Products ON dbo.tbl_SalesItems.SaleItemCode = dbo.tbl_Products.ProductCode
    WHERE       (dbo.tbl_SalesItems.SaleID = @SaleID) AND (dbo.tbl_Products.ProductGroup = 13)
    )

SET @OutWashCount = 
    (
    SELECT COUNT(dbo.tbl_SalesItems.SaleItemCode) AS CountItems
    FROM        dbo.tbl_SalesItems LEFT OUTER JOIN
                dbo.tbl_Products ON dbo.tbl_SalesItems.SaleItemCode = dbo.tbl_Products.ProductCode
    WHERE       (dbo.tbl_SalesItems.SaleID = @SaleID) AND (dbo.tbl_Products.ProductGroup = 14)
    )

SET @ExtraCount = 
    (
    SELECT COUNT(dbo.tbl_SalesItems.SaleItemCode) AS CountItems
    FROM        dbo.tbl_SalesItems LEFT OUTER JOIN
                dbo.tbl_Products ON dbo.tbl_SalesItems.SaleItemCode = dbo.tbl_Products.ProductCode
    WHERE       (dbo.tbl_SalesItems.SaleID = @SaleID) AND (dbo.tbl_Products.ProductGroup = 16)
    )

SET @ATCType = 0

if @InOutWashCount = 1 and @OutWashCount = 0 and @ExtraCount = 0
    SET @ATCType = 1

if @InOutWashCount = 0 and @OutWashCount = 1 and @ExtraCount = 0
    SET @ATCType = 2

RETURN @ATCType

END

and doing three SELECT's from that?

Is this the best way of doing it? Would I be better creating a temptable and then doing three SELECT's from that? Or creating a View like

SELECT      dbo.tbl_SalesItems.SaleID, 
        dbo.tbl_Products.ProductGroup, 
        COUNT(dbo.tbl_SalesItems.SaleItemCode) AS CountItems

FROM        dbo.tbl_SalesItems LEFT OUTER JOIN
            dbo.tbl_Products ON dbo.tbl_SalesItems.SaleItemCode = dbo.tbl_Products.ProductCode

GROUP BY    dbo.tbl_Products.ProductGroup, 
        dbo.tbl_SalesItems.SaleID

and doing three SELECT's from that?

Thanks for reading! I hope this makes sense and any suggestions are greatly appreciated!

BiigJiim

Answer 1

I hope I've understood you correctly, but a query like this might be what you need (BTW I haven't tested this and I'm not sure about using COUNT - what happens if it counts a number > 1?):

(Edit: I have added some test data and results to help clarify the situation also debugged a couple of copy and paste errors.)

Basically each left join represents sale item products from a particular group the case statement is the driver for the logic.

Test it out and have a play around, the temporary tables at the top will allow you to use your own test data to try out different cases.

Also I remmed out the WHERE clause and added a group by so you can see all the test data at a glance...

DECLARE @SaleId int
DECLARE @tbl_SalesItems AS TABLE(SaleID int, SaleItemCode varchar(10))
DECLARE @tbl_Products AS TABLE(ProductGroup int, ProductCode varchar(10))

INSERT INTO @tbl_SalesItems(SaleID, SaleItemCode) VALUES (1,'Product1')
INSERT INTO @tbl_SalesItems(SaleID, SaleItemCode) VALUES (2,'Product2')
INSERT INTO @tbl_SalesItems(SaleID, SaleItemCode) VALUES (3,'Product1')
INSERT INTO @tbl_SalesItems(SaleID, SaleItemCode) VALUES (3,'Product2')
INSERT INTO @tbl_SalesItems(SaleID, SaleItemCode) VALUES (3,'Product3')

INSERT INTO @tbl_Products(ProductGroup, ProductCode) VALUES (13, 'Product1')
INSERT INTO @tbl_Products(ProductGroup, ProductCode) VALUES (14, 'Product2')
INSERT INTO @tbl_Products(ProductGroup, ProductCode) VALUES (16, 'Product3')
INSERT INTO @tbl_Products(ProductGroup, ProductCode) VALUES (16, 'Product4')

SET @SaleId = 1

SELECT  si.SaleId
       ,CASE
        WHEN COUNT(pg13.ProductCode) = 1 AND COUNT(pg14.ProductCode) = 0 AND COUNT(pg16.ProductCode)  = 0
        THEN 1
        WHEN COUNT(pg13.ProductCode) = 0 AND COUNT(pg14.ProductCode) = 1 AND COUNT(pg16.ProductCode)  = 0
        THEN 2
        ELSE 0 END AS ATCType
FROM        @tbl_SalesItems si
LEFT JOIN   @tbl_Products  pg13
    ON si.SaleItemCode = pg13.ProductCode
    AND (pg13.ProductGroup = 13)
LEFT JOIN   @tbl_Products  pg14
    ON si.SaleItemCode = pg14.ProductCode
    AND (pg14.ProductGroup = 14)
LEFT JOIN   @tbl_Products  pg16
    ON si.SaleItemCode = pg16.ProductCode
    AND (pg16.ProductGroup = 16)
--WHERE si.SaleId = @SaleId
GROUP BY si.SaleId

Results:

SaleId  ATCType
1       1
2       2
3       0
4       2