• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

Help with query #2

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
PhilippeRenaud
Asked:
PhilippeRenaud
  • 6
  • 5
1 Solution
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
PhilippeRenaudAuthor Commented:
Sorry for the line   dbo.mf_getMaskedGL

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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
cyberkiwiCommented:
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
 
PhilippeRenaudAuthor Commented:
But I have SQL 2008 R2 ..   it should work then ... mmm
0
 
cyberkiwiCommented:
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
 
PhilippeRenaudAuthor Commented:
LOL
0
 
PhilippeRenaudAuthor Commented:
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
 
cyberkiwiCommented:
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

Open in new window

0
 
PhilippeRenaudAuthor Commented:
Perfect ! Thanks !
0
 
PhilippeRenaudAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now