cumulative query part 2

The following query works great for cumulative data for the 2 columns shown.  The level of complexity is as follows.  The data in the table also has a opData_Type_ID.  If the ID = 1 then the data is actual, if the ID = 2 then the data is projected.  I need to add 2 columns to the query for the projected data in the same row as the corresponding actual data.  If there is no data for that month it should return 0
SELECT 
a.opData_Year, 
a.opData_month ,
a.opData_value+ ISNULL( (SELECT SUM(ISNULL(b.opData_Value,0)) from opData_Monthly b 
where (b.opData_Year = a.opData_Year and b.opData_month < a.opData_month) and a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate and b.Opdata_Date >=@StartDate and b.opData_Date <=@EndDate and opData_Type_ID = 1
or (b.opData_Year < a.opData_year) AND a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate AND b.Opdata_Date >=@StartDate and b.opData_Date <=@EndDate and opData_Type_ID = 1),0 ) as CActualValue,
 
a.opData_cost+ ISNULL( (SELECT SUM(b.opData_cost) from opData_Monthly b 
 
where (b.opData_Year = a.opData_Year and b.opData_month < a.opData_month) and a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate and b.Opdata_Date >=@StartDate and b.opData_Date <=@EndDate and opData_Type_ID = 1
or (b.opData_Year < a.opData_year) AND a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate AND b.Opdata_Date >=@StartDate and b.opData_Date <=@EndDate and opData_Type_ID = 1),0 ) as CActualCost
 
FROM opData_Monthly a 
WHERE a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate
Order by opData_Date

Open in new window

softdimensionsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ralmadaConnect With a Mentor Commented:
What if you try something like this?
SELECT 
	a.opData_Year, 
	a.opData_month ,
	a.opData_value+ ISNULL( (SELECT SUM(ISNULL(b.opData_Value,0)) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1),0 ) as CActualValue,
 
	a.opData_cost+ ISNULL( (SELECT SUM(b.opData_cost) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1),0 ) as CActualCost,
 
	a.opData_cost+ ISNULL( (SELECT SUM(ISNULL(b.opData_Value,0)) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2),0 ) as ProjectedValue,
 
	a.opData_cost+ ISNULL( (SELECT SUM(b.opData_cost) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2),0 ) as ProjectedCost
 
FROM opData_Monthly a 
WHERE a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate
Order by opData_Date

Open in new window

0
 
ralmadaCommented:
You mean like this?
SELECT 
	a.opData_Year, 
	a.opData_month ,
	a.opData_value+ ISNULL( (SELECT SUM(ISNULL(b.opData_Value,0)) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1),0 ) as CActualValue,
 
	a.opData_cost+ ISNULL( (SELECT SUM(b.opData_cost) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 1),0 ) as CActualCost,
 
	ISNULL( (SELECT SUM(ISNULL(b.opData_Value,0)) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2),0 ) as ProjectedValue,
 
	ISNULL( (SELECT SUM(b.opData_cost) from opData_Monthly b 
				where (b.opData_Year = a.opData_Year and 
					b.opData_month < a.opData_month) and 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate and 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2
					or (b.opData_Year < a.opData_year) AND 
					a.Opdata_Date >=@StartDate and 
					a.opData_Date <=@EndDate AND 
					b.Opdata_Date >=@StartDate and 
					b.opData_Date <=@EndDate and opData_Type_ID = 2),0 ) as ProjectedCost
 
FROM opData_Monthly a 
WHERE a.Opdata_Date >=@StartDate and a.opData_Date <=@EndDate
Order by opData_Date

Open in new window

0
 
softdimensionsAuthor Commented:
The sql statement you returned gives 2 rows per month.  The data in the columns are correct but I only want to see 1 row per month year value
0
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.

 
ralmadaCommented:
Are the two rows duplicate? try adding distinct:
select distinct......
 
0
 
softdimensionsAuthor Commented:
using DISTINCT renders the following  result set.

2008      7      2008-07-01 00:00:00.000      7.02      0.1      0      0
2008      8      2008-08-01 00:00:00.000      15.04      0.2      7.02      0.1
2008      9      2008-09-01 00:00:00.000      24.06      0.3      15.04      0.2
2008      10      2008-10-01 00:00:00.000      34.08      0.4      24.06      0.3
2008      11      2008-11-01 00:00:00.000      45.1      0.5      34.08      0.4
2008      12      2008-12-01 00:00:00.000      57.12      0.6      45.1      0.5
2009      1      2009-01-01 00:00:00.000      58.14      0.7      57.12      0.6
2009      2      2009-02-01 00:00:00.000      60.16      0.8      58.14      0.7
2009      3      2009-03-01 00:00:00.000      63.18      0.9      60.16      0.8
2009      4      2009-04-01 00:00:00.000      67.2      1      63.18      0.9
2009      5      2009-05-01 00:00:00.000      72.22      1.1      67.2      1
2009      6      2009-06-01 00:00:00.000      78.24      1.2      72.22      1.1
2009      7      2009-07-01 00:00:00.000      85.26      1.3      78.24      1.2
2009      8      2009-08-01 00:00:00.000      93.28      1.4      85.26      1.3
2009      9      2009-09-01 00:00:00.000      102.3      1.5      93.28      1.4
2009      10      2009-10-01 00:00:00.000      112.32      1.6      102.3      1.5
2009      11      2009-11-01 00:00:00.000      123.34      1.7      112.32      1.6
2009      12      2009-12-01 00:00:00.000      135.36      1.8      123.34      1.7
2010      1      2010-01-01 00:00:00.000      136.38      1.9      135.36      1.8
2010      2      2010-02-01 00:00:00.000      138.4      2      136.38      1.9
2010      3      2010-03-01 00:00:00.000      141.42      2.1      138.4      2
2010      4      2010-04-01 00:00:00.000      145.44      2.2      141.42      2.1
2010      5      2010-05-01 00:00:00.000      150.46      2.3      145.44      2.2
2010      6      2010-06-01 00:00:00.000      156.48      2.4      150.46      2.3
2010      7      2010-07-01 00:00:00.000      163.5      2.5      156.48      2.4

With this temp set of data the projected and actual are the same but look at the difference between row 1 and 2
0
 
ralmadaCommented:
I'm not sure I'm following you.  Can you please advise if the query worked? If not please provide some sample data and expected result.
0
 
softdimensionsAuthor Commented:
2008      7      2008-07-01 00:00:00.000      7.02      0.1      0      0
2008      8      2008-08-01 00:00:00.000      15.04      0.2      7.02      0.1
2008      9      2008-09-01 00:00:00.000      24.06      0.3      15.04      0.2

The above is the result with the query

2008      7      2008-07-01 00:00:00.000      7.02      0.1      7.02      0.1
2008      8      2008-08-01 00:00:00.000      15.04      0.2     14.04      0.2
2008      9      2008-09-01 00:00:00.000      24.06      0.3      24.06      0.3

is the expected result.  In this case the data is the same for projected and actual since it is temp data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.