Combining queries 1

[draylan]

Hi,

I need some help trying to figure this out. Please see the example tables below and 2 separate queries. Is there a way to combine them together?

fh
ID Field1 Field2 Field3 Time1
1 In Null U1 2011-12-01
1 Out In U1 2011-12-02
1 Wait Out U2 2011-12-03
2 Out In U1 2011-12-02
3 In Wait U3 2011-12-05

m
ID Col1 Col2 Col3
1 xxx xxx xxx
2 bbb xxx xxx
3 aaa xxx xxx
4 xxx xxx xxx

select m.ID, m.Col1, m.Col2, m.Col3, fh.Field3
   From Master9 as m
    Inner Join Master10 as fh
    On m.ID = fh.ID
Where fh.Field1 = Wait

select fh.ID, 
  DateDiff(minute, 
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End))
  As TimeDiff
From Master10 as fh
  Inner Join Master 9 as m
  On fh.ID = m.ID
Where m.Col1 = 'xxx'
Group By fh.ID
Having DateDiff(minute,
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End)) > 0
Order By fh.ID

May be a bit confusing, but any help would be appreciated. Thanks!

D

 

[gmmastros]

Combining Queries" could mean different things to different people. To help us understand, can you show what the expected results would be based on the same data you posted?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[draylan]

Hi George, you helped me last time with the 2nd query!

Sorry, to be more clear, the 2nd query outputs 2 columns and I basically want to just insert the TimeDiff into the first query since the IDs are directly related. So, here is the expected output I am looking for:

ID Col1 Col2 Col3 Field3 TimeDiff
1 xxx xxx xxx U2 1440

Hope that helps.

 

[dhulbert]

Select ID  Col1  Col2  Col3  Field3 TimeDiff
From (select m.ID, m.Col1, m.Col2, m.Col3, fh.Field3   
      From Master9 as m    
           Inner Join Master10 as fh    
           On m.ID = fh.ID
      Where fh.Field1 = Wait) qry1

Inner Join
 
      (select fh.ID,   DateDiff(minute, 
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End))  
      As TimeDiff
From  Master10 as fh  
      Inner Join Master 9 as m  
      On fh.ID = m.ID
Where m.Col1 = 'xxx'
Group By fh.ID
Having DateDiff(minute,Min(Case When fh.Field1 = 'In' Then fh.Time1 End), Min(Case When fh.Field1 = 'Out' Then fh.Time1 End)) > 0) qry2

on qry1.id = qry2.id

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[gmmastros]

Ok. I understand now. Thanks for clarifying.

The challenge here is that you are using a group by query, which can cause problems with your query. There is a technique you can use to accommodate this type of query. Actually, there are several ways...

1. You could store the output of the group by query in to a temp table or table variable and then join to the temp table to get your results.

Like this:

[!]Declare @Temp Table(Id Int, Duration Int)

Insert Into @Temp(ID, Duration)[/!]
select fh.ID, 
  DateDiff(minute, 
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End))
  As TimeDiff
From Master10 as fh
  Inner Join Master 9 as m
  On fh.ID = m.ID
Where m.Col1 = 'xxx'
Group By fh.ID
Having DateDiff(minute,
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End)) > 0
Order By fh.ID

select m.ID, m.Col1, m.Col2, m.Col3, fh.Field3, T.Duration
From   Master9 as m
       Inner Join Master10 as fh
         On m.ID = fh.ID
[!]       Inner Join @Temp T
         On m.ID = T.Id[/!]
Where fh.Field1 = 'Wait'

2. You could use a common table expression (assuming you are using SQL2005 or newer).

[!];With Durations As
([/!]
select fh.ID, 
  DateDiff(minute, 
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End))
  As TimeDiff
From Master10 as fh
  Inner Join Master9 as m
  On fh.ID = m.ID
Where m.Col1 = 'xxx'
Group By fh.ID
Having DateDiff(minute,
      Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
      Min(Case When fh.Field1 = 'Out' Then fh.Time1 End)) > 0
[!])[/!]
select m.ID, m.Col1, m.Col2, m.Col3, fh.Field3[!], Durations.TimeDiff[/!]
From   Master9 as m
       Inner Join Master10 as fh
         On m.ID = fh.ID
       [!]Inner Join Durations
         On m.ID = Durations.Id[/!]
Where fh.Field1 = 'Wait'

3. You could use a derived table, like this:

select m.ID, m.Col1, m.Col2, m.Col3, fh.Field3[!], Durations.TimeDiff[/!]
From   Master9 as m
       Inner Join Master10 as fh
         On m.ID = fh.ID
       Inner Join [!]([/!]
          select fh.ID, 
                 DateDiff(minute, 
                 Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
                 Min(Case When fh.Field1 = 'Out' Then fh.Time1 End))
                   As TimeDiff
          From   Master10 as fh
                 Inner Join Master9 as m
                   On fh.ID = m.ID
          Where  m.Col1 = 'xxx'
          Group By fh.ID
          Having DateDiff(minute,
                 Min(Case When fh.Field1 = 'In' Then fh.Time1 End),
                 Min(Case When fh.Field1 = 'Out' Then fh.Time1 End)) > 0
          [!]) As Durations
          On m.ID = Durations.Id[/!]
Where fh.Field1 = 'Wait'

Each method will return the same results. Under the hood, SQL Server will treat the 2nd and 3rd as the exact same query (with identical execution plans and performance). The first query is likely to be a little bit slower (or a lot if you have a ton of data).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[draylan]

dhulbert - Thanks for the code. I may have done something wrong but was unable to execute the code. It didn't seem to like the last line for some reason.

George - This is very helpful. I am trying these out now and looks very promising. Thanks so much... learning a lot here. Will revert back with my results.