Databases
StackOverflow2013
Postsdbo
POBest approach? Loop through records, create a temptable, use a view?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest approach? Loop through records, create a temptable, use a view?
    primarykey
    data
    text
    <p>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.</p> <p>I want to classify each sale based on the numbers of items for that sale by certain product groups, specifically:</p> <p>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.</p> <p>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.</p> <p>Else Sale is Type0</p> <p>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.</p> <p>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:</p> <pre><code>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 </code></pre> <p>and doing three SELECT's from that?</p> <p>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</p> <pre><code>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 </code></pre> <p>and doing three SELECT's from that?</p> <p>Thanks for reading! I hope this makes sense and any suggestions are greatly appreciated!</p> <p>BiigJiim</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBiigJiim
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload