SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping sets and sub query ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raghu_grdr
Starting Member

17 Posts

Posted - 03/26/2013 :  11:28:49  Show Profile  Reply with Quote
Hi,

I have a below query

SELECT
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 output

MODE 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 - 03/26/2013 :  11:30:23  Show Profile  Reply with Quote
234 is the opening balance
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000