SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


status on a given date or date range


status on a given date or date range

Author
Message
Dena Mansfield
Dena Mansfield
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 77
I have a situation where we need to be able to query the status a claim was at any given time.

For example, I have a table in (2008R2):

CREATE TABLE ClaimStatus(
ClaimID int NOT NULL,
StatusDate datetime NOT NULL,
StatusCode int NOT NULL)

INSERT INTO ClaimStatus VALUES (7150,'2013-03-11 10:41:29.823',100)
INSERT INTO ClaimStatus VALUES (7150,'2013-03-12 07:20:41.720',300)
INSERT INTO ClaimStatus VALUES (7150,'2013-03-15 13:35:50.000',310)
INSERT INTO ClaimStatus VALUES (7148,'2013-03-01 10:41:29.780',100)
INSERT INTO ClaimStatus VALUES (7148,'2013-03-10 07:21:26.557',300)
INSERT INTO ClaimStatus VALUES (7148,'2013-03-20 13:35:50.000',310)
INSERT INTO ClaimStatus VALUES (7149,'2013-02-01 01:19:20.110',100)
INSERT INTO ClaimStatus VALUES (7149,'2013-02-14 07:21:26.557',300)
INSERT INTO ClaimStatus VALUES (7149,'2013-03-14 00:35:50.000',310)
INSERT INTO ClaimStatus VALUES (7147,'2013-02-01 01:19:20.110',100)
INSERT INTO ClaimStatus VALUES (7147,'2013-02-14 07:21:26.557',300)
INSERT INTO ClaimStatus VALUES (7147,'2013-03-10 00:35:50.000',310)

Let's say I need a query that would tell me which claims were at a status of 300 on 3/14/2013. Some of these claims were, in fact, at a status of 300 on that day but how do I query that since there is no entry for the specific date of 3/14/2013? ( ie: claim 7148 was changed to a status of 300 on 3/10 and was not changed to another status until 3/20, so I would need that claim pulled back in my query as it would have still been status 300 on 3/14.)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167817 Visits: 39500
Something like this:



CREATE TABLE ClaimStatus(
ClaimID int NOT NULL,
StatusDate datetime NOT NULL,
StatusCode int NOT NULL);

INSERT INTO ClaimStatus VALUES (7150,'2013-03-11 10:41:29.823',100);
INSERT INTO ClaimStatus VALUES (7150,'2013-03-12 07:20:41.720',300);
INSERT INTO ClaimStatus VALUES (7150,'2013-03-15 13:35:50.000',310);
INSERT INTO ClaimStatus VALUES (7148,'2013-03-01 10:41:29.780',100);
INSERT INTO ClaimStatus VALUES (7148,'2013-03-10 07:21:26.557',300);
INSERT INTO ClaimStatus VALUES (7148,'2013-03-20 13:35:50.000',310);
INSERT INTO ClaimStatus VALUES (7149,'2013-02-01 01:19:20.110',100);
INSERT INTO ClaimStatus VALUES (7149,'2013-02-14 07:21:26.557',300);
INSERT INTO ClaimStatus VALUES (7149,'2013-03-14 00:35:50.000',310);
INSERT INTO ClaimStatus VALUES (7147,'2013-02-01 01:19:20.110',100);
INSERT INTO ClaimStatus VALUES (7147,'2013-02-14 07:21:26.557',300);
INSERT INTO ClaimStatus VALUES (7147,'2013-03-10 00:35:50.000',310);

declare @StatusDate date = '20130314',
@StatusCode int = 300;

with BaseData as (
select
ClaimID,
StatusDate,
StatusCode,
rn = row_number() over (partition by ClaimID order by StatusDate desc)
from
ClaimStatus
where
cast(StatusDate as date) <= @StatusDate
)
select * from BaseData where rn = 1 and StatusCode = @StatusCode;

drop table ClaimStatus;




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)
Dena Mansfield
Dena Mansfield
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 77
That works! Thanks very much!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113000 Visits: 18293
I think that the above would miss Claim 7149 which as I read your issue should show up on the list. This is easy enough to correct by changing @StatusDate to a datetime and the where clause to StatusDate <= @StatusDate

_______________________________________________________________

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.

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)
Dena Mansfield
Dena Mansfield
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 77
Good call. Those times really get in the way sometimes. Smile Thank you!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167817 Visits: 39500
Sean Lange (3/20/2013)
I think that the above would miss Claim 7149 which as I read your issue should show up on the list. This is easy enough to correct by changing @StatusDate to a datetime and the where clause to StatusDate <= @StatusDate


Actually, Sean, my code works as is. If you change the value in @StatusCode from 300 to 210 it catches Claim 7149 just fine.

If you look closely, I cast the Datetime column to a date value (this is SQL Server 2008) and do a <+. Else wise you need to add 1 day to the date you are using, for 2013-03-14 you would want < '2013-03-15' if looking at the full datetime value in StatusDate.

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)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167817 Visits: 39500
Dena Mansfield (3/20/2013)
Good call. Those times really get in the way sometimes. Smile Thank you!


Look carefully at my code, I convert the datetime value to a date only value since this is a SQL Server 2008 forum.

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
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113000 Visits: 18293
Lynn Pettis (3/20/2013)
Sean Lange (3/20/2013)
I think that the above would miss Claim 7149 which as I read your issue should show up on the list. This is easy enough to correct by changing @StatusDate to a datetime and the where clause to StatusDate <= @StatusDate


Actually, Sean, my code works as is. If you change the value in @StatusCode from 300 to 210 it catches Claim 7149 just fine.

If you look closely, I cast the Datetime column to a date value (this is SQL Server 2008) and do a <+. Else wise you need to add 1 day to the date you are using, for 2013-03-14 you would want < '2013-03-15' if looking at the full datetime value in StatusDate.


Lynn, claim 7149 was in a status of 300 until 00:35:50.000 on 2013-03-14 and the code you posted does not return it.

_______________________________________________________________

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.

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 Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167817 Visits: 39500
Sean Lange (3/21/2013)
Lynn Pettis (3/20/2013)
Sean Lange (3/20/2013)
I think that the above would miss Claim 7149 which as I read your issue should show up on the list. This is easy enough to correct by changing @StatusDate to a datetime and the where clause to StatusDate <= @StatusDate


Actually, Sean, my code works as is. If you change the value in @StatusCode from 300 to 210 it catches Claim 7149 just fine.

If you look closely, I cast the Datetime column to a date value (this is SQL Server 2008) and do a <+. Else wise you need to add 1 day to the date you are using, for 2013-03-14 you would want < '2013-03-15' if looking at the full datetime value in StatusDate.


Lynn, claim 7149 was in a status of 300 until 00:35:50.000 on 2013-03-14 and the code you posted does not return it.


Based on the OP's original post the status changed on that date from 300 to 310, so this record would not be shown. His requirement simply specified that on a given date was a claim still in a given state, not if it had been in that state. The claim changed state on that date.


Let's say I need a query that would tell me which claims were at a status of 300 on 3/14/2013. Some of these claims were, in fact, at a status of 300 on that day but how do I query that since there is no entry for the specific date of 3/14/2013? ( ie: claim 7148 was changed to a status of 300 on 3/10 and was not changed to another status until 3/20, so I would need that claim pulled back in my query as it would have still been status 300 on 3/14.)



Nothing above indicated that he wanted to see those claims that changed status on that date from 300, but that were still in a status of 300 on a given date and had not changed yet. Nothing indicates that time is relevant to the query.

Trust me, if time had been relevant to the query I would have accounted for it.

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
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113000 Visits: 18293
Lynn Pettis (3/21/2013)
Sean Lange (3/21/2013)
Lynn Pettis (3/20/2013)
Sean Lange (3/20/2013)
I think that the above would miss Claim 7149 which as I read your issue should show up on the list. This is easy enough to correct by changing @StatusDate to a datetime and the where clause to StatusDate <= @StatusDate


Actually, Sean, my code works as is. If you change the value in @StatusCode from 300 to 210 it catches Claim 7149 just fine.

If you look closely, I cast the Datetime column to a date value (this is SQL Server 2008) and do a <+. Else wise you need to add 1 day to the date you are using, for 2013-03-14 you would want < '2013-03-15' if looking at the full datetime value in StatusDate.


Lynn, claim 7149 was in a status of 300 until 00:35:50.000 on 2013-03-14 and the code you posted does not return it.


Based on the OP's original post the status changed on that date from 300 to 310, so this record would not be shown. His requirement simply specified that on a given date was a claim still in a given state, not if it had been in that state. The claim changed state on that date.


Let's say I need a query that would tell me which claims were at a status of 300 on 3/14/2013. Some of these claims were, in fact, at a status of 300 on that day but how do I query that since there is no entry for the specific date of 3/14/2013? ( ie: claim 7148 was changed to a status of 300 on 3/10 and was not changed to another status until 3/20, so I would need that claim pulled back in my query as it would have still been status 300 on 3/14.)



Nothing above indicated that he wanted to see those claims that changed status on that date from 300, but that were still in a status of 300 on a given date and had not changed yet. Nothing indicates that time is relevant to the query.

Trust me, if time had been relevant to the query I would have accounted for it.


I guess that comes down to interpretation and what the OP wants. It did have a status of 300 on that day, for at least part of it. I was trying to point out a possible issue depending on what the OP needs. It seems to me that the claim in question should show up for that day with a status of either 300 or 310 because it did in fact have both of those values on that day.

_______________________________________________________________

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.

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)
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