Need help with this date diff expression

Question

Post reply

Need help with this date diff expression

Karen Roslund

SSCommitted

Points: 1724
More actions
May 1, 2009 at 6:21 am

#202825

Hi I have a requirement to the follow:
Initial Dx Date (defined as not having an Depression dx at least 4 months prior to 5/1/07, list if between 5/1/07 and 4/30/08)
and for the life of me i cannot figure how should i have datediff expression
My query is as follows
select client_ID, eff_dt from diags where and eff_dt <> Datediff(month,eff_dt,4)
any help will be appreicated.
Thanks,
Karen

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

say2rose Grasshopper Points: 22 More actions · Answer 1

you could use dateadd(month, -4,eff_date) to get the 4 months prior date.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

What exactly are you trying to do? Also in the select that you posted you have "where and ...". You left out the conditional. If you can explain your requirements more clearly there will be lots of people willing to help. 🙂

_______________________________________________________________

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/

Karen Roslund SSCommitted Points: 1724 More actions · Answer 3

so should i use eff_dt = dateadd(month,-4,eff_dt) or what

Karen Roslund SSCommitted Points: 1724 More actions · Answer 4

Actually this is my requirement

Index Episode Start Date = Starting episode of treatment during the intake period with no prior claims for 120 days and a new diagnosis of depression. (DSM-IV-TR 296.2x, 296.3x, and 311)

I have figured out the second part but have difficulty find the period with no prior claims for 120 days

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

so you need to exlude records that do not have a claim at any point during the 4 months prior to the current one?

_______________________________________________________________

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/

Karen Roslund SSCommitted Points: 1724 More actions · Answer 6

Index Episode Start Date = Starting episode of treatment during the intake period with no prior claims for 120 days and a new diagnosis of depression. (DSM-IV-TR 296.2x, 296.3x, and 311)

so this is what i have come up so far

The temptable shows client with the above depression codes with the specific time period and below is the cte that i wrote which i think would take care of the prior claims.. but i am still wondering if this is correct

[Code]

and ca.Claim_dt <= Dateadd(day,-120,'5/1/2007'))

[/code]

[Code]

;with cte_claims as(

select t.Client_Id, cli_dob, t.eff_dt,ca.claim_dt from #tempTable t,tbl_Claims_master cm,tbl_claims_det cd, tbl_claims_adj ca

Where

t.client_Id = cm.Client_Id

and cm.claim_mst_Id = cd.claim_mst_Id

and cd.claim_det_Id = ca.claim_det_Id

and ca.Claim_dt <= Dateadd(day,-120,'5/1/2007'))

[/code]

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

See if this is getting you closer.

--this should now return a list of client_Ids that have claims in the period you want to exclude

;with cte_claims as(

select t.Client_Id

from #tempTable t

join tbl_Claims_master cm on t.client_Id = cm.Client_Id and t.client_Id = cm.Client_Id

join tbl_claims_det cd on cm.claim_mst_Id = cd.claim_mst_Id

join tbl_claims_adj ca on cd.claim_det_Id = ca.claim_det_Id

Where ca.Claim_dt between Dateadd(day,-120,'5/1/2007')) and '5/1/2007'

group by Client_Id

select client_ID, eff_dt from diags

left join cte_claims on diags.Client_Id = cte_claims.Client_Id

where cte_claims.Client_Id is null --we only want the records that are not in the list above

_______________________________________________________________

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/

Karen Roslund SSCommitted Points: 1724 More actions · Answer 8

Thanks for your answer and that is actually the no.. i was looking for...

would both the queries result in the same answer????

[Code]

delete from #tempTable t

Left join cte_claims c on t.Client_Id = C.client_Id

where c.Client_Id is null

[/code]

delete from #tempTable where client_Id not in (Select client_Id from cte_claims)

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Yeah. Either way it is deleting #tempTable where there is not a match in cte_Claims.

_______________________________________________________________

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/

Karen Roslund SSCommitted Points: 1724 More actions · Answer 10

Slange,

Thanks a lot for helping me out.

Regards,

Karen

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Happy to help. thanks for letting me know that worked for you. 😉

_______________________________________________________________

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/