April 2, 2015 at 2:45 pm
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
INNER JOIN [dbo].[LoanApplication] AS la ON la.[LoanApplicationGUID] = dhi.LoanApplicationGUID
WHERE
dhi.[LoanApplicationGUID] = '6B17FDF0-C614-4759-9F54-72756BAEED9D'
ORDER BY
dhi.[timestamp] DESC
That returns the following:
LoanApplicationGUIDtimestampbundle_idvalue
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 4:58 PM300007B
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 4:58 PM300007RB
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 4:50 PM300007RB
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 4:41 PM300007RB
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 4:38 PM300007RB
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:55 PM300007NB
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:43 PM300007RB
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:41 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:40 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:38 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:35 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:32 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:24 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:16 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 12:07 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-2-14 3:20 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-2-14 2:56 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D12-2-14 2:54 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D11-28-14 11:03 AM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D11-28-14 10:59 AM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D11-28-14 10:57 AM300007C
I need to have the following return the following:
LoanApplicationGUIDtimestampbundle_idvalue
6B17FDF0-C614-4759-9F54-72756BAEED9D12-3-14 4:58 PM300007B
6B17FDF0-C614-4759-9F54-72756BAEED9D12-2-14 3:20 PM300007VI
6B17FDF0-C614-4759-9F54-72756BAEED9D11-28-14 11:03 AM300007VI
The difference is the lastest time in the day for each day. I hope this make sense. Thanks for any and all help with this!
Fred Stemp
DBA, DFU
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
April 2, 2015 at 3:00 pm
First, I'd be remiss if I didn't comment on the (nolock). That is almost certainly a bad idea
Anyway, you want something like:
WITH a AS (
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
ROW_NUMBER() OVER (PARTITION BY dhi.GUID, CAST(dhi.timestamp AS DATE) ORDER BY dhi.timestamp DESC) rn
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
INNER JOIN [dbo].[LoanApplication] AS la ON la.[LoanApplicationGUID] = dhi.LoanApplicationGUID
WHERE
dhi.[LoanApplicationGUID] = '6B17FDF0-C614-4759-9F54-72756BAEED9D'
)
SELECT
[GUID],
[timestamp],
[bundle_id],
[value]
FROM a
WHERE rn = 1
ORDER BY
dhi.[timestamp] DESC
April 2, 2015 at 3:11 pm
Thanks for the reply!
I have to use WITH(NOLOCK) because this dat come from a scrub table that a XML Shredding process is constantly running. And the powers to be want this ASAP instead of waiting for it to get to the DW. But that's a whole other iss lol.
When I run your CTE I get two error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '('.
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Did I miss something in my copy? Thanks again! I really appreciate the fast response!!
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
April 2, 2015 at 3:17 pm
I figured out the issue, just a simple comma. lol Thanks again for all the help I really appreciate it!!!
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply