SQL Select Help

Hello EE,

With the help of this code:


DECLARE @tmp1 table ( [Num] varchar(50)
                              , [Desc] varchar(50) )
INSERT INTO @tmp1 VALUES('0001', 'Num Test 1')
INSERT INTO @tmp1 VALUES('0002', 'Num Test 2')

--SELECT * FROM @tmp1


DECLARE @tmp2 table ( [Num] varchar(50)
                              , [dteYear] varchar(4)
                              , [Per1] money
                              , [Per2] money
                              , [Per3] money )
INSERT INTO @tmp2 VALUES('0001', '2009', 50, 100, 150)
INSERT INTO @tmp2 VALUES('0001', '2010', 500, 455, 144)
INSERT INTO @tmp2 VALUES('0001', '2008', 111, 19, 90)
INSERT INTO @tmp2 VALUES('0002', '2009', 233, 24, 70)
INSERT INTO @tmp2 VALUES('0002', '2010', 12, 224, 703)

select
      case when t.orderby=1 then N.Num end Num,
       t.dteYear,
      SQ.Per1, SQ.Per2, SQ.Per3
from
(      select distinct Num from @tmp2) N cross join
(      select 1 as orderby, 2010 as dteYear union all
      select 2, 2009 union all
      select 3, 2011) t
left join @tmp2 SQ on SQ.Num=N.Num and SQ.dteYear=t.dteYear
order by N.Num, t.orderby


how could I add rows of total at begining that would be the sum for each column year(exept for 1st col because its a String and also 2nd col because its a year) only col that has money

in other words... from that code I paste for you that works, i would like this result:

TOTAL       2010        512.00     679.00      847.00
NULL        2009        283.00     324.00      220.00
NULL        2011        0.00      0.00           0.00
0001        2010        500.00        455.00      144.00
NULL        2009        50.00        100.00        150.00
NULL        2011        NULL        NULL        NULL
0002        2010        12.00        224.00        703.00
NULL        2009        233.00        24.00        70.00
NULL        2011        NULL        NULL        NULL


can you help?

thanks
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Ok, this then
;with
	Years(orderby, dteYear) as (
		select 1 as orderby, 2010 as dteYear union all
		select 2, 2009 union all
		select 3, 2011),
	Nums(Num) as (
		select distinct Num from @tmp2)
select Num, dteYear, Per1, Per2, Per3
from
(
select
	case when t.orderby=1 then N.Num end Num,
	case when t.orderby=1 then t.dteYear end dteYear,
	SQ.Per1, SQ.Per2, SQ.Per3,
	N.Num sorter, t.orderby
from Nums N
cross join Years t
left join @tmp2 SQ on SQ.Num=N.Num and SQ.dteYear=t.dteYear
union all
select
	case when t.orderby=1 then 'TOTAL' end Num,
	case when t.orderby=1 then t.dteYear end dteYear,
	SUM(per1), SUM(Per2), SUM(per3), -1, t.orderby
from Nums N
cross join Years t
left join @tmp2 SQ on SQ.Num=N.Num and SQ.dteYear = t.dteYear
group by t.dteYear, t.orderby
) SQ2
order by sorter, orderby

Open in new window

0
 
cyberkiwiCommented:
Try this for the final query:
;with Years(orderby, dteYear) as (
	select 1 as orderby, 2010 as dteYear union all
    select 2, 2009 union all
	select 3, 2011
	)
select Num, dteYear, Per1, Per2, Per3
from
(
select
      case when t.orderby=1 then N.Num end Num,
      case when t.orderby=1 then t.dteYear end dteYear,
      SQ.Per1, SQ.Per2, SQ.Per3,
      N.Num sorter, t.orderby
from (select distinct Num from @tmp2) N
cross join Years t
left join @tmp2 SQ on SQ.Num=N.Num and SQ.dteYear=t.dteYear
union all
select 'TOTAL', null, SUM(per1), SUM(Per2), SUM(per3), -1, -1
from @tmp2 t2
join Years t on t.dteYear = t2.dteYear
) SQ2
order by sorter, orderby

Open in new window


Regards,
Richard
0
 
PhilippeRenaudAuthor Commented:
you must had start to look at it jus as when I edited my question .. : /

but i dont think its huge mods for you.. can you refresh the page? lol
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
PhilippeRenaudAuthor Commented:
Thanks!
0
 
PhilippeRenaudAuthor Commented:
I have some modification to make, I will create a new question after that it would be 100% done =)

be right back
0
 
PhilippeRenaudAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.