Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015

    Red face Unanswered: Divide time period over days

    SOLVED; Just after writing this post I found a way to simulate excel formula in SQL.

    Hi, I started a few months ago with SQL and VBA as excel became to slow to handle the data. Most of the files I converted already but I have now to start with the more complex ones :-(.

    I have following in excel (workbook with tripdata I use for invoicing the haulier):
    Basis data (export from other system) : Trip ID, StartDate, StartTime, EndDate, EndTime
    Then formula fields : Weekday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

    Based on the basis data I first define the weekday on the startday, then I allocate the time to the correct day column or colums if EndDate > Startdate.

    Has anyone an idea how to do this via SQL or VBA ?

    Thanks already for the ideas/help

    Last edited by JanLofgren; 09-04-15 at 15:41. Reason: Solved

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Welcome to the site. Posting your solution may help somebody else in the future.

  3. #3
    Join Date
    Sep 2015
    Quote Originally Posted by pbaldy View Post
    Welcome to the site. Posting your solution may help somebody else in the future.
    Paul, you're right. It could help others.

    Below the SQL I translated from the formulas I had in excel

    UPDATE Table
    SET Table.Weekday = Weekday([StartDate],2),
    Table.Monday = IIf((Weekday([StartDate],2)=1 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=1 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)-6=1 And [EndTime]<[StartTime]),[EndTime],0),
    Table.Tuesday = IIf((Weekday([StartDate],2)=2 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=2 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)-1=2 And [EndTime]<[StartTime]),[EndTime],0),
    Table.Wednesday = IIf((Weekday([StartDate],2)=3 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=3 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=3 And [EndTime]<[StartTime]),[EndTime],0),
    Table.Thursday = IIf((Weekday([StartDate],2)=4 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=4 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=4 And [EndTime]<[StartTime]),[EndTime],0),
    Table.Friday = IIf((Weekday([StartDate],2)=5 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=5 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=5 And [EndTime]<[StartTime]),[EndTime],0),
    Table.Saturday = IIf((Weekday([StartDate],2)=6 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=6 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=6 And [EndTime]<[StartTime]),[EndTime],0),
    Table.Sunday = IIf((Weekday([StartDate],2)=7 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=7 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=7 And [EndTime]<[StartTime]),[EndTime],0);

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts