NESTED SQL SELECT STATEMENT QUESTION

jaigan1979,

OK here goes,

The goal is to return a unique Band refernce for each supplier (only 1 band per supplier). The band value is then used to query a rates table later on.

The pickUpDate is the start date chosen by the user (they are hiring a car)

the bkgDate is the date of the booking (the current date as they will be booking online)


The idea is we can set pick up date range periods which we can use to control the rates (different rates for different times of the year) - you will notice that no pickUpDate ranges (between pickUpStart and pickUpEnd) overlap for any one supplier

the bkgDateRanges allow us to run short term rate changes at various points of the year. You will notice that the bulk of these date ranges are the same (bkgDateStart - bkgDateEnd). This only really comes into play when the booking date (current date) falls inside one of the short term rate change periods. rowID 8 is a good example of this and represents a rate change wich only applies to bookings made between the 4th and 6th January 2005.


So the logic is as follows:

Find the closest pickUpDate range matches for all suppliers

then

filter down to closest bkgDateRange match for each supplier (if we are in a short term rate change period the correct row will superceed all others)


Basically that's as far as I've got. The statement orders the results so that the correct row for each supplier ID is on top (i.e. it's the first row for that supplier ID that I want). I now need to fileter out the non-relevant rows.


Does that make sense?



PJORDANNA

Lets take  output of ur select query as an starting point. So we have right now rows with repeating supplier values. You want to remove except first one from each supplier's rows. You will have to create a loop with cursor

declare a cursor which has the result of  your select query
declare one temporary table variable
now take one temporary variable to hold supplier id and initialize it with '' (empty string)
fetch one row from cursor
start while loop
check if currunt supplier id matches with temp variable. if they dont match (first time temp will be empty string so it will match) then insert into temporary table variable and assign current cursor row supplier to temporary variable in that if block. if they match dont do anything just skip.

This will give u ur first row of each supplier's rows into temporary table just return it and use it

Hope this will help you. If any better solution please let me know.

Naishal.

DECLARE @supplier char(4)
create table #outputTable ( rowID int, country char(3),destination char(3), bkgDateStart smalldatetime,
                  bkgDateEnd smalldatetime, pickUpstart smalldatetime, pickUpEnd smalldatetime,
                    carCode char(1), supplier char(4) )
declare myCursor CURSOR for
select distinct y. supplier from
(
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
) y

open myCursor
fetch next from myCursor into @supplier
while @@fetch_status = 0
 begin
      insert #outputTable
      SELECT top 1 x.*
        FROM
      (
            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
      ) x
        where x.supplier = @supplier
     fetch next from myCursor into @supplier

 end
close mycursor
deallocate myCursor

select * from #outputTable

could you give it a try ?

select b.*
from ch_bandChoice_ESP_IBZ b
inner join (
      select supplier, country, destination, (
            select top 1 rowid
            from ch_bandChoice_ESP_IBZ
            where supplier = a.supplier and country = a.country
            and destination = a.destination
            and '2005-01-06' between bkgDateStart  and bkgDateEnd
            and pickUpStart <= '2005-01-11'
            and pickUpEnd >= '2005-01-11'
            order by a.bkgDateEnd - bkgDateStart) as rowid
      from (
            select distinct supplier, country, destination
            from ch_bandChoice_ESP_IBZ a
      ) a
) c on b.rowid = c.rowid

Oops typo

select b.*
from ch_bandChoice_ESP_IBZ b
inner join (
      select supplier, country, destination, (
            select top 1 rowid
            from ch_bandChoice_ESP_IBZ
            where supplier = a.supplier and country = a.country
            and destination = a.destination
            and '2005-01-06' between bkgDateStart  and bkgDateEnd
            and pickUpStart <= '2005-01-11'
            and pickUpEnd >= '2005-01-11'
            order by a.bkgDateEnd - bkgDateStart) as rowid
      from (
            select distinct supplier, country, destination
            from ch_bandChoice_ESP_IBZ
      ) a
) c on b.rowid = c.rowid

Hilaire,

Getting the following error in Query Analyzer:



Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'bkgDateEnd'.




PJORDANNA

Karinloos,

Getting the following error in Query Analyzer:



Server: Msg 213, Level 16, State 5, Line 29
Insert Error: Column name or number of supplied values does not match table definition.




PJORDANNA

i think i understood the problem...

see if this one helps...

since you are half way thru your problem...i will continue from the half way...

select top 100 c.* from ch_bandChoice_ESP_IBZ c inner join

     (select top 1 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

-- i have added TOP 1 in the query next to the inner join...i havnt checked it thou...
--but not sure whether it will work..

or you can loop thr the result set obtained from this query and can eliminate the DUPLICATE entries of the SUPPLIER in ur front end

dim strSupplierID as string
do while not rs.eof
   
    if strsupplierID = rs.fields("Supplier") THEN
        'leave this part blank
    ELSE
        strsupplierID = rs.fields("Supplier")
        'when this is run the first time the control comes here and strSupplierID gets intialized
        'next time it comes in the loop, if it comes to this poin then that is for the next supplier...
        'if it doesn not then the supplier is the same...and hence you get only the first record from the query
        'as per this statement written here...you get ur desired records in this part..
    END IF
    rs.movenext

loop


let me know if this thing works...

regards
Jag

sorry type  (left out the BAND field in the temp table as it was en empty field and didnt include it in my test of your data )

DECLARE @supplier char(4)
create table #outputTable ( rowID int, country char(3),destination char(3), bkgDateStart smalldatetime,
                bkgDateEnd smalldatetime, pickUpstart smalldatetime, pickUpEnd smalldatetime,
                   carCode char(1), band char(5), supplier char(4) )
declare myCursor CURSOR for
select distinct y. supplier from
(
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
) y

open myCursor
fetch next from myCursor into @supplier
while @@fetch_status = 0
 begin
     insert #outputTable
     SELECT top 1 x.*
        FROM
     (
          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
     ) x
        where x.supplier = @supplier
     fetch next from myCursor into @supplier

 end
close mycursor
deallocate myCursor

select * from #outputTable

Hilaire,

Thanks for that. Much slicker solution and it works great!




PJORDANNA