Concatenation of three fields with conditional parenthesis around last two fields for a field in a View
-
The single record view uses this:
ISNULL(dbo.APD_Stips1.Stip_Abv, N' ') + N' ' + N'(' + ISNULL(dbo.APD_Stips2.Dt_Start, N' ') + N' ' + ISNULL(dbo.APD_Stips2.Dt_End, N' ' + N')') + N')'
SQL Server 2008r2
Most of the three fields have a Stips Code, a begin period, a end period.
In most cases the code above returns something like this: BO (03/01 08/15)
In the case where there is a code, but the Begin period and End period are empty, it returns one of the two formats.
Actual example: mostly this PM ( ) and sometimes this CL ( ))
The desired output would be this: PM
In MSACCESS or a later version of SQL the IF statement would make this easy.
How would this be done in SQL Server 2008r2?
Later these fields are consumed by another query that groups-by ID_Well. The results look like this.
-
If you can have either start date and end date or no dates at all, you can make it simpler.
ISNULL(dbo.APD_Stips1.Stip_Abv + N' ', N' ') + ISNULL( N'(' + dbo.APD_Stips2.Dt_Start + N' ' + dbo.APD_Stips2.Dt_End + N')', N'')
If not, you can play with the CASE or NULLIF().
-
Full example:
CREATE TABLE dbo.SampleData(
Stip_Abv nvarchar(10),
Dt_Start nvarchar(10),
Dt_End nvarchar(10))
INSERT INTO SampleData
VALUES( 'PM', '05/15', '06/20'),
( 'PM', NULL, '06/20'),
( 'PM', '05/15', NULL),
( 'PM', NULL, NULL)
SELECT ISNULL(Stip_Abv + N' ', N' ') + ISNULL( N'(' + Dt_Start + N' ' + Dt_End + N')', N''),
ISNULL(Stip_Abv + N' ', N' ') + ISNULL( N'(' + NULLIF( ISNULL( Dt_Start, N'N/A') + N' ' + ISNULL( Dt_End, N'N/A'), N'N/A N/A') + N')', N'')
FROM SampleData;
DROP TABLE dbo.SampleData;
Please note the way that you're supposed to provide sample data so we can work on it.
Try to remove the 3-part reference to your columns by using table alias.
-
Wow, it looks so simple now! Thanks!
Sorry to take so long to get back. I inherited this table in MSAccess and migrated it to SQL Server.
Just realized that your ISNull sometimes returns a () and most of the time not.
I am stinking (err. thinking) that the previous front-end design allowed random empty characters.
So, you actually solved a different problem as well.
Sorry I didn't get sample data again. I really need to figure this out.
In this case, the data would have not been conistant.
This is an obscure lightly corner of the database that needs some care and attention.
It wasn't broken, it just isn't up to par until I had time to look at it.
Thanks again for your help.
-
Quick followup to the random parens.
MSAccess is using the SQL Server Native Cleint 11.0 to provide linked tables.
On the MSAccess side the empty Date (that are really Characters to denote a schedule) all show up as Null.
On the SQL Server side the empty fields using a Len function shows up as either 0 Len or NULL.
The 0 Len data failed the ISNULL T-SQL and did what it was suppose to do - display the parens.
Realizing this, I can update all of the 0 Len Character fields to NULL and this solution works perfectlly.
This might be of interest to anyone using AZURE with a front-end that links with the standard SQL Server Native Client 11.0
The solution above solved my problem. It also helped me solve a problem nobody realized existed until we needed the code.
Thanks so much!
Next time, I promise to include the create table with sample data!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply