Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help with Date and Time


Query Help with Date and Time

Author
Message
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
jdbrown239 (4/25/2013)
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.


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24247 Visits: 37978
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());




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24247 Visits: 37978
jdbrown239 (4/26/2013)
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


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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
jdbrown239 (4/26/2013)
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


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
Will do thanks again!

Doug
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search