subquery calculation

fwstealer
fwstealer used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Few things:

1. Why do you convert to M/YYYY format? You can't sort that in chronological order. At least you should add a 0 before single digit months:

right('0'+Cast(Month(Date) as varchar),2) + '/' + Cast(Year(Date) as varchar)

2. Why do you cast to DECIMAL(9,6) and then to FLOAT? What type is the column in the first place?

3. You need a join column between the table and the view otherwise how do you know how to match the rows? How do you know that the first FraxExpiring value, 1.40, needs to be paired to 7/2011      0?

Author

Commented:
1. the date is like 2011-07-01 00:00:00.000 and i need the format in 7/2011
will try your suggestion

2. it is:  decimal(18,10), null
only i could figure it out and get it to work, but would like to keep 2 digits after decimal

3. yah I'm not sure how to do that
For 2 you should convert to DECIMAL(18,2) and for sure you don't want to cast to FLOAT especially if you need 2 decimals.

For 3 I'm afraid you didn't understand the issue. You HAVE to have a way of pairing the rows from 1 table to the rows from the other table. It should be a column on which you can build a condition in the ON clause. Is there a column like that?

And to help with this further I will ask what are the values returned by your second query. You said there are 2 rows returned. Why 2? Is there a difference between the 2 rows?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
2. got you; so i did -- cast(newshares as DECIMAL(9,2)) as 'New Shares'
and returns 0.00, 5.00, etc..

3. unfortunately no i don't have a column - all i have the enddate - such as 5/26/2012.
query 2 I do a row count and take 70 percent of the row count. I mentioned 2 because I have 2 rows for the test range of 5/1 to 5/31 and get the value of 1.40.
That doesn't work. We are missing information here. I have another question:

Query2 has a date range from '05/01/2012' to'05/30/2012' and returns 2 rows with the same value: 1.40. We can fix that using DISTINCT. However you then put the 1.40 value in Query3 next to each month even though 1.40 is for moth of May as it seems. Is this your intention?

Author

Commented:
Query2 has a date range from '05/01/2012' to'05/30/2012' and returns 2 rows with the same value: 1.40.

No - I'll try to be more clear. for that time period there are two rows so 70% of 2 is 1.40 which is correct.

I need to find the number of rows for month and get 70% of the row count.
Try below. I formatted a bit the code and cometized it:

select 
	right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + 
		Cast(Year(psu.[Date]) as varchar)						as [Date],
	cast(psu.newshares as DECIMAL(9,2))							as [New Shares],
	fe.[FraxExpiring]
from 
	ProjectedSalesUnits psu
	cross join
	(
		select distinct
			(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'
	) fe
where 
	FiscalYear = 'FY12'

Open in new window

Author

Commented:
that places 1.40 in each row; i know that for 06/2012 there is a row count of 36 and 70% of that is 25.2

how do we get it to loop through each month?
That was my question in the first place, is there a column or info that can be used as a match between the 2 tables? I also asked if Q2 is returning numbers for may then why is matched to all the other months?

Try this:

select 
	right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + 
		Cast(Year(psu.[Date]) as varchar)						as [Date],
	cast(psu.newshares as DECIMAL(9,2))							as [New Shares],
	fe.[FraxExpiring]
from 
	ProjectedSalesUnits psu
	inner join 
	(
		select 
			month(enddate) as [month],
			(count(*) * .70) as [FraxExpiring] 
		from 
			vw_FraxExpiring 
		where 
			isActive = 1
			and ContractName <> 'TEMPLATE' 
			and contractID not in (656,1947)
			and ContractType = 'Fractional Program' 
			and StatusTypeID = 1  
			and enddate >= '2012-01-01' 
			and enddate <= '2012-12-31 23:59:59.999'
		group by 
			month(enddate)
	) fe
		on fe.[month]=Month(psu.[Date])
where 
	FiscalYear = 'FY12'

Open in new window

You have to make sure that the months from ProjectedSalesUnits based on FY12 filter are the same with the months from the vw_FraxExpiring with


                  and enddate >= '2012-01-01'
                  and enddate <= '2012-12-31 23:59:59.999'
filter. I just assumed that. You will have to make a relation between FY12 and those dates.

Author

Commented:
yah my bad.

the vw_fraxexpiring is:

select * from openquery([astrosql2],
'
select tm_Contracts.ContractID,
tm_Contracts.ContractName,
tm_Contracts.IsTemplate,
tm_Contracts.StatusTypeID,
tm_Contracts.StatusDate,
tm_Contracts.StartDate,
tm_Contracts.EndDate,
tm_Contracts.DateCreated,
tm_Contracts.OrigEndDate,
tm_Contracts.ContractType,
tm_Accounts_1.AccountName,
tm_Accounts_1.IsActive
from
ASTROSQL2.astro.dbo.tm_Contracts
inner join
tr_AccountContracts ON tm_Contracts.ContractID = tr_AccountContracts.ContractID
inner join
tm_Accounts AS tm_Accounts_1 ON tr_AccountContracts.AccountID = tm_Accounts_1.AccountID
where tm_Contracts.ContractType = ''Fractional Program''
'
)

with the enddate having data as 2012-09-08

in this view would it be wise to have the FiscalYear as a column as well? if so I'm not sure how to do that - look at end date and determine the FiscalYear?

Example of FY12
7/2011
8/2011
9/2011
10/2011
11/2011
12/2011
1/2012
2/2012
3/2012
4/2012
5/2012
6/2012
We can make it work, however if the tables are big it might take a while to run because we join on calculated values and those are not indexed:

select 
	right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + 
		Cast(Year(psu.[Date]) as varchar)						as [Date],
	cast(psu.newshares as DECIMAL(9,2))							as [New Shares],
	fe.[FraxExpiring]
from 
	ProjectedSalesUnits psu
	inner join 
	(
		select 
			Year(enddate)		as [Year],
			month(enddate)		as [month],
			(count(*) * .70)	as [FraxExpiring] 
		from 
			vw_FraxExpiring 
		where 
			isActive = 1
			and ContractName <> 'TEMPLATE' 
			and contractID not in (656,1947)
			and ContractType = 'Fractional Program' 
			and StatusTypeID = 1  
		group by 
			Year(enddate),
			month(enddate)
	) fe
		on fe.[Year]	=Year(psu.[Date])
		on fe.[month]	=Month(psu.[Date])
where 
	FiscalYear = 'FY12'

Open in new window

Author

Commented:
Error with - incorrect syntax near the keyword 'on'

on fe.[month]      =Month(psu.[Date])
Remove the "on" word from that line only and replace it with AND.

Author

Commented:
my bad - should have seen that.

Author

Commented:
so the above query returns data for 5 and 6 months but none of the rest of them like 7/2011 through 4/2012

Example of FY12
7/2011
8/2011
9/2011
10/2011
11/2011
12/2011
1/2012
2/2012
3/2012
4/2012
5/2012 - returns
6/2012 - returns
That is because the view doesn't have data for those months. Run this and see what it returns:

		select 
			Year(enddate)		as [Year],
			month(enddate)		as [month],
			(count(*) * .70)	as [FraxExpiring] 
		from 
			vw_FraxExpiring 
		where 
			isActive = 1
			and ContractName <> 'TEMPLATE' 
			and contractID not in (656,1947)
			and ContractType = 'Fractional Program' 
			and StatusTypeID = 1  
		group by 
			Year(enddate),
			month(enddate)

Open in new window

Author

Commented:
if the month of 5 only has a row count of 2 then the value should be 1.40; I'm getting back 4.90 -- not sure how that is possible.
Ok, run this and see what it returns:

		select 
			Year(enddate)		as [Year],
			month(enddate)		as [month],
			count(*)			as [months_rows],
			(count(*) * .70)	as [FraxExpiring] 
		from 
			vw_FraxExpiring 
		where 
			isActive = 1
			and ContractName <> 'TEMPLATE' 
			and contractID not in (656,1947)
			and ContractType = 'Fractional Program' 
			and StatusTypeID = 1  
		group by 
			Year(enddate),
			month(enddate)

Open in new window

Author

Commented:
returned 78 rows of data like - if i focus on 5 and 6 month for 2012.

Year       month      months_rows       FraxExpiring
2012      5              7                       4.90
2012      6              36                        25.20

i know that 5/2012 should have a value of .70 as there is only 1 row now
As you can see for month 5, May, of year 2012 there are 7 rows and not 2, so the result 4.90 is correct.

Is there data for other months than 5 and 6?

Author

Commented:
oh my bad as we were not looking at the where conditions

yes
Year      month      months_rows      FraxExpiring
2013      1      18      12.60
2014      1      8      5.60
2015      1      12      8.40
2016      1      9      6.30
2017      1      7      4.90
2018      1      1      0.70
2013      2      10      7.00
2014      2      9      6.30
2015      2      6      4.20
2016      2      4      2.80
2017      2      3      2.10
2018      2      4      2.80
2026      2      1      0.70
2013      3      13      9.10
2014      3      5      3.50
2015      3      15      10.50
2016      3      5      3.50
2017      3      5      3.50
2018      3      5      3.50
2020      3      5      3.50
2026      3      3      2.10
2013      4      16      11.20
2014      4      6      4.20
2015      4      4      2.80
2016      4      11      7.70
2017      4      1      0.70
2025      4      1      0.70
2026      4      1      0.70
2012      5      7      4.90
2013      5      20      14.00
2014      5      8      5.60
2015      5      9      6.30
2016      5      5      3.50
2017      5      9      6.30
2012      6      36      25.20
2013      6      19      13.30
2014      6      10      7.00
2015      6      13      9.10
2016      6      5      3.50
2017      6      8      5.60
2012      7      16      11.20
2013      7      14      9.80
2014      7      4      2.80
2015      7      18      12.60
2016      7      3      2.10
2017      7      1      0.70
2021      7      3      2.10
2012      8      23      16.10
2013      8      7      4.90
2014      8      6      4.20
2015      8      6      4.20
2016      8      5      3.50
2017      8      3      2.10
2012      9      24      16.80
2013      9      5      3.50
2014      9      12      8.40
2015      9      1      0.70
2016      9      6      4.20
2017      9      3      2.10
2018      9      1      0.70
2012      10      19      13.30
2013      10      11      7.70
2014      10      9      6.30
2015      10      1      0.70
2016      10      3      2.10
2017      10      2      1.40
2012      11      12      8.40
2013      11      11      7.70
2014      11      10      7.00
2015      11      2      1.40
2016      11      4      2.80
2017      11      4      2.80
2012      12      36      25.20
2013      12      12      8.40
2014      12      15      10.50
2015      12      7      4.90
2016      12      4      2.80
2017      12      3      2.10

Author

Commented:
if i run the following on the view it pulls back only 1 row as expected since there is only 1 per the conditions below

select *,[Date] = right('0'+Cast(Month(EndDate) as varchar),2) + '/' + Cast(Year(EndDate) as varchar)
FROM [MSAccess].[dbo].[vw_FraxExpiring] where
                  isActive = 1
                  and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
                  and
                  enddate >= '05/01/2012' and enddate <= '05/30/2012'


i took the last query and did this:
select right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + Cast(Year(psu.[Date]) as varchar) as [Date],
      cast(psu.newshares as DECIMAL(9,2))      as [New Shares],
      fe.[FraxExpiring]
from
      ProjectedSalesUnits psu
      inner join
      (
         select
                Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  count(*)                  as [months_rows],
                  (count(*) * .70)      as [FraxExpiring]
            from
                  vw_FraxExpiring
            where
                  isActive = 1
                  and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
            group by
                  Year(enddate),
                  month(enddate)
      ) fe
            on fe.[Year]      =Year(psu.[Date])
            and fe.[month]      =Month(psu.[Date])
where
      FiscalYear = 'FY12'

got:
Date      New Shares      FraxExpiring
05/2012      5.00                      4.90
06/2012      6.00                      25.20

the fraxexpiring for 5/2012 should be .70
I wonder why you have years in the future but maybe that is the design.

I am not sure what you mean by "we were not looking at the where clause", as I copied as it was from you Q2 to be used in my subquery and then I fitered by F12 as you have in your Q1.

Anyway, as I said, the numbers here prove to be correct: for the year 2012 and month 5 you have 7 entries(rows) hence the 4.90 value.

This query really all it does is to count the rows per year and month based on enddate column and calculate 70% of that.

Data is correct so I don't see what the problem is.
"the fraxexpiring for 5/2012 should be .70"

Why you keep saying that when the results 2 posts above say quite different. They show:

2012    5      7     4.90

That is the correct data and is coming from your table.
Why are you using this interval:

 enddate >= '05/01/2012' and enddate <= '05/30/2012'

in your original query? Isn't it supposed to look at entire month of May, which ends at 05/31/2012 23:59:59.999?

Author

Commented:
yes - have rows in future by design

for 5/2012 there are 7 rows but I need to find the rows that are expiring for that month in 2012. right now there is only 1 so that is where i come up with the .70.

I'm using enddate >= '05/01/2012' and enddate <= '05/30/2012' as a test but need it to be by the beginning day of each month to the ending day of each month in the selected fiscal year. i select fy12 the months are as follows:
7/2011
8/2011
9/2011
10/2011
11/2011
12/2011
1/2012
2/2012
3/2012
4/2012
5/2012
6/2012

so the results returned by the query should be something like

Date      New Shares      FraxExpiring
7/2011      0.00            0.70
8/2011      5.00            5.25
9/2011      4.00            2.0
10/2011      4.00            2.0
11/2011      3.00            2.0
12/2011      6.00            2.0
1/2012      3.00            2.0
2/2012      4.00            2.0
3/2012      4.00            2.0
4/2012      3.00            2.0
5/2012      5.00            0.70
6/2012      6.00            2.0

note the fraxexpiring has copy/pasted values
In this case we need to find the correct dates filter in the subquery. Right now we have:

select 
			Year(enddate)		as [Year],
			month(enddate)		as [month],
			count(*)			as [months_rows],
			(count(*) * .70)	as [FraxExpiring] 
		from 
			vw_FraxExpiring 
		where 
			isActive = 1
			and ContractName <> 'TEMPLATE' 
			and contractID not in (656,1947)
			and ContractType = 'Fractional Program' 
			and StatusTypeID = 1  
		group by 
			Year(enddate),
			month(enddate)

Open in new window


So it will count all the entries per year and month of the enddate only. If that is not what you want we have to come up with a condition that will filter the rows down to what exactly you need. Can you explain what are the exact rows that you need from this query?

Author

Commented:
sure and i really do appreciate your help.

Author

Commented:
I've got a table like this that I use to query for the FiscalYear.

recID      Month      FiscalYear      Qtr      FiscalYr_Qtr
1      2008-07-01 00:00:00.000      FY09      Q1      FY09-Q1
2      2008-08-01 00:00:00.000      FY09      Q1      FY09-Q1
3      2008-09-01 00:00:00.000      FY09      Q1      FY09-Q1
4      2008-10-01 00:00:00.000      FY09      Q2      FY09-Q2
5      2008-11-01 00:00:00.000      FY09      Q2      FY09-Q2
6      2008-12-01 00:00:00.000      FY09      Q2      FY09-Q2
7      2009-01-01 00:00:00.000      FY09      Q3      FY09-Q3
8      2009-02-01 00:00:00.000      FY09      Q3      FY09-Q3
9      2009-03-01 00:00:00.000      FY09      Q3      FY09-Q3
10      2009-04-01 00:00:00.000      FY09      Q4      FY09-Q4
11      2009-05-01 00:00:00.000      FY09      Q4      FY09-Q4
12      2009-06-01 00:00:00.000      FY09      Q4      FY09-Q4
13      2009-07-01 00:00:00.000      FY10      Q1      FY10-Q1
14      2009-08-01 00:00:00.000      FY10      Q1      FY10-Q1
15      2009-09-01 00:00:00.000      FY10      Q1      FY10-Q1
16      2009-10-01 00:00:00.000      FY10      Q2      FY10-Q2
17      2009-11-01 00:00:00.000      FY10      Q2      FY10-Q2
18      2009-12-01 00:00:00.000      FY10      Q2      FY10-Q2
19      2010-01-01 00:00:00.000      FY10      Q3      FY10-Q3
20      2010-02-01 00:00:00.000      FY10      Q3      FY10-Q3
21      2010-03-01 00:00:00.000      FY10      Q3      FY10-Q3
22      2010-04-01 00:00:00.000      FY10      Q4      FY10-Q4
23      2010-05-01 00:00:00.000      FY10      Q4      FY10-Q4
24      2010-06-01 00:00:00.000      FY10      Q4      FY10-Q4
25      2010-07-01 00:00:00.000      FY11      Q1      FY11-Q1
26      2010-08-01 00:00:00.000      FY11      Q1      FY11-Q1
27      2010-09-01 00:00:00.000      FY11      Q1      FY11-Q1
28      2010-10-01 00:00:00.000      FY11      Q2      FY11-Q2
29      2010-11-01 00:00:00.000      FY11      Q2      FY11-Q2
30      2010-12-01 00:00:00.000      FY11      Q2      FY11-Q2
31      2011-01-01 00:00:00.000      FY11      Q3      FY11-Q3
32      2011-02-01 00:00:00.000      FY11      Q3      FY11-Q3
33      2011-03-01 00:00:00.000      FY11      Q3      FY11-Q3
34      2011-04-01 00:00:00.000      FY11      Q4      FY11-Q4
35      2011-05-01 00:00:00.000      FY11      Q4      FY11-Q4
36      2011-06-01 00:00:00.000      FY11      Q4      FY11-Q4
37      2011-07-01 00:00:00.000      FY12      Q1      FY12-Q1
38      2011-08-01 00:00:00.000      FY12      Q1      FY12-Q1
39      2011-09-01 00:00:00.000      FY12      Q1      FY12-Q1
40      2011-10-01 00:00:00.000      FY12      Q2      FY12-Q2
41      2011-11-01 00:00:00.000      FY12      Q2      FY12-Q2
42      2011-12-01 00:00:00.000      FY12      Q2      FY12-Q2
43      2012-01-01 00:00:00.000      FY12      Q3      FY12-Q3
44      2012-02-01 00:00:00.000      FY12      Q3      FY12-Q3
45      2012-03-01 00:00:00.000      FY12      Q3      FY12-Q3
46      2012-04-01 00:00:00.000      FY12      Q4      FY12-Q4
47      2012-05-01 00:00:00.000      FY12      Q4      FY12-Q4
48      2012-06-01 00:00:00.000      FY12      Q4      FY12-Q4
49      2012-07-01 00:00:00.000      FY13      Q1      FY13-Q1
50      2012-08-01 00:00:00.000      FY13      Q1      FY13-Q1
51      2012-09-01 00:00:00.000      FY13      Q1      FY13-Q1
52      2012-10-01 00:00:00.000      FY13      Q2      FY13-Q2
53      2012-11-01 00:00:00.000      FY13      Q2      FY13-Q2
54      2012-12-01 00:00:00.000      FY13      Q2      FY13-Q2
55      2013-01-01 00:00:00.000      FY13      Q3      FY13-Q3
56      2013-02-01 00:00:00.000      FY13      Q3      FY13-Q3
57      2013-03-01 00:00:00.000      FY13      Q3      FY13-Q3
58      2013-04-01 00:00:00.000      FY13      Q4      FY13-Q4
59      2013-05-01 00:00:00.000      FY13      Q4      FY13-Q4
60      2013-06-01 00:00:00.000      FY13      Q4      FY13-Q4
61      2013-07-01 00:00:00.000      FY14      Q1      FY14-Q1
62      2013-08-01 00:00:00.000      FY14      Q1      FY14-Q1
63      2013-09-01 00:00:00.000      FY14      Q1      FY14-Q1
64      2013-10-01 00:00:00.000      FY14      Q2      FY14-Q2
65      2013-11-01 00:00:00.000      FY14      Q2      FY14-Q2
66      2013-12-01 00:00:00.000      FY14      Q2      FY14-Q2
67      2014-01-01 00:00:00.000      FY14      Q3      FY14-Q3
68      2014-02-01 00:00:00.000      FY14      Q3      FY14-Q3
69      2014-03-01 00:00:00.000      FY14      Q3      FY14-Q3
70      2014-04-01 00:00:00.000      FY14      Q4      FY14-Q4
71      2014-05-01 00:00:00.000      FY14      Q4      FY14-Q4
72      2014-06-01 00:00:00.000      FY14      Q4      FY14-Q4
73      2014-07-01 00:00:00.000      FY15      Q1      FY15-Q1
74      2014-08-01 00:00:00.000      FY15      Q1      FY15-Q1
75      2014-09-01 00:00:00.000      FY15      Q1      FY15-Q1
76      2014-10-01 00:00:00.000      FY15      Q2      FY15-Q2
77      2014-11-01 00:00:00.000      FY15      Q2      FY15-Q2
78      2014-12-01 00:00:00.000      FY15      Q2      FY15-Q2
79      2015-01-01 00:00:00.000      FY15      Q3      FY15-Q3
80      2015-02-01 00:00:00.000      FY15      Q3      FY15-Q3
81      2015-03-01 00:00:00.000      FY15      Q3      FY15-Q3
82      2015-04-01 00:00:00.000      FY15      Q4      FY15-Q4
83      2015-05-01 00:00:00.000      FY15      Q4      FY15-Q4
84      2015-06-01 00:00:00.000      FY15      Q4      FY15-Q4
85      2015-07-01 00:00:00.000      FY16      Q1      FY16-Q1
86      2015-08-01 00:00:00.000      FY16      Q1      FY16-Q1
87      2015-09-01 00:00:00.000      FY16      Q1      FY16-Q1
88      2015-10-01 00:00:00.000      FY16      Q2      FY16-Q2
89      2015-11-01 00:00:00.000      FY16      Q2      FY16-Q2
90      2015-12-01 00:00:00.000      FY16      Q2      FY16-Q2
91      2016-01-01 00:00:00.000      FY16      Q3      FY16-Q3
92      2016-02-01 00:00:00.000      FY16      Q3      FY16-Q3
93      2016-03-01 00:00:00.000      FY16      Q3      FY16-Q3
94      2016-04-01 00:00:00.000      FY16      Q4      FY16-Q4
95      2016-05-01 00:00:00.000      FY16      Q4      FY16-Q4
96      2016-06-01 00:00:00.000      FY16      Q4      FY16-Q4
97      2016-07-01 00:00:00.000      FY17      Q1      FY17-Q1
98      2016-08-01 00:00:00.000      FY17      Q1      FY17-Q1
99      2016-09-01 00:00:00.000      FY17      Q1      FY17-Q1
100      2016-10-01 00:00:00.000      FY17      Q2      FY17-Q2
101      2016-11-01 00:00:00.000      FY17      Q2      FY17-Q2
102      2016-12-01 00:00:00.000      FY17      Q2      FY17-Q2
103      2017-01-01 00:00:00.000      FY17      Q3      FY17-Q3
104      2017-02-01 00:00:00.000      FY17      Q3      FY17-Q3
105      2017-03-01 00:00:00.000      FY17      Q3      FY17-Q3
106      2017-04-01 00:00:00.000      FY17      Q4      FY17-Q4
107      2017-05-01 00:00:00.000      FY17      Q4      FY17-Q4
108      2017-06-01 00:00:00.000      FY17      Q4      FY17-Q4
109      2017-07-01 00:00:00.000      FY18      Q1      FY18-Q1
110      2017-08-01 00:00:00.000      FY18      Q1      FY18-Q1
111      2017-09-01 00:00:00.000      FY18      Q1      FY18-Q1
112      2017-10-01 00:00:00.000      FY18      Q2      FY18-Q2
113      2017-11-01 00:00:00.000      FY18      Q2      FY18-Q2
114      2017-12-01 00:00:00.000      FY18      Q2      FY18-Q2
115      2018-01-01 00:00:00.000      FY18      Q3      FY18-Q3
116      2018-02-01 00:00:00.000      FY18      Q3      FY18-Q3
117      2018-03-01 00:00:00.000      FY18      Q3      FY18-Q3
118      2018-04-01 00:00:00.000      FY18      Q4      FY18-Q4
119      2018-05-01 00:00:00.000      FY18      Q4      FY18-Q4
120      2018-06-01 00:00:00.000      FY18      Q4      FY18-Q4
121      2018-07-01 00:00:00.000      FY19      Q1      FY19-Q1
122      2018-08-01 00:00:00.000      FY19      Q1      FY19-Q1
123      2018-09-01 00:00:00.000      FY19      Q1      FY19-Q1
124      2018-10-01 00:00:00.000      FY19      Q2      FY19-Q2
125      2018-11-01 00:00:00.000      FY19      Q2      FY19-Q2
126      2018-12-01 00:00:00.000      FY19      Q2      FY19-Q2
127      2019-01-01 00:00:00.000      FY19      Q3      FY19-Q3
128      2019-02-01 00:00:00.000      FY19      Q3      FY19-Q3
129      2019-03-01 00:00:00.000      FY19      Q3      FY19-Q3
130      2019-04-01 00:00:00.000      FY19      Q4      FY19-Q4
131      2019-05-01 00:00:00.000      FY19      Q4      FY19-Q4
132      2019-06-01 00:00:00.000      FY19      Q4      FY19-Q4
133      2019-07-01 00:00:00.000      FY20      Q1      FY20-Q1
134      2019-08-01 00:00:00.000      FY20      Q1      FY20-Q1
135      2019-09-01 00:00:00.000      FY20      Q1      FY20-Q1
136      2019-10-01 00:00:00.000      FY20      Q2      FY20-Q2
137      2019-11-01 00:00:00.000      FY20      Q2      FY20-Q2
138      2019-12-01 00:00:00.000      FY20      Q2      FY20-Q2
139      2020-01-01 00:00:00.000      FY20      Q3      FY20-Q3
140      2020-02-01 00:00:00.000      FY20      Q3      FY20-Q3
141      2020-03-01 00:00:00.000      FY20      Q3      FY20-Q3
142      2020-04-01 00:00:00.000      FY20      Q4      FY20-Q4
143      2020-05-01 00:00:00.000      FY20      Q4      FY20-Q4
144      2020-06-01 00:00:00.000      FY20      Q4      FY20-Q4


Based on the selection say FY12, I then am trying to produce this:
Date      New Shares      FraxExpiring
7/2011      0.00            0.70
8/2011      5.00            5.25
9/2011      4.00            2.0
10/2011      4.00            2.0
11/2011      3.00            2.0
12/2011      6.00            2.0
1/2012      3.00            2.0
2/2012      4.00            2.0
3/2012      4.00            2.0
4/2012      3.00            2.0
5/2012      5.00            0.70
6/2012      6.00            25.20

based on the data from the vw_fraxexpiring.

so if we ran one of the previous queries such as:
select right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + Cast(Year(psu.[Date]) as varchar) as [Date],
      cast(psu.newshares as DECIMAL(9,2))      as [New Shares],
      fe.[FraxExpiring]
from
      ProjectedSalesUnits psu
      inner join
      (
            select
                  Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  (count(*) * .70)      as [FraxExpiring]
            from
                  vw_FraxExpiring
            where
                  isActive = 1
                  and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program'
                  and StatusTypeID = 1  
            group by
                  Year(enddate),
                  month(enddate)
      ) fe
            on fe.[Year]      =Year(psu.[Date])
            and fe.[month]      =Month(psu.[Date])
where
      FiscalYear = 'FY12'


We'd have results like:
Date      New Shares      FraxExpiring
7/2011      0.00            0.70
8/2011      5.00            5.25
9/2011      4.00            2.0
10/2011      4.00            2.0
11/2011      3.00            2.0
12/2011      6.00            2.0
1/2012      3.00            2.0
2/2012      4.00            2.0
3/2012      4.00            2.0
4/2012      3.00            2.0
5/2012      5.00            0.70
6/2012      6.00            25.20
The problem is not with this table, this is straight forward and no issue, on eentry for each month spanning few years also into the future.

The problem is with the view:

            select 
                  Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  (count(*) * .70)      as [FraxExpiring] 
            from 
                  vw_FraxExpiring 
            where 
                  isActive = 1
                  and ContractName <> 'TEMPLATE' 
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program' 
                  and StatusTypeID = 1  
            group by 
                  Year(enddate),
                  month(enddate)

Open in new window


You initially said that for year 2012 and month 5 you should get only 2 rows and 1.40 for that calculation, later you said it should be only 1 row and 0.70 calculation. So I am a bit confused here. When you posted the results coming from this query for 2012 and 5 you got 7 rows and 4.90 calculation.

So what is with this view?

Author

Commented:
the view is setup to only get certain data elements from a much larger table.

yes by saying that 5/2012 there are only 2 rows that met the conditions and therefore gave a value of 1.40. now the data has been changed so there is only 1 row that meets the condition so there is a value of .70.

there are 7 total rows for 5/2012 but there is only 1 row that is set to expire in the 5th month of 2012.

from
                  vw_FraxExpiring
            where
                  isActive = 1
                  and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program'
                  and StatusTypeID = 1

and of that view get match the date(5/2011) and 70% of the # frax shares expiring in a month per the selected fiscal year like fy12
And how can we tell which is the row that expires. This is our issue because we need to filter those down.

Author

Commented:
i was thinking of the rows that have enddate in the fiscalyear

enddate has value like 2012-05-26

so that falls under fy12

which is what was selected as a filter of fy12

Author

Commented:
another thought was setting up a new column in the view based on fiscal year

I modified the view as such:

select * from openquery([astrosql2],
'
select tm_Contracts.ContractID,
tm_Contracts.ContractName,
tm_Contracts.IsTemplate,
tm_Contracts.StatusTypeID,
tm_Contracts.StatusDate,
tm_Contracts.StartDate,
tm_Contracts.EndDate,
tm_Contracts.DateCreated,
tm_Contracts.OrigEndDate,
tm_Contracts.ContractType,
tm_Accounts_1.AccountName,
tm_Accounts_1.IsActive,
[Date] = right(''0''+Cast(Month(EndDate) as varchar),2) + ''/'' + Cast(Year(EndDate) as varchar)
from
ASTROSQL2.astro.dbo.tm_Contracts
inner join
tr_AccountContracts ON tm_Contracts.ContractID = tr_AccountContracts.ContractID
inner join
tm_Accounts AS tm_Accounts_1 ON tr_AccountContracts.AccountID = tm_Accounts_1.AccountID
where tm_Contracts.ContractType = ''Fractional Program''
'
)

so based on the enddate i now have a new column: Date that holds data like
7/2011
8/2011
9/2011
10/2011
11/2011
12/2011
1/2012
2/2012
3/2012
4/2012
5/2012
6/2012

so is there a way to also determine the fiscal year from this new column?

like 7/2012 would be fy13 since the fiscal year runs 7 to 6

Author

Commented:
cause now i could run the following:

SELECT (count(*) * .70)      as [FraxExpiring]
  FROM [MSAccess].[dbo].[vw_FraxExpiring] where isActive = 1
                  and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
                  and fy = 'FY12'

and hopefully get a result set like:
Date      New Shares      FraxExpiring
7/2011      0.00            0.70
8/2011      5.00            5.25
9/2011      4.00            2.0
10/2011      4.00            2.0
11/2011      3.00            2.0
12/2011      6.00            2.0
1/2012      3.00            2.0
2/2012      4.00            2.0
3/2012      4.00            2.0
4/2012      3.00            2.0
5/2012      5.00            4.90
6/2012      6.00            25.20
There is no  need for the new column in the view because if we work with the enddate column when we extract YEAR and MONTH and then join those values with the YEAR and MONTH from the table with the fiscal year based on FY12, which I did in my query, we will have the correct matches.

What confuses me is that you still say that for month 5 in the view data we only have 1 expired entry but you still didn't say what that means, expired. How is that row from the view different from the rest 6 for the month of 5?

Author

Commented:
my thought on the new column was that it would make it easier to query

it looks like the data has been changed so there isn't 1 row now.

I believe this is the query that was last used:
select right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + Cast(Year(psu.[Date]) as varchar) as [Date],
      cast(psu.newshares as DECIMAL(9,2))      as [New Shares],
      
      fe.[FraxExpiring]
from
      ProjectedSalesUnits psu
      inner join
      (
         select
                Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  count(*)                  as [months_rows],
                  (count(*) * .70)      as [FraxExpiring]
            from
                  vw_FraxExpiring
            where
                  isActive = 1
                  and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
            group by
                  Year(enddate),
                  month(enddate)
      ) fe
            on fe.[Year]      =Year(psu.[Date])
            and fe.[month]      =Month(psu.[Date])
where
      FiscalYear = 'FY12'

returned:
Date      New Shares      FraxExpiring
05/2012      5.00      4.20
06/2012      6.00      25.20

but why not all of the fy12 rows?
like those of:
7/2011      0.00            0.70
8/2011      5.00            5.25
9/2011      4.00            2.0
10/2011      4.00            2.0
11/2011      3.00            2.0
12/2011      6.00            2.0
1/2012      3.00            2.0
2/2012      4.00            2.0
3/2012      4.00            2.0
4/2012      3.00            2.0
Because of the inner join and I believe that the subquery only returns 2 rows:

         select
                Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  count(*)                  as [months_rows],
                  (count(*) * .70)      as [FraxExpiring] 
            from 
                  vw_FraxExpiring 
            where 
                  isActive = 1
                  and ContractName <> 'TEMPLATE' 
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program' 
                  and StatusTypeID = 1
            group by 
                  Year(enddate),
                  month(enddate)

Open in new window


But this is a data issue, there is nothing wrong with the query, unless your view has something.

Please check what this query returns, but I think we did that. Anyway let's check again.

Author

Commented:
returns:

Year      month      months_rows      FraxExpiring
2013      1      18      12.60
2014      1      8      5.60
2015      1      12      8.40
2016      1      9      6.30
2017      1      7      4.90
2018      1      1      0.70
2013      2      10      7.00
2014      2      9      6.30
2015      2      6      4.20
2016      2      4      2.80
2017      2      3      2.10
2018      2      4      2.80
2026      2      1      0.70
2013      3      13      9.10
2014      3      5      3.50
2015      3      15      10.50
2016      3      5      3.50
2017      3      5      3.50
2018      3      5      3.50
2020      3      5      3.50
2026      3      3      2.10
2013      4      16      11.20
2014      4      6      4.20
2015      4      4      2.80
2016      4      11      7.70
2017      4      1      0.70
2025      4      1      0.70
2026      4      1      0.70
2012      5      6      4.20
2013      5      20      14.00
2014      5      8      5.60
2015      5      9      6.30
2016      5      5      3.50
2017      5      9      6.30
2012      6      36      25.20
2013      6      19      13.30
2014      6      11      7.70
2015      6      13      9.10
2016      6      5      3.50
2017      6      8      5.60
2012      7      16      11.20
2013      7      14      9.80
2014      7      4      2.80
2015      7      18      12.60
2016      7      3      2.10
2017      7      1      0.70
2021      7      3      2.10
2012      8      23      16.10
2013      8      7      4.90
2014      8      6      4.20
2015      8      6      4.20
2016      8      5      3.50
2017      8      4      2.80
2012      9      24      16.80
2013      9      5      3.50
2014      9      12      8.40
2015      9      1      0.70
2016      9      6      4.20
2017      9      3      2.10
2018      9      1      0.70
2012      10      19      13.30
2013      10      11      7.70
2014      10      9      6.30
2015      10      1      0.70
2016      10      3      2.10
2017      10      2      1.40
2012      11      12      8.40
2013      11      11      7.70
2014      11      10      7.00
2015      11      2      1.40
2016      11      4      2.80
2017      11      4      2.80
2012      12      36      25.20
2013      12      12      8.40
2014      12      15      10.50
2015      12      7      4.90
2016      12      4      2.80
2017      12      3      2.10

Author

Commented:
I've also got this other table that contains the fiscal year mappings: FiscalYr_Qtr which i posted above
As I said is a data issue. I sorted the results you posted and this is what it returns for 2012:


2012      5      6      4.20
2012      6      36      25.20
2012      7      16      11.20
2012      8      23      16.10
2012      9      24      16.80
2012      10      19      13.30
2012      11      12      8.40
2012      12      36      25.20

So basically only the months 5 and 6 are there to be joined with the months for FY12 , which are July 2011 to June 2012. You can filter that query fo the years 2012 and earlier to see what I am talking about:

         select
                Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  count(*)                  as [months_rows],
                  (count(*) * .70)      as [FraxExpiring] 
            from 
                  vw_FraxExpiring 
            where 
                  isActive = 1
                  and ContractName <> 'TEMPLATE' 
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program' 
                  and StatusTypeID = 1

                  -- let's filter by year
                  and Year(enddate)<=2012

            group by 
                  Year(enddate),
                  month(enddate)

Open in new window


You will see that this query returns only the rows I gave above. There is no data before month of 2012 05.

Author

Commented:
yes true those are the only two months but I need all months for the fiscal year.
I agree but the problem is with your data not the query. A query is only returning what it can find in the tables and views and, in this case, your view, vw_FraxExpiring , has HAS NO DATA for the months before 2012 05, that is May 2012.

So you either fix the view to return those months or insert them in the source table if you can.

At this point this is all I can say.

Again, the query works fine.

Author

Commented:
yes - the data has an issue. but the view pulls back a load of data including date before may 2012.

select * from openquery([astrosql2],
'
select tm_Contracts.ContractID,
tm_Contracts.ContractName,
tm_Contracts.IsTemplate,
tm_Contracts.StatusTypeID,
tm_Contracts.StatusDate,
tm_Contracts.StartDate,
tm_Contracts.EndDate,
tm_Contracts.DateCreated,
tm_Contracts.OrigEndDate,
tm_Contracts.ContractType,
tm_Accounts_1.AccountName,
tm_Accounts_1.IsActive,
[Date] = right(''0''+Cast(Month(EndDate) as varchar),2) + ''/'' + Cast(Year(EndDate) as varchar)
from
ASTROSQL2.astro.dbo.tm_Contracts
inner join
tr_AccountContracts ON tm_Contracts.ContractID = tr_AccountContracts.ContractID
inner join
tm_Accounts AS tm_Accounts_1 ON tr_AccountContracts.AccountID = tm_Accounts_1.AccountID
where tm_Contracts.ContractType = ''Fractional Program''
'
)

returns row all the way back to 2006.

Author

Commented:
think I'm seeing this as but need a tweak.

select id, right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + Cast(Year(psu.[Date]) as varchar) as [Date],
      cast(psu.newshares as DECIMAL(9,2))      as [New Shares],
 fiscalyear,
      fe.[FraxExpiring]
from
      ProjectedSalesUnits psu
      inner join
      (
         select
                Year(enddate) as [Year], month(enddate)      as [month],
                  count(*) as [months_rows], (count(*) * .70)      as [FraxExpiring]
            from
                  vw_FraxExpiring
            where
                  isActive = 1       and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947) and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
            group by
                  Year(enddate),month(enddate)
      ) fe
            on fe.[Year] = Year(psu.[Date])            and fe.[month] = Month(psu.[Date])
where
      FiscalYear = 'FY12'


--this returns the only two months -- 5 and 6 but i need to see all months for the fiscal year. such as:



select id, right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + Cast(Year(psu.[Date]) as varchar) as [Date],
      cast(psu.newshares as DECIMAL(9,2))      as [New Shares],
      fraxexpiring = (NULL),      
      fiscalyear
from
      ProjectedSalesUnits psu
where
      FiscalYear = 'FY12'

--need to show all months in the fiscal year with the fraxexpiring for the given month. if there is nothing found (like NULL) then blank otherwise show the value

id      Date      NewShares      fraxexpiring      fiscalyear
1      07/2011      0.00            FY12
2      08/2011      5.00            FY12
3      09/2011      4.00            FY12
4      10/2011      4.00            FY12
5      11/2011      3.00            FY12
6      12/2011      6.00            FY12
7      01/2012      3.00            FY12
8      02/2012      4.00            FY12
9      03/2012      4.00            FY12
10      04/2012      3.00            FY12
11      05/2012      5.00      4.20      FY12
12      06/2012      6.00      25.20      FY12
I am afraid you don't understand. There is nothing we can do until this query:

         select
                Year(enddate)            as [Year],
                  month(enddate)            as [month],
                  count(*)                  as [months_rows],
                  (count(*) * .70)      as [FraxExpiring] 
            from 
                  vw_FraxExpiring 
            where 
                  isActive = 1
                  and ContractName <> 'TEMPLATE' 
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program' 
                  and StatusTypeID = 1

                  -- let's filter by year
                  and Year(enddate)<=2012

            group by 
                  Year(enddate),
                  month(enddate)

Open in new window


returns all the data before month 2012 5. That is the problem. Please check snd find out why the view doesn't return that data.

The view code that you posted maybe returns everything bit the problem is with this:

         select *
            from 
                  vw_FraxExpiring 
            where 
                  isActive = 1
                  and ContractName <> 'TEMPLATE' 
                  and contractID not in (656,1947)
                  and ContractType = 'Fractional Program' 
                  and StatusTypeID = 1
order by enddate

Open in new window


This is the view we use in the query and it doesn't return enddates before 2012 5.
It might be the filter you used in the WHERE clause, I don't know, but the fact is that it doesn't return the data you are looking for.

Author

Commented:
if i run:

SELECT *
  FROM [dbo].[vw_FraxExpiring]
  where
                  isActive = 1       and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947) and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
                  and enddate >= '07/01/2011' and enddate <= '06/30/2012'

i get 42 rows of data; not that 7/2011 to 7/2012 is the fiscal year. so how do i get the results like:

id      Date      NewShares      fraxexpiring      fiscalyear
1      07/2011      0.00            FY12
2      08/2011      5.00            FY12
3      09/2011      4.00            FY12
4      10/2011      4.00            FY12
5      11/2011      3.00            FY12
6      12/2011      6.00            FY12
7      01/2012      3.00            FY12
8      02/2012      4.00            FY12
9      03/2012      4.00            FY12
10      04/2012      3.00            FY12
11      05/2012      5.00      4.20      FY12
12      06/2012      6.00      25.20      FY12
I can't help until I see some results. Please show those 42 rows. There is something wrong with data but I need to see.

Author

Commented:
SELECT contractID, IsTemplate, StatusTypeID,StatusDate,StartDate,EndDate,DateCreated,OrigEndDate,ContractType,IsActive,Date
  FROM [dbo].[vw_FraxExpiring]
  where
                  isActive = 1       and ContractName <> 'TEMPLATE'
                  and contractID not in (656,1947) and ContractType = 'Fractional Program'
                  and StatusTypeID = 1
                  and enddate >= '07/01/2011' and enddate <= '06/30/2012'


contractID      IsTemplate      StatusTypeID      StatusDate      StartDate      EndDate      DateCreated      OrigEndDate      ContractType      IsActive      Date
1865      0      1      2010-04-21 11:29:34.000      2008-06-27      2012-06-26      2010-01-22 00:00:00.000      2012-06-22      Fractional Program      1      06/2012
1121      0      1      2010-05-19 16:06:30.000      2007-06-28      2012-06-28      2007-06-28 00:00:00.000      2012-06-28      Fractional Program      1      06/2012
1583      0      1      2012-01-11 15:06:15.643      2007-06-15      2012-06-15      2009-06-02 00:00:00.000      2012-06-02      Fractional Program      1      06/2012
4700      0      1      2011-06-01 16:22:23.467      2011-06-30      2012-06-30      2011-06-01 16:22:52.183      2012-06-30      Fractional Program      1      06/2012
1608      0      1      2010-04-20 16:49:05.000      2009-06-30      2012-06-30      2009-06-30 00:00:00.000      2012-06-30      Fractional Program      1      06/2012
1093      0      1      2010-02-04 10:39:24.000      2007-06-18      2012-06-18      2007-06-18 00:00:00.000      2012-06-18      Fractional Program      1      06/2012
4619      0      1      2011-05-11 13:30:32.210      2011-06-10      2012-06-10      2011-05-11 13:31:02.897      2012-06-10      Fractional Program      1      06/2012
4685      0      1      2011-05-25 16:45:27.567      2011-06-27      2012-06-27      2011-05-25 16:46:08.740      2012-06-27      Fractional Program      1      06/2012
1109      0      1      2009-12-15 12:22:31.000      2007-06-26      2012-06-26      2007-06-26 00:00:00.000      2012-06-26      Fractional Program      1      06/2012
4696      0      1      2011-05-31 18:39:33.310      2011-06-01      2012-06-01      2011-05-31 18:40:46.293      2012-05-27      Fractional Program      1      06/2012
1112      0      1      2010-03-30 15:16:37.000      2007-06-28      2012-06-28      2007-06-28 00:00:00.000      2012-06-28      Fractional Program      1      06/2012
4497      0      1      2011-04-01 14:16:25.570      2011-06-25      2012-06-25      2011-04-01 14:17:06.133      2012-06-25      Fractional Program      1      06/2012
1124      0      1      2010-03-22 16:01:36.000      2007-06-29      2012-06-29      2007-06-29 00:00:00.000      2012-06-29      Fractional Program      1      06/2012
1117      0      1      2009-05-01 10:50:01.000      2007-06-27      2012-06-27      2007-06-27 00:00:00.000      2012-06-27      Fractional Program      1      06/2012
4473      0      1      2011-03-22 13:58:45.000      2011-06-01      2012-06-01      2011-03-22 13:59:19.687      2012-06-01      Fractional Program      1      06/2012
1088      0      1      2009-04-30 15:21:44.000      2007-06-08      2012-06-08      2007-06-08 00:00:00.000      2012-06-08      Fractional Program      1      06/2012
3674      0      1      2010-06-18 19:32:18.727      2010-06-20      2012-06-19      2010-06-18 19:34:11.190      2012-06-19      Fractional Program      1      06/2012
1106      0      1      2009-09-23 11:51:35.000      2007-06-25      2012-06-25      2007-06-25 00:00:00.000      2012-06-25      Fractional Program      1      06/2012
1254      0      1      2010-04-06 13:49:55.000      2007-06-29      2012-06-28      2007-12-20 00:00:00.000      2012-06-20      Fractional Program      1      06/2012
1137      0      1      2010-04-05 14:40:33.000      2007-07-01      2012-06-30      2007-07-06 00:00:00.000      2012-07-06      Fractional Program      1      06/2012
1104      0      1      2009-05-01 11:07:36.000      2007-06-19      2012-06-19      2007-06-19 00:00:00.000      2012-06-19      Fractional Program      1      06/2012
1119      0      1      2010-01-22 15:28:27.000      2007-06-27      2012-06-27      2007-06-27 00:00:00.000      2012-06-27      Fractional Program      1      06/2012
1084      0      1      2010-01-22 14:01:42.000      2007-05-31      2012-05-31      2007-05-31 00:00:00.000      2012-05-31      Fractional Program      1      05/2012
1096      0      1      2010-01-22 12:01:15.000      2007-06-07      2012-06-07      2007-06-07 00:00:00.000      2012-06-07      Fractional Program      1      06/2012
1126      0      1      2010-03-30 16:02:47.000      2007-06-29      2012-06-29      2007-06-29 00:00:00.000      2012-06-29      Fractional Program      1      06/2012
1114      0      1      2010-01-22 14:03:35.000      2007-05-31      2012-05-31      2007-05-31 00:00:00.000      2012-05-31      Fractional Program      1      05/2012
1864      0      1      2010-01-27 15:58:28.000      2007-06-18      2012-06-17      2010-01-15 00:00:00.000      2012-06-15      Fractional Program      1      06/2012
4645      0      1      2011-05-16 20:05:42.340      2011-06-16      2012-06-16      2011-05-16 20:06:12.460      2012-06-16      Fractional Program      1      06/2012
4515      0      1      2011-04-05 19:29:08.590      2011-06-10      2012-06-10      2011-04-05 19:30:59.163      2012-06-10      Fractional Program      1      06/2012
1585      0      1      2010-03-19 11:53:40.000      2009-05-31      2012-05-31      2009-05-31 00:00:00.000      2012-05-31      Fractional Program      1      05/2012
1298      0      1      2010-01-22 15:22:05.000      2008-05-31      2012-05-31      2008-03-01 00:00:00.000      2012-06-01      Fractional Program      1      05/2012
1361      0      1      2010-03-30 15:22:42.000      2004-05-31      2012-05-31      2008-06-11 00:00:00.000      2012-05-11      Fractional Program      1      05/2012
1129      0      1      2009-04-30 15:37:51.000      2007-06-30      2012-06-30      2007-06-30 00:00:00.000      2012-06-30      Fractional Program      1      06/2012
1098      0      1      2009-05-01 07:57:03.000      2007-06-15      2012-06-15      2007-06-15 00:00:00.000      2012-06-15      Fractional Program      1      06/2012
1095      0      1      2009-05-01 08:59:52.000      2007-06-12      2012-06-12      2007-06-12 00:00:00.000      2012-06-12      Fractional Program      1      06/2012
1102      0      1      2009-05-01 11:01:25.000      2007-06-15      2012-06-15      2007-06-15 00:00:00.000      2012-06-15      Fractional Program      1      06/2012
1904      0      1      2011-06-20 13:07:51.407      2007-06-19      2012-06-18      2010-03-01 00:00:00.000      2012-06-01      Fractional Program      1      06/2012
1128      0      1      2010-03-30 15:14:23.000      2007-06-30      2012-06-30      2007-06-30 00:00:00.000      2012-06-30      Fractional Program      1      06/2012
1948      0      1      2010-04-02 17:01:56.000      2010-05-31      2012-05-31      2010-05-31 00:00:00.000      2012-05-31      Fractional Program      1      05/2012
1131      0      1      2010-01-22 14:51:07.000      2007-06-30      2012-06-30      2007-06-30 00:00:00.000      2012-06-30      Fractional Program      1      06/2012
1135      0      1      2010-04-26 11:07:26.000      2007-06-25      2012-06-25      2007-06-25 00:00:00.000      2012-06-25      Fractional Program      1      06/2012
4471      0      1      2011-03-22 13:40:26.340      2011-06-28      2012-06-28      2011-03-22 13:41:19.273      2012-06-28      Fractional Program      1      06/2012
No offence but as you can see the enddate column only has dates for 2012 months 5 and 6. All the 42 rows are from months of May and June 2012.

I think we are wasting our time here. You are looking at column enddate and no other date columns.

Author

Commented:
no worries - true but i have to show the all the data even if there is no data in that given column; there is other data that must be visible. See below even though row id 1 has not data in the fraxexpiring i still need to show it the other data elements and just render nothing to the screen if there is no data

id      Date      NewShares      fraxexpiring      fiscalyear
1      07/2011      0.00            FY12
2      08/2011      5.00            FY12
3      09/2011      4.00            FY12
4      10/2011      4.00            FY12
5      11/2011      3.00            FY12
6      12/2011      6.00            FY12
7      01/2012      3.00            FY12
8      02/2012      4.00            FY12
9      03/2012      4.00            FY12
10      04/2012      3.00            FY12
11      05/2012      5.00      4.20      FY12
12      06/2012      6.00      25.20      FY12
Then use a left join and an ISNULL on [FraxExpiring] column to replace it with 0:

select 
	right('0'+Cast(Month(psu.[Date]) as varchar),2) + '/' + 
		Cast(Year(psu.[Date]) as varchar)						as [Date],
	cast(psu.newshares as DECIMAL(9,2))							as [New Shares],
	ISNULL(fe.[FraxExpiring],0)                as [FraxExpiring]
from 
	ProjectedSalesUnits psu
	left join 
	(
		select 
			Year(enddate)		as [Year],
			month(enddate)		as [month],
			(count(*) * .70)	as [FraxExpiring] 
		from 
			vw_FraxExpiring 
		where 
			isActive = 1
			and ContractName <> 'TEMPLATE' 
			and contractID not in (656,1947)
			and ContractType = 'Fractional Program' 
			and StatusTypeID = 1  
		group by 
			Year(enddate),
			month(enddate)
	) fe
		on fe.[Year]	=Year(psu.[Date])
		and fe.[month]	=Month(psu.[Date])
where 
	FiscalYear = 'FY12'

Open in new window

Author

Commented:
outstanding help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial