TSQL Join query help

Question

Post reply

TSQL Join query help

Gators

SSC Enthusiast

Points: 126
More actions
February 7, 2013 at 12:51 pm

#269910

Table does a read and write that are the same div_time and Unit however the filled time changes . The Div_time will not equal the Filled_time . I need to find when this unit with the same Div_time had filled_times that don't match. I should see Machine 5 in my results.
Div_Time Unit_id Filled_time
1/1/2013 01:00 Machine5 1/1/2013 12:10
1/1/2013 01:00 Machine5 1/1/2013 12:15
1/1/2013 01:15 Machine7 1/1/2013 12:19
1/1/2013 01:15 Machine7 1/1/2013 12:19
Thanks
Sorry Machine7 is the same

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You haven't received much response because your question is so vague. I think this may be what you are looking for.

Notice how I provided a table and sample data. This is something you should do in the future.

if object_id('tempdb..#Something') is not null

drop table #Something

create table #Something

(

Div_Time datetime,

Unit_id varchar(25),

Filled_time datetime

)

insert #Something

select '1/1/2013 01:00', 'Machine5', '1/1/2013 12:10' union all

select '1/1/2013 01:00', 'Machine5', '1/1/2013 12:15' union all

select '1/1/2013 01:15', 'Machine7', '1/1/2013 12:19' union all

select '1/1/2013 01:15', 'Machine7', '1/1/2013 12:19'

select distinct s1.Div_time, s1.Unit_id

from #Something s1

join #Something s2 on s1.Div_Time = s2.Div_Time and s1.Filled_time <> s2.Filled_time

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

I made an assumption that you make two readings and two readings only on each machine. That assumption let's me join the table on itself and put the two readings on one line. [EDIT: Sean posted his response while I was working on mine. We had the same idea. The only difference is that I added more data such as the reading number and the actual time differences. But essentially the same query.]

SELECT

tt1.ID

,tt1.Mach_Name AS [MachineName]

,tt1.Div_Time AS [1stDivTime]

,tt1.Filled_Time AS [1stFilledTime]

,tt1.Diff1 AS [1stDuration]

,tt2.Div_Time AS [2ndDivTime]

,tt2.Filled_Time AS [2ndFilledTime]

,DATEDIFF(MINUTE,tt2.Div_Time,tt2.Filled_Time) AS [2ndDuration]

,DATEDIFF(MINUTE,tt2.Div_Time,tt2.Filled_Time)-tt1.Diff1 AS [Diff]

FROM

(

SELECT

*

,DATEDIFF(MINUTE,Div_Time,Filled_Time) AS [Diff1]

FROM

#TempTable

) AS tt1

INNER JOIN

#TempTable AS tt2

ON tt1.ID = tt2.ID-1

AND tt1.Reading = 1

AND DATEDIFF(MINUTE,tt2.Div_Time,tt2.Filled_Time)-tt1.Diff1 <> 0

Output:

ID MachineName 1stDivTime 1stFilledTime 1stDuration 2ndDivTime 2ndFilledTime 2ndDuration Diff

1 Machine5 2013-01-01 01:00:00.000 2013-01-01 12:10:00.000 670 2013-01-01 01:00:00.000 2013-01-01 12:15:00.000 675 5

7 Machine11 2013-01-01 01:45:00.000 2013-01-01 12:40:00.000 655 2013-01-01 01:45:00.000 2013-01-01 12:28:00.000 643 -12

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

How about something like this to avoid the self-join? Uses Sean's setup data.

SELECT Div_time, Unit_id

,TimeDifferenceMin=DATEDIFF(minute, MIN(Filled_Time), MAX(Filled_Time))

FROM #Something

GROUP BY Unit_id, Div_time

HAVING MAX(Filled_Time) <> MIN(Filled_Time)

Edit: Added the time difference.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Gators SSC Enthusiast Points: 126 More actions · Answer 4

Thanks for All the help, I appreciate all the input. Helped greatly...

Thank You

Here is what i came up with however I had to create a tmp table and join back to get both records.

SELECT

Divice_time

, Unit

, COUNT(DISTINCT Fill_time) AS cnt

FROM

Table

GROUP BY

Divice_time

, Unit

HAVING

COUNT(DISTINCT Fill_time) > 1

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

dwain.c (2/7/2013)
How about something like this to avoid the self-join? Uses Sean's setup data.
SELECT Div_time, Unit_id
,TimeDifferenceMin=DATEDIFF(minute, MIN(Filled_Time), MAX(Filled_Time))
FROM #Something
GROUP BY Unit_id, Div_time
HAVING MAX(Filled_Time) <> MIN(Filled_Time)
Edit: Added the time difference.

+1 Thanks Dwain, not sure why I didn't come up with it.