Help adding a column based on a query

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help adding a column based on a query

Author

Topic

jasonpbyu
Starting Member

3 Posts

Posted - 2015-03-27 : 17:45:06

I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below. Thanks for any assistance!

SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber",
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
dbo.vw_tphillip_Customer_Inv.Total AS "Total",
dbo.vw_tphillip_Customer_Inv.Due AS "Due",
dbo.vw_tphillip_Customer_Inv.Overdue AS "Overdue"
FROM dbo.vw_tphillip_Customer_Inv
WHERE dbo.vw_tphillip_Customer_Inv.billtoCustomerNumber = 'BGC1000'
AND dbo.vw_tphillip_Customer_Inv.Due > 0.00
AND dbo.vw_tphillip_Customer_Inv.TransactionType <> 'Contract Invoice'
ORDER BY dbo.vw_tphillip_Customer_Inv.Overdue DESC

select SUM (dbo.vw_tphillip_Customer_Inv.Due) AS "TOTAL2" from dbo.vw_tphillip_Customer_Inv
where dbo.vw_tphillip_Customer_Inv.billtoCustomerNumber = 'BGC1000'
AND dbo.vw_tphillip_Customer_Inv.TransactionType <> 'Contract Invoice'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 17:47:40

You want the same value on each row? I would just store it in a variable in that case so that it doesn't run for each row. Then use the variable in your query: select column1, column2, @var1...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jasonpbyu
Starting Member

3 Posts

Posted - 2015-03-27 : 17:50:55

thanks, i wouldnt know how to use it as a variable. if i can just get a value to return then the report program will let me use it in email, but i can't have 2 individual queries in there, need to get that returned in the results of the first query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 17:56:15

Can you use a stored procedure? That'll make it one query to the program.

create proc someprocname
as
set nocount on;
declare @total2 int; --change the data type to whatever the Due column is or higher

select @total2 = SUM (dbo.vw_tphillip_Customer_Inv.Due) AS "TOTAL2"
from dbo.vw_tphillip_Customer_Inv
where dbo.vw_tphillip_Customer_Inv.billtoCustomerNumber = 'BGC1000'
AND dbo.vw_tphillip_Customer_Inv.TransactionType <> 'Contract Invoice';

SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber",
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
dbo.vw_tphillip_Customer_Inv.Total AS "Total",
dbo.vw_tphillip_Customer_Inv.Due AS "Due",
dbo.vw_tphillip_Customer_Inv.Overdue AS "Overdue",
@total2 AS TOTAL2
FROM dbo.vw_tphillip_Customer_Inv
WHERE dbo.vw_tphillip_Customer_Inv.billtoCustomerNumber = 'BGC1000'
AND dbo.vw_tphillip_Customer_Inv.Due > 0.00
AND dbo.vw_tphillip_Customer_Inv.TransactionType <> 'Contract Invoice'
ORDER BY dbo.vw_tphillip_Customer_Inv.Overdue DESC;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 17:58:11

You could also try this, but I haven't dug into it to see if they are equivalent. They probably aren't equivalent.

SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber",
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
dbo.vw_tphillip_Customer_Inv.Total AS "Total",
dbo.vw_tphillip_Customer_Inv.Due AS "Due",
dbo.vw_tphillip_Customer_Inv.Overdue AS "Overdue",
SUM (dbo.vw_tphillip_Customer_Inv.Due) AS "TOTAL2"
FROM dbo.vw_tphillip_Customer_Inv
WHERE dbo.vw_tphillip_Customer_Inv.billtoCustomerNumber = 'BGC1000'
AND dbo.vw_tphillip_Customer_Inv.Due > 0.00
AND dbo.vw_tphillip_Customer_Inv.TransactionType <> 'Contract Invoice'
GROUP BY dbo.vw_tphillip_Customer_Inv.InvoiceNumber,
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
dbo.vw_tphillip_Customer_Inv.Total AS "Total",
dbo.vw_tphillip_Customer_Inv.Due AS "Due",
dbo.vw_tphillip_Customer_Inv.Overdue AS "Overdue"
ORDER BY dbo.vw_tphillip_Customer_Inv.Overdue DESC;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jasonpbyu
Starting Member

3 Posts

Posted - 2015-03-27 : 18:13:37

Thanks. the last one doesn't work because the total2 needs to be the sum of all the due amounts. the other one with the variable @total2 is erroring out telling me "Must declare the scalar variable "@total2"."

I am heading out for the day, will continue to tinker with it next week. Didn't think it was going to be this rough LOL. Simply need the value in the 2nd query to return somewhere in my first query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 18:15:32

If it's erroring out due to @total2 missing, then you didn't grab the entire thing I posted. You have to run the entire script: from the create proc and then all the way down to the end. Then you just exec the proc which will execute the stuff inside.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources