April 28, 2009 at 10:56 am
Hello
I have created the querry with the conditions (in where clause), and my question is how can I match the condition of different field name to other field name. For example,
If I have six different fields name such as "DERECEIVEDDATE","DECLEAREDDATE", "SomaticMCReceivedDate", "SomaticMCClearedDate", and "PsycMCReceivedDate",
"PsycMCClearedDate" .
I want to know if a case is full pending and this is how they count as full pending:
saying case A is assigned to DE to review then it must shows a date in field "DERECEIVEDDATE" and assume that date is 1/1/2009. Somehow case A has to pass through SomaticMC or PsycMC or it can pass through both SomaticMC and PsycMC to review at the same time, then it must show a date in field SomaticMCClearedDate or PsycMCClearedDate or both fields to review and assume that date is 1/5/2009. If the SomaticMC or PsycMC or both fields is/are completed reviewing case A then it should show a date in SomaticMCClearedDate or PsycMCClearedDate or both fields should have date.
So in order to be considered "Full Pending", there are two conditions to be considered:
First, it has to have a date in DEReceivedDate (DERECEIVEDDATE IS NOT NULL), DEClearedDate IS NULL, SomaticMCReceivedDate IS NOT NULL, SomaticMCClearedDate IS NULL OR NOT NULL, PsycMCReceivedDate IS NOT NULL, PsycMCClearedDate IS NULL OR NOT NULL.
Second, if DERECEIVED IS NOT NULL, DECLEAREDDATE IS NOT NULL, then SomaticMCReceivedDate IS NOT NULL, SomaticMCClearedDate IS NULL, PsycMCReceivedDate IS NOT NULL, PsycMCClearedDate IS NULL.
Please take a look on my where clause condition to see if there is/are something wrong with my condition like the description mentioned above.
declare @startdate datetime, @enddate datetime, @office varchar(5), @Tmp varchar(5)
set @startdate = '01/01/2009'
set @enddate = '04/11/2009'
set @office = 'ALL'
create table #Names
(
[Name] varchar(50)
)
create table #temp
(
[Name] varchar(50),
[Type] varchar(2),
ReceivedDate datetime,
OfficeName varchar(50)
)
declare @Name varchar(50)
declare @ReceivedDate datetime
declare @Type varchar(2)
--DE
insert into #Names
select distinct DEName from ROCAPData
where DEName is not NULL
declare cur cursor read_only
for
Select [Name] from #Names
open cur
fetch next from cur into @Name
while @@fetch_status = 0
begin
if @office = 'ALL'
begin
insert into #temp
select @Name, DDS as OfficeName, DEReceivedDate as ReceivedDate, 'DE' as [Type]
from ROCAPData
where DEname = @Name and (DEClearedDate IS NULL and DESecondClearedDate IS NULL and DEThirdClearedDate IS NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NULL and DEThirdClearedDate IS NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NOT NULL and DEThirdClearedDate IS NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NOT NULL and DEThirdClearedDate IS NOT NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NOT NULL and DEThirdClearedDate IS NOT NULL and DEFourthClearedDate IS NOT NULL)and DEReceivedDate between @startdate and @endDate
end
else
begin
insert into #temp
select @Name,DDS as OfficeName, DEReceivedDate as ReceivedDate, 'DE' as [Type] from ROCAPData
where DEname = @Name and (DEClearedDate IS NULL and DESecondClearedDate IS NULL and DEThirdClearedDate IS NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NULL and DEThirdClearedDate IS NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NOT NULL and DEThirdClearedDate IS NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NOT NULL and DEThirdClearedDate IS NOT NULL and DEFourthClearedDate IS NULL) OR (DEClearedDate IS NOT NULL and DESecondClearedDate IS NOT NULL and DEThirdClearedDate IS NOT NULL and DEFourthClearedDate IS NOT NULL) and DEReceivedDate between @startdate and @endDate and DDS = @office
end
fetch next from cur into @Name
end
close cur
deallocate cur
delete from #Names
--Somatic
insert into #Names
select distinct SomaticMCName from ROCAPData
where SomaticMCName is not NULL
declare cur cursor read_only
for
Select [Name] from #Names
open cur
fetch next from cur into @Name
while @@fetch_status = 0
begin
if @office = 'ALL'
begin
insert into #temp
select @Name, DDS as OfficeName, SomaticMCReceivedDate as ReceivedDate, 'SM' as [Type]
from ROCAPData
where SomaticMCName = @Name and SomaticMCClearedDate IS NULL and SomaticMCSecondClearedDate IS NULL and SomaticMCThirdClearedDate IS NULL and SomaticMCFourthClearedDate IS NULL and SomaticMCReceivedDate between @startdate and @endDate
end
else
begin
insert into #temp
select @Name, DDS as OfficeName, SomaticMCReceivedDate as ReceivedDate, 'SM' as [Type]
from ROCAPData
where SomaticMCName = @Name and SomaticMCClearedDate IS NULL and SomaticMCSecondClearedDate IS NULL and SomaticMCThirdClearedDate IS NULL and SomaticMCFourthClearedDate IS NULL and SomaticMCReceivedDate between @startdate and @endDate and DDS = @office
end
fetch next from cur into @Name
end
close cur
deallocate cur
delete from #Names
--Phsyc
insert into #Names
select distinct PsycMCName from ROCAPData
where PsycMCName is not NULL
declare cur cursor read_only
for
Select [Name] from #Names
open cur
fetch next from cur into @Name
while @@fetch_status = 0
begin
if @office = 'ALL'
begin
insert into #temp
select @Name, DDS as OfficeName, PsycMCReceivedDate as ReceivedDate, 'PY' as [Type]
from ROCAPData
where PsycMCName = @Name and PsycMCClearedDate IS NULL and PsycMCSecondClearedDate IS NULL and PsycMCThirdClearedDate IS NULL and PsycMCFourthClearedDate IS NULL and PsycMCReceivedDate between @startdate and @endDate
end
else
begin
insert into #temp
select @Name,DDS as OfficeName, PsycMCReceivedDate as ReceivedDate, 'PY' as [Type]
from ROCAPData
where PsycMCName = @Name and PsycMCClearedDate IS NULL and PsycMCSecondClearedDate IS NULL and PsycMCThirdClearedDate IS NULL and PsycMCFourthClearedDate IS NULL and PsycMCReceivedDate between @startdate and @endDate and DDS = @office
end
fetch next from cur into @Name
end
close cur
deallocate cur
delete from #Names
select [Name], [Type], OfficeName, ReceivedDate, count(ReceivedDate) as TotalPending
into #tempfinal
from #temp
group by [Name], [Type], OfficeName, ReceivedDate
insert into #tempfinal(TotalPending)
select sum(TotalPending) from #tempfinal
select * from #tempfinal order by TotalPending
drop table #tempfinal
drop table #Names
drop table #temp
Thank you
April 28, 2009 at 11:05 am
First, why are you using cursors for this?
Second, I see a whole bunch of columns in the Where clauses that aren't mentioned in your description.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 11:10 am
Hi
Yes there is a whole bunch of column fields name in where clause. But if you look carefully then it's just repeated columns such as DEClearedDate then DESecondClearedDate or DEThirdClearedDate and DEFourthClearedDate. If a case returns for second time then DESecondReceivedDate and DESecondClearedDate or a case returns for third time then DEThirdReceivedDate and DEThirdClearedDate.
That why I just take firt return as an example to ask.
Thanks
P.S: Thanks in advance for fixing the errors on Where clause
April 28, 2009 at 11:45 am
I started to reformat your code so I could read it better, and unfortunately you have some logic errors in WHERE clauses. I'm going to leave it as an exercise for you to identify the problems and let us know what you think are the problems.
Suggestion, reformat and indent your code appropriately, and it should just jump right out at you.
April 28, 2009 at 1:46 pm
Hi there,
can you help me with my where clause conditions?
Thanks
April 28, 2009 at 1:52 pm
Not until you have worked it first. Just pay attention to your parens as you work through the where clause.
April 29, 2009 at 6:47 am
Can someone help me with this one? I don't know what I did to make this work.
Thanks
April 29, 2009 at 7:04 am
josephptran2002 (4/28/2009)
HiYes there is a whole bunch of column fields name in where clause. But if you look carefully then it's just repeated columns such as DEClearedDate then DESecondClearedDate or DEThirdClearedDate and DEFourthClearedDate. If a case returns for second time then DESecondReceivedDate and DESecondClearedDate or a case returns for third time then DEThirdReceivedDate and DEThirdClearedDate.
That why I just take firt return as an example to ask.
Thanks
P.S: Thanks in advance for fixing the errors on Where clause
It's probably something you can't change, but I can't help feeling that this structure is poorly designed.
Surely the received and cleared dates for each department (or whatever DE, SomaticMC and PsycMC are) should be stored in separate tables; these records should then link back to the 'master' record and have a 'visit count'. The code could then check a master record against the received/cleared dates associated with the max 'visit count' rather than needing complex WHERE clauses testing for NULL values.
What happens when you eventually get a case which make 5 or more 'visits'? 🙂 Do you restructure the whole system?
Derek
April 29, 2009 at 11:28 am
can someone in here help me out for this question?
Thanks
April 29, 2009 at 12:32 pm
How about you first try to fix the things we have already noted for you in what you originally posted? I pointed you to a logic error that I discovered just by starting to reformat your code. Proper indention and paying attention to your Parens should help. One more clue, check the OR conditions, if any are true the contions in the AND contions won't matter.
We all have full time jobs and we do this in what ever available free time we can make. You really need to help us help you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply