July 29, 2010 at 4:34 am
Hi there I have the following TSQL -
SELECT[Tenancy Reference]
,[Place Reference]
,[Tenancy Type]
,[Tenancy Category]
,[Method Of Payment]
,[Termination Reason]
,[Tenancy Start Date]
,[Tenancy End Date]
,[EVICTION MONTH]
,[EVICTION YEAR]
,[Correspondace Name]
,[Person Name]
,[Current Balance]
,[Estate Code]
,[Location Type]
,[Number Of Bedrooms]
,[Area Code]
,[Management Area]
,[Scheme]
,[Address]
,[DateOfBirth]
,[Age]
,[Gender]
,[OriginCode]
,[Vulnerable]
,[Tenant Order]
FROM
(
SELECT TEN.[tenancy-ref]AS 'Tenancy Reference'
,LOC.[place-ref] AS 'Place Reference'
,TEN.[tncy-type] AS 'Tenancy Type'
,TEN.[tncy-cat]AS 'Tenancy Category'
,TEN.[pmt-method]AS 'Method Of Payment'
,TEN.[term-reason] AS 'Termination Reason'
,TEN.[tncy-start] AS 'Tenancy Start Date'
,TEN.[tncy-end]AS 'Tenancy End Date'
,DATEPART(MM,TEN.[tncy-end])AS 'EVICTION MONTH'
,DATEPART(YYYY,TEN.[tncy-end])AS 'EVICTION YEAR'
,TEN.[corr-name1] AS 'Correspondace Name'
,PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' ' + PER.SURNAME AS [Person Name]
,TEN.[curr-balance] AS 'Current Balance'
,LOC.[estate-code]AS 'Estate Code'
,LOC.[location-type]AS 'Location Type'
,LOC.[bedrooms] AS 'Number Of Bedrooms'
,LOC.[area-code]AS 'Area Code'
,LOC.[mgt-area] AS 'Management Area'
,LOC.[scheme] AS 'Scheme'
,PLA.address1 + ',' + PLA.address2 + ',' + PLA.address3 + ',' + PLA.address4 AS Address
,PER.[D-O-B] AS DateOfBirth
, DATEDIFF([year] ,PER.[D-O-B], GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])
THEN 0 WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B]) AND
DAY(GETDATE()) >= DAY(PER.[D-O-B])
THEN 0 ELSE 1 END AS Age
,PER.GENDER AS Gender
,PER.[ORIGIN-CODE] AS OriginCode
,CASE WHEN EXTEN.[POTENTIALLY-VULNERABLE] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'Vulnerable'
,TENPER.[TENANT-ORDER] AS 'Tenant Order'
FROM dbo.[IH_RE-TENANCY] AS TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] TENPL
ON
TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] LOC
ON
TENPL.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE] PLA
ON
LOC.[place-ref] = PLA.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]
INNER JOIN
dbo.[CORE_CO-PERSON] PER
ON
TENPER.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
dbo.[CORE_CO-PERSON-EXTENSION] EXTEN
ON
EXTEN.[PERSON-REF] = PER.[PERSON-REF]
WHERE TEN.[term-reason]IN ('EVAS','EVAB','EVRE')
ANDTENPER.[ON-TNCY] = 'yes'
)alias
This pulls me back 20 records -
Tenancy Reference Place Reference Tenancy Type Tenancy Category Method Of Payment Termination Reason Tenancy Start Date Tenancy End Date EVICTION MONTH EVICTION YEAR Correspondace Name Person Name Current Balance Estate Code Location Type Number Of Bedrooms Area Code Management Area Scheme Address DateOfBirth Age Gender OriginCode Vulnerable Tenant Order
-------------------- -------------------- ------------ ---------------- ----------------- -------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------ -------------- ------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ----------- ------------- ------------------ --------- --------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ----------- ---------- -------------------------------------------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010110340046 2010110340 *** GEN HBDD EVRE 2009-03-23 00:00:00.000 2010-05-16 00:00:00.000 5 2010 Miss R Drew Miss Rita Drew .0000 CEN FLAT 1 CHA MHS MHSHOMES 34 Steddys Court,Bryant Street,,Chatham 1984-06-14 00:00:00.000 26 F W1 Yes 1
2010210550039 2010210550 *** GEN HBCS EVRE 2008-07-28 00:00:00.000 2010-07-25 00:00:00.000 7 2010 Mr G Zygo Mr Gary Zygo 2522.9400 PAL FLAT 1 CHA MHS MHSHOMES 55 Chatham Grove,,,Chatham 1979-06-26 00:00:00.000 31 M W1 Yes 1
201075148097 2010751480 ASTT GEN CASH EVRE 2004-10-04 00:00:00.000 2010-05-30 00:00:00.000 5 2010 Ms E Pithouse Ms Elizabeth Pithouse .0000 MEL MAISON 3 CHA MHS MHSHOMES 148 Melville Court,,,Chatham 1972-12-30 00:00:00.000 37 F W1 Yes NULL
201090008060 2010900080 *** GEN HBCS EVRE 2001-03-12 00:00:00.000 2010-06-20 00:00:00.000 6 2010 Mr J G Belchermarks Mr John Belcher-Marks 1509.1000 WEE FLAT 1 CHA MHS MHSHOMES 8 Pine House,Gorse Avenue,,Chatham 1942-01-11 00:00:00.000 68 M W1 No NULL
2011080520036 2011080520 *** GEN ALLP EVRE 2009-01-26 00:00:00.000 2010-04-18 00:00:00.000 4 2010 Mrs T Okeowo Mrs Theresa Okeowo 6435.6600 WEE MAISON 3 CHA MHS MHSHOMES 52 Silverweed Road,,,Chatham 1962-09-20 00:00:00.000 47 F B2 Yes 1
2011080680102 2011080680 *** GEN CASH EVRE 2008-08-04 00:00:00.000 2010-05-09 00:00:00.000 5 2010 Mr A Bello Mr Azeez Bello .0000 WEE MAISON 3 CHA MHS MHSHOMES 68 Silverweed Road,,,Chatham 1970-02-02 00:00:00.000 40 M B2 No 1
2013320170024 2013320170 *** GEN LHA EVRE 2008-04-21 00:00:00.000 2010-05-30 00:00:00.000 5 2010 Miss S White Miss Sara Jayne White 3951.3200 PRI HOUSE 3 CHA MHS MHSHOMES 17 Romney Road,,,Chatham 1974-11-27 00:00:00.000 35 F W1 Yes 1
2113720300019 2113720300 ASTT GEN ALLP EVRE 2007-08-27 00:00:00.000 2010-07-18 00:00:00.000 7 2010 Mr A Bond Mr Ashley Bond 1825.8400 BOR FLAT 1 ROC MHS MHSHOMES 30 Cambria Avenue,,,Rochester 1988-06-28 00:00:00.000 22 M W1 Yes 1
2114170050078 2114170050 ASTT GEN ALLP EVRE 2009-05-04 00:00:00.000 2010-04-25 00:00:00.000 4 2010 Miss K Bridgewater Miss Kelly Bridgewater .0000 TRO STUDIO 1 ROC MHS MHSHOMES 5 Gainsborough House,Gravel Walk,,Rochester 1989-10-11 00:00:00.000 20 F W1 No 1
211447349029 2114473490 *** GEN HBCS EVRE 1999-02-22 00:00:00.000 2010-07-11 00:00:00.000 7 2010 Miss H Stone Miss Hayley Stone 1548.2400 MAI HOUSE 2 ROC MHS MHSHOMES 349 Maidstone Road,,,Rochester 1965-08-26 00:00:00.000 44 F NULL No NULL
2114830430091 2114830430 ASTT GEN ALLP EVRE 2009-07-13 00:00:00.000 2010-06-13 00:00:00.000 6 2010 Miss D Benham Miss Debbie Benham 2854.6800 BOR FLAT 2 ROC MHS MHSHOMES 43 Shorts Way,,,Rochester 1987-02-02 00:00:00.000 23 F W1 No 1
211540005037 2115400050 *** GEN HB EVRE 2004-07-26 00:00:00.000 2010-07-11 00:00:00.000 7 2010 Mr K Bennett Mr Kevin Bennett 2375.2200 DEL HOUSEGAR 3 ROC MHS MHSHOMES 5 Glovers Mill,,,Rochester 1963-06-30 00:00:00.000 47 M W1 No NULL
2213541190078 2213541190 ASTT GEN ALLP EVRE 2009-09-07 00:00:00.000 2010-07-18 00:00:00.000 7 2010 Mrs D Ringwelska-Sobiecka & Mr M Sobiecki Mrs Dorota Ringwelska-Sobiecka 3741.1100 EAR FLAT 3 STR MHS MHSHOMES 119 Albatross Avenue,,Strood,Rochester 1978-03-18 00:00:00.000 32 F W3 No 1
2213541190078 2213541190 ASTT GEN ALLP EVRE 2009-09-07 00:00:00.000 2010-07-18 00:00:00.000 7 2010 Mrs D Ringwelska-Sobiecka & Mr M Sobiecki Mr Marcin Sobiecki 3741.1100 EAR FLAT 3 STR MHS MHSHOMES 119 Albatross Avenue,,Strood,Rochester 1975-03-01 00:00:00.000 35 M W3 No 2
2213870080044 2213870080 *** GEN CASH EVRE 2008-10-27 00:00:00.000 2010-05-30 00:00:00.000 5 2010 Mrs L Wray Mrs Linda Wray 3332.6600 EAR FLAT 2 STR MHS MHSHOMES 8 Cormorant Close,,Strood,Rochester 1964-09-09 00:00:00.000 45 F W1 Yes 1
2214880050040 2214880050 *** GEN ALLP EVRE 2008-10-06 00:00:00.000 2010-06-13 00:00:00.000 6 2010 Mr C Buduki Mr Christopher Buduki 902.1800 EAR STUDIO 1 STR MHS MHSHOMES 5 Snipe Court,Bligh Way,Strood,Rochester 1984-09-20 00:00:00.000 25 M B2 Yes 1
2553500050082 2553500050 FOYE SUPP ALLP EVRE 2009-02-16 00:00:00.000 2010-06-13 00:00:00.000 6 2010 Miss E Doroudy Miss Elaheh Doroudy 50.9600 CAS FOYER 1 CHA SUPPH ENDEAV 5 The Endeavour Foyer,Shipwrights Avenue,,Chatham 1991-09-21 00:00:00.000 18 F M4 No 1
2553500110067 2553500110 FOYE SUPP ALLP EVAS 2010-02-15 00:00:00.000 2010-05-16 00:00:00.000 5 2010 Mr A Coveney Mr Andrew Coveney 42.5800 CAS FOYER 1 CHA SUPPH ENDEAV 11 The Endeavour Foyer,Shipwrights Avenue,,Chatham 1993-04-16 00:00:00.000 17 M W1 No 1
2553500140045 2553500140 FOYE SUPP ALLP EVAS 2010-02-22 00:00:00.000 2010-05-30 00:00:00.000 5 2010 Mr D Childe-Freeman Mr Daniel Childe-Freeman 47.0400 CAS FOYER 1 CHA SUPPH ENDEAV 14 The Endeavour Foyer,Shipwrights Avenue,,Chatham 1990-01-31 00:00:00.000 20 M W1 No 1
2553500160045 2553500160 FOYE SUPP ALLP EVRE 2009-05-25 00:00:00.000 2010-07-25 00:00:00.000 7 2010 Miss C Marsh Miss Chelsey Marsh 286.6000 CAS FOYER 1 CHA SUPPH ENDEAV 16 The Endeavour Foyer,Shipwrights Avenue,,Chatham 1992-07-05 00:00:00.000 18 F W1 No 1
(20 row(s) affected)
You will notice that the last field [Tenant Order] has either NULL or 1 or 2. All I want to call back is when this when the [Tenant Order] is NULL or 1. So in essence I will only pull back 19 records instead.
Anyone can help with the code on this.
BEacues when I add -
ANDTENPER.[TENANT-ORDER] IS NULL
ORTENPER.[TENANT-ORDER] = '1'
to the where clause I pull back 10982 records?????
Very confussed.
Thanks
Ryan
July 29, 2010 at 4:37 am
WHERE TEN.[term-reason]IN ('EVAS','EVAB','EVRE')
AND TENPER.[ON-TNCY] = 'yes'
)alias
WHERE [Tenant Order] IS NULL OR [Tenant Order] = '1'
July 29, 2010 at 4:42 am
Suspect it just needs some parentheses as follows:
AND (TENPER.[TENANT-ORDER] IS NULL
OR TENPER.[TENANT-ORDER] = '1')
July 29, 2010 at 4:57 am
Thanks guys.
Both work.
Was driving me mad!! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply