YTD report help please

Question

select a.cmpcode, L1Order, L1HDrTXT,
 L1FTRTxT, L2Order,L2HDRTXT,
  L2ftrTxT,LEAFORDER, LEAFHDRTXT, 
  LEAFFTRTXT, Yr , PERIOD,
   BALCODE,REPBASIS, FULL_VALUE,
 b.code, L3ORDER, L3HDRTXT, 
 sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'ACTUALS' and a."repbasis" = 0) then a."full_value" else 0 end )AS  MTD_ACTUAL,
 sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'BUDGET' and a."repbasis" = 7) then a."full_value" else 0 end ) AS MTD_PLAN, 
sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'BUDGET' and a."repbasis" = 7) then a."full_value" else 0 end ) - 
 sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'ACTUALS' and a."repbasis" = 0) then a."full_value" else 0 end ) AS MTD_VAR,
 sum(case  when a."balcode" = 'ACTUALS' and a."repbasis" = 0 then a."full_value" else 0 end ) YTD_ACTUAL,
 sum(case  when a."balcode" = 'BUDGET' and a."repbasis" = 7 then a."full_value" else 0 end ) YTD_PLAN,
 sum(case  when (a."balcode" = 'BUDGET' and a."repbasis" = 7) then a."full_value" else 0 end ) - 
 sum(case  when (a."balcode" = 'ACTUALS' and a."repbasis" = 0) then a."full_value" else 0 end ) YTD_VAR,
 HIM.L2FTRHIDE,HIM.l3ftrhide,e.el3_code,
 dateadd(dd,-(day(dateadd(mm,1,ltrim(str(@Reportyear))+'-'+ltrim(str(@BeginPeriod))+'-01'))),
 dateadd(mm,1,ltrim(str(@Reportyear))+'-'+ltrim(str(@BeginPeriod))+'-01')) as lastofmth,
 dateadd(dd,-(day(dateadd(mm,1,ltrim(str(@Reportyear))+'-'+ltrim(str(@EndPeriod))+'-01'))),
 dateadd(mm,1,ltrim(str(@Reportyear))+'-'+ltrim(str(@EndPeriod))+'-01')) as lastofmthPeriod
  
 
   FROM oas_himlist him
inner join oas_agmlist agm on him.grpcode = agm.code 
inner join oas_balance a on
	(agm.cmpincfrom = a.cmpcode or agm.cmpincfrom = '*') and
	(agm.el1incfrom <= a.el1 and agm.el1incto >= a.el1) and 
	(agm.el2incfrom <= a.el2 and agm.el2incto >= a.el2 or agm.el2incfrom = '*') and 
	(agm.el3incfrom <= a.el3 and agm.el3incto >= a.el3 or agm.el3incfrom = '*') 
inner join oas_company b on a.cmpcode = b.code
inner join oas_el1_element c on a.el1 = c.el1_code and a.cmpcode = c.el1_cmpcode 
inner join oas_el2_element d on a.el2 = d.el2_code and a.cmpcode = d.el2_cmpcode
inner join oas_el3_element e on a.el3=e.el3_code and a.cmpcode=e.el3_cmpcode
where him."code" = 'GASUMMARY' and a."balcode" in ('ACTUALS', 'BUDGET') and a."repbasis" in (0, 7)
-- and yr=2011 and period=1 
--and b.code='CYX'
  AND yr=@ReportYear and period between @BeginPeriod and @EndPeriod
group by a.cmpcode, L1Order, L1HDrTXT,
 L1FTRTxT, L2Order,L2HDRTXT,
  L2ftrTxT,LEAFORDER, LEAFHDRTXT, 
  LEAFFTRTXT, Yr , PERIOD,
   BALCODE,REPBASIS, FULL_VALUE,
 b.code, L3ORDER, L3HDRTXT,l2ftrhide,l3ftrhide,el3_code

The above query gives me YTD for a report. My problem is when i into parameter values 

for 

begperiod=1,end period 1 i get correct values in my result set but when i enter in a range like begperiod=1 and endperiod 2 .. I dont get my values correct.. any suggestions??

Please help

---

FM

Answer 1

what is wrong about the results when you set a range? Values are included that are outside the specified range? values missing?

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 2

Hi Tim,

First when i give the Range, the MTD values goes to Zero... and my totals get doubled. If the total is 100 in my SUM cells it shows 200.

---

FM

Answer 3

sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'ACTUALS' and a."repbasis" = 0) then a."full_value" else 0 end )AS  MTD_ACTUAL,
 sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'BUDGET' and a."repbasis" = 7) then a."full_value" else 0 end ) AS MTD_PLAN, 
sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'BUDGET' and a."repbasis" = 7) then a."full_value" else 0 end ) - 
 sum(case  when (a."period" = @BeginPeriod and a.period=@EndPeriod and a."balcode" = 'ACTUALS' and a."repbasis" = 0

The above will go to zero when you specify a range because a."period" won't equal both the @BeginPeriod and @EndPeriod. Need to do >= @BeginPeriod and <=@EndPeriod.

I don't see totals in the SQL unless those are the "full_value" lines (YTD_*). If that is it, since your parameters aren't directly consumed by these calculations, I would suspect your table joins may be causing duplicate rows. Without knowing the data, it is difficult to say. You may need to specify more restrictive criteria in the ON statement to correct that. Make the above fix and see if the double totals still exist.

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.