Time Question

Time Question

Post by Jason MacKenzi » Sat, 14 Feb 2004 18:44:14



I have the following query that groups parts into hourly buckets which is no
big deal.

 SELECT

 PlantArea,
 Linename,
 COUNT(*) AS [Total Parts]

 FROM         tblPartToPartTimes PTP

 WHERE
 RecordTime >= '2/13/2004 12:00 AM' AND RecordTime <= '2/13/2004 11:59 PM'

 GROUP BY PlantArea, Linename,  DATEPART(day, RecordTime), DATEPART(hh,
RecordTime)
 ORDER BY MIN(RecordTime)

What I need now is to group the parts into hourly buckets that run from 10
minutes after the hour to 10 minutes after the next hour. ie 1:10-2:10.

As usual I'm suffering from confusion and any help is appreciated,

Jason

 
 
 

Time Question

Post by Anith Se » Sat, 14 Feb 2004 20:35:04


Please post a simplified CREATE TABLE statement, set of input data &
expected results.

--
Anith

 
 
 

Time Question

Post by Aaron Bertrand - MV » Sat, 14 Feb 2004 20:50:15


Quote:> > RecordTime >= '2/13/2004 12:00 AM' AND RecordTime <= '2/13/2004 11:59

PM'

I wouldn't handle either endpoints this way.  This will ensure two things...
that even 11:59:59.666 is included, and that no changes to server / language
/ dateformat settings will result in bad data or an error:

RecordTime >= '20040213' AND RecordTime < '20040214'

Quote:>> What I need now is to group the parts into hourly buckets that run from
10
>> minutes after the hour to 10 minutes after the next hour. ie 1:10-2:10.

Maybe this example will give you some ideas.

CREATE TABLE blat
(
 dt DATETIME
)

SET NOCOUNT ON

INSERT blat SELECT '20040213 00:00:01' -- ignored
INSERT blat SELECT '20040213 00:09:01' -- ignored
INSERT blat SELECT '20040213 00:10:01'
INSERT blat SELECT '20040213 01:09:01'
INSERT blat SELECT '20040213 01:10:01'
INSERT blat SELECT '20040213 02:10:01'
INSERT blat SELECT '20040213 23:08:01'
INSERT blat SELECT '20040213 23:10:01'
INSERT blat SELECT '20040213 23:59:59'
INSERT blat SELECT '20040214 00:09:59'
INSERT blat SELECT '20040214 01:00:00' -- ignored

SELECT CONVERT(CHAR(8), DATEADD(MINUTE, -10, dt), 112),
 DATEPART(HOUR, DATEADD(MINUTE, -10, dt)),
 COUNT(*)
FROM
 blat
 WHERE DATEADD(MINUTE, -10, dt) >= '20040213'
  AND DATEADD(MINUTE, -10, dt) < '20040214'
 GROUP BY CONVERT(CHAR(8), DATEADD(MINUTE, -10, dt), 112),
 DATEPART(HOUR, DATEADD(MINUTE, -10, dt))
  ORDER BY 1,2

DROP TABLE blat

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Quote:

>  GROUP BY PlantArea, Linename,  DATEPART(day, RecordTime), DATEPART(hh,
> RecordTime)
>  ORDER BY MIN(RecordTime)

> What I need now is to group the parts into hourly buckets that run from 10
> minutes after the hour to 10 minutes after the next hour. ie 1:10-2:10.

> As usual I'm suffering from confusion and any help is appreciated,

> Jason

 
 
 

Time Question

Post by Jason MacKenzi » Sat, 14 Feb 2004 20:52:13


Hi Anith,

CREATE TABLE [dbo].[tblPartToPartTimes] (
 [EntryID] [int] IDENTITY (1, 1) NOT NULL ,
 [RecordTime] [datetime] NULL ,
 [PlantArea] [varchar] (15) NULL ,
 [Linename] [varchar] (15) NULL
) ON [PRIMARY]

Insert into tblPartToPartTimes
SELECT  '2/13/2004 1:47:57 PM', 'Finals', 'Line 7'
union
SELECT  '2/13/2004 1:45:57 PM', 'Finals', 'Line 7'
union
SELECT  '2/13/2004 1:15:57 PM', 'Finals', 'Line 7'union
union
SELECT  '2/13/2004 1:08:57 PM', 'Finals', 'Line 7'
union
SELECT  '2/13/2004 12:50:57 PM', 'Finals', 'Line 7'
union
SELECT  '2/13/2004 12:35 PM', 'Finals', 'Line 7'

Expected results:

Area        Line           Total Parts
Finals     Line 7         3
Finals     Line 7         3

Right now with the query based on the hour and not 10 minutes after the hour
it returns:

Area        Line           Total Parts
Finals     Line 7             2
Finals     Line 7             4


Quote:> Please post a simplified CREATE TABLE statement, set of input data &
> expected results.

> --
> Anith

 
 
 

Time Question

Post by Aaron Bertrand - MV » Sat, 14 Feb 2004 21:07:51


The query I posted does it:

SELECT CONVERT(CHAR(8), DATEADD(MINUTE, -10, RecordTime), 112),
 DATEPART(HOUR, DATEADD(MINUTE, -10, RecordTime)),
 COUNT(*)
FROM
 tblPartToPartTimes
 WHERE DATEADD(MINUTE, -10, RecordTime) >= '20040213'
  AND DATEADD(MINUTE, -10, RecordTime) < '20040214'
 GROUP BY CONVERT(CHAR(8), DATEADD(MINUTE, -10, RecordTime), 112),
 DATEPART(HOUR, DATEADD(MINUTE, -10, RecordTime))
  ORDER BY 1,2

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


> Hi Anith,

> CREATE TABLE [dbo].[tblPartToPartTimes] (
>  [EntryID] [int] IDENTITY (1, 1) NOT NULL ,
>  [RecordTime] [datetime] NULL ,
>  [PlantArea] [varchar] (15) NULL ,
>  [Linename] [varchar] (15) NULL
> ) ON [PRIMARY]

> Insert into tblPartToPartTimes
> SELECT  '2/13/2004 1:47:57 PM', 'Finals', 'Line 7'
> union
> SELECT  '2/13/2004 1:45:57 PM', 'Finals', 'Line 7'
> union
> SELECT  '2/13/2004 1:15:57 PM', 'Finals', 'Line 7'union
> union
> SELECT  '2/13/2004 1:08:57 PM', 'Finals', 'Line 7'
> union
> SELECT  '2/13/2004 12:50:57 PM', 'Finals', 'Line 7'
> union
> SELECT  '2/13/2004 12:35 PM', 'Finals', 'Line 7'

> Expected results:

> Area        Line           Total Parts
> Finals     Line 7         3
> Finals     Line 7         3

> Right now with the query based on the hour and not 10 minutes after the
hour
> it returns:

> Area        Line           Total Parts
> Finals     Line 7             2
> Finals     Line 7             4



> > Please post a simplified CREATE TABLE statement, set of input data &
> > expected results.

> > --
> > Anith

 
 
 

Time Question

Post by Aaron Bertrand - MV » Sat, 14 Feb 2004 21:11:35


PS you might consider adding a computed column, or creating a view, so you
can query directly against the 10-minute-offset value instead of constantly
performing date math.

One way to do it would be to add a column AS DATEADD(MINUTE, -10,
RecordTime)

Another way to do it would be to have the procedure that enters the row
calculate which "hour" the data belongs to, and store that in a tinyint.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 
 
 

Time Question

Post by Jason MacKenzi » Sat, 14 Feb 2004 21:16:09


Thanks Aaron. Stop by next time you're in my neck of the woods. I owe you at
least 20 cases of beer over for all the help over the last 4 years!



Quote:> PS you might consider adding a computed column, or creating a view, so you
> can query directly against the 10-minute-offset value instead of
constantly
> performing date math.

> One way to do it would be to add a column AS DATEADD(MINUTE, -10,
> RecordTime)

> Another way to do it would be to have the procedure that enters the row
> calculate which "hour" the data belongs to, and store that in a tinyint.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/

 
 
 

Time Question

Post by Kevin Doyl » Sat, 14 Feb 2004 21:20:45


Group like this.
BY PlantArea, Linename,  DATEPART(day, RecordTime),
case when datepart(mi,RecordTime)<10 then
        datepart(hh,dateadd(hh,-1,RecordTime))
else
        datepart(hh,RecordTime)
end

Kevin Doyle

Quote:>-----Original Message-----
>I have the following query that groups parts into hourly
buckets which is no
>big deal.

> SELECT

> PlantArea,
> Linename,
> COUNT(*) AS [Total Parts]

> FROM         tblPartToPartTimes PTP

> WHERE
> RecordTime >= '2/13/2004 12:00 AM' AND RecordTime

<= '2/13/2004 11:59 PM'
Quote:

> GROUP BY PlantArea, Linename,  DATEPART(day,

RecordTime), DATEPART(hh,
Quote:>RecordTime)
> ORDER BY MIN(RecordTime)

>What I need now is to group the parts into hourly buckets
that run from 10
>minutes after the hour to 10 minutes after the next hour.
ie 1:10-2:10.

>As usual I'm suffering from confusion and any help is
appreciated,

>Jason

>.

 
 
 

Time Question

Post by Tom Morea » Sat, 14 Feb 2004 21:23:50


Now Aaron can't drink as much beer as that, so he'll need help from one of
his MVP buddies...  ;-0

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


Thanks Aaron. Stop by next time you're in my neck of the woods. I owe you at
least 20 cases of beer over for all the help over the last 4 years!



Quote:> PS you might consider adding a computed column, or creating a view, so you
> can query directly against the 10-minute-offset value instead of
constantly
> performing date math.

> One way to do it would be to add a column AS DATEADD(MINUTE, -10,
> RecordTime)

> Another way to do it would be to have the procedure that enters the row
> calculate which "hour" the data belongs to, and store that in a tinyint.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/

 
 
 

Time Question

Post by Aaron Bertrand - MV » Sat, 14 Feb 2004 21:29:54


Quote:> Now Aaron can't drink as much beer as that, so he'll need help from one of
> his MVP buddies...  ;-0

When I still lived in Canada, I could.  The beer is half as strong and even
less appealing in this area.  :-)
 
 
 

Time Question

Post by Tom Morea » Sat, 14 Feb 2004 21:41:10


Switch back to sake!  The countdown to Seattle begins ...  ;-)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql



Quote:> Now Aaron can't drink as much beer as that, so he'll need help from one of
> his MVP buddies...  ;-0

When I still lived in Canada, I could.  The beer is half as strong and even
less appealing in this area.  :-)
 
 
 

1. sqlldr & date/time question

Hello.... And thank you in adnvace for anyones help

I want to use sqlldr to load the date/time into the same date field in my
table.  I can get the date in there ok, and I can get the time in with the use
of a different field, but I can't find anything that tells me how to do get the
date/time in the same field.  I've found how to do this with an 'insert'
statement, but I need to use sqlldr.....   Can anyone tell me how to format the
*.ctl file?  Currently I have;

load data
infile *
append into table vmstat
fields terminated by ","
(ddate, ttime, vmstat)
begindata
30-Jan-02, 0122, 'data'

2. Page Size in 32-bit Btrieve

3. Date/Time Question

4. Scheduling DTS Packages.

5. Date time question

6. Printing from a specific input tray - HELP!

7. Newbie- DataEnvir./Data Report run time question

8. There must be an easier way !

9. recover database until time question

10. Date and Time Question

11. Date Time Question

12. Query time question

13. Oracle Date/Time Question