# Excel overlap formula to (MS) SQL ?



## Gram123 (Mar 15, 2001)

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:


> =IF(C1>0,SUMPRODUCT((C1<=$B$1:$B$10)*(B1>=$C$1:$C$10))>1,SUMPRODUCT((A1<=$B$1:$B$10)*(B1>=$A$1:$A$10))>1)


(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


----------



## draceplace (Jun 8, 2001)

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


> and if Item A and Item B both have an ATA, these can be ignored too.


----------



## ckphilli (Apr 29, 2006)

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.


----------



## Gram123 (Mar 15, 2001)

draceplace said:


> Just to get it started lets call the sql table Shipping.
> I'll stop there as I'm unsure of where you are with SQL knowledge. I'm not sure what you mean by
> 
> 
> ...


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).



ckphilli said:


> Do you have a test environment for this?


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.



ckphilli said:


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


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.



ckphilli said:


> 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?


Absolutely.



ckphilli said:


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


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


----------



## Gram123 (Mar 15, 2001)

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


----------



## ckphilli (Apr 29, 2006)

Thanks for the info and sample. I'll work up something for you tomorrow and explain it so you can reuse.


----------



## ckphilli (Apr 29, 2006)

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-


```
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]
```


----------



## Gram123 (Mar 15, 2001)

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


----------



## lighttech (May 6, 2015)

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!


----------

