Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
first thank you for having this forum! QV is a completely new tool to me - no training no exposure all SSRS and Crystal previously so please forgive me if I am not following proper protocol or asking my questions correctly. I have spent hours searching the QV help files, the internet and this forum as well as just "trying to figure it out" all to no avail. Possibly I am not asking my question correctly - on to the point:
I have an application that grabs records from a shop floor database. I have just about everything working the way I want it to but the times. the users of this application will pick a day (or 2 or 3, etc) and will count a full day as 6am to 6am. there are multitudes of entries per day (aka hundreds broken down by second) so I would like to for the user to be able to pick their days and the straight table to show either the option to select from single hours (not every hour from the output. . ie 6/6/2014 4:00:00am, 6/6/2014 5:00:00am, etc instead of 6/6/2014 4:00:01am, 6/6/2014 4:02:00am. so somewhat of a group by hour or rollup on the hours to eliminate clutter. I would like the straight table to then output all the details between the selections. I have a current selections box that seems ideal to this but when I launch this the dates are in order but the times are not so I cannot gather the appropriate 6am to 6am requirement. any and all help is appreciated.
Subtract 0.25 (=6/24) from the timestamps (aka datetimes) to get the correct day. Use the frac function to get the time part of the timestamp and the floor function to round down to hours:
Load
...some fields...,
, floor(MyDate - 0.25) as MyDate
, floor(frac(MyDate),1/24) as MyTime
From ...somewhere...;
You can use daystart() to define your day start at 6 am
Load
Timestamp,
daystart(Timestamp, 0, 0.25) as DayStart,
Date(floor(daystart( Timestap, 0, 0.25)),'MM/DD/YYYY') as DayStartDate
hour(Timestamp) as Hour,
minute(TimeStamp) as Minute
From ...somewhere...;
thank you both ever so much - i will implement these solutions and report back my results - much obliged!
I used a little bit of a combo to accomplish the task – so far it seems to be working – again thank you both
LOAD STARTDATE,
daystart(STARTDATE, 0, 0.25) as DayStart,
dayend(STARTDATE, 0, 0.25) as DayEnd
FROM
C:\Users\COMPUTER\Desktop\DATAFILE.xlsx
(ooxml, embedded labels, table is data);