How to do an ongoing count

Question

How to do an ongoing count

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
May 17, 2010 at 9:01 am

#72047

Hi there,
I have the following SQL code
SELECT TEN.[tenancy-ref] AS 'Tenancy Reference'
,CASE WHEN TEN.[curr-balance] >0
THEN TEN.[curr-balance]
ELSE 0 END AS 'Arrears'
,TEN.[curr-balance]+ TEN.[hb-arrears] AS 'Arrears Inc HB'
,TEN.[curr-balance]+ TEN.[hb-arrears] + TEN.[sp-arrears]AS 'Arrears Inc HB and SP'
,TEN.[net-rent] AS 'Net Rent'
,TEN.[pmt-method] AS 'Payment Method'
,TEN.[tncy-status] AS 'Tenancy Status'
,TEN.[corr-name1] AS 'Tenant Name'
,PLAC.[address1] AS 'Address 1'
,PLAC.[address2] AS 'Address 2'
,PLAC.[address3] AS 'Address 3'
,PLAC.[address4] AS 'Address 4'
,PLAC.[address5] AS 'Address 5'
,PLAC.[post-code] AS 'PostCode'
,CASE WHEN TEN.[curr-balance] <0
THEN TEN.[curr-balance]
ELSE 0 END AS 'PrePaid'
,TEN.[rent-group] AS 'Rent Group'
,TEN.[tncy-type] AS 'Tenancy Type'
,CASE WHEN TEN.[tncy-status]= 'FOR'
THEN LOC.[former-arrs-ofcr]
ELSE LOC.[arrears-ofcr]END AS 'Arrears Officer'
,OFFI.[OFCR-TITLE]+ ' '+ INITIALS + ' ' + [SURNAME] AS 'Arrears Officer Name'
,PLAC.[parish-code]AS 'Parish Code'
,LOC.[bedrooms] AS 'Bedrooms'
,LOC.[location-type] AS 'Location Type'
,LOC.[building-type] AS 'Building Type'
,LOC.[mgt-area]AS 'Management Area'
,LOC.[scheme] AS 'Scheme'
,LOC.[accounts-company]AS 'Accounts Company'
,TEN.[gross-rent] AS 'Gross Rent'
,TEN.[tncy-start] AS 'Tenancy StartDate'
,TEN.[tncy-end] AS 'Tenancy EndDate'
,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears] <0
THEN 0
ELSE TEN.[curr-balance]+ TEN.[hb-arrears]END AS 'Arrears Inc HB Excluding Credits'
,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears] <0
THEN 0
ELSE TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears]END AS 'Arrears Inc HB and SP Excluding Credits'
,TEN.[tncy-cat] AS 'Tenancy Category'
FROM dbo.[IH_RE-TENANCY] AS TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPLAC
ON
TEN.[tncy-sys-ref] = TENPLAC.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] AS LOC
ON
TENPLAC.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE]AS PLAC
ON
LOC.[place-ref] = PLAC.[place-ref]
LEFT OUTER JOINdbo.[IH_OFFICER] AS OFFI
ON
LOC.[arrears-ofcr] = OFFI.[OFFICER-CODE]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]
WHERE TENPLAC.[prime-place] = 'yes'
ANDTENPER.[ON-TNCY] = 'yes'
AND TENPER.[END-DATE] IS NULL
Which for the most part works very well. In some instances due to the fact that I am linking to the dbo.[IH_RE-TNCY-PERSON] TENPER from the dbo.[IH_RE-TENANCY] AS TEN I am getting duplicate entries.
For example I am getting the following tenancy reference twice - 1151401.
I need to include both, however is there away that I can create another field in the TSQL that will have an ongoing count in the new field. So the first instance of 1151401 is 1, the second is 2 and if a thrid exsisted would be 3.
I've been trying all sorts, but am unable to work out how to do this.
Thanks
Ryan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

{edit after i posted, i saw this is in a SQL 2000 forum, and this requires 2005 and up.

doing this in SQL2000 usually requires asticking the results in a temp table, and incrementing the column for the counter; let me know if you are really sql2000 only}

the row_number() function can do what you are asking;

try this SQL, i created the counter you are looking for with the alis "RW", and it's partitioning on column dbo.[IH_RE-TNCY-PERSON].TENPER

see if this works for you:

SELECT row_number() OVER(partition by dbo.[IH_RE-TNCY-PERSON].TENPER order by dbo.[IH_RE-TNCY-PERSON].TENPER) AS RW,

TEN.[tenancy-ref] AS 'Tenancy Reference'

,CASE WHEN TEN.[curr-balance] >0

THEN TEN.[curr-balance]

ELSE 0 END AS 'Arrears'

,TEN.[curr-balance]+ TEN.[hb-arrears] AS 'Arrears Inc HB'

,TEN.[curr-balance]+ TEN.[hb-arrears] + TEN.[sp-arrears]AS 'Arrears Inc HB and SP'

,TEN.[net-rent] AS 'Net Rent'

,TEN.[pmt-method] AS 'Payment Method'

,TEN.[tncy-status] AS 'Tenancy Status'

,TEN.[corr-name1] AS 'Tenant Name'

,PLAC.[address1] AS 'Address 1'

,PLAC.[address2] AS 'Address 2'

,PLAC.[address3] AS 'Address 3'

,PLAC.[address4] AS 'Address 4'

,PLAC.[address5] AS 'Address 5'

,PLAC.[post-code] AS 'PostCode'

,CASE WHEN TEN.[curr-balance] <0

THEN TEN.[curr-balance]

ELSE 0 END AS 'PrePaid'

,TEN.[rent-group] AS 'Rent Group'

,TEN.[tncy-type] AS 'Tenancy Type'

,CASE WHEN TEN.[tncy-status]= 'FOR'

THEN LOC.[former-arrs-ofcr]

ELSE LOC.[arrears-ofcr]END AS 'Arrears Officer'

,OFFI.[OFCR-TITLE]+ ' '+ INITIALS + ' ' + [SURNAME] AS 'Arrears Officer Name'

,PLAC.[parish-code]AS 'Parish Code'

,LOC.[bedrooms] AS 'Bedrooms'

,LOC.[location-type] AS 'Location Type'

,LOC.[building-type] AS 'Building Type'

,LOC.[mgt-area]AS 'Management Area'

,LOC.[scheme] AS 'Scheme'

,LOC.[accounts-company]AS 'Accounts Company'

,TEN.[gross-rent] AS 'Gross Rent'

,TEN.[tncy-start] AS 'Tenancy StartDate'

,TEN.[tncy-end] AS 'Tenancy EndDate'

,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears] <0

THEN 0

ELSE TEN.[curr-balance]+ TEN.[hb-arrears]END AS 'Arrears Inc HB Excluding Credits'

,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears] <0

THEN 0

ELSE TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears]END AS 'Arrears Inc HB and SP Excluding Credits'

,TEN.[tncy-cat] AS 'Tenancy Category'

FROM dbo.[IH_RE-TENANCY] AS TEN

INNER JOIN

dbo.[IH_RE-TNCY-PLACE] AS TENPLAC

ON

TEN.[tncy-sys-ref] = TENPLAC.[tncy-sys-ref]

INNER JOIN

dbo.[IH_IH-LOCATION] AS LOC

ON

TENPLAC.[place-ref] = LOC.[place-ref]

INNER JOIN

dbo.[CORE_CO-PLACE]AS PLAC

ON

LOC.[place-ref] = PLAC.[place-ref]

LEFT OUTER JOIN dbo.[IH_OFFICER] AS OFFI

ON

LOC.[arrears-ofcr] = OFFI.[OFFICER-CODE]

INNER JOIN

dbo.[IH_RE-TNCY-PERSON] TENPER

ON

TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]

WHERE TENPLAC.[prime-place] = 'yes'

AND TENPER.[ON-TNCY] = 'yes'

AND TENPER.[END-DATE] IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Hi there,

Thanks for taking the time to responding -

Alas, I get the following error when I run your code -

Server: Msg 195, Level 15, State 10, Line 1

'row_number' is not a recognized function name.

Does this mean I am unable to achieve what I want in SQL2000?

Mnay Thanks

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

I have now run the query over SQL2005 and it works how I want it to -

See sample below -

Tenancy Reference Arrears Arrears Inc HB Arrears Inc HB and SP Net Rent Payment Method Tenancy Status Tenant Name Address 1 Address 2 Address 3 Address 4 Address 5 PostCode PrePaid Rent Group Tenancy Type Arrears Officer Arrears Officer Name Parish Code Bedrooms Location Type Building Type Management Area Scheme Accounts Company Gross Rent Tenancy StartDate Tenancy EndDate Arrears Inc HB Excluding Credits Arrears Inc HB and SP Excluding Credits Tenancy Category RW

-------------------- --------------------- --------------------- --------------------- ---------- -------------- -------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------- -------------------- -------------------- --------------- --------------------- ---------- ------------ --------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------- ------------- ------------- --------------- ---------- ---------------- ---------- ----------------------- ----------------------- -------------------------------- --------------------------------------- ---------------- --------------------

1002101 1052.62 1052.62 1052.62 0 DDEB CUR Ms S Rapp 28 Constitution Hill Chatham Kent ME5 7DP 0.00 O LEA NI05 Mr J Donovan 01 2 FLAT PURCHASE MHS LEASED 100 0 2005-09-10 00:00:00.000 NULL 1052.62 1052.62 LEA 1

1002801 810.00 810.00 810.00 0 DDEB CUR Miss C Janion 58 Cordelia Crescent Rochester Kent ME1 3JB 0.00 O LEA NI05 Mr J Donovan 11 0 FLAT PURCHASE MHS LEASED 100 0 2003-11-27 00:00:00.000 NULL 810.00 810.00 LEA 1

106401 400.00 400.00 400.00 0 CASH CUR Mr M S Gill 183 Wayfield Road Chatham Kent ME5 0JB 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 400.00 400.00 LEA 1

1080401 550.00 550.00 550.00 0 CASH CUR Mr A Haines 13 Blockmakers Court Shipwrights Avenue Chatham Kent ME4 5JE 0.00 O LEA NI05 Mr J Donovan 01 3 FLAT PURCHASE MHS LEASED 100 0 2004-10-09 00:00:00.000 NULL 550.00 550.00 LEA 1

109901 867.02 867.02 867.02 0 CASH CUR Mr & Mrs J Jabbour 1 Cazeneuve Street Rochester Kent ME1 1XU 0.00 O LEA NI05 Mr J Donovan 11 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 867.02 867.02 LEA 1

109901 867.02 867.02 867.02 0 CASH CUR Mr & Mrs J Jabbour 1 Cazeneuve Street Rochester Kent ME1 1XU 0.00 O LEA NI05 Mr J Donovan 11 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 867.02 867.02 LEA 2

1123102 710.00 710.00 710.00 0 DDEB CUR Mr J Dow 42 Stirling Close Rochester Kent ME1 3AJ 0.00 O LEA NI05 Mr J Donovan 11 0 FLAT PURCHASE MHS LEASED 100 0 2004-12-11 00:00:00.000 NULL 710.00 710.00 LEA 1

1140901 180.00 180.00 180.00 0 DDEB CUR Lodhi Finance Ltd 4 Ordnance Street Chatham Kent ME4 6SL 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 180.00 180.00 LEA 1

1146101 180.00 180.00 180.00 0 DDEB CUR Mrs A Bricknell 6 Ordnance Street Chatham Kent ME4 6SL 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 180.00 180.00 LEA 1

1151401 180.00 180.00 180.00 0 DDEB CUR Mr S & Mrs P Patel 8 Ordnance Street Chatham Kent ME4 6SL 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 180.00 180.00 LEA 1

1151401 180.00 180.00 180.00 0 DDEB CUR Mr S & Mrs P Patel 8 Ordnance Street Chatham Kent ME4 6SL 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 180.00 180.00 LEA 2

As you can see I have two entries for 109901 and 1151401 and so the RW column is recording 1 and then 2.

What I now want to do is say if RW is greater then one then the value in the Arrears column would be 0.00

So like below

1151401 180.00 180.00 180.00 0 DDEB CUR Mr S & Mrs P Patel 8 Ordnance Street Chatham Kent ME4 6SL 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 180.00 180.00 LEA 1

1151401 0.00 180.00 180.00 0 DDEB CUR Mr S & Mrs P Patel 8 Ordnance Street Chatham Kent ME4 6SL 0.00 O LEA NI05 Mr J Donovan 01 0 UNIT PURCHASE MHS LEASED 100 0 2003-04-01 00:00:00.000 NULL 180.00 180.00 LEA 2

In my Select Statement I have the current logic -

SELECT TEN.[tenancy-ref] AS 'Tenancy Reference'

,CASE WHEN TEN.[curr-balance] >0

THEN TEN.[curr-balance]

ELSE 0 END AS 'Arrears'

So If the [curr-balance] is over 0.00 then pull me back the figure - otherwise just 0.00 it.

I have changed it slightly -

SELECT TEN.[tenancy-ref] AS 'Tenancy Reference'

,CASE WHEN TEN.[curr-balance] >0 AND 'RW' > '1'

THEN TEN.[curr-balance]

ELSE 0 END AS 'Arrears'

So I am thinking that if the [curr-balance]is over 0.00 AND the RW field is over one then pull me back a 0.00 figure. However - ALL of the Arrears field is coming up as 0.00 - Why?

Thanks