Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query Help with Date and Time Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 13,282, Visits: 12,116
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)
Post #1446989
Posted Friday, April 26, 2013 7:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 23,243, Visits: 31,938
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());





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)
Post #1447004
Posted Friday, April 26, 2013 9:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 11:44 AM
Points: 133, Visits: 282
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)&gt; = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE)&gt;= cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) &gt;= cast(GETDATE() as DATE)and co.CO_BOOKING_DATE is not NULL

Thanks you guys for hanging in there.

Doug
Post #1447064
Posted Friday, April 26, 2013 9:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 23,243, Visits: 31,938
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)&gt; = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE)&gt;= cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) &gt;= 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.



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)
Post #1447068
Posted Friday, April 26, 2013 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 13,282, Visits: 12,116
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)&gt; = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE)&gt;= cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) &gt;= 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)
Post #1447073
Posted Friday, April 26, 2013 11:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 11:44 AM
Points: 133, Visits: 282
Will do thanks again!

Doug
Post #1447113
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse