Start and end of holidays for a customer

[webisti]

Hi


I do have a table with
CUSTOMER DATE
customer A 11/06/2011
customer A 12/06/2011
customer A 13/06/2011
customer A 14/06/2011
customer A 15/06/2011
customer B 14/06/2011
customer B 15/06/2011
customer C 15/06/2011
customer C 16/06/2011
customer C 17/06/2011
customer C 18/06/2011
customer C 19/06/2011
customer C 20/06/2011

I am trying to make a query that shows me:
Customer Start Holiday end Holiday
customer A 12/06/2011 15/06/2011
customer C 15/06/2011 20/06/2011

Can smb help me out.

Thanks

[pbaldy]

Try

SELECT Customer, Min([DATE]) AS StartHoliday, Max([DATE]) AS EndHoliday
FROM TableName
GROUP BY Customer

[webisti]

I tried that before but did not helped since many customers do have just two days closed (SAT,SUN) what I want is the query to show me that the customer is closed for more than ex 3 consecutive days.

[webisti]

Here is the sql of the file resulting in a kind of pivot table where customer is closed a certain date of the period, for some is 1 or 2 days for others 10-12 days.
SELECT FG_MI_TBL_W.customer, FG_MI_TBL_W.[10/06/2011] AS 10JUNE, FG_MI_TBL_W.[11/06/2011], FG_MI_TBL_W.[12/06/2011] AS 12JUNE, FG_MI_TBL_W.[13/06/2011] AS 13JUNE, FG_MI_TBL_W.[14/06/2011] AS 14JUNE, FG_MI_TBL_W.[15/06/2011] AS 15JUNE, FG_MI_TBL_W.[16/06/2011] AS 16JUNE, FG_MI_TBL_W.[17/06/2011] AS 17JUNE, FG_MI_TBL_W.[18/06/2011] AS 18JUNE, FG_MI_TBL_W.[19/06/2011] AS 19JUNE, FG_MI_TBL_W.[20/06/2011] AS 20JUNE, FG_MI_TBL_W.[21/06/2011] AS 21JUNE, FG_MI_TBL_W.[22/06/2011] AS 22JUNE, FG_MI_TBL_W.[23/06/2011] AS 23JUNE, FG_MI_TBL_W.[24/06/2011] AS 24JUNE, FG_MI_TBL_W.[25/06/2011] AS 25JUNE, FG_MI_TBL_W.[26/06/2011] AS 26JUNE, FG_MI_TBL_W.[27/06/2011] AS 27JUNE, FG_MI_TBL_W.[28/06/2011] AS 28JUNE, FG_MI_TBL_W.[29/06/2011] AS 29JUNE, FG_MI_TBL_W.[30/06/2011] AS 30JUNE, FG_MI_TBL_W.[01/07/2011] AS 01JULY, FG_MI_TBL_W.[02/07/2011] AS 02JULY, FG_MI_TBL_W.[03/07/2011] AS 03JULY, FG_MI_TBL_W.[04/07/2011] AS 04JULY, FG_MI_TBL_W.[05/07/2011] AS 05JULY, FG_MI_TBL_W.[06/07/2011] AS 06JULY, FG_MI_TBL_W.[07/07/2011] AS 07JULY, FG_MI_TBL_W.[08/07/2011] AS 08JULY, FG_MI_TBL_W.[09/07/2011] AS 09JULY, FG_MI_TBL_W.[10/07/2011] AS 10JULY, FG_MI_TBL_W.[11/07/2011] AS 11JULY, FG_MI_TBL_W.[12/07/2011] AS 12JULY, FG_MI_TBL_W.[13/07/2011] AS 13JULY
FROM FG_MI_TBL_W;

[pbaldy]

So use a DateDiff() function on the min and max and use it in the criteria.

[webisti]

still it did not worked as I want...
Can u post SQL if that is possible.

[pbaldy]

Without access to the data, this is a wild guess

SELECT Customer, Min([DATE]) AS StartHoliday, Max([DATE]) AS EndHoliday
FROM TableName
GROUP BY Customer
HAVING DateDiff("d", Min([DATE]), Max([DATE])) > 2

[webisti]

Id did not worked out.
here is a sample of data I do have
CustomerSTART_DTAdam10-Jun-11Adam11-Jun-11Adam12-Jun-11Adam13-Jun-11Adam14-Jun-11Adam15-Jun-11Adam16-Jun-11Adam17-Jun-11Adam18-Jun-11Adam19-Jun-11Adam20-Jun-11Adam21-Jun-11Adam22-Jun-11Adam23-Jun-11Adam24-Jun-11Adam25-Jun-11Adam26-Jun-11Adam03-Jul-11Adam10-Jul-11Adam17-Jul-11Adam24-Jul-11Adam31-Jul-11Adam07-Aug-11Adam14-Aug-11Adam15-Aug-11Adam21-Aug-11Adam28-Aug-11Adam04-Sep-11Adam11-Sep-11Adam18-Sep-11Adam25-Sep-11

What I want is that the query finds that the customer is closed for more than three consecutive days closed and it another column I telling me start of holiday and end of holiday. Is that possible?
Thanks

[webisti]

Here is the correct format of the table:
Customer START_DT
Adam 10-Jun-11
Adam 11-Jun-11
Adam 12-Jun-11
Adam 13-Jun-11
Adam 14-Jun-11
Adam 15-Jun-11
Adam 16-Jun-11
Adam 17-Jun-11
Adam 18-Jun-11
Adam 19-Jun-11
Adam 20-Jun-11
Adam 21-Jun-11
Adam 22-Jun-11
Adam 23-Jun-11
Adam 24-Jun-11
Adam 25-Jun-11
Adam 26-Jun-11
Adam 03-Jul-11
Adam 10-Jul-11
Adam 17-Jul-11
Adam 24-Jul-11
Adam 31-Jul-11
Adam 07-Aug-11
Adam 14-Aug-11
Adam 15-Aug-11
Adam 21-Aug-11
Adam 28-Aug-11
Adam 04-Sep-11
Adam 11-Sep-11
Adam 18-Sep-11
Adam 25-Sep-11

[pbaldy]

What would be the desired result based on that data?

[webisti]

The desred result would come out in a query telling

Customer Holidays
Adam 10 until 26 June (criteria is that the customer is on holidays for more than Three consecutive days)

[pbaldy]

Your new data isn't like the original example. Because you've got other records for the customer in there, you can't simply pull the min and max. What would happen if the customer had 2 holidays that lasted more than 3 days? Offhand I can't think of an SQL solution. I suspect you're going to have to build a function that loops through a given customer's records, looking for consecutive dates, using variables to keep track of the relevant items.