Exclude Duplicates by datetime

[Chumley40]

I am trying to get my brain around this. i have a need to create a table that includes the following:
Client, test, and then a cound of individual tests with the following rules. This table will be used as a dimension table for a cube

Level0 means include all the tests for the client within a time period
Level1 means count only once any test performed within a time period of 1 month from the first test (can be 30 days if necessary)
Level2 means count only once any test performed within a time period of 2 months from the first test (can be 60 days if necessary)

select
clientID,
testdimid,
COUNT(testid) as Level0,--this one gets everything, doesn't exclude duplicate data
datedimid, --do I need this date here?
???? as Level1, --I want to count only the first result in a one month period
???? as Level2 -- I want to count only the first result in a two month period
from
Testtable
group by
DateoftestDimId,
PatientID,
TestDimId

thanks for any help or tips.
TJ

 

[imex]

Can you post a sample data of the table TestTable and the expected result?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Chumley40]

ClientID test dateoftest
1400 Escherichia coli 12/14/12 10:02
1400 Escherichia coli 12/28/12 19:07
6099 Acinetobacter baumannii 10/24/12 10:14
6099 Acinetobacter baumannii 11/26/12 9:32
6099 Escherichia coli 10/24/12 10:14
6099 Escherichia coli 11/6/12 9:32
8054 Escherichia coli 11/21/12 0:00
8054 Escherichia coli 11/22/12 0:00
8054 Escherichia coli 12/12/12 10:01
8054 Pseudomonas aeruginosa 12/12/12 10:01
8054 Pseudomonas aeruginosa 12/12/12 10:02
8054 Pseudomonas aeruginosa 4/12/13 10:03

My results would be
clientID, test Level0 level1 Level2
1400 Escherichia coli 2 1 1
6099 Acinetobacter baumannii 2 2 1 (Level 1 would be two because it is looking at every test that is not duplicated for 30 days, Level 2 would be 1 because the two results are duplicated in 60 days)
8054 Escherichia coli 3 1 1
8054 Pseudomonas Aeruginosa 3 1 2 Level 1 would be one because there are duplicates the first 30 day period, then one > 60 days later with no additional tests performed in the second 30 day period. Level 2 would be two because the next test is > 60 days later


Basically for each 30 day period, which is broken down by levels, they want to exclude duplicate results by 30 days, 60 days, 90 days, etc.

Hope this gives you enough information. Thank you for looking,
TJ

 

[imex]

I do not know if I understand correctly, but try:

with CTE_R as
(
    select 
        ClientID, 
        Test, 
        DateOfTest,
        ROW_NUMBER() OVER(PARTITION BY ClientID, Test ORDER BY DateOfTest) as RowNum
    from TestTable
)

select
    t.ClientID,
    t.Test,
    COUNT(t.DateOfTest) as Level0,
    COUNT(CASE WHEN t.RowNum = 1 OR DATEDIFF(DAY, p.DateOfTest, t.DateOfTest) > 30 
              then t.DateOfTest end) as Level1,
    COUNT(CASE WHEN t.RowNum = 1 OR DATEDIFF(DAY, p.DateOfTest, t.DateOfTest) > 60 
              then t.DateOfTest end) as Level2
from CTE_R as t
left join CTE_R as p
    on p.ClientID = t.ClientID and
       p.Test = t.Test and
       p.RowNum = t.RowNum - 1
group by
    t.ClientID,
    t.Test
order by 
    t.ClientID,
    t.Test

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Chumley40]

Thanks, I will try this and let you know,
TJ