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 strongm 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
 
Thanks to both of you

Its working great.

Much appreciated

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top