Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL server query solution

Posted on 2004-10-08
Medium Priority
796 Views
Say I have a table Table1 with 3 cols
Table1
(
consultant_id  int,
date_value     datetime,
available_ind  char(1)
)
and if I am looking for max number of consecutive days that a consultant is available within a date range, is there any way to do so using a SELECT i.e. not having to cursor through.

sample data (ordered by consultant_id, date_value)

consultant_id    date_value    available_ind
1                     1/1/04           Y
1                     1/15/04         Y
1                     2/2/04           Y
1                     2/3/04           N
1                     2/11/04         Y
1                     3/1/04           Y
1                     3/11/04         N
1                     3/21/04         Y
1                     3/22/04         Y

so the result should be that the consultant is available for max of 3 consecutive days.
0
Question by:nishaj
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 12

Expert Comment

ID: 12262261

select consultant_id, max(datediff(d, date_value1, date_value2))
from data d1 inner join data d2 on d1.consultant_id = d2.consultant_id and d1.date_value <= d2.date_value
where not exists(select 1 from data d3 where d3.consultant_id = d1.consultant_id and d3.date_value between d1.date_value and d2.date_value and d.available_ind = 'N')
0

LVL 12

Expert Comment

ID: 12262275
forgot the  group by clause:

select d1.consultant_id, max(datediff(d, d1.date_value, d2.date_value))
from data d1 inner join data d2 on d1.consultant_id = d2.consultant_id and d1.date_value <= d2.date_value
where not exists(select 1 from data d3 where d3.consultant_id = d1.consultant_id and d3.date_value between d1.date_value and d2.date_value and d.available_ind = 'N')
group by consultant_id
0

Author Comment

ID: 12262518
did not work.
sample data
1      1/1/2004      Y
1      1/2/2004      Y
1      1/3/2004      N
1      1/4/2004      Y
1      1/5/2004      Y
1      1/6/2004      Y
1      1/7/2004      Y
1      1/8/2004      N
2      1/1/2004      Y
2      1/2/2004      Y
2      1/3/2004      N
corrected the alias in the last row to be d3? is that what was intended.

results
1              3
2              1
0

LVL 12

Accepted Solution

monosodiumg earned 750 total points
ID: 12262702
So we are exactly one day short?

select d1.consultant_id, max(datediff(d, d1.date_value, d2.date_value)) + 1
from data d1 inner join data d2 on d1.consultant_id = d2.consultant_id and d1.date_value <= d2.date_value
where not exists(select 1 from data d3 where d3.consultant_id = d1.consultant_id and d3.date_value between d1.date_value and d2.date_value and d3.available_ind = 'N')
group by consultant_id

>corrected the alias in the last row to be d3? is that what was intended.
Yes.
0

Author Comment

ID: 12262726
Another possible way..
SELECT
cons_key,
max (count_diff)
FROM
(
SELECT
c1.consultant_key cons_key,
(DATEDIFF(DAY,c1.date_value, min (c2.date_value))) count_diff
FROM
Cal1 c1,
cal1 c2
WHERE
c1.avail_ind = "Y"
AND
c2.date_value > c1.date_value
AND
c2.avail_ind = "N"
AND
c1.consultant_key = c2.consultant_key
group by
c1.consultant_key,
c1.date_value
) x
group by cons_key
0

Author Comment

ID: 12262750
Thanks monosodiumg.
0

LVL 12

Expert Comment

ID: 12265788
I think you need to check your alternative solution on a set where the longest available period is on after which there are no N days e.g.:
1     1/1/2004     Y
1     1/2/2004     Y
1     1/3/2004     N
1     1/4/2004     Y
1     1/5/2004     Y
1     1/6/2004     Y
1     1/7/2004     Y
2     1/1/2004     Y
2     1/2/2004     Y

On this set your inner select will fail to pick up the period from 1/4 to 1/7 for consultant 1 because of the  c2.avail_ind = "N"  clause.

I've realised I could add a clause to mine to make it slightly faster:
select d1.consultant_id, max(datediff(d, d1.date_value, d2.date_value)) + 1
from data d1 inner join data d2 on d1.consultant_id = d2.consultant_id and d1.date_value <= d2.date_value
where not exists(select 1 from data d3 where d3.consultant_id = d1.consultant_id and d3.date_value between d1.date_value and d2.date_value and d3.available_ind = 'N')
where d1.available_ind = 'Y' and d2.available_ind = 'Y'
group by consultant_id
0

Author Comment

ID: 12276158
Yes you are right, monosodiummg, thanks for pointing that out.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
###### Suggested Courses
Course of the Month6 days, 18 hours left to enroll