?
Solved

SQL Variables

Posted on 2010-01-12
13
Medium Priority
?
211 Views
Last Modified: 2012-05-08
SQL 2008 Management Studio

The attached scripting works fine ... but as this is calculating totals, is there a way of utilising those values are variable (to prevent unncessary further sql searches)...such as adding numbers together or percentages etc.

the existing values created by script are as follows:

TotalCreated, Hoax, Invalids, DNCA

would like add more columns to be displayed as followed:

TotalNonActive == (Hoax+Invalids+DNCA)
TotalAvail == (TotalCreated-TotalNonActive)

other calculations such as percentages:
PercentageAvail == TotalAvail as a percentage of TotalCreated
PercentageDNCA == DNCA as a percentage of TotalCreated

Is this possible in a SQL query ?
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA

FROM LeadSourceProviders a

Open in new window

0
Comment
Question by:amillyard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26294564
SELECT a.*, TotalNonActive == (Hoax+Invalids+DNCA) , TotalAvail == (TotalCreated-TotalNonActive)
FROM
(
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA
FROM LeadSourceProviders a
) A
0
 

Author Comment

by:amillyard
ID: 26294612
Hi there, thanks for the response.

I am getting the following syntax error as follows:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'A'.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 800 total points
ID: 26294629
SELECT a.*, TotalNonActive = (Hoax+Invalids+DNCA) , TotalAvail = (TotalCreated-TotalNonActive)
FROM
(
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA
FROM LeadSourceProviders a
) A
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:amillyard
ID: 26294645
almost there -- just this syntax error as follows:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'TotalNonActive'.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26294735
And in the same line, adding the percentage calcs are as easy as this:
SELECT a.*, TotalNonActive = (Hoax+Invalids+DNCA) , TotalAvail = (TotalCreated-TotalNonActive), PercentageAvail = (TotalCreated-TotalNonActive) / TotalCreated * 100.0, PercentageDNCA = DNCA / TotalCreated * 100.0
FROM 
(
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA
FROM LeadSourceProviders a
) A

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26294745
oops, just saw your last post, try this one then
SELECT a.*, TotalNonActive = (Hoax+Invalids+DNCA) , TotalAvail = (TotalCreated-(Hoax+Invalids+DNCA)), PercentageAvail = (TotalCreated-(Hoax+Invalids+DNCA)) / TotalCreated * 100.0, PercentageDNCA = DNCA / TotalCreated * 100.0 
FROM  
( 
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA 
FROM LeadSourceProviders a 
) A

Open in new window

0
 

Author Comment

by:amillyard
ID: 26294775
CGLuttrell:

I see.  I'm sure that works -- just that I am still getting a syntax error relating to the variable:

TotalNonActive -- this compiles and displays fine if used by itself...just when referenced in the scripting (on right of that same line).
0
 

Author Comment

by:amillyard
ID: 26294803
ok thanks - have updated.

am getting computational error as follows:

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

I suspect a couple of the fields are    zero   value - how does SQL handle this please?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26294822
You have to wrap those with a case statement.
SELECT a.*, TotalNonActive = (Hoax+Invalids+DNCA) , TotalAvail = (TotalCreated-(Hoax+Invalids+DNCA)), PercentageAvail = CASE when TotalCreated = 0 THEN 0 ELSE (TotalCreated-(Hoax+Invalids+DNCA)) / TotalCreated * 100.0) END, PercentageDNCA = CASE when TotalCreated = 0 THEN 0 ELSE DNCA / TotalCreated * 100.0  ) END
FROM   
(  
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA  
FROM LeadSourceProviders a  
) A

Open in new window

0
 

Author Comment

by:amillyard
ID: 26294888
sorry... getting syntax error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'A'.
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 1200 total points
ID: 26294919
Sorry, a couple misplaced ).  Hard to add logic on the fly without being able to run it.  Try this one.
SELECT a.*, TotalNonActive = (Hoax+Invalids+DNCA) , TotalAvail = (TotalCreated-(Hoax+Invalids+DNCA)), PercentageAvail = CASE when TotalCreated = 0 THEN 0 ELSE (TotalCreated-(Hoax+Invalids+DNCA)) / TotalCreated * 100.0 END, PercentageDNCA = CASE when TotalCreated = 0 THEN 0 ELSE DNCA / TotalCreated * 100.0 END 
FROM    
(   
SELECT a.LeadSourceProvider_ID, a.LeadSourceProviderName, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID ) as TotalCreated, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND HoaxLeadStatus=1) as Hoax, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND InvalidNumbersStatus=1) as Invalids, (SELECT COUNT(*) FROM MasterAccounts m WHERE m.LeadSourceProvider_ID = a.LeadSourceProvider_ID AND DoNotCallAgainStatus=1) as DNCA   
FROM LeadSourceProviders a   
) A

Open in new window

0
 

Author Comment

by:amillyard
ID: 26294967
CGLuttrell: spot - on -- great :-)   thank you for persisting.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26295027
Your Welcome! Glad I could help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question