Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Variables

Posted on 2010-01-12
13
Medium Priority
?
221 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
  • 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
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.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

575 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