SQL Server 2005 Express : Group By

Question

A distributor have various outlets to sale products. Products are based on categories.

Outlets can receive products from the distributors and from other outlets (transfers). Opening balance (Qty), receipt (Qty), transfer IN (Qty), transfer Out (Qty), sale (Qty), closing balance (Qty) of every day is maintained in a table txnTransactions. The structure of the table is follows:

Create table txnTransactions
(
  dDate datetime,
  iCategoryID int (FK),
  iProduct ID int (FK),
  iOutletID int (FK),
  iOpeningBalance int,
  iReceipt int,
  iTranIN int,
  iTranOut int,
  iSale int,
  iClosingBalance int
)

A report is to be shown to the distributor for the total transactions (product-wise) within a specified period (Ex. 01/03/2013 to 31/03/2013) for all the outlets (I have used 0 as value to check if it is all outlets or specific outlet).

In this report the Opening Balance of all the Products is to be retrieved only of the start day (Ex. 01/03/2013) and then the total (sum) of all the other fields (Product-wise) is to be shown.

I have achieved the same with the help of temp tables. But the query takes a very long time that results in the Time out. If I use simple group by I need to use aggregate function for Opening Balance that results in wrong data.

Select 
   sum(iOpeningBalance), sum(iReceipt), sum(TranIN), sum(TranOut), sum(Sale) 
from 
   txnTransactions 
where 
   datediff(d, dDate, @startDate) <= 0 
   and datediff(d, dDate, @endDate) >= 0
group by 
   iProductID

Don't want to use sum(iOpeningBalance)...

The sample output is as follows:

ProductID OpeningBalance Receipts TranIN TranOut Sale Total/Balance
1         10             100      40     50      50   50
2         35             165      50     100     50   100
3         3              147      10     60      10   90

The opening balance is of the startdate and all other fields are the sum of the duration provided by startdate & enddate (input params for procedures).

Total/Balance is: openingbalance + sum(receipts) + sum(TranIn) - sum(TranOut) - sum(Sale)

Need help to optimize the query. Thanks in advance.

Mahmoud Gamal · Answer 1 · 2013-03-26T14:31:49.110

0

You can use the CASE expression with the condition if date is the first day of month or not: dDate = DATEADD(month, DATEDIFF(month, 0, dDate), 0):

SELECT
  SUM(CASE 
        WHEN CAST(dDate AS DATE) = DATEADD(month, 
                                           DATEDIFF(month, 0, dDate), 
                                           0) THEN iOpeningBalance
        ELSE 0 END) AS iOpeningBalance, 
  SUM(iReceipt), 
  SUM(TranIN), 
  SUM(TranOut), 
  SUM(Sale) 
FROM txnTransactions 
WHERE DATEDIFF(d, dDate, @startDate) <= 0 
  AND DATEDIFF(d, dDate, @endDate) >= 0
GROUP BY iProductID;

Edit

Try this:

WITH CTE
AS
(
  SELECT
    ProductID,
    SUM(ReceiptS) TotalReceiptS, 
    SUM(TranIN) TotalTranIN, 
    SUM(TranOut) TotalTranOut, 
    SUM(Sale) TotalSale
  FROM txnTransactions 
  GROUP BY ProductID
)
SELECT
  c.*,
  [Total/Balance] = (SELECT TOP 1 OpeningBalance
                     FROM txnTransactions AS t
                     WHERE t.ProductID = c.ProductID
                     AND CAST(t.dDate AS DATE) = DATEADD(month,
                                                         DATEDIFF(month, 0, t.dDate), 
                                                         0)) + 

                     TotalReceiptS + 
                     TotalTranIN - 
                     TotalTranOut - 
                     TotalSale  
FROM CTE AS c;

SQL Fiddle Demo

edited Mar 26 '13 at 14:31

answered Mar 26 '13 at 07:24

Mahmoud Gamal

78,257
17
139
164

Thanks for the prompt reply. I missed to state that I also need to provide the Total also, like, (iOpeningBalanace + sum(Receipt) + sum(tranIn) - sum(TransOut) - sum(Sale)) as Total. My apologies for not stating in the original question. Moreover the date can be any it is not specific that it will be the first date of the month. But I think I can handle that to compare it with [at]startDate. The problem is in getting the Opening Balance. – Dhruv Ganesh Mar 26 '13 at 10:03
@DhruvGanesh - For the first issue, you can do this in an outer query: `WITH CTE AS( ... put your current query here ...) SELECT iOpeningBalance + TotalReceipt + TotalTranIn - ... FROM CTE;`; For the second issue, it seems like I have mis understood your question, the code I posted will evaluate the total of iopeneingBalance only if `dDate` is the first day of the month, if not it won't include its value in the total, is this right? Also please post some sample data from this table with the desired output it will be very helpful understand your issue. Thanks – Mahmoud Gamal Mar 26 '13 at 10:07
ProductID OpeningBalance Receipt TranIn Sale TranOut Total 1 10 100 50 40 60 60 2 3 327 0 100 30 200 3 7 163 30 150 40 10 so on.. Here Opening Balance (as I had stated earlier) is for the startdate & all other fields has to be a result of the addition. I hope I am able to clarify the problem. Please provide the complete query so that I can understand in a better manner. Thanks @Mohmoud – Dhruv Ganesh Mar 26 '13 at 11:00
@DhruvGanesh - You can edit your question and post whatever you want to post there, but don't post files, just sample of data and the desired output. – Mahmoud Gamal Mar 26 '13 at 11:02
Thanks for the guidance. I had made changes to the original question (in the end) for displaying the output. Kindly refer to that & please provide an appropriate solution. Thanks - Dhruv – Dhruv Ganesh Mar 26 '13 at 11:32
@DhruvGanesh - See my edit, not that, I modified your sample data and included a `dDate` of type `Date` the opening balance then will be calculated with only the value of the first day of the month. Try it and let me know if this is what you are looking for or not. – Mahmoud Gamal Mar 26 '13 at 14:36

SQL Server 2005 Express : Group By

1 Answers1

Edit

SQL Fiddle Demo