Help Needed - SQL Server Forums

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)

Help Needed

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-09 : 08:25:17

Hi There: I posted earlier but didn't include the entire query so it was difficult for someone to figure out the logic.

I'm new to TSQL and have been tasked with revising this existing procedure. It seems like a tremendous amount of code for one proc, but it is below. I am unable to figure what is happening where it is bold faced. What is masterrecord?

Any help is greatly appreciated.

Thanks,

/*Temp Table Generation*/
select distinct
chrMSA
Into #TempTable1
from(
select distinct Table2.chrMSA
from Table3
inner join Table2
on Table3.fkCode1=Table2.pkCode1
inner join Table4
on Table4.pkCode3=Table2.fkCode3
inner join Table5
on Table5.fkGuidKey=Table3.PKGuid
Where Table5.fkUser=@fkUser and Table5.fkReport=@fkReport and intRecycleBin=0
and fkActionType between '1' and '5'
) as DataSet
where chrMSA >'00000'
union
select 'ZALL'
/*Return Record Set*/
Select distinct case when Linking='199' then 1
when Linking='198' then 2
when Linking='197' then 3
when Linking='196' then 4
when Linking='195' then 5 end as Linking,
chrCategory,
chrMSA,
case when chrMSA ='ZALL' then 'Total of all MSA/MDs' else (select Max(chrMSAName) from tbl_MSA where chrMSACode=chrMSA ) end as MSA_Name,
chrSortOrder,
chrItem,
sum(CountA) as CountA,
sum(AmountA) as AmountA,
sum(CountB) as CountB,
sum(AmountB) as AmountB,
sum(CountC) as CountC,
sum(AmountC)as AmountC,
sum(CountD) as CountD,
sum(AmountD)as AmountD,
sum(CountE) as CountE,
sum(AmountE)as AmountE,
sum(CountF) as CountF,
sum(AmountF)as AmountF,
sum(CountG) as CountG,
sum(AmountG) as AmountG
from (/*Consolidates and calculates numbers*/
Select distinct case when MasterRecord.Linking>'198' then '199'
when MasterRecord.Linking='200' then '199'
else MasterRecord.Linking end as Linking,
case when MasterRecord.Linking>'198' then '1990-March 2000'
when MasterRecord.Linking<'196' then '1959 or Earlier'
else MasterRecord.Linking+'0-'+MasterRecord.Linking+'9' end as chrCategory,
MasterRecord.chrMSA,
MasterRecord.chrSortOrder,
MasterRecord.chrItem,
case when MasterRecord.chrLoanType in ('2','3','4') and MasterRecord.fkLoanPurpose='1' and MasterRecord.fkPropertyType in ('1','2') then count(distinct MasterRecord.chrAppNo) end as CountA,
case when MasterRecord.chrLoanType in ('2','3','4') and MasterRecord.fkLoanPurpose='1' and MasterRecord.fkPropertyType in ('1','2') then sum(MasterRecord.monLoanAmount) end as AmountA,
case when MasterRecord.chrLoanType ='1' and MasterRecord.fkLoanPurpose='1' and MasterRecord.fkPropertyType in ('1','2') then count(distinct MasterRecord.chrAppNo) end as CountB,
case when MasterRecord.chrLoanType ='1' and MasterRecord.fkLoanPurpose='1' and MasterRecord.fkPropertyType in ('1','2') then sum(MasterRecord.monLoanAmount) end as AmountB,
case when MasterRecord.fkLoanPurpose='3' and MasterRecord.fkPropertyType in ('1','2') then count(distinct MasterRecord.chrAppNo) end as CountC,
case when MasterRecord.fkLoanPurpose='3' and MasterRecord.fkPropertyType in ('1','2') then sum(MasterRecord.monLoanAmount) end as AmountC,
case when MasterRecord.fkLoanPurpose='2' and MasterRecord.fkPropertyType in ('1','2') then count(distinct MasterRecord.chrAppNo) end as CountD,
case when MasterRecord.fkLoanPurpose='2' and MasterRecord.fkPropertyType in ('1','2') then sum(MasterRecord.monLoanAmount) end as AmountD,
case when MasterRecord.fkPropertyType='3' then count(distinct MasterRecord.chrAppNo) end as CountE,
case when MasterRecord.fkPropertyType='3' then sum(MasterRecord.monLoanAmount) end as AmountE,
case when MasterRecord.fkOccupancy='2' and MasterRecord.chrLoanType in ('1','2','3','4') and MasterRecord.fkLoanPurpose in ('1','2','3') and MasterRecord.fkPropertyType in ('1','2','3') then count(distinct MasterRecord.chrAppNo) end as CountF,
case when MasterRecord.fkOccupancy='2' and MasterRecord.chrLoanType in ('1','2','3','4') and MasterRecord.fkLoanPurpose in ('1','2','3') and MasterRecord.fkPropertyType in ('1','2','3') then sum(MasterRecord.monLoanAmount) end as AmountF,
case when MasterRecord.chrLoanType in ('1','2','3','4') and MasterRecord.fkLoanPurpose in ('1','2','3') and MasterRecord.fkPropertyType ='2' then count(distinct MasterRecord.chrAppNo) end as CountG,
case when MasterRecord.chrLoanType in ('1','2','3','4') and MasterRecord.fkLoanPurpose in ('1','2','3') and MasterRecord.fkPropertyType ='2' then sum(MasterRecord.monLoanAmount) end as AmountGfrom(/*Report Generator*/
Select distinct ReportFormat.Linking,
ReportFormat.chrMSA,
ReportFormat.chrItem,
ReportFormat.chrSortOrder,
DataSet.fkOccupancy,
DataSet.chrLoanType,
DataSet.fkLoanPurpose,
DataSet.fkPropertyType,
DataSet.monLoanAmount,
DataSet.chrAppNo
from (/*Give a ready data set of all records in the correct form and notation*/
Select distinct '200' as Linking,
chrMSA,
pkActionType as chrSortOrder,
chrName as chrItem
from #TempTable1
cross join tbl_Action_Type
Where pkActionType between '1' and '5'
union
Select distinct '199' as Linking,
chrMSA,
pkActionType as chrSortOrder,
chrName as chrItem
from #TempTable1
cross join tbl_Action_Type
Where pkActionType between '1' and '5'
union
Select distinct '198' as Linking,
chrMSA,
pkActionType as chrSortOrder,
chrName as chrItem
from #TempTable1
cross join tbl_Action_Type
Where pkActionType between '1' and '5'
union
Select distinct '197' as Linking,
chrMSA,
pkActionType as chrSortOrder,
chrName as chrItem
from #TempTable1
cross join tbl_Action_Type
Where pkActionType between '1' and '5'
union
Select distinct '196' as Linking,
chrMSA,
pkActionType as chrSortOrder,
chrName as chrItem
from #TempTable1
cross join tbl_Action_Type
Where pkActionType between '1' and '5'
union
Select distinct '195' as Linking,
chrMSA,
pkActionType as chrSortOrder,
chrName as chrItem
from #TempTable1
cross join tbl_Action_Type
Where pkActionType between '1' and '5'
) as ReportFormat
left Join
(/*DataSet*/
Select distinct Table3.chrAppNo,
case when left(chrHomeAge,3)<'196' then '195'
else left(chrHomeAge,3) end as linking,
Table2.chrMSA,
Table3.fkActionType as chrSortOrder,
Table3.fkOccupancy,
Table3.chrLoanType,
Table3.fkLoanPurpose,
Table3.fkPropertyType,
Table3.monLoanAmount
from Table3
inner join Table2
on Table3.fkCode1=Table2.pkCode1
inner join Table4
on Table4.pkCode3=Table2.fkCode3
inner join Table5
on Table5.fkGuidKey=Table3.PKGuid
Where Table5.fkUser=@fkUser and Table5.fkReport=@fkReport and intRecycleBin=0
and (Table3.fkCode1 is not null and Table3.fkCode1>1)
and fkActionType between '1' and '5'
union
Select distinct Table3.chrAppNo,
case when left(chrHomeAge,3)<'196' then '195'
else left(chrHomeAge,3) end as linking,
'ZALL',
Table3.fkActionType as chrSortOrder,
Table3.fkOccupancy,
Table3.chrLoanType,
Table3.fkLoanPurpose,
Table3.fkPropertyType,
Table3.monLoanAmount
from Table3
inner join Table2
on Table3.fkCode1=Table2.pkCode1
inner join Table4
on Table4.pkCode3=Table2.fkCode3
inner join Table5
on Table5.fkGuidKey=Table3.PKGuid
Where Table5.fkUser=@fkUser and Table5.fkReport=@fkReport and intRecycleBin=0
and (Table3.fkCode1 is not null and Table3.fkCode1>1)
and fkActionType between '1' and '5'
) as DataSet
on ReportFormat.Linking=DataSet.Linking
and ReportFormat.chrMSA=DataSet.chrMSA
and ReportFormat.chrSortOrder=DataSet.chrSortOrder
)as MasterRecord
group by MasterRecord.Linking,
MasterRecord.chrMSA,
MasterRecord.chrSortOrder,
MasterRecord.chrItem,
MasterRecord.fkOccupancy,
MasterRecord.chrLoanType,
MasterRecord.fkLoanPurpose,
MasterRecord.fkPropertyType
) as Combined
group by Linking,
chrCategory,
chrMSA,
chrSortOrder,
chrItem
drop table #TempTable1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources