troubleshooting Question

subquery calculation

Avatar of fwstealer
fwstealerFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
55 Comments1 Solution307 ViewsLast Modified:
i need to place functionality from one query into another.

query1
select [Date] = Cast(Month(Date) as varchar) + '/' + Cast(Year(Date) as varchar),
cast(cast(newshares as DECIMAL(9,6)) as float) as 'New Shares'
from ProjectedSalesUnits where FiscalYear = 'FY12'
returns
Date      New Shares
7/2011      0
8/2011      5
9/2011      4
10/2011      4
11/2011      3
12/2011      6
1/2012      3
2/2012      4
3/2012      4
4/2012      3
5/2012      5
6/2012      6


query2
select (count(*) * .70) as 'FraxExpiring' from vw_FraxExpiring where isActive = 1
and ContractName <> 'TEMPLATE'
and (not (contractID in (656,1947)))
and ContractType = 'Fractional Program'
and StatusTypeID = 1  
and
enddate >= '05/01/2012' and enddate <= '05/30/2012'
returns 1.40 as there are 2 rows.


now i need to incoporate query 2 into query3 below per the date so here is where im at now.

query3
select [Date] = Cast(Month(Date) as varchar) + '/' + Cast(Year(Date) as varchar),
cast(cast(newshares as DECIMAL(9,6)) as float) as 'New Shares',
(select (count(*) * .70) as 'FraxExpiring' from vw_FraxExpiring where
 isActive = 1
and ContractName <> 'TEMPLATE'
and (not (contractID in (656,1947)))
and ContractType = 'Fractional Program'
and StatusTypeID = 1  
and
enddate >= '05/01/2012' and enddate <= '05/30/2012') as 'FraxExpiring'
from ProjectedSalesUnits where FiscalYear = 'FY12'
returns the following
Date      New Shares      FraxExpiring
7/2011      0            1.40
8/2011      5            1.40
9/2011      4            1.40
10/2011      4            1.40
11/2011      3            1.40
12/2011      6            1.40
1/2012      3            1.40
2/2012      4            1.40
3/2012      4            1.40
4/2012      3            1.40
5/2012      5            1.40
6/2012      6            1.40

That works for 5/2012 but is not going to work for all the other months. How do I get this to work for all the months?
ASKER CERTIFIED SOLUTION
Zberteoc

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 55 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 55 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros