RunningValue Function for Year over Year Comparison

I have a query which returns sales dollars grouped on a yearly basis,
along with some other groupings. How would I display the year
information in a matrix and still be able to compare the first year to
the second. The data set returned has a column for dollars and one for
years, it does not seperate dollar values into different columns for
each year. Should I be using the Running Value Function or is there a
different way I should be trying to accomplish this task. This is
fairly common task for our reports, and we need the ablity to return
muliplte years in our data set with an unknown number of years return.
Here is the query.
select
pd.division_desc
,pr.region_desc
,po.opco_desc
,sh.opco_ident
,sh.opco_num
,ocmtt.customer_major_type_desc
,ot.gl_year
,ot.gl_period
,ot.gl_week
,sum(total_sales) total_sales
,sum(total_actual_cost) total_actual_cost
from
pfg_dw.dbo.sales_header sh
inner join
pfg_dw.dbo.opco_time ot
on
sh.opco_num=ot.opco_num
and
sh.day_end_num=ot.day_end_num
inner join
pfg_dw.dbo.opco_customer oc
on
sh.customer_ident=oc.customer_ident
inner join
pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
on
oc.major_type_ident= ocmtt.customer_major_type_ident
inner join
pfg_dw.dbo.pfg_opco po
on
sh.opco_ident=po.opco_ident
inner join
pfg_dw.dbo.pfg_region pr
on
po.region_ident=pr.region_ident
inner join
pfg_dw.dbo.pfg_division pd
on
pd.division_ident=pr.division_ident
where
(
ot.day_end_date between
dateadd(dd,-datepart(dw,getdate())-6,getdate())
and
dateadd(dd,-datepart(dw,getdate())+1,getdate())
)
or
(
ot.day_end_date between
dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
and
dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
)
group by
pd.division_desc
,pr.region_desc
,po.opco_desc
,sh.opco_ident
,sh.opco_num
,o.cmtt.customer_major_type_desc
,ot.gl_year
,ot.gl_period
,ot.gl_weekHere is a better example of what I am trying to do
Company Name 1st Year 2ndYear Difference
xyz 100 200 100
abc 150 200 50
How do I compute the difference column in a matrix if my result set
looks like the following:
Company Year $Total
xyz 1st Year 100
xyz 2nd Year 200
abc 1st Year 150
abc 2nd Year 200
sfibich wrote:
> I have a query which returns sales dollars grouped on a yearly basis,
> along with some other groupings. How would I display the year
> information in a matrix and still be able to compare the first year to
> the second. The data set returned has a column for dollars and one for
> years, it does not seperate dollar values into different columns for
> each year. Should I be using the Running Value Function or is there a
> different way I should be trying to accomplish this task. This is
> fairly common task for our reports, and we need the ablity to return
> muliplte years in our data set with an unknown number of years return.
> Here is the query.
> select
> pd.division_desc
> ,pr.region_desc
> ,po.opco_desc
> ,sh.opco_ident
> ,sh.opco_num
> ,ocmtt.customer_major_type_desc
> ,ot.gl_year
> ,ot.gl_period
> ,ot.gl_week
> ,sum(total_sales) total_sales
> ,sum(total_actual_cost) total_actual_cost
> from
> pfg_dw.dbo.sales_header sh
> inner join
> pfg_dw.dbo.opco_time ot
> on
> sh.opco_num=ot.opco_num
> and
> sh.day_end_num=ot.day_end_num
> inner join
> pfg_dw.dbo.opco_customer oc
> on
> sh.customer_ident=oc.customer_ident
> inner join
> pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
> on
> oc.major_type_ident= ocmtt.customer_major_type_ident
> inner join
> pfg_dw.dbo.pfg_opco po
> on
> sh.opco_ident=po.opco_ident
> inner join
> pfg_dw.dbo.pfg_region pr
> on
> po.region_ident=pr.region_ident
> inner join
> pfg_dw.dbo.pfg_division pd
> on
> pd.division_ident=pr.division_ident
> where
> (
> ot.day_end_date between
> dateadd(dd,-datepart(dw,getdate())-6,getdate())
> and
> dateadd(dd,-datepart(dw,getdate())+1,getdate())
> )
> or
> (
> ot.day_end_date between
> dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
> and
> dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
> )
> group by
> pd.division_desc
> ,pr.region_desc
> ,po.opco_desc
> ,sh.opco_ident
> ,sh.opco_num
> ,o.cmtt.customer_major_type_desc
> ,ot.gl_year
> ,ot.gl_period
> ,ot.gl_week|||Multiply second year with -1 and calculate the total at the end.
While displaying it again multiply it with -1.
Hope this works.
Regards,
Cem
"sfibich" <sfibich@.pfgc.com> wrote in message
news:ep5mtspmEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Here is a better example of what I am trying to do
> Company Name 1st Year 2ndYear Difference
> xyz 100 200 100
> abc 150 200 50
> How do I compute the difference column in a matrix if my result set
> looks like the following:
> Company Year $Total
> xyz 1st Year 100
> xyz 2nd Year 200
> abc 1st Year 150
> abc 2nd Year 200
>
> sfibich wrote:
> > I have a query which returns sales dollars grouped on a yearly basis,
> > along with some other groupings. How would I display the year
> > information in a matrix and still be able to compare the first year to
> > the second. The data set returned has a column for dollars and one for
> > years, it does not seperate dollar values into different columns for
> > each year. Should I be using the Running Value Function or is there a
> > different way I should be trying to accomplish this task. This is
> > fairly common task for our reports, and we need the ablity to return
> > muliplte years in our data set with an unknown number of years return.
> >
> > Here is the query.
> > select
> > pd.division_desc
> > ,pr.region_desc
> > ,po.opco_desc
> > ,sh.opco_ident
> > ,sh.opco_num
> > ,ocmtt.customer_major_type_desc
> > ,ot.gl_year
> > ,ot.gl_period
> > ,ot.gl_week
> > ,sum(total_sales) total_sales
> > ,sum(total_actual_cost) total_actual_cost
> > from
> > pfg_dw.dbo.sales_header sh
> > inner join
> > pfg_dw.dbo.opco_time ot
> > on
> > sh.opco_num=ot.opco_num
> > and
> > sh.day_end_num=ot.day_end_num
> > inner join
> > pfg_dw.dbo.opco_customer oc
> > on
> > sh.customer_ident=oc.customer_ident
> > inner join
> > pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
> > on
> > oc.major_type_ident= ocmtt.customer_major_type_ident
> > inner join
> > pfg_dw.dbo.pfg_opco po
> > on
> > sh.opco_ident=po.opco_ident
> > inner join
> > pfg_dw.dbo.pfg_region pr
> > on
> > po.region_ident=pr.region_ident
> > inner join
> > pfg_dw.dbo.pfg_division pd
> > on
> > pd.division_ident=pr.division_ident
> > where
> > (
> > ot.day_end_date between
> > dateadd(dd,-datepart(dw,getdate())-6,getdate())
> > and
> > dateadd(dd,-datepart(dw,getdate())+1,getdate())
> > )
> > or
> > (
> > ot.day_end_date between
> > dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
> > and
> > dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
> > )
> > group by
> > pd.division_desc
> > ,pr.region_desc
> > ,po.opco_desc
> > ,sh.opco_ident
> > ,sh.opco_num
> > ,o.cmtt.customer_major_type_desc
> > ,ot.gl_year
> > ,ot.gl_period
> > ,ot.gl_week