Solved

Help with query  #2

Posted on 2012-04-12
11
479 Views
Last Modified: 2012-08-15
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
Comment
Question by:PhilippeRenaud
  • 6
  • 5
11 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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

by:cyberkiwi
Comment Utility
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

by:PhilippeRenaud
Comment Utility
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
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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

by:PhilippeRenaud
Comment Utility
But I have SQL 2008 R2 ..   it should work then ... mmm
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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

by:PhilippeRenaud
Comment Utility
LOL
0
 
LVL 1

Author Comment

by:PhilippeRenaud
Comment Utility
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

by:
cyberkiwi earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
Comment Utility
Perfect ! Thanks !
0
 
LVL 1

Author Comment

by:PhilippeRenaud
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

7 Experts available now in Live!

Get 1:1 Help Now