SQL View Help 2

[G12Consult]

I have the following view:

 SELECT TOP (100) PERCENT GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY, SUM(dbo.ASSET_INFO_DETAILS.VALUE) AS TOTAL_VALUE

FROM dbo.OWNERS 
INNER JOIN dbo.ASSET_OWNERSHIP ON dbo.OWNERS.OWNER_ID = dbo.ASSET_OWNERSHIP.OWNER_ID 
INNER JOIN dbo.ASSETS ON dbo.ASSET_OWNERSHIP.ASSET_ID = dbo.ASSETS.ASSET_ID 
INNER JOIN GBL_ADMIN.dbo.ASSET_TYPES ON dbo.ASSETS.ASSET_TYPE_ID = GBL_ADMIN.dbo.ASSET_TYPES.ASSET_TYPE_ID 
INNER JOIN dbo.ASSET_INFO_DETAILS ON dbo.ASSETS.ASSET_ID = dbo.ASSET_INFO_DETAILS.ASSET_ID 
WHERE (dbo.ASSETS.INC_NET_WORTH = 1) 
GROUP BY GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY, dbo.OWNERS.USER_ID 
ORDER BY GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY DESC]

Which produces

DR 1285000.00
CR 275000.00

How would I add another row to the query which will substract CR from DR so I end up with:

DR 1285000.00
CR 275000.00
Total 1010000.00

 

[simian336]

Try This...

SELECT TOP (100) PERCENT GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY,
SUM(dbo.ASSET_INFO_DETAILS.VALUE) AS TOTAL_VALUE

FROM dbWNERS
INNER JOIN dbo.ASSET_OWNERSHIP ON dbWNERS.OWNER_ID = dbo.ASSET_OWNERSHIP.OWNER_ID
INNER JOIN dbo.ASSETS ON dbo.ASSET_OWNERSHIP.ASSET_ID = dbo.ASSETS.ASSET_ID
INNER JOIN GBL_ADMIN.dbo.ASSET_TYPES ON dbo.ASSETS.ASSET_TYPE_ID = GBL_ADMIN.dbo.ASSET_TYPES.ASSET_TYPE_ID
INNER JOIN dbo.ASSET_INFO_DETAILS ON dbo.ASSETS.ASSET_ID = dbo.ASSET_INFO_DETAILS.ASSET_ID
WHERE (dbo.ASSETS.INC_NET_WORTH = 1)
GROUP BY GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY, dbWNERS.USER_ID

Union

SELECT 'Total',
SUM(dbo.ASSET_INFO_DETAILS.VALUE) AS TOTAL_VALUE
FROM dbWNERS
INNER JOIN dbo.ASSET_OWNERSHIP ON dbWNERS.OWNER_ID = dbo.ASSET_OWNERSHIP.OWNER_ID
INNER JOIN dbo.ASSETS ON dbo.ASSET_OWNERSHIP.ASSET_ID = dbo.ASSETS.ASSET_ID
INNER JOIN GBL_ADMIN.dbo.ASSET_TYPES ON dbo.ASSETS.ASSET_TYPE_ID = GBL_ADMIN.dbo.ASSET_TYPES.ASSET_TYPE_ID
INNER JOIN dbo.ASSET_INFO_DETAILS ON dbo.ASSETS.ASSET_ID = dbo.ASSET_INFO_DETAILS.ASSET_ID
WHERE (dbo.ASSETS.INC_NET_WORTH = 1)

Simi

 

[G12Consult]

Hi,

that gives me the additional row which is great but it is adding the DR & CR rows. I need to substract the CR row from the DR row

Thanks

 

[G12Consult]

I was just thinking, should this calculation be done at the front end (I am using ASP.Net)

 

[gmmastros]

Minor change to Simi's suggestion:

SELECT TOP (100) PERCENT GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY, 
SUM(dbo.ASSET_INFO_DETAILS.VALUE) AS TOTAL_VALUE

FROM dbo.OWNERS 
INNER JOIN dbo.ASSET_OWNERSHIP ON dbo.OWNERS.OWNER_ID = dbo.ASSET_OWNERSHIP.OWNER_ID 
INNER JOIN dbo.ASSETS ON dbo.ASSET_OWNERSHIP.ASSET_ID = dbo.ASSETS.ASSET_ID 
INNER JOIN GBL_ADMIN.dbo.ASSET_TYPES ON dbo.ASSETS.ASSET_TYPE_ID = GBL_ADMIN.dbo.ASSET_TYPES.ASSET_TYPE_ID 
INNER JOIN dbo.ASSET_INFO_DETAILS ON dbo.ASSETS.ASSET_ID = dbo.ASSET_INFO_DETAILS.ASSET_ID 
WHERE (dbo.ASSETS.INC_NET_WORTH = 1) 
GROUP BY GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY, dbo.OWNERS.USER_ID 

Union 

SELECT 'Total', 
SUM([!]Case When GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY = 'DR' Then dbo.ASSET_INFO_DETAILS.VALUE
         When GBL_ADMIN.dbo.ASSET_TYPES.ACCOUNTING_ENTITY = 'CR' Then -dbo.ASSET_INFO_DETAILS.VALUE
		 Else 0 End[/!]) AS TOTAL_VALUE
FROM dbo.OWNERS 
INNER JOIN dbo.ASSET_OWNERSHIP ON dbo.OWNERS.OWNER_ID = dbo.ASSET_OWNERSHIP.OWNER_ID 
INNER JOIN dbo.ASSETS ON dbo.ASSET_OWNERSHIP.ASSET_ID = dbo.ASSETS.ASSET_ID 
INNER JOIN GBL_ADMIN.dbo.ASSET_TYPES ON dbo.ASSETS.ASSET_TYPE_ID = GBL_ADMIN.dbo.ASSET_TYPES.ASSET_TYPE_ID 
INNER JOIN dbo.ASSET_INFO_DETAILS ON dbo.ASSETS.ASSET_ID = dbo.ASSET_INFO_DETAILS.ASSET_ID 
WHERE (dbo.ASSETS.INC_NET_WORTH = 1)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

I have done it both ways. I had a project where the user was shown all there records in a table view so I just did what you are doing here and inserted a record a the bottom with the totals. However that was many records.

Simi

 

[G12Consult]

Thanks to both of you

Its working great.

Much appreciated