Using group by day when a day begins from 23:00:00

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 2005 Forums

Transact-SQL (2005)

Using group by day when a day begins from 23:00:00

Author

Topic

AmirJan
Starting Member

4 Posts

Posted - 2007-08-09 : 08:41:25

I must make a query that it groups for days a production. How can make it if the production day begins to 23:00: 00 of the previous day in order then to end to 22:59: 59 of the successive day?
please help me

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-09 : 08:53:22

Not sure what you are asking. Can you provide an example that demonstrates what you need?

I suspect that all you might need to do is subtract an hour from your DateTime column:

DateAdd(hour,-1, YourDate)

But it is hard to tell w/o more info.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-09 : 09:40:55

[code]
select
[Day] = dateadd(day,datediff(day,0,dateadd(hour,1,MyDate)),0)
from
(
-- Test data, beginning and end of day
select Mydate = convert(datetime,'20070809 23:00:00.000') union all
select Mydate = convert(datetime,'20070810 22:59:59.997')
) a

Results:
Day
------------------------------------------------------
2007-08-10 00:00:00.000
2007-08-10 00:00:00.000

(2 row(s) affected)

[/code]

CODO ERGO SUM

AmirJan
Starting Member

4 Posts

Posted - 2007-08-09 : 11:18:24

Sorry, the problem was this:
I have sample table with this field:
DataTime_Start,
DateTime_End,
DurataSec,
ProblemMachine.
I want to sum DurataSEC for the problem selected and grouping by Day just to visualize it in Dundas Chart.
On the axis X i must put a single day for each month.
If i want visualize example APRIL, I can make this SELECT:
SELECT sum(DurataSec) as Durata from table where DataTime_start>="31/03/2007 23:00:00" and DataTime_Start<"30/04/2007 23:00:00" AND ProblemMachine="Maintenance" group by DAY.
Now, if the 'production DAY' for our line production, start each day from 23:00:00 and finish the next day at 22:59:59 how can visualize it on chart?

I can't use Groub BY DAY. (I believe)

X michael valentin jones :With your example can resolve my problem?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-09 : 12:03:12

Please give us some sample data and expected results that demonstrate your situation.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-09 : 12:06:55

quote:
Originally posted by AmirJan
...X michael valentin jones :With your example can resolve my problem?

What happened when you tried it?

CODO ERGO SUM

AmirJan
Starting Member

4 Posts

Posted - 2007-08-10 : 03:49:30

OK,
I have a table with this fields:
DataInizio,DataFine,Durata,codice,motivo.
The field 'Durata' represent the seconds that the line production is stopped caused from 'MOTIVO'.
Now i have need to retrieve the sum of 'Durata' where the month is APRIL and the MOTIVO is "MAINTENANCE" and group it for day, because i must visualize it on CHART where the axis X is a singole DAY of month and axis Y is the sum of the second.
Well, the SELECT is
SELECT sum(Durata),motivo where DATAINIZIO>="31/03/2007 23:00:00" AND DATAINIZIO<"30/04/2007 23:00:00" and motivo = "MAINTENAICE" group by DAY(DATAINIZIO)
The response is

quote:

DAY -- Somma sec
1 ------ 2300
2 ------ 1000
3 ------ 500
4 ------ 100
5 ------ 560
6 ------ 40
..
..
30 ------ 1234

All this work was OK if the 'production DAY' begins at 00:00:00 and finish at 23:59:59 of the same DAY. But for our industry the 'production DAY' begins from 23:00:00 of the previuos day, and finish at 22:59:59 of the next day.
If i use the select before, i make big mistake of production stopped.
please help me ..

quote:

x Michael Valentine Jones:
I have tried it but I have not understood how can i use it for me..
I believe is the correct way in order to resolve my problem.

AmirJan
Starting Member

4 Posts

Posted - 2007-08-10 : 04:44:30

Wow!!!

The idea to ADD 1 hour of mr Michael Valentine Jones was correct.
With this SELECT i have resolve my problem:

SELECT day(DATEADD(hour,1,DataInizio)),count(*) FROM Fermate WHERE DATEADD(hour,1,DataInizio)>='04/01/2007 00:00:00'
and DATEADD(hour,1,DataInizio)<'05/01/2007 00:00:00'
group by day(DATEADD(hour,1,DataInizio))
order by day(DATEADD(hour,1,DataInizio))

THANKS ! THANKS ! .....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources