Solved

SQL Select Help

Posted on 2012-04-11
6
325 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 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