Solved

SQL Select Help

Posted on 2012-04-11
6
326 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 data tools breakdown.. 1 27
shrink table after huge delete 2 28
Can I skip a node in XML? 9 35
T-SQL: Need Group By to use "fuzzy logic"?? 3 23
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 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