Determine which fields to select based on date

[Trmbne2000]

Hi, I'm developing a service provider listing service. Part of the specification is to have both an address and a 'seasonal address' for each listing. The database stores the start and end months during which the seasonal address should be displayed as the address. Right now I am just grabbing everything out of the database, and using ASP to determine which address to display (seasonal if it is within the month range, normal otherwise [this works fine]).

However, now the client wants me to change how the records are sorted. They want everything to sort by State, then City, then Company Name. The problem is that I have two fields for State (seasonal and normal), and same for City, so I can't figure out how to sort them correctly. Each record has its own value for when the seasonal address should start and end.

I've been experimenting with the following SQL statement, but it doesn't work right:

SELECT C.* FROM ServiceMap.tblCaptains C, ServiceMap.tblCaptRegionLink R WHERE C.Status = '1' AND C.ListingStart <= '8/8/2008' AND C.ListingEnd >= '8/8/2008' AND R.Region = 'SE' AND R.Capt_ID = C.ID ORDER BY R.InRegn DESC,
CASE WHEN S_From <> '' AND S_From < S_To AND (DATEPART(m,getDate()) >= S_From AND DATEPART(m,getDate()) <= S_To) THEN S_State
WHEN S_From <> '' AND S_From < S_To AND (DATEPART(m,getDate()) < S_From OR DATEPART(m,getDate()) > S_To) THEN State
WHEN S_From <> '' AND S_From > S_To AND (DATEPART(m,getDate()) <= S_From AND DATEPART(m,getDate()) >= S_To) THEN State
WHEN S_From <> '' AND S_From > S_To AND (DATEPART(m,getDate()) > S_From OR DATEPART(m,getDate()) < S_To) THEN S_State 
WHEN S_From = '' THEN State
END
ASC, C.Company ASC, C.FName ASC

Does anyone have any ideas?
Thanks,
Andrew

[Delerna]

I suggest you take the address display decision away from asp and move it to the query.
What I mean is like this
sorry to be cryptic but I can't determine enough from your post to show my meaning clearer
I am assuming you are comparing the seasonal month range with the month of the current date.

select YourOtherFields,
          case when datepart(m,getdate())>=r.startmonth 
                    and datepart(m,getdate())<=r.endmonth
          then r.address else c.address end as address,
 
          case when datepart(m,getdate())>=r.startmonth 
                    and datepart(m,getdate())<=r.endmonth
          then r.state else c.state end as state
 
from tblCaptains c
join tblCaptRegionLink r on YourJoinConditions
 

Now that you only have 1 address field and 1 state field.
sorting should be much easier.

[Delerna]

Actually, reading your post again you are using S_From and S_To for the month comparison, so use them!

select YourOtherFields,
          case when datepart(m,getdate())>=S_From 
                    and datepart(m,getdate())<=S_To
          then S_Address else Address end as address,
 
          case when datepart(m,getdate())>=S_From
                    and datepart(m,getdate())<=S_To
          then S_State else state end as State
 
from tblCaptains c
join tblCaptRegionLink r on YourJoinConditions