Help with This TSQL Code - 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 with This TSQL Code

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-08 : 18:21:15

I'm new to TSQL, so this is probably a dumb question, but I cannot find any resolution to my question.

I cannot figure out what is happening after:

from (/*Consolidates and calculates numbers*/
Select distinct case when MasterRecord.Linking>'198' then '199'

What is MasterRecord and what is it referring to? Is there somewhere in the online book that I can find additional help on the syntax?

Thanks for any suggestions!

/*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,

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-08 : 20:57:33

It might help to post the remaining part of the query.

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-08 : 21:02:52

quote:
Originally posted by SamC

It might help to post the remaining part of the query.

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-08 : 21:04:28

quote:
Originally posted by SunnyDee

quote:
Originally posted by SamC

It might help to post the remaining part of the query.

Sorry - here is the remainder of the query:

/*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 AmountG
from(/*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

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-09 : 10:48:11

quote:
Originally posted by SunnyDee

I cannot figure out what is happening after:

from (/*Consolidates and calculates numbers*/
Select distinct case when MasterRecord.Linking>'198' then '199'

What is MasterRecord and what is it referring to? Is there somewhere in the online book that I can find additional help on the syntax?

MasterRecord is referring to

)as MasterRecord

which is the 22nd line from the bottom of your post above.

Sam

PS: use [ code]your sql script here[ / code] and include the indentation next time.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 11:53:28

You're new to t-sql....

Did you write this or inhert it?

Brett

8-)

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-09 : 18:53:56

Thank you, and I will follow your suggestion.

I inherited it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources