softdimensions
asked on
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
ASKER
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
Are the two rows duplicate? try adding distinct:
select distinct......
select distinct......
ASKER
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
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
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window