Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Join query help Expand / Collapse
Author
Message
Posted Thursday, February 7, 2013 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 9, 2013 8:45 AM
Points: 6, Visits: 41
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
Post #1417322
Posted Thursday, February 7, 2013 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1417365
Posted Thursday, February 7, 2013 4:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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

Post #1417381
Posted Thursday, February 7, 2013 6:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 3,627, Visits: 5,275
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1417417
Posted Friday, February 8, 2013 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 9, 2013 8:45 AM
Points: 6, Visits: 41
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
Post #1417661
Posted Friday, February 8, 2013 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1417700
Posted Friday, February 8, 2013 8:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Sean Lange (2/8/2013)
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.

+2

My first attempts to answer these somewhat vague questions often prove to be less than optimal. This community has a lot of brain-power behind it and I'm humbled and learning every day.

 
Post #1417743
Posted Sunday, February 10, 2013 5:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 3,627, Visits: 5,275
Sean Lange (2/8/2013)
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.


You probably lacked my coffee buzz that morning.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1418156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse