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
2 1577 Delerna 1,134
Recognized Expert Top Contributor
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 1,134
Recognized Expert Top Contributor
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bill |
last post by:
Hello out there;
This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.
I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ......
|
by: Melissa |
last post by:
I have this table:
TblProjectYear
ProjectYearID
ProjectYearStartDate
ProjectYearEndDate
The Project Year will always span across December 31; for example 9/1/04 to
6/30/05. How do I build a...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
...
|
by: Jeff |
last post by:
I have a system on a network and want to determine if anyone is currently
connected to the back-end files.
An interesting twist is that I have noticed that some users can be connected
(have the...
|
by: Jana |
last post by:
Greetings Access Gurus! I am working on an app to send batch
transactions to our bank, and the bank requires that we place an
effective date on our files that is 'one business day in the future,...
| |
by: jennwilson |
last post by:
I am trying to generate a report based on a query that will list any records where an individual has a date listed that matches the specified time for one or both of the date fields. The two fields...
|
by: creative1 |
last post by:
Hello everyone.
I am experiencing a strange problem that I can't fix on my own. I think I need expert's suggestions for this.
The problem is:
I want to print account statement (or any other...
|
by: Jerry911 |
last post by:
I have a form that uses a pick list to select a specified date/time range based on the case value. Case 1-7 have pre-defined date/time functions, but case 8 is for a custom date/time range. I only...
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.
...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |