Monday, March 12, 2012

how to get rid of old records

I have a patient reviewing database
Master:
ID, Name, Address, etc.
Detail:
ID, Interviewing date, symptom, etc.

And I collect info monthly.
When I want to count how many patients have suffered from the symptom and classify them by period, which I only need the latest info, lots of old records of the same ID emerges. I am new in TSQL and have trouble in retrieving the up-to-date info. PL Help me!
Thank u.

Try this one:

declare @.symtom nvarchar(100)
set @.symtom = 'headache'

select
count(distinct ID) as count,
year([Interviewing date]) as year,
month([Interviewing date]) as month
from Detail
where symptom = @.symtom
group by year([Interviewing date]), month([Interviewing date])

No comments:

Post a Comment