Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL View Help 2

Status
Not open for further replies.

G12Consult

Programmer
May 12, 2016
77
AU
I have the following view:

Code:
 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
 
Try This...

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(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)

Simi
 
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
 
I was just thinking, should this calculation be done at the front end (I am using ASP.Net)
 
Minor change to Simi's suggestion:

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top