1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel overlap formula to (MS) SQL ?

Discussion in 'Software Development' started by Gram123, Apr 1, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hellos,
    I am trying to transfer some Excel VBA macro stuff back to the source (MS) SQL view.

    Records in the View are items currently on site, or scheduled to arrive.
    All records have an Estimated Time of Arrival and Estimated Time of Departure (they haven't arrived yet)
    Some records will have an Actual Time of Arrival but no Actual Time of Departure (they're on site now)
    Records with an Actual Time of Departure have already left site, so these are omitted from the results.

    What I need to do is add a column to the View, and assign a value (e.g. "CLASH") to any records where the current schedule means that if something is not changed, there will be an overlap. In practice, this will be specific to locations on site, but we won't worry about that here for the sake of simplicity.

    So overlaps / clashes occur when:
    1) Item A is on site now - it has an ATA of now or earlier, it has an ETD (which could potentially be earlier than now), but it does not have an ATD.
    Item B is scheduled to arrive - it does not yet have an ATA, but the period between it's ETA and ETD overlaps the period between Item A's ATA and ETD.
    Item B's ETA and ETD could potentially both be earlier than now, but in most cases, the ETD will be later than now.

    2) Item A and Item B are both scheduled to arrive. Neither has an ATA yet, but the period between Item A's ETA and ETD overlaps the period between Item B's ETA and ETD.


    Items cannot physically be on site (at the same location) at the same time, so any record with an ATD can be ignored, and if Item A and Item B both have an ATA, these can be ignored too.


    In Excel, say I had exported 10 records (rows).
    Col A = ETA
    Col B = ETD
    Col C = ATA
    Col D = ATD
    Col E = Item ID
    Col F = Clash formula

    So the formula in cell F1 would be something like:
    (though obviously I wouldn't use a static reference to the last row, in case the formula had to be ran on larger samples).

    So.................
    How do I do this in SQL?
    How do I check each record against in a View against all the other records in the same View, and assign a value if there is any overlap?

    My SQL knowledge is limited, so any help is appreciated!

    Cheers,
    Gram123
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Just to get it started lets call the sql table Shipping. The basic statement to view that table is:
    Select *
    From Shipping

    Next would be to set what I call the 'Universe'.
    Select ETA, ETD, ATA
    From Shipping
    Where ETA > 1 and ATD is NULL

    If you table is large, while developing\testing its sometime useful to use the TOP clause
    Select TOP (1000) *
    From Shipping

    I'll stop there as I'm unsure of where you are with SQL knowledge. I'm not sure what you mean by
     
  3. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,393
    Do you have a test environment for this?

    Are you intending to maintain the 'clash' instances, or are you expecting to only see them when querying?

    I'm assuming you mean to take each row's item values and iterate through the rest of the rows in the table to determine clashes. Is this the goal?

    It would help if you could provide 10 rows of data or so.
     
  4. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    My SQL knowledge is limited, but I'm working with a big existing View with various table joins and Group Bys, and I pretty much understand what's already there.

    What I meant by that bit you quoted was that if 2 Items are already on site (have ATAs, but not ATDs), then they can't physically be in the same location, so we don't need to worry about them. Someone must have relocated one of the items.

    So "clashes" can only occur if one or both items are yet to arrive (one or both must not have an ATA). It's all about scheduling clashes - i.e. a heads up that if something isn't changed, we're going to have a problem (as opposed to a physical clash of trying to put 2 items in that have already arrived into the same location).

    I'm just opening the View scripted as an ALTER TO, and then commenting out the Alter > Go bits. So in that sense, I can test the results are correct.

    We have an Excel doc that is linked to an SQL temp Table. The results of the View I'm working on is dropped into this temp table by a Job, which runs daily (in the middle of the night).
    So, the Clash values need to make it to the temp table, and ultimately Excel, but they're not saved permanently.


    Absolutely.

    I'll get back to you on this later today...
     
  5. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Ok, I've added some sample data in the attached Excel file.
    The real records have many more fields than this, but they're not relevant to determining the clashes.

    Treating the attached as though it is the View results, as ran at 08:00 on 02/04/2015.

    1) Item2 and Item3 were estimated to arrive at the same location (9) at overlapping times, so initially they would have been marked as CLASH.
    Then, Item2 arrived (got an ATA). Whilst on site, its ATD to ETD range still overlapped with Item3's ETA to ETD range, so both records were still marked as CLASH.
    Then Item2 departed (before Item3 even arrived). As soon as it got an ATD, Item2 was no longer able to clash with anything, so the CLASH value was removed from both Item2 and Item3.

    2) Item4 and Item5 were estimated to arrive at the same location (6), but at non-overlapping times, so initially, neither was marked as CLASH.
    Then Item4 arrived several hours late. Right at that point, there is still no Clash, but the user knows that there is no way Item4 will depart at the ETD (which is right now), so he will modify this date/time to, say, 02/04/2015 13:00.
    This then causes an overlap, because Item5 is estimated to arrive at the same location at 09:00, so both records would then be marked CLASH.

    3) Item9 and Item10 haven't arrived yet, but they are both scheduled to arrive at the same location at overlapping times, so both are marked CLASH.


    I hope this makes sense......

    Cheers,
    G
     

    Attached Files:

  6. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,393
    Thanks for the info and sample. I'll work up something for you tomorrow and explain it so you can reuse.
     
  7. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,393
    Ok this should get you started.
    1) Run the query below as it sits. This will give you all your items + 2 additional rows where clashes occured
    2) Remove 'distinct' and uncomment the commented lines. This will show you the comparisons.

    I didn't intend for this to be done, just getting you started and something to learn with. Make sure you update the from and join view names.

    Good luck-

    Code:
    SELECT DISTINCT a.[Zone]
          ,a.[Location]
          ,a.[Item Name]
          ,a.[Item ID]
          ,a.[Company]
          ,a.[ETA]
          ,a.[ETD]
          ,a.[ATA]
          ,a.[ATD]
    	  --,DATEDIFF(hh,a.ETA,b.ETA) AS DIFF
    	  ,CASE WHEN DATEDIFF(hh,a.ETA,b.ETA) <= 1 AND DATEDIFF(hh,a.ETA,b.ETA) >= (-1) AND b.ATA = ''
    	       THEN 'CLASH' END AS 'CLASH'
    	  --,b.[Item ID] AS 'CLASHING WITH'
      FROM [test].[dbo].[testtbl] a
      LEFT JOIN [test].[dbo].[testtbl] b ON a.[Item ID]!=b.[Item ID]
     
  8. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    I've found a solution to this that works in combination with my existing code.
    It's slow to run, though, so we're looking at migrating the whole job to another platform.

    Thanks for your help anyway!

    G
     
  9. lighttech

    lighttech

    Joined:
    May 6, 2015
    Messages:
    10
    I've found a much easier way to deal with the whole SQL issue with a company called datapine. Take a look. They use some excellent data visualization tools to help you get a better understanding of exactly what's going on. The best thing is you don't have to be a technical whizz to use it. It's a really useful tool!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1145818

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice