nishaj
asked on
SQL server query solution
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.
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.
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Another possible way..
SELECT
cons_key,
max (count_diff)
FROM
(
SELECT
c1.consultant_key cons_key,
(DATEDIFF(DAY,c1.date_valu
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
SELECT
cons_key,
max (count_diff)
FROM
(
SELECT
c1.consultant_key cons_key,
(DATEDIFF(DAY,c1.date_valu
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
ASKER
Thanks monosodiumg.
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
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
ASKER
Yes you are right, monosodiummg, thanks for pointing that out.
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')