Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Syntax Help

Status
Not open for further replies.
nickrugado

nickrugado

MIS
Dec 5, 2005
40
0
0
US
I have this query that uses MRI database if anyone knows that software.

The query has 10 sub queries... I'm having trouble breaking up the 1 into it's 10 to find the problem...

Any help would be appreciated.

Thanks,

Select distinct name.unitid AS [Unit ID], (Select Distinct chgcode from rmrecc where name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease AND chgcode<>’RNT’ AND chgcode <> ‘PRK’ and chgcode <>’PCT’ AND chgcode <> ‘PCO’ AND chgcode<>’SUB’ and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’) AS PG, (select Distinct amount from rmrecc where chgcode=’RNT’ AND name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease and EndDate>Current_Timestamp and InEffect=’y’) AS [Market Rent], (Select sum(amount) from rmrecc where name.unitid=rmrecc.unitid AND chgcode<>’prk’ AND status = ‘c’ and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’)AS [Current Rent], (Select Sum(Amount) from rmrecc where chgcode=’prk’ and name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’) as [Monthly Parking], (Select Distinct amount from rmrecc where name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease AND chgcode<>’RNT’ AND chgcode <> ‘PRK’ and chgcode <>’PCT’ AND chgcode <> ‘PCO’ AND chgcode <> ‘PUE’ AND chgcode <> ‘PUG’ AND chgcode <> ‘PUW’ AND chgcode <> ‘PUR’ AND chgcode<>’SUB’ and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’ )*-1 AS [Rent Credit], (Select Distinct amount from rmrecc where name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease AND chgcode=’PUG’ and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’ )*-1 AS [Gas Credit], (Select Distinct amount from rmrecc where name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease AND chgcode=’PUE’ and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’ )*-1 AS [Electricity Credit], (Select Distinct amount from rmrecc where name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease AND chgcode=’PUW’ and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’ )*-1 AS [Water/Sewer Credit], (Select Distinct amount from rmrecc where name.unitid=rmrecc.unitid and rmrecc.rmlease=rmlease.rmlease AND chgcode=’PUR’ and EndDate>Current_Timestamp and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' and InEffect=’y’ )*-1 AS [Refuse Credit], (Select Distinct max(Effdate) from RMRECC where name.unitid=rmrecc.unitid AND Effdate between '3/1/2008' AND '3/31/2008' and InEffect=’y’) as [Effective Date], (Select Distinct Max(rmlease.expire) from rmlease where rmlease.unitid=name.unitid) AS [Expire Date], (Select sum(tranamt) from rmsdlg where name.nameid=rmsdlg.nameid AND seccode='SEC') *-1 AS [Security Deposit], (Select sum(tranamt) from rmsdlg where name.nameid=rmsdlg.nameid AND seccode='PET') *-1 AS [Pet Deposit], (Select sum(tranamt) from rmsdlg where name.nameid=rmsdlg.nameid AND seccode='KEY') AS [Laundry Key Deposit], (Select sum(tranamt) from rmsdlg where name.nameid=rmsdlg.nameid AND seccode='MSC')*-1 AS [Misc Deposit], (Select sum(tranamt) from rmsdlg where name.nameid=rmsdlg.nameid)*-1 AS [Total Deposit] from name inner join rmrecc on rmrecc.unitid=name.unitid inner join rmsdlg on rmsdlg.nameid=name.nameid inner join rmlease on rmrecc.unitid=rmlease.unitid where (select max(rmlease) from rmlease where name.unitid=rmlease.unitid and (select max(expire) from rmlease where rmlease.unitid=name.unitid)=expire)=rmlease.rmlease and status = ‘c’ and vacate is null and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' Group By name.unitid, rmrecc.effdate, name.nameid, rmlease.unitid, rmlease.rmlease, name.status Order by name.unitid
 
Sort by date Sort by votes
to find the problem
Which problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
this looks better. but we still don't know what the problem is.

Code: 
Select distinct name.unitid AS [Unit ID]
     , (Select Distinct chgcode 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           AND chgcode <> 'RNT' 
           AND chgcode <> 'PRK' 
           and chgcode <> 'PCT' 
           AND chgcode <> 'PCO' 
           AND chgcode <> 'SUB' 
           and EndDate > Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' AND '3/31/2008' 
           and InEffect = 'y') AS PG
     , (select Distinct amount 
          from rmrecc 
         where chgcode = 'RNT' 
           AND name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           and EndDate>Current_Timestamp 
           and InEffect = 'y') AS [Market Rent]
     , (Select sum(amount) 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           AND chgcode<>'prk' 
           AND status = 'c' 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y')AS [Current Rent]
     , (Select Sum(Amount) 
          from rmrecc 
         where chgcode = 'prk' 
           and name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           and EndDate>Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y') as [Monthly Parking]
     , (Select Distinct amount 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           AND chgcode<>'RNT' 
           AND chgcode <> 'PRK' 
           and chgcode <>'PCT' 
           AND chgcode <> 'PCO' 
           AND chgcode <> 'PUE' 
           AND chgcode <> 'PUG' 
           AND chgcode <> 'PUW' 
           AND chgcode <> 'PUR' 
           AND chgcode<>'SUB' 
           and EndDate>Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y' )*-1 AS [Rent Credit]
     , (Select Distinct amount 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           AND chgcode = 'PUG' 
           and EndDate>Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y' )*-1 AS [Gas Credit]
     , (Select Distinct amount 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           AND chgcode = 'PUE' 
           and EndDate>Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y' )*-1 AS [Electricity Credit]
     , (Select Distinct amount 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           AND chgcode = 'PUW' 
           and EndDate>Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y' )*-1 AS [Water/Sewer Credit]
     , (Select Distinct amount 
          from rmrecc 
         where name.unitid = rmrecc.unitid 
           and rmrecc.rmlease = rmlease.rmlease 
           AND chgcode = 'PUR' 
           and EndDate>Current_Timestamp 
           and rmrecc.effdate BETWEEN '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y' )*-1 AS [Refuse Credit]
     , (Select Distinct max(Effdate) 
          from RMRECC 
         where name.unitid = rmrecc.unitid 
           AND Effdate between '3/1/2008' 
           AND '3/31/2008' 
           and InEffect = 'y') as [Effective Date]
     , (Select Distinct Max(rmlease.expire) 
          from rmlease 
         where rmlease.unitid = name.unitid) AS [Expire Date]
     , (Select sum(tranamt) 
          from rmsdlg 
         where name.nameid = rmsdlg.nameid 
           AND seccode = 'SEC') *-1 AS [Security Deposit]
     , (Select sum(tranamt) 
          from rmsdlg 
         where name.nameid = rmsdlg.nameid 
           AND seccode = 'PET') *-1 AS [Pet Deposit]
     , (Select sum(tranamt) 
          from rmsdlg 
         where name.nameid = rmsdlg.nameid 
           AND seccode = 'KEY') AS [Laundry Key Deposit]
     , (Select sum(tranamt) 
          from rmsdlg 
         where name.nameid = rmsdlg.nameid 
           AND seccode = 'MSC')*-1 AS [Misc Deposit]
     , (Select sum(tranamt) 
          from rmsdlg 
         where name.nameid = rmsdlg.nameid)*-1 AS [Total Deposit]  
from name 
inner join rmrecc 
   on rmrecc.unitid = name.unitid 
inner join rmsdlg 
   on rmsdlg.nameid = name.nameid 
inner join rmlease 
   on rmrecc.unitid = rmlease.unitid 
 where (select max(rmlease) 
          from rmlease 
         where name.unitid = rmlease.unitid 
           and (select max(expire) 
          from rmlease 
         where rmlease.unitid = name.unitid) = expire) = rmlease.rmlease 
   and status = 'c' 
   and vacate is null 
   and rmrecc.effdate BETWEEN '3/1/2008' 
   AND '3/31/2008' 
Group By name.unitid
       , rmrecc.effdate
       , name.nameid
       , rmlease.unitid
       , rmlease.rmlease
       , name.status 
Order by name.unitid

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
67
carp
carp
jchewsmith
  • Locked
  • Question
SQL Syntax 1
Replies
1
Views
58
JarlH
JarlH
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
67
carp
carp
soushi01
  • Locked
  • Question
Nested For Loop update statement (pl/sql) 1
Replies
2
Views
71
soushi01
soushi01
excelcus
  • Locked
  • Question
SQL Query Help
Replies
4
Views
51
excelcus
excelcus

Part and Inventory Search

Sponsor

Back
Top