Narrow Down Date Ranges - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Narrow Down Date Ranges

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2011-09-22 : 09:01:15

Ok, I have a table that over 300,000 records in it. Pretty standard for an industrial environment, but I need to be able to pull out very specific information. The three columns of relevance in this query are PipeDiameter/PipeGauge/dtTimeStamp. I first ran the following query to pull out all of the recorded PipeDiameter/PipeGauge combinations:


SELECT DISTINCT PipeDiameter AS PipeD, PipeGauge AS PipeG, dtTimeStamp
FROM tblPipeCount
WHERE (PipeDiameter <> 0) AND (PipeGauge <> 0)
ORDER BY PipeDiameter ASC

This yielded the exact results I was looking for. Now for the tricky part… There are two shifts:
Shift 1: 07:00:00.000 to 18:59:59.999
Shift 2: 19:00:00.000 to 06:59:59.999 (The next day)

I have to now pull in all the records where a specific pipe pair (that I pulled in by the afore mentioned query) by a single shift. So I would need only one time stamp from each shift so I can identify the shift that ran that pair on a specific day. So lets say 12.75/0.371 were run on:
2010-03-25 12:51:15.377
2010-06-15 11:48:52.707
2010-06-16 21:54:45.937
2010-06-16 22:49:38.083

What I need my query to kick back is:
2010-03-25 12:51:15.377
2010-06-15 11:48:52.707
2010-06-16 21:54:45.937

This tells me that that Shift 1 ran that pipe pair on 2010-03-25 and 2010-06-15. It also tells me that Shift 2 ran that pipe pair on 2010-06-16 21:54:45.937.

Is this at all possible? Or do I have to individually go through every record to find this data. I have 139 different possible pairs; I can handle 139, but not 345,967 different records.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-22 : 09:26:27

MmmmK, it would be helpful to know how you came up with 12.75/0.371. I don't see any correllation of the pair you stated to your second block of timestamps. If there's a relation between the "pairs" and the "shifts", then it would be fairly easy to return the records you're looking for. A couple questions:

1. Are the shifts defined on one record, for example:

TableA
ShiftName FromTime ToTime

or are they:
Table: Shifts
ShiftName TimeStamp

2. Can you describe how the pair relates to the times.

Is it:

Table: Pairs

PairName TimeStamp

or possibly:

Item TimeStamp
FirstPartOfPair time...
SecondPartOfPair time...

Hopefully that makes sense...

MKz71
Starting Member

30 Posts

Posted - 2011-09-22 : 09:39:14

The shift is not identified in the table. This is an assembly line, when ever the pipe is cut it creates a record. The record includes the PipeDiameter and PipeGauge and a time stamp (amongst other data). The 12.75/0.371 is made up data and there is no relation between the pairs and the shifts, it is based on whatever the line is set to and that specific pair can span across multiple days depending upon orders. The information I have given you above is all fabricated and not taken from an actual query, nor is real data needed to get my point accross. I know this is not a simple task by any means, I just don't know anything when it comes to SQL. I just need to pull out one (1) date/time between a 12hr time block that I gave earlier, for a specific pair that I will plug in going back as far as the first entry in the db in 2009.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-22 : 09:51:15

Yes, you're correct, we don't need real data, but we need to be able to see relationships. You said that 12.75/0.371 is made up. That's no problem, but what does it refer to? Is it a combination of PipeDiameter and PipeGauge like this:

Select PipeDiameter+'/'+PipeGauge as Pair

MKz71
Starting Member

30 Posts

Posted - 2011-09-22 : 10:10:00

Yes. I'm sorry I thought that was implied. To correct any further error, I am not using a string like that in any of my queries either. PipeDiameter = 12.75 PipeGauge=0.371. Again I thought that was implied by the way I structured my question, and did not mean to confuse in any way. Sorry.

So Where PipeDiameter = 12.75 and PipeGauge=0.371, Pull out a SINGLE date/time from EACH day and shift where it takes place. If this pipe is run 250 over the course of the last year, and lets just say for simplistic reasoning both shifts ran that pipe, the query should return 500 records of Date/Time. One for each shift that ran it (SHIFT 1= 7AM-7PM, SHIFT 2= 7PM-7AM). I just need to identify which shift ran that pipe on which day(s). A new shift constitutes a new run of the pipe, so even if that same diameter and gauge is run for 48hrs straight (starting at 7AM) it is understood to be 4 seperate runs of that pipe when a new shift takes over.

MKz71
Starting Member

30 Posts

Posted - 2011-09-22 : 11:09:42

OK I ran the following SQL and it gives me all the pipes that were cut of the specified pair for the AM shift. Is there any way I can narrow this down to display only the first time stamp pulled from each day? Meaning, it shows all of the pipe cut (multiple time stamps per day) which is over 1000 records. Is this possible? Here is my Query:


SELECT dtTimeStamp
FROM tblPipeCount
WHERE (PipeDiameter = 8.625) AND (PipeGauge = 0.319) AND (DATEPART(hh,[dtTimeStamp]) >= 7 AND DATEPART(hh,[dtTimeStamp]) <19)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-22 : 11:36:04

This is probably not an ideal solution, but given the last query, you could do something like this so that you will still get the first event for each day:

[CODE]
SELECT CONVERT(CHAR(8), dtTimeStamp,10) as DatePortion,
MIN(dtTimeStamp) as FirstTime

FROM @tblPipeCount
WHERE (PipeDiameter = 8.625) AND (PipeGauge = 0.319) AND
(DATEPART(hh,[dtTimeStamp]) >= 7 AND DATEPART(hh,[dtTimeStamp]) <19)

GROUP BY CONVERT(CHAR(8), dtTimeStamp,10)
[/CODE]

MKz71
Starting Member

30 Posts

Posted - 2011-09-22 : 11:57:39

This works great. I only need to know the day not the time, because the time is understood to be in the range already. This works way better than using SELECT DISTINCT (DATEPART(dd, [dtTimeStamp])) because it was only giving me the day and not the entire date. Thank you for your time and patience.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-22 : 12:12:06

No problem...you'd probably improve performance if you pull the datepart functions out of the Where clause. This is something I've been grappling with for a while. Essentially, the way I understand it, if you place a function on a column within the Where clause, it forces SQL Server to evaluate each individual row rather than scanning for indexes. This can decrease performance, especially with a large data set. I'll think about a way to reconstruct your "where" clause, but I'm guessing someone more expert in SQL will have an improved WHERE clause before I figure it out :)

MKz71
Starting Member

30 Posts

Posted - 2011-09-23 : 07:41:14

I was wondering if you knew how to structure the second half of the dtTimeStamp part of the where clause to include 7pm to 7am? I would have to evaluate a range that would involve the first half being >= the 19th hour on one day and < the 7th hour on the very next day. Example of a random date:

(dtTimeStamp >= 12/12/2012 19:00:00.000 AND dtTimeStamp < 12/13/2012 7:00:00.000)

The hour would be static, but the date would obviously change as I need to filter through all of the dates in the dtTimeStamp column. Any ideas? I don't have any real knowledge of this stuff, including the DATEPART function. I am guessing its a matter of adding an additional paremeter to one side doing a DATEPART(dd, [dtTimeStamp] -1) but as I said, I have no clue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 08:21:57

[code]DECLARE @Sample TABLE
(
theDate DATETIME NOT NULL
)

INSERT @Sample
(
theDate
)
VALUES ('20100325 12:51:15.377'),
('20100615 11:48:52.707'),
('20100616 21:54:45.937'),
('20100616 22:49:38.083')

SELECT *
FROM @Sample
/*
theDate
2010-03-25 12:51:15.377
2010-06-15 11:48:52.707
2010-06-16 21:54:45.937
2010-06-16 22:49:38.083
*/

SELECT MIN(theDate) AS FromDate,
MAX(theDate) AS ToDate,
1 + (DATEPART(HOUR, theDate) - 7) / 12 AS Shift
FROM @Sample
GROUP BY CAST(DATEADD(HOUR, -7, theDate) AS DATE),
1 + (DATEPART(HOUR, theDate) - 7) / 12
/*
FromDate ToDate Shift
2010-03-25 12:51:15.377 2010-03-25 12:51:15.377 1
2010-06-15 11:48:52.707 2010-06-15 11:48:52.707 1
2010-06-16 21:54:45.937 2010-06-16 22:49:38.083 2
*/[/code]

MKz71
Starting Member

30 Posts

Posted - 2011-09-23 : 09:44:25

I am trying This:
[CODE]
DATEPART(hh,[dtTimeStamp]) >= 19 AND DATEPART(dd, [dtTimeStamp] + 1, DATEPART(hh,[dtTimeStamp]) < 7)))
[/CODE]

But I keep getting a syntax error... Any thoughts?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 10:03:03

Yes, try my suggestion first.
And then, this rewrite

DATEPART(hh,[dtTimeStamp]) >= 19 OR DATEPART(hh,[dtTimeStamp]) < 7

N 56°04'39.26"
E 12°55'05.63"

MKz71
Starting Member

30 Posts

Posted - 2011-09-23 : 10:19:07

That would work, but I have to be able to jump one day on the second half. If a pipe is cut on 12/12/12 @ 10:00PM and 12/13/12 @ 1:00AM, it is cut by the same shift. I only want to return one record from each shift, if I could do a between (date1 7pm and date1+1 7am) it would pull all the records in from 12/12/12 7PM - 12/13/12 7AM. If I do what your suggesting it would pull in dtTimeStamp < 12/12/12 7AM OR dtTimeStamp > 12/12/12 7PM. This may pull in valid dates, but the two sides constitute a new shift because they are the same day. This would also return two items for each shift because it could fall on both days within one shift.

Any way to adjust your 'DATEPART(hh,[dtTimeStamp]) >= 19 OR DATEPART(hh,[dtTimeStamp]) < 7' code to make the second DATEPART add 1 day to the dtTimeStamp?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 10:47:50

Did you try my suggestion at all?

N 56°04'39.26"
E 12°55'05.63"

MKz71
Starting Member

30 Posts

Posted - 2011-09-23 : 11:34:02

To be honest with you... there is a lot going on with it and I don't understand SQL enough to know what is happening.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources