Need help with query for previous year spend vs. current year spen

Need help with query for previous year spend vs. current year spen

Post by SmVmZiBTdH » Sun, 09 Sep 2007 01:00:02


Hi,

I need to come up with a query for some financial statement reports. The
reports require current year $$ vs. previous year $$ by month, quarter and
year. I'm building a stored procedure to produce the necessary dataset for
my reports. So, in the sp, I build two temp tables one for previous year
and one for current year. How do I bash these tables together in order to
get a previous year $$ column and a current year $$ column by account number
and branch location? I think I have part of the answer join the two temp
tables on account number and branch location and select all columns giving an
alias to one of the $$ columns for either previous or current year. The
sticky part is, there may be account numbers in current year that don exist
in previous year and vice versa. I like to put $0.00 in this case where
there was no spend. I tried a full outer join, but I get way too many
records and a lot of duplicate values. Has anyone ever faced this problem
and do you have a solution? I like to avoid using cursors if possible
because they seem to degrade performance.
--
Thanks,

Jeff
 
 
 

Need help with query for previous year spend vs. current year spen

Post by SmltYm » Sun, 09 Sep 2007 05:14:03

do a "select distinct" to remove duplicates





> my reports. So, in the sp, I build two temp tables one for previous year >> and one for current year. How do I bash these tables together in order to >> get a previous year $$ column and a current year $$ column by account number >> and branch location? I think I have part of the answer join the two temp > > tables on account number and branch location and select all columns giving an > > alias to one of the $$ columns for either previous or current year. The > > sticky part is, there may be account numbers in current year that don exist > > in previous year and vice versa. I like to put $0.00 in this case where > > there was no spend. I tried a full outer join, but I get way too many > > records and a lot of duplicate values. Has anyone ever faced this problem > > and do you have a solution? I like to avoid using cursors if possible > > because they seem to degrade performance.> > -- > > Thanks,> > > > Jeff