Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

#### Feedback

"...I have learned more through this forum than I did on a two day course. Thanks to everyone for their help and other postings that I have found useful..."

#### Geography

Where in the world do Tek-Tips members come from?

# Combining queries

 Forum Search FAQs Links Jobs Whitepapers MVPs
 draylan (IS/IT--Management) 3 May 12 10:12
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

#### CODE

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

#### CODE

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 (Programmer) 3 May 12 10:13
 "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? -GeorgeMicrosoft SQL Server MVPMy BlogsSQLCoptwitter"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 draylan (IS/IT--Management) 3 May 12 10:22
 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 TimeDiff1    xxx   xxx   xxx   U2    1440Hope that helps.
 dhulbert (TechnicalUser) 3 May 12 10:37

#### CODE

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
(1952-2001)

 gmmastros (Programmer) 3 May 12 10:46
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:

#### CODE

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).

#### CODE

;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:

#### CODE

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
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 draylan (IS/IT--Management) 3 May 12 12:00
 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.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!