June 23, 2014 at 3:34 am
Hi,
I am using a case statement to define what output should be given based on a date in a varchar field.
I am finding if a free text field holds the word ‘CED:’ then applying rules from it:
If CED is greater than today, display CED.
If CED is less than or equal to today, add months from initial term to today.
If CED is not in string, add months from initial term to today.
If comment field is null, add months from initial term to today.
Else be blank (this is never true as rules mean it would be in above)
select
case when cs.COMMENT like '%CED:%' and (convert(varchar(10),left(SUBSTRING (cs.comment,PATINDEX('%CED:%',cs.comment)+4,LEN(cs.Comment)),10),103) > convert(varchar(10),getdate(),103))
then convert(varchar(10),left(SUBSTRING (cs.comment,PATINDEX('%CED:% ',cs.comment)+4,LEN(cs.Comment)),10),103)
when cs.COMMENT like '%CED:%' and (convert(varchar(10),left(SUBSTRING (cs.comment,PATINDEX('%CED:%',cs.comment)+4,LEN(cs.Comment)),10),103) <= convert(varchar(10),getdate(),103))
then convert(varchar(10),DATEADD(mm,cs.INITIALTERM,GETDATE()),103)
when cs.COMMENT not like '%CED:%'
then convert(varchar(10),DATEADD(mm,cs.INITIALTERM,GETDATE()),103)
when cs.COMMENT is null
then convert(varchar(10),DATEADD(mm,cs.INITIALTERM,GETDATE()),103)
else '' end as [Contract End Date]
FROMC_COMPANYSERVICE cs with (nolock)
The problem is trying to perform greater than on varchar fields.
Is there any way to perform these calculations within this case statement. I can always output to Excel (which is the final destination) and do in there but would rather do it all in SQL.
I tried to amend the queries, looked across different forums with no luck. I’ve even tried to alter the column but getting The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Many Thanks,
Gwyn
June 23, 2014 at 3:38 am
In what format are the dates stored in the table ? Can you please post some sample data ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 23, 2014 at 3:53 am
Hi Sachin,
They're currently stored in a varchar(max) field.
Some examples
IDCOMMENT
1New service
2NULL
3CED:01/09/2014
4CED:01/10/2007
5Contract renewed CED:31/10/2016
Thanks,
Gwyn
June 23, 2014 at 6:37 am
-- Simplify THE query using (CROSS) APPLY:
select case
when cs.COMMENT like '%CED:%' and DateFilter > convert(varchar(10),getdate(),103) then DateFilter
when cs.COMMENT like '%CED:%' and DateFilter <= convert(varchar(10),getdate(),103) then DateResult
when cs.COMMENT not like '%CED:%' then DateResult
when cs.COMMENT is null then DateResult
else '' end as [Contract End Date]
FROM C_COMPANYSERVICE cs with (nolock)
CROSS APPLY (
SELECT
DateFilter = convert(varchar(10),left(SUBSTRING (cs.comment,PATINDEX('%CED:%',cs.comment)+4,LEN(cs.Comment)),10),103),
DateResult = convert(varchar(10),DATEADD(mm,cs.INITIALTERM,GETDATE()),103)
) x
-- Looks like the CASE can be simplified to this:
SELECT [Contract End Date] = CASE
WHEN cs.COMMENT LIKE '%CED:%' AND CAST(DateFilter AS DATE) > CAST(GETDATE() AS DATE) THEN DateFilter
ELSE DateResult END
FROM C_COMPANYSERVICE cs with (nolock)
CROSS APPLY (
SELECT
DateFilter = CONVERT(VARCHAR(10),left(SUBSTRING (cs.comment,PATINDEX('%CED:%',cs.comment)+4,LEN(cs.Comment)),10),103),
DateResult = CONVERT(VARCHAR(10),DATEADD(mm,cs.INITIALTERM,GETDATE()),103)
) x
-- which is the same as this:
SELECT [Contract End Date] = CASE
WHEN cs.COMMENT LIKE '%CED:%' AND CONVERT(DATE, DateString, 103) > CAST(GETDATE() AS DATE) THEN CAST(DateString AS CHAR(10))
ELSE CONVERT(CHAR(10),DATEADD(mm,cs.INITIALTERM,GETDATE()),103) END
FROM C_COMPANYSERVICE cs with (nolock)
CROSS APPLY (
SELECT DateString = LEFT(SUBSTRING (cs.comment,PATINDEX('%CED:%',cs.comment)+4,LEN(cs.Comment)),10)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 23, 2014 at 6:54 am
Thanks, that is fantastic, a great lesson for me too ?
I’ve never used cross apply before. I had a look at using to DECLARE a variable etc but this is brilliant.
I’ve incorporated it into the rest of my script and it works perfectly.
Cheers for your help.
Gwyn
June 23, 2014 at 7:20 am
I'm glad Chris was able to help you out. However, I can't help but notice that you are using NOLOCK. Are you ok with missing and/or duplicate data? Do you know what that hint really does? Take a look at these articles to make sure you understand what it is really doing.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2014 at 8:29 am
The NOLOCK was drilled into me at my last job by the DBA there. He said it would stop deadlocks given records are constantly updating. It was a telco, so millions of call records that were being updated and queried.
I’ve read those articles and understand why it’s used (didn’t know much before). Don’t think I need NOLOCK with database here.
Thanks
June 23, 2014 at 9:29 am
gwyn.jones (6/23/2014)
The NOLOCK was drilled into me at my last job by the DBA there. He said it would stop deadlocks given records are constantly updating. It was a telco, so millions of call records that were being updated and queried.I’ve read those articles and understand why it’s used (didn’t know much before). Don’t think I need NOLOCK with database here.
Thanks
For a telco it might be ok much of the time but for billing I sure hope they aren't using it. I would pretty peeved if I got billed twice for a call because the DBA used NOLOCK.
Certainly understanding it and knowing when/if it is ok is good. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply