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: CODEDeclare @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
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom |
|
Talk To Other Members
Read More Threads Like This One