November 4, 2010 at 2:33 pm
Once more for the other field then, (Sorry about the non-encoding, my browser's having issues today)
SELECT top 20 tud.Value
FROM cv3taskuserdata tud
WHERE ISDATE(tud.value) = 1
SELECT top 20 tud.value
FROM cv3taskuserdata tud
WHERE ISDATE( tud.value) = 0 AND tud.Value IS NOT NULL
We'll find this little data-foulup yet. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 4, 2010 at 3:13 pm
Thank you Craig,
Here are the results from the first query:
SELECT top 20 tud.Value
FROM cv3taskuserdata tud
WHERE ISDATE(tud.value) = 1
2010-10-07
2010-10-07 08:58
2010-10-29
2010-10-29 10:11
2010-10-07
2010-10-07 09:01
2010-10-07
2010-10-07 09:00
2010-10-12
2010-10-12 13:23
2010-10-12
2010-10-12 13:24
2010-10-12
2010-10-12 13:24
2010-10-12
2010-10-12 13:25
2010-10-07
2010-10-07 08:49
2010-07-20
2010-07-20 14:21
Here are the results from the second query:
SELECT top 20 tud.value
FROM cv3taskuserdata tud
WHERE ISDATE( tud.value) = 0 AND tud.Value IS NOT NULL
ADM User: Labrie
ADM Item Removed (Override) - Fondaparinux 2.5 mg/0.5 mL Syringe; (see order detail)
ADM User: Labrie
ADM Item Removed (Override) - Clopidogrel 75 mg Tablet; (see order detail)
Click button at right to lauch content -->
Modify End Time/Date if different than default
Click button at right to lauch content -->
30 Minute(s)
0858
Modify End Time/Date if different than default
Click button at right to lauch content -->
30 Minute(s)
1011
Click button at right to lauch content -->
1
Click button at right to lauch content -->
Click button at right to lauch content -->
Click button at right to lauch content -->
Click button at right to lauch content -->
Click button at right to lauch content -->
November 4, 2010 at 3:21 pm
PFlorenzano-641896 (11/4/2010)
ADM User: LabrieADM Item Removed (Override) - Fondaparinux 2.5 mg/0.5 mL Syringe; (see order detail)
ADM User: Labrie
ADM Item Removed (Override) - Clopidogrel 75 mg Tablet; (see order detail)
Click button at right to lauch content -->
Modify End Time/Date if different than default
Click button at right to lauch content -->
30 Minute(s)
0858
Modify End Time/Date if different than default
Click button at right to lauch content -->
30 Minute(s)
1011
Click button at right to lauch content -->
1
Click button at right to lauch content -->
Click button at right to lauch content -->
Click button at right to lauch content -->
Click button at right to lauch content -->
Click button at right to lauch content -->
GYEAH! :ermm: :sick: That is either one seriously overloaded field or that's an error list from a different software.
Okay, well, the field overload is apparently the problem. Just dealt with this on another thread. See <here> for a review of your issue.
Basically what's happened is whatever filter you're running to only get the dates out of this table is happening AFTER it attempts to do this calculation. This is expected in the optimizer, that it will do things differently, if it thinks it's quicker. You got lucky in 2k5, I'm surprised this didn't fry on you earlier.
You need to un-overload the field, or you need to use the tricks I pointed out to SQL-Tucker in the other thread. At best guess, you're going to want to absorb the datetime rows out of this table, drop it to a temp table, and then link the temp table into the main query.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 2:46 pm
Hello Craig!
The OPTION (FORCE ORDER) worked great! Thank you so much! I'm going to run this in a test environment and execute an optimization plan against it.
Pete
November 8, 2010 at 3:02 pm
PFlorenzano-641896 (11/8/2010)
Hello Craig!The OPTION (FORCE ORDER) worked great! Thank you so much! I'm going to run this in a test environment and execute an optimization plan against it.
Pete
Be aware: That fix also worked for in the other thread, but *it's not guaranteed* unless you force it through a subquery. Double check near the end where I bring that up.
However, happy to hear that it's working better.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 4:58 pm
I would suggest getting all of the fields in a CTE, then doing the DateDiff in the final select:
WITH CTE AS
(
SELECT cv.currentlocation,
cv.ClientDisplayName,
cv.IDCode as MRN,
cv.VisitIDCode as AccountNum,
cv.visitstatus,
convert(varchar,cv.admitdtm,101)admitdt,
cv.providerdisplayname,
o.name,
o.summaryline,
oto.TaskStatusCode,
dateadd (dd,datediff(dd,0,oto.SignificantDtm),0)as DateAdministered,
oto.SignificantTime as TimeAdministered,
oto.SummaryLine,
oto.TaskComment,
tud.value AS PMFDATE,
oto.performedfromdtm
--DATEDIFF(minute, oto.performedfromdtm, tud.value) AS Duration
FROM CV3Order o
JOIN CV3ClientVisit cv
ON o.ClientGUID = cv.ClientGUID
AND o.chartGUID = cv.chartGUID
JOIN CV3OrderTaskOccurrence oto
ON o.GUID = oto.OrderGUID
JOIN cv3taskuserdata tud
ON tud.taskoccurrenceguid = oto.guid
--WHERE dateadd (dd,datediff(dd,0,oto.SignificantDtm),0)= dateadd(dd,datediff(dd,0,@thisdate)-1,0) -- -1 used to capture data from previous date
WHERE tud.userdatacode = 'PRX IVPB EndDTM'
AND oto.taskstatuscode = 'Performed'
AND (cv.visitstatus = 'adm' or cv.visitstatus = 'dsc')
)
SELECT *,
Duration = datediff(minute, performedfromdtm, PMFDATE)
FROM CTE
ORDER BY currentlocation asc, significantdtm ASC
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 8, 2010 at 5:04 pm
WayneS (11/8/2010)
I would suggest getting all of the fields in a CTE, then doing the DateDiff in the final select:
cte's are guaranteed to be pre-processed by the optimizer before the rest of the query? Hm, didn't know that. I'm actually a little surprised that the optimizer wouldn't drill into a cte the same way it would a regular subquery or a view without hints.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 5:42 pm
Craig Farrell (11/8/2010)
WayneS (11/8/2010)
I would suggest getting all of the fields in a CTE, then doing the DateDiff in the final select:cte's are guaranteed to be pre-processed by the optimizer before the rest of the query? Hm, didn't know that. I'm actually a little surprised that the optimizer wouldn't drill into a cte the same way it would a regular subquery or a view without hints.
The trick here is that you're separating the where clause, and the datediff functions. All that you will get out of the CTE are the records that pass the where clause - where you then apply the DateDiff function. (It's always worked for me!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 8, 2010 at 5:50 pm
I really don't want to kick a fallen peer, but this is near to the up most mistake people make when designing tables.
Use the correct data type for your columns to avoid this hassle.
Datetime info needs to go into a datetime data typed column. There is no excuse to do it any other way !
You need to figure out the actual format your data has been stored in, which - chances are - are dependant on the actual client machines date and time settings of the regional settings :sick:
I hope you will be able to rectify this situation in your objects, because this will bite you in the back fairly frequent.
You also need to double check your new instance is in stalled with the same date time settings as your old one, even regarding these server side regional settings.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2010 at 6:07 pm
WayneS (11/8/2010)
The trick here is that you're separating the where clause, and the datediff functions. All that you will get out of the CTE are the records that pass the where clause - where you then apply the DateDiff function. (It's always worked for me!)
Vewy Intewesting. One more bullet in my 'ways to shoot an overloaded field' gun.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply