Solved

SQL Select Help

Posted on 2012-04-11
6
323 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
  • 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 37838290
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now