Solved

SQL Select Help

Posted on 2012-04-11
6
327 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:PhilippeRenaud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 37834625
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
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 37834637
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 37834693
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 37837422
Thanks!
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 37838094
I have some modification to make, I will create a new question after that it would be 100% done =)

be right back
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question