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.
| Author |
Topic |
|
raghu_grdr
Starting Member
17 Posts |
 Posted - 2013-03-26 : 11:28:49
|
| Hi,I have a below querySELECT MODE,TYPE,ITEMNAME,ITEMID,RECEIPTNO,DATE--,[DESC],[DESC] = CASE WHEN ITEMNAME IS NULL THEN 'Grand-Total' WHEN ITEMNAME IS NOT NULL AND DATE IS NULL THEN 'Total' ELSE [DESC] END,(SELECT OPENINGBALANCE FROM OPENINGBALANCE WHERE OPENINGBALANCE.ITEMID = VIEW_LEDGER.ITEMID) AS 'OPENINGBALANCE',SUM(RECEIPTQTY) 'RECEIPTQTY',SUM(ISSUEQTY) 'ISSUEQTY' ,ISNULL((SELECT OPENINGBALANCE FROM OPENINGBALANCE WHERE OPENINGBALANCE.ITEMID = VIEW_LEDGER.ITEMID),0) + SUM(ISNULL(RECEIPTQTY,0) - ISNULL(ISSUEQTY,0)) 'BALANCE' FROM VIEW_LEDGER --WHERE ITEMID = VIEW_LEDGER.ITEMID GROUP BY GROUPING SETS((MODE,TYPE,ITEMNAME,DATE, RECEIPTNO,ITEMID,[DESC]), (ITEMNAME))which give me below outputMODE TYPE ITEMNAME ITEMID RECEIPTNO DATE DESC OPENINGBALANCE RECEIPTQTY ISSUEQTY BALANCE ISSUE PURCHASE RETURN A3804 86 333 22-03-13 Purchase Return - Supplier2 234 0 32 202 RECEIPT PURCHASE WITH PO A3804 86 21 20-03-13 Supplier - Supplier1 234 45 0 279 RECEIPT SUPPLIER A3804 86 123 16-03-13 NULL 234 45 0 279 NULL NULL A3804 NULL NULL NULL Total NULL 90 32 58 ISSUE PRODUCTION ISSUE ADC 1245 84 321 22-03-13 PRODUCTION ISSUE - KARTHIKEYAN.N 321 0 NULL 321 RECEIPT PURCHASE WITH PO ADC 1245 84 1 21-03-13 Supplier - Supplier1 321 2000 0 2321 RECEIPT SUPPLIER ADC 1245 84 4532 15-03-13 NULL 321 10 0 331 RECEIPT SUPPLIER ADC 1245 84 123 16-03-13 NULL 321 43 0 364 NULL NULL ADC 1245 NULL NULL NULL Total NULL 2053 0 2053 In the total balance currently it is showing 90 - 32 = 58 , instead i need to show as (234 + 90 - 32 = 292). |
|
|
raghu_grdr
Starting Member
17 Posts |
Posted - 2013-03-26 : 11:30:23
|
| 234 is the opening balance |
 |
|
|
|
|
|
|
|
Grouping sets and sub query ?