SQL For a Derived Table

building semantic layer
1

Hi Guys I wonder if someone maybe able to help me out with a little SQL.

I am trying to create a Derived Table in my universe which looks at a table that contains multiple values of a single ‘Call’ the table I’m looking at is Called ‘Call_Events’ Each event has different event statuses. I want to create a Derived Table which gives me the unique call reference once, followed by all the call headings in separate columns. If a call has had an event with that heading give me a true or false value in return.

Im not an SQL expert so this is where im asking for a little help. Originally I had something along the lines off;

Select
Distinct (Call_Events.Link_to_Call) as Call_Ref,
Case When Call_Events.Event_Code = ‘A’ Then ‘Yes’ Else ‘No’ end ‘Allocated’,
Case When Call_Events.Event_Code = ‘AK’ Then ‘Yes’ Else ‘No’ end ‘Acknowledged’,
Case When Call_Events.Event_Code = ‘BU’ Then ‘Yes’ Else ‘No’ end ‘Bud Upflt Reqsd’,
Case When Call_Events.Event_Code = ‘C’ Then ‘Yes’ Else ‘No’ end ‘Complete’
From
Call_Events

That doesn’t give me one line with everything on it. I think I need to insert a loop and let it keep looking. But im not 100% sure on what the correct code would be? Can anyone point me in the right direction please?..

Im using SQL Server 2000

Regards
Mark

lugg2000 (BOB member since 2007-11-07)

2

Hi,

Maybe the SQL Server syntax differs from Oracle one for the columns naming but…
Try this:

Select 
Distinct (Call_Events.Link_to_Call) as Call_Ref, 
Case When Call_Events.Event_Code = 'A' Then 'Yes' Else 'No' end as Allocated, 
Case When Call_Events.Event_Code = 'AK' Then 'Yes' Else 'No' end as Acknowledged, 
Case When Call_Events.Event_Code = 'BU' Then 'Yes' Else 'No' end as Bud_Upflt_Reqsd, 
Case When Call_Events.Event_Code = 'C' Then 'Yes' Else 'No' end as Complete
From 
Call_Events

Regards,

Ghislain

ggouzy :fr: (BOB member since 2005-10-25)

3

No the code does work but i get this…

Call_Ref Allocated Acknowledged Bug_Uplift_Request etc
1 Yes No No
1 No Yes No

When what i want is
Call_Ref Allocated Acknowledged Bug_Uplift_Request etc
1 Yes Yes No

I think i need to insert a loop or something?

Regards
Mark

lugg2000 (BOB member since 2007-11-07)

4

No, if Call_Events.Link_to_Call is a primary key, your code will works well.
Can you execute:


select max(req.cnt) from (
select Call_Events.Link_to_Call, count(*) as cnt from Call_Events
group by Call_Events.Link_to_Call
) req

and post the result?

ggouzy :fr: (BOB member since 2005-10-25)

5

In this table Link_to_Calls is a seconday key! There is a primary key for Call_Events!

And the result i get back from the above is 1149?

lugg2000 (BOB member since 2007-11-07)

6

Maybe you need something like this


SELECT Key, 
            CASE when first_YES_NO = 1 Then 'YES' Else 'NO' END f_YES_NO, 
            CASE when second_YES_NO = 1 Then 'YES' Else 'NO' END s_YES_NO, 
            CASE when third_YES_NO = 1 Then 'YES' Else 'NO' END t_YES_NO
FROM
(
  SELECT KEY, 
  MAX(CASE when code = 'value1' Then 1 Else 0 END) first_YES_NO
  MAX(CASE when code = 'value2' Then 1 Else 0 END) second_YES_NO
  MAX(CASE when code = 'value3' Then 1 Else 0 END) third_YES_NO
  FROM
  YOURTABLE
  GROUP BY KEY
) A

Let us know your result.

Obviously you can reduce the query size by incorporating both max and yes/no together

BO Person (BOB member since 2007-11-14)

7

Im sorry i cant even get that one to work!

lugg2000 (BOB member since 2007-11-07)

8

Replace Key to Call_Events.Link_to_Call
Replace code to Call_Events.Event_Code

Also replace appropriate values. That must do the trick.

It works fine on SQL in my end.

BO Person (BOB member since 2007-11-14)

9

Hi,

You need to aggregate the values…
Try this:

select subquery.Call_Ref,
case when subquery.flg_allocated = 0 then 'No' else 'Yes' end as Allocated,
case when subquery.flg_acknowledged = 0 then 'No' else 'Yes' end as Acknowledged,
case when subquery.flg_bud_Upflt_Reqsd = 0 then 'No' else 'Yes' end as Bud_Upflt_Reqsd,
case when subquery.flg_complete = 0 then 'No' else 'Yes' end as Complete
from
(Select 
Distinct (Call_Events.Link_to_Call) as Call_Ref, 
sum(case when Call_Events.Event_Code = 'A' Then 1 Else 0 end) as flg_allocated, 
sum(case When Call_Events.Event_Code = 'AK' Then 1 Else 0 end) as flg_acknowledged, 
sum(case When Call_Events.Event_Code = 'BU' Then 1 Else 0 end) as flg_bud_Upflt_Reqsd, 
sum(case When Call_Events.Event_Code = 'C' Then 1 Else 0 end) end as flg_complete 
From 
Call_Events
group by Call_Events.Link_to_Call) subquery

I hope the query behavior fits to your needs :wink:

Regards,

Ghislain

ggouzy :fr: (BOB member since 2005-10-25)

10

I did all that, :hb: but its working now! So thanks so much

lugg2000 (BOB member since 2007-11-07)

11

This is Perfect and works great!!

You Guys are truely Great!!

lugg2000 (BOB member since 2007-11-07)