Solved

# Help with query  #2

Posted on 2012-04-12
551 Views
For CyberKiwi,

See attached,

Ok if you run that query, you will its almost the results of yours but with some errors.
Let me explain.  I have put -1 in Total (first 3 rows) because I am not sure how to get the total.

What I try to achieve, is when its Budget 2011 or Budget 2010 I need to go get the value in @table3

if its Actual 2011, I need to go inside @table4

I did case when on each P1 p2 p3 and when orderby is not 1 or 2, I did select on @table4

I dont know if its efficiant or not, maybe there is a better way..?

Last thing is for the first 3 rows (wich is Total) my total are not working properly..
for P1 P2 and P3

It seems to work for the first Row of Total... second row are all 0 but if you look at third row of total.. at P3  it writes 465  but it should be 180 (60+30+90)

This is what I need to fix if you can help me it would be appreciate..im done after.

let me know if its not clear ?

Thanks a lot kiwi.
SQLQuery4.sql
0
Question by:PhilippeRenaud
• 6
• 5

LVL 58

Expert Comment

ID: 37840127
Is there a reason why this line

[Total] = CASE WHEN t.orderby = 1 OR t.orderby = 3

is different from all other cases that are orderby in (1,2)?
0

LVL 58

Expert Comment

ID: 37840204
declare      @SelectedDate            varchar(4)
select      @SelectedDate      = 2011

;WITH
Years(orderby, dteYear) as (
select 1 as orderby, @SelectedDate as dteYear
union all
select 2, @SelectedDate - 1
union all
select 3, @SelectedDate),

GL(GL_Code, GL_Description) as (
SELECT  [GL_Code] = wf.GL_Code
,      [GL_Description] = g.GL_Description
FROM @table1 wf
INNER JOIN @table2 g on g.GL_Code = wf.GL_Code)

SELECT
[Formatted_GL] = CASE WHEN t.orderby = 1 THEN isnull(dbo.mf_getMaskedGL( GL_Code ), 'TOTAL') END,
[Description] = CASE WHEN t.orderby = 1 THEN GL_Description END,
WorkDte = t.dteYear,
[myDesc] = CASE WHEN t.orderby = 1 OR t.orderby = 2
THEN 'Budget ' + convert(varchar, t.dteYear)
ELSE 'Actual ' + convert(varchar, t.dteYear)
END,
[Total] = P1+P2+P3,
P1, P2, P3
FROM (
select
N.GL_Code,
N.GL_Description,
--[Total] = P1+P2+P3
[P1] = COALESCE(SUM(a.P1), SUM(CASE WHEN b.P=1 then b.GL_AMOUNT end), 0),
[P2] = COALESCE(SUM(a.P2), SUM(CASE WHEN b.P=2 then b.GL_AMOUNT end), 0),
[P3] = COALESCE(SUM(a.P3), SUM(CASE WHEN b.P=3 then b.GL_AMOUNT end), 0),
N.GL_Code sorter,
t.orderby
from GL N
cross join Years t
left join @table3 a on a.GL_Code = N.GL_Code and a.Working_Date = t.dteYear
and t.orderby in (1,2)
left join @table4 b on b.GL_Code = N.GL_Code and b.[Year] = t.dteYear
and t.orderby = 3
group by GROUPING sets (
(t.orderby, t.dteYear, N.GL_Code, N.GL_Description),
(t.orderby)
)
) SQ2
join Years t on t.orderby = SQ2.orderby
ORDER BY SQ2.sorter, SQ2.orderby
0

LVL 1

Author Comment

ID: 37842329

I forgot to remove it, instead just write GL_code for you because you dont have that function..!

For your question, It was an error, it should be the same for total also.

when I run I have an error on line :  Incorrect syntax near 'sets'.

do you have that also ?
0

LVL 58

Expert Comment

ID: 37842524
Ah, I didn't see 2005... I ran mine on 2008. That missing function isn't a problem, I just create one to get past it.

The technique will be similar using rollup instead of grouping sets - a bit messy though.
The alternative is to split the 2 grouping sets into UNION parts (as before) but using the now working query.

I'll check back in the morning, too late now.
0

LVL 1

Author Comment

ID: 37842532
But I have SQL 2008 R2 ..   it should work then ... mmm
0

LVL 58

Expert Comment

ID: 37842587
try

exec sp_dbcmptlevel 'dbname', 100

My mind's not the clearest and it's 1:41am here, but I think it needs to be at 100.
However, read up on compatibility level because IT WILL BREAK other parts of your application(s)

Better to just double up the query, change the GROUP BY between the two and UNION ALL between them
0

LVL 1

Author Comment

ID: 37842597
LOL
0

LVL 1

Author Comment

ID: 37842604
I'll wait for you when you awake then... im lost with all those union all..

thanks again ..

Btw, you're right... my compatibility level is at 90 (sql 2005)
I cannot really change it because once the query will work, i need to install at clients and some are still sql 2005 .. and others sql 2008 .. it will break like you said.
0

LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 37846051
``````declare @table1 table (GL_Code varchar(10))
insert @table1 select '400001'
insert @table1 select '400002'
insert @table1 select '400003'

declare @table2 table (GL_Code varchar(10), [GL_Description] varchar(100))
insert @table2 select '400001', 'SD01'
insert @table2 select '400002', 'SD02'
insert @table2 select '400003', 'SD03'

declare @table3 table (GL_Code varchar(10), Working_Date int, P1 money, P2 money, P3 money)
insert @table3 select '400001', 2011, 66, 4, 5
insert @table3 select '400002', 2011, 0, 9, 15
insert @table3 select '400003', 2011, 144, 24, 445

declare @table4 table (GL_Code varchar(10), GL_Amount money, [Year] int, P int)
insert @table4 select '400001', 40, 2011, 1
insert @table4 select '400001', 50, 2011, 2
insert @table4 select '400001', 60, 2011, 3
insert @table4 select '400002', 10, 2011, 1
insert @table4 select '400002', 20, 2011, 2
insert @table4 select '400002', 30, 2011, 3
insert @table4 select '400003', 70, 2011, 1
insert @table4 select '400003', 80, 2011, 2
insert @table4 select '400003', 90, 2011, 3

declare      @SelectedDate            varchar(4)
select      @SelectedDate      = 2011

;WITH
Years(orderby, dteYear) as (
select 1 as orderby, @SelectedDate as dteYear
union all
select 2, @SelectedDate - 1
union all
select 3, @SelectedDate),
GL(GL_Code, GL_Description) as (
SELECT  [GL_Code] = wf.GL_Code
,[GL_Description] = g.GL_Description
FROM @table1 wf
INNER JOIN @table2 g on g.GL_Code = wf.GL_Code),
Data as (
select
N.GL_Code,
N.GL_Description,
--[Total] = P1+P2+P3
[P1] = COALESCE(SUM(a.P1), SUM(CASE WHEN b.P=1 then b.GL_AMOUNT end), 0),
[P2] = COALESCE(SUM(a.P2), SUM(CASE WHEN b.P=2 then b.GL_AMOUNT end), 0),
[P3] = COALESCE(SUM(a.P3), SUM(CASE WHEN b.P=3 then b.GL_AMOUNT end), 0),
t.orderby
from GL N
cross join Years t
left join @table3 a on a.GL_Code = N.GL_Code and a.Working_Date = t.dteYear
and t.orderby in (1,2)
left join @table4 b on b.GL_Code = N.GL_Code and b.[Year] = t.dteYear
and t.orderby = 3
group by t.orderby, t.dteYear, N.GL_Code, N.GL_Description)
SELECT
[Formatted_GL] = CASE WHEN t.orderby = 1 THEN isnull(dbo.mf_getMaskedGL( GL_Code ), 'TOTAL') END,
[Description] = CASE WHEN t.orderby = 1 THEN GL_Description END,
WorkDte = t.dteYear,
[myDesc] = CASE WHEN t.orderby = 1 OR t.orderby = 2
THEN 'Budget ' + convert(varchar, t.dteYear)
ELSE 'Actual ' + convert(varchar, t.dteYear)
END,
[Total] = P1+P2+P3,
P1, P2, P3
FROM (
SELECT *
FROM Data
union all
SELECT null, null, sum(P1), sum(P2), sum(P3), orderby
FROM Data
GROUP BY orderby
) SQ
JOIN Years t on t.orderby = SQ.orderby
ORDER BY SQ.GL_Code, SQ.orderby
``````
0

LVL 1

Author Closing Comment

ID: 37851047
Perfect ! Thanks !
0

LVL 1

Author Comment

ID: 38298539
Hi Kiwi... Im having real trouble with that one...can you help me?

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_27830144.html

many thanks...
0

## Featured Post

Question has a verified solution.

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