NESTED SQL SELECT STATEMENT QUESTION

Hi Experts,

I have the following table:

rowID      country      destination      bkgDateStart      bkgDateEnd      pickUpStart      pickUpEnd           carCode      band      supplier
1      ESP      IBZ      01/12/2004      31/12/2005      01/01/2005      04/01/2005            A      ACH1
2      ESP      IBZ      01/12/2004      31/12/2005      05/01/2005      08/01/2005            B      ACH1
3      ESP      IBZ      01/12/2004      31/12/2005      09/01/2005      12/01/2005            C      ACH1
4      ESP      IBZ      01/12/2004      31/12/2005      13/01/2005      16/01/2005            D      ACH1
5      ESP      IBZ      01/12/2004      31/12/2005      17/01/2005      20/01/2005            E      ACH1
6      ESP      IBZ      01/12/2004      31/12/2005      21/01/2005      25/01/2005            F      ACH1
7      ESP      IBZ      01/01/2005      10/01/2005      01/01/2005      31/12/2005            G      ACH1
8      ESP      IBZ      04/01/2005      06/01/2005      01/01/2005      31/12/2005            H      ACH1
9      ESP      IBZ      01/12/2004      31/12/2005      01/01/2005      04/01/2005            A      ACH2
10      ESP      IBZ      01/12/2004      31/12/2005      05/01/2005      08/01/2005            B      ACH2
11      ESP      IBZ      01/12/2004      31/12/2005      09/01/2005      12/01/2005            C      ACH2
12      ESP      IBZ      01/12/2004      31/12/2005      13/01/2005      16/01/2005            D      ACH2
13      ESP      IBZ      01/12/2004      31/12/2005      17/01/2005      20/01/2005            E      ACH2
14      ESP      IBZ      01/12/2004      31/12/2005      21/01/2005      25/01/2005            F      ACH2


And I am querying it using the nested SQL statement below. The statement first chooses the rows where "pickUpStart" and "pickUpEnd" are closest to the date specified (i.e. the date specified falls between those dates), then it chooses the closest match for each of those rows to "bkgDateStart" and "bkgDateEnd" and returns the result. The statement is as follows:



select top 100 c.* from ch_bandChoice_ESP_IBZ c inner join

      (select a.pickUpStart, a.pickUpEnd, a.bkgDateStart, a.bkgDateEnd AS bkgDateEnd FROM
      
            (select * from ch_bandChoice_ESP_IBZ where (pickUpStart <= CONVERT(DATETIME, '2005-01-11 00:00:00', 102)) and (pickUpEnd >= CONVERT(DATETIME, '2005-01-11 00:00:00', 102)))
      
      a where CONVERT(DATETIME, '2005-01-06 00:00:00', 102) between a.bkgDateStart  and a.bkgDateEnd group by a.pickUpStart,a.pickUpEnd,a.bkgDateStart,a.bkgDateEnd)

b on c.pickUpStart=b.pickUpStart and c.pickUpEnd=b.pickUpEnd and c.bkgDateStart=b.bkgDateStart and c.bkgDateEnd=b.bkgDateEnd order by (c.bkgDateEnd-c.bkgDateStart), supplier


This particular statement returns the following result:


rowID      country      destination      bkgDateStart      bkgDateEnd      pickUpStart      pickUpEnd                      carCode      band      supplier
8      ESP      IBZ      2005-01-04 00:00:00      2005-01-06 00:00:00      2005-01-01 00:00:00      2005-12-31 00:00:00             H      ACH1
7      ESP      IBZ      2005-01-01 00:00:00      2005-01-10 00:00:00      2005-01-01 00:00:00      2005-12-31 00:00:00             G      ACH1
3      ESP      IBZ      2004-12-01 00:00:00      2005-12-31 00:00:00      2005-01-09 00:00:00      2005-01-12 00:00:00             C      ACH1
11      ESP      IBZ      2004-12-01 00:00:00      2005-12-31 00:00:00      2005-01-09 00:00:00      2005-01-12 00:00:00             C      ACH2



I now need to continue with the nested statement in order to filter the result down to distinct supplier codes returning only the top row for each supplier code. In this example the final returned rows should be:

rowID      country      destination      bkgDateStart      bkgDateEnd      pickUpStart      pickUpEnd                      carCode      band      supplier
8      ESP      IBZ      2005-01-04 00:00:00      2005-01-06 00:00:00      2005-01-01 00:00:00      2005-12-31 00:00:00             H      ACH1
11      ESP      IBZ      2004-12-01 00:00:00      2005-12-31 00:00:00      2005-01-09 00:00:00      2005-01-12 00:00:00             C      ACH2



I.E. the first row for supplier ACH1 and the first row for supplier ACH2


I am having difficulty in doing this. Do any of you have any ideas?



Thanks,




PJORDANNA