Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drprune
Starting Member
4 Posts |
 Posted - 2007-08-09 : 09:37:29
|
| I have written a Web application for our Customer Service department that replaces the paper sign-in log sheet that they have traditionally used for walk-in customers. The app includes an ASP page that reproduces the sign-in log sheet in the form of an HTML table populated from a SQL Server table. The database includes 3 datetime fields (SignInTime, RepTimeIn, RepTimeOut). When a new customer comes in, they are entered into the system by a receptionist; as part of this process, he/she enters a value into the SignInTime field. Values are entered into the other 2 datetime fields later by the customer service rep who processes the customer.Currently, I am using the the following SELECT statement to populate the customer sign-in log page:SELECT ID, CreatedDate, CustName, convert(varchar,SignInTime,108) Sign, Reason, RepName, convert(varchar,RepTimeIn,108) RepIn, convert(varchar,RepTimeOut,108) RepOut, Notes, convert(varchar,RepTimeIn - SignInTime,108) WaitTime, convert(varchar,RepTimeOut - RepTimeIn,108) RepTime FROM " & DBTableName WHERE CreatedDate = '" & Date() & "' ORDER BY ID DESC"In addition to SQL table data, this SELECT statement produces 2 calculated values - "WaitTime" and "RepTime" - from the 3 datetime fields, and these new values are also written to the HTML table. In its current form, the calculated values from the SELECT statement are always written to the HTML table. However, I would like to do the following for each record record returned by the query:1. Calculate and write "WaitTime" to the HTML table ONLY after a value has been entered into the RepTimeIn field.2. Calculate and write "RepTime" to the HTML table ONLY after a value has been entered into the RepTimeOut field.I thought that CASE might be the approach to take, but I have been unable to produce the desired results. Can anyone help? Thanks. |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2007-08-09 : 14:06:52
|
Assuming RepTimeIn & RepTimeOut will be null if there is no value entered, you can try this.CASE WHEN RepTimeIn IS NULL THEN '' ELSE convert(varchar,RepTimeIn - SignInTime,108) END AS WaitTime,CASE WHEN RepTimeOut IS NULL THEN '' ELSE convert(varchar,RepTimeOut - RepTimeIn,108) END AS RepTime You should use stored procedures for this kind of queries and avoid concatinating queries.ThanksKarunakaran |
 |
|
|
|
|
|
|
|
Conditional SELECT with CASE(?)