April 23, 2013 at 10:18 am
HI
I am selecting from a table and setting a condition in the "Where" clause on a column that has a time and date stamp. When I use set the condition for a 20 min threshold for the time stamp I get data (which is expected)
WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) and a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) or a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())
But when I remove the 20 min threshold from the clause I get no data at all but I should get more data. Can anyone see what i am missing?
WHERE c.CTS_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE()) and a.APP_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE()) or c.CTS_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE())
I even tried removing the reference to time altogether and still get no data.
WHERE c.CTS_LAST_SAVED_DATE = GETDATE() and a.APP_LAST_SAVED_DATE = GETDATE() or c.CTS_LAST_SAVED_DATE = GETDATE()
Doug
April 23, 2013 at 10:43 am
When you are comparing DATETIME Dates = the date AND time must be equal.
If your data Type is DATE then TIME is eliminated, however GETDATE() will return DATE AND TIME. So they are not equal because there is no implicit conversion from GETDATE to Date
So your where clause needs to consider this.
WHERE My_DATE_Field = CAST(GETDATE() AS DATE)
OR
WHERE My_DATETIME_Field >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)
AND My_DATETIME_Field < CAST(CAST(GETDATE() + 1 AS DATE) AS DATETIME)
April 23, 2013 at 1:08 pm
Both still show no rows. I am looking for rows where the date is right now (today) regardless of the time stamp so I will get the current day each time I run it.
April 23, 2013 at 1:21 pm
Maybe something like this?
WHERE cast(c.CTS_LAST_SAVED_DATE as DATE) = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE) = cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) = cast(GETDATE() as DATE)
At first glance that looks horribly nonSARGable but usually casting a datetime to a date will not render that nonSARGable.
_______________________________________________________________
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/
April 23, 2013 at 2:35 pm
Hey Sean
I tried this and had to stop the query after 25 mins of running. Any other suggestions would be appreciated.
Doug
April 23, 2013 at 2:38 pm
jdbrown239 (4/23/2013)
Hey SeanI tried this and had to stop the query after 25 mins of running. Any other suggestions would be appreciated.
Doug
Well we are all just guessing here because we can't see what you see. Can you post ddl and sample data so we can help? Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
April 23, 2013 at 2:45 pm
Here is the problem, we can't see what you see. This means with as little information as you provided, all we can do is take shots in the dark and hope we hit the target.
Barring direct access to your server and the database, you really need to provide us with as much information as possible to be able to help you. Please imagine if we had asked you for help only providing the info you provided, how much could you do to help us knowing nothing else?
Help us help you, and you can do this my reading and following the instructions in the first article I reference below in my signature block. The article will walk you through everything you need to post and how to do it to get the best possible answers quickly.
April 25, 2013 at 1:52 pm
Here is the original query with the time stamp
Select a.app_key AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER,
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey
WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) and a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) or a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())
Here is a sample result set
APP_NUMBER CREDIT_AMOUNT DEALER_NUM APP_CONTRACT_NUMBER APPLICATION_STATUS BOOKING_DATE CUSTOMER CUSTOMER_DBA CCAN DATE_ENTERED FEDERAL_ID
955315 2281.00 7183385800 NULL NULL NULL Zirh Limited Liability Company NULL 1347518 04/25/2013 NULL
955192 6850.00 9549701691 NULL Approved NULL Claremore Tire Center, Inc. NULL 1380831 04/25/2013 NULL
955229 20000.00 2677597066 NULL Approved - SFP NULL Steven S Levine DMD NULL 1380854 04/25/2013 NULL
953008 46000.00 6196961699 NULL Approved NULL Tom Paige Catering Company NULL 1344979 04/12/2013 NULL
955252 17601.00 8008168138 NULL Approved NULL American Tire & Auto Care, Inc NULL 1380871 04/25/2013 72-0944075
Here is the query with no time stamp where I get no results.
Select a.app_key AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER,
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey WHERE c.CTS_LAST_SAVED_DATE = CAST(DATEADD(day,1,CURRENT_TIMESTAMP) as DATE) and a.APP_LAST_SAVED_DATE = CAST(DATEADD(day,1,CURRENT_TIMESTAMP) as DATE) or c.CTS_LAST_SAVED_DATE = CAST(CURRENT_TIMESTAMP as DATE)
April 25, 2013 at 2:01 pm
I am going to go out on a limb here and guess that you didn't bother to read the article that was suggested by myself and Lynn? Look at what you have posted so far in this thread and ask yourself if you honestly think you have posted enough information for you to answer this.
We can't begin to post a solution because we don't tables and/or data to work with here. Help us to help you and will be rewarded with tested and fast code.
_______________________________________________________________
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/
April 25, 2013 at 6:16 pm
Sean
I did read the article but the query involves joins on four tables along with converting XML data. Unfortunately I can't figure out an easy way to provide a single sample table you could build and have you load sample data with dates and time stamp to illustrate the problem. If I could I probably wouldn't be asking the question I have on the 'Where' clause so I provided as much information as I could. Posting this question as the article suggest its a bit beyond my skill set right now. Thanks for the info and trying to assist me. Maybe my next quest will be a little easier to post in the manner suggested.
April 26, 2013 at 7:32 am
jdbrown239 (4/25/2013)
SeanI did read the article but the query involves joins on four tables along with converting XML data. Unfortunately I can't figure out an easy way to provide a single sample table you could build and have you load sample data with dates and time stamp to illustrate the problem. If I could I probably wouldn't be asking the question I have on the 'Where' clause so I provided as much information as I could. Posting this question as the article suggest its a bit beyond my skill set right now. Thanks for the info and trying to assist me. Maybe my next quest will be a little easier to post in the manner suggested.
You don't have to post only a single table. There is nothing wrong with posting several tables. The issue is that we can't help you figure out to get your query right because we have nothing to work with. If you can post ddl and sample data we can help. If you can't, I wish you the best of luck figuring this out.
_______________________________________________________________
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/
April 26, 2013 at 7:49 am
Here is one of your queries reformatted to make it more readable. I am concerned about the WHERE clause. You are using both AND and OR in the clause but you haven't explicitly defined how these are evaluated by using parens. This actually makes it harder to understand the order of evaluation without having to think about the precedence between AND and OR.
Select
a.app_key AS APP_NUMBER,
a.CREDIT_AMOUNT,
REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM,
c.cts_contract_number AS APP_CONTRACT_NUMBER,
CASE co.CO_UD_APP_STATUS_TBDESC
WHEN 'PO Issued'
THEN CASE a.APP_DECISION_CODE WHEN 7
THEN 'Booked'
ELSE 'PO Issued'
END
WHEN 'Approved'
THEN CASE co.CO_LESSOR
WHEN '421'
THEN 'Approved - SFP'
WHEN '423'
THEN 'Approved - SFP'
WHEN '424'
THEN 'Approved - SFP'
ELSE 'Approved'
END
ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS,
CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE,
CONVERT(XML, a.APP_DATA, 0).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER,
CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8
THEN CONVERT(XML, a.APP_DATA, 0).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')
ELSE NULL
END AS CCAN,
CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA, 0).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED,
CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)') AS FEDERAL_ID
FROM
dbo.APPLICATION AS a
LEFT OUTER JOIN dbo.RPT_CO AS co
ON a.APP_KEY = co.APP_FKEY
LEFT OUTER JOIN dbo.DEALER AS d
ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY
LEFT OUTER JOIN dbo.Contract_Setup AS c
ON a.app_key = c.cts_app_fkey
WHERE
c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND
c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) AND
a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND
a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) OR
a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND
a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE());
April 26, 2013 at 9:29 am
Lynn
You guys were right about the 'Where' clause being the issue. I added a few indexes to the table to increase performance and went back to a suggested 'where' clause by Sean and I was able to get the data without a time stamp.
WHERE cast(c.CTS_LAST_SAVED_DATE as DATE)> = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE)>= cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) >= cast(GETDATE() as DATE)and co.CO_BOOKING_DATE is not NULL
Thanks you guys for hanging in there.
Doug
April 26, 2013 at 9:45 am
jdbrown239 (4/26/2013)
LynnYou guys were right about the 'Where' clause being the issue. I added a few indexes to the table to increase performance and went back to a suggested 'where' clause by Sean and I was able to get the data without a time stamp.
WHERE cast(c.CTS_LAST_SAVED_DATE as DATE)> = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE)>= cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) >= cast(GETDATE() as DATE)and co.CO_BOOKING_DATE is not NULLThanks you guys for hanging in there.
Doug
Glad we could help.
I would like to suggest that you work with well formatted code, something like I posted versus what you had posted. You will find that if you take the time to make it "pretty" it is also easier to debug and maintain.
April 26, 2013 at 9:55 am
jdbrown239 (4/26/2013)
LynnYou guys were right about the 'Where' clause being the issue. I added a few indexes to the table to increase performance and went back to a suggested 'where' clause by Sean and I was able to get the data without a time stamp.
WHERE cast(c.CTS_LAST_SAVED_DATE as DATE)> = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE)>= cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) >= cast(GETDATE() as DATE)and co.CO_BOOKING_DATE is not NULLThanks you guys for hanging in there.
Doug
Glad you were able to get it sorted out.
_______________________________________________________________
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply