Matching the where clause condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Matching the where clause condition

Author

Topic

Josephptran
Starting Member

14 Posts

Posted - 2009-04-29 : 13:32:23

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

Thanks

P.S: I don't know how this will work, please email me and let me know the answer at josephptran2002@hotmail.com

Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 13:39:36

rather than posting whole query it would be better if you can explain what specific problem you're having with sample code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources