Solved

SQL server query solution

Posted on 2004-10-08
8
795 Views
Last Modified: 2012-05-05
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
Comment
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
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:monosodiumg
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

by:monosodiumg
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

by:nishaj
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 12

Accepted Solution

by:
monosodiumg earned 250 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

by:nishaj
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

by:nishaj
ID: 12262750
Thanks monosodiumg.
0
 
LVL 12

Expert Comment

by:monosodiumg
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

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question