Selecting time range from each day of the month

[cardimom]

I need a report that shows "FtOperationEnd" from various time ranges like 10am-6pm for each day of a month. I can get one day by using select but not just that time range for multiple days. I cant do a parameter because the report is based on the following command and the FtOperationEnd does not show up in it.


SELECT MachineName,EType,FtOperationEnd,U64PhysNetworkBytes

FROM "engine"."JobStatus" "JobStatus" INNER JOIN "engine"."Machines" "Machines" ON "JobStatus"."RidMachine"="Machines"."Rid"

WHERE DATE(RecordWriteTime) >= {?BeginningDate} and DATE(RecordWriteTime) <= {?EndingDate}

Thanks a lot

Di

 

[lbass]

Is "FtOperationEnd" a datetime field that you want to limit to certain time frames? If so, you should be able to build that into your command, as in:

SELECT MachineName,EType,FtOperationEnd,U64PhysNetworkBytes

FROM "engine"."JobStatus" "JobStatus" INNER JOIN "engine"."Machines" "Machines" ON "JobStatus"."RidMachine"="Machines"."Rid"

WHERE DATE(RecordWriteTime) >= {?BeginningDate} and
DATE(RecordWriteTime) <= {?EndingDate} and
Hour(FtOperationEnd) >= {?StartHour} and
Hour(FtOperationEnd) < {?EndHour}+1

...where the hour parameters are created within the command and are number parameters. If using Hour() isn't accepted, you could try: {fn hour(FtOperationEnd)} instead.

-LB

 

[cardimom]

Thanks. Yes I want to select certain time frames. This did not work.

 

[lbass]

Instead of saying that something doesn't work, you should respond with the error message you received or explain what happened. Were you able to close out and save the command?

You have not yet identified whether FtOperationEnd is in fact a datetime field.

-LB

 

[cardimom]

Sorry about not answering correctly. Yes, FtOperationEnd is a datetime field. 3/23/2007 7:50:41PM This how the datetime field looks. I replaced what was the command with your command and got Failed To open a rowset. Details 42000Sybase ODBC Driver Syntax error or access violation.

Thanks,

 

[lbass]

I can't see from your original post what table FtOperationEnd comes from. Is what you are showing the actual command?

I didn't mean for you to literally copy what I wrote. Instead you need to plug in your table.field name, so that it looks something like:

Hour("table"."FtOperationEnd") >= {?StartHour} and
Hour("table"."FtOperationEnd") < {?EndHour}

The hours would need to be entered in military time, e.g., 13 for 1pm, 14 for 2pm, etc. Note that I changed the last clause.

-LB

 

[cardimom]

I made progress it works like this but is there an easier way then having to add each day?


{@JobEndTime} in DateTime (2007, 03, 01, 10, 00, 38) to DateTime (2007, 03, 01, 18, 09, 29)
or {@JobEndTime} in DateTime (2007, 03, 02, 10, 00, 38) to DateTime (2007, 03, 02, 18, 09, 29)
or {@JobEndTime} in DateTime (2007, 03, 03, 10, 00, 38) to DateTime (2007, 03, 03, 18, 09, 29)
or {@JobEndTime} in DateTime (2007, 03, 04, 10, 00, 38) to DateTime (2007, 03, 04, 18, 09, 29)
or {@JobEndTime} in DateTime (2007, 03, 05, 10, 00, 38) to DateTime (2007, 03, 05, 18, 09, 29)
or {@JobEndTime} in DateTime (2007, 03, 06, 10, 00, 38) to DateTime (2007, 03, 06, 18, 09, 29)

 

[lbass]

Where are you doing this? Are you using a record selection formula AND a command? Bad idea--although it will work, it will be slow. Using your last example, change the record selection formula to:

Date({@JobEndTime}) in date(2007,03,01) to date(2007,03,06) and
Time({@JobEndTime}) in time(10,00,38) to time(18,09,29)

-LB

 

[cardimom]

Thank you so so much!! I knew there had to be an easier way. Believe it or not. I am learning.

 

[cardimom]

Now I have another problem 10 am - 6pm works great but I also need 6Pm-6AM and I changed the time to reflect that but it gives me the times I dont want like 6am-6pm too

 

[lbass]

Which date does the shift belong to? You should adjust the shifts to fall within the correct date, but using a formula like the following (assuming that the shift belongs to the date in which it begins):

//{@6pm-5:59am}:
Date({@JobEndTime}) in date(2007,03,01) to date(2007,03,06) and
Time(dateadd("h",-6,{@JobEndTime})) in time(12,0,0) to time(23,59,59)

You would need to adjust all your shifts by the same 6 hours.

-LB

 

[cardimom]

Thanks this works, but I want to get a total of bytes transmitted for those times. The 10am-6pm I grouped by change of day and that works great. But how can I group the 6pm-6am and get just that time range.

 

[lbass]

If you want to create groups by date and then by shift, insert a group on:

dateadd("h",-6,{@JobEndTime})

on change of date, and then insert a second group on this formula {@Shift}:

if Time(dateadd("h",-6,{@JobEndTime})) in time(0,0,0) to time(3,59,59) then
"6am to 10am" else if
if Time(dateadd("h",-6,{@JobEndTime})) in time(4,0,0) to time(11,59,59) then
"10am to 6pm" else if
if Time(dateadd("h",-6,{@JobEndTime})) in time(12,0,0) to time(23,59,59) then
"6pm to 6am"

-LB