Subquey or #temp
-
Hello all
I am beginning with programming in SQL and I have to decide between two technics. The #tmp or a subquery.
I have a table that contains a date, a department number, the total of sales.
I need to get the following result
DEP_NUMBER, TOTAL of the SALE for period1 (ex. 20050101 to 20050107), TOTAL of the SALE for period2 (ex. 20050108 to 20050115), the difference in % between the two totals
Up to know I can do :
select
dep_number,
( select sum (dep_key_turnover) as Turm from departmentsales b
where b.period >= 20050101 and b.period <= 20050107 AND
a
.dep_number = b.dep_number
)
AS TurnW1 ,
(
select sum (dep_key_turnover) as Turm from departmentsales b
where b.period >= 20050108 and b.period <= 20050115 AND
a
.dep_number = b.dep_number
)
AS Turnw2,
from
departmentsales a
group
by dep_number
order
by dep_number
This gives me the correct values for both columns but I do not know how to get the percentage
The other option I found is writing 2 #temp tables
select dep_number, sum (dep_key_turnover) as Turm, 0 AS Turn2
INTO #tmp
FROM departmentsales
where period >= 20050101 and period <= 20050107
group by dep_number
and then performing a join on the tables
Thanks in advance for the help
-
Hi Jean-Pierre,
I'd probably do something like this (so a bit of a combination of the two techniques
)...Good luck!
--This SQL script is safe to run
declare @departmentsales table (period int, dep_number int, dep_key_turnover money)
insert into @departmentsales
select 20050101, 1, 311.11
union select 20050101, 2, 344.44
union select 20050108, 1, 333.33
union select 20050108, 2, 322.22
union select 20050101, 3, 355.55
union select 20050108, 4, 366.66
select isnull(p1.dep_number, p2.dep_number) as dep_number, TurnW1, TurnW2,
((TurnW2 / TurnW1) - 1) * 100 as percentage_increase
from
(select dep_number, sum(dep_key_turnover) as TurnW1 from @departmentsales where period between 20050101 AND 20050107 group by dep_number) AS p1
full outer join
(select dep_number, sum(dep_key_turnover) as TurnW2 from @departmentsales where period between 20050108 AND 20050115 group by dep_number) AS p2
on p1.dep_number = p2.dep_number
order by p1.dep_number
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan
Thanks for the tip. It's just working fine if the total of the sale is not 0, in this case I get a "Divide by zero" error
I tried : ((TurnW2 / Isnull (TurnW1,1) ) - 1) * 100 as percentage_increase
but it doesn't help...
Any idea
-
Just use a case statement
Something like...
select isnull(p1.dep_number, p2.dep_number) as dep_number, TurnW1, TurnW2,
case when TurnW1 = 0 then 'N/A' else cast(((TurnW2 / TurnW1) - 1) * 100 as varchar(10)) end as percentage_increase
from
(select dep_number, sum(dep_key_turnover) as TurnW1 from @departmentsales where period between 20050101 AND 20050107 group by dep_number) AS p1
full outer join
(select dep_number, sum(dep_key_turnover) as TurnW2 from @departmentsales where period between 20050108 AND 20050115 group by dep_number) AS p2
on p1.dep_number = p2.dep_number
order by p1.dep_number
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Too easy sometimes
Thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply