Automate a number - 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)

Automate a number

Author

Topic

M8KWR
Starting Member

26 Posts

Posted - 2007-07-24 : 11:53:23

I need to automatically generated a number in a new column within a view.

I need to look at a column called "RESULTCODE" when the value equals "APP" it would put a 1 against this, and then a 1 against all other records until it gets to another "APP" when it would increment to a 2

Is this possible...

Many thanks for any help in advance.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 11:56:07

Can you provide some sample code and expected output?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 12:15:22

[code]Adobe Acrobat 1
Adobe Acrobat 1
Adobe Acrobat 1
Adobe Acrobat 1
PageMager 2
PageMager 2
SQL Server 3
SQL Server 3
SQL Server 3
SQL Server 3
SQL Server 3[/code]

E 12°55'05"
N 56°04'39"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 12:24:35

Is there a primary key in the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 12:28:45

SELECT t1.*, (SELECT COUNT(DISTINCT t2.ResultCode FROM Table1 AS t2 WHERE t2.ResultCode <= t1.ResultCode)
FROM Table1 AS t1

E 12°55'05.76"
N 56°04'39.42"

M8KWR
Starting Member

26 Posts

Posted - 2007-07-25 : 02:51:16

The output would be something like

A = 0
App = 1
X = 1
C = 1
APP = 2
C = 2
X = 2
G = 2
APP = 3
K = 3
APP = 4
APP = 5

and so on,,,

Hope that helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 02:53:54

So that is the output. How about the sample data ?

KH
[spoiler]Time is always against us[/spoiler]

M8KWR
Starting Member

26 Posts

Posted - 2007-07-25 : 03:07:50

I hope this sample data helps you. The data is first sort by the ACCOUNTNO and then by ONDATE, this then would put the RESULTCODE in the correct order, before the process i require. If you requir anything else then please let me know. Thanks

ACCOUNTNO ONDATE RESULTCODE
A6021451658&OE1!& 16/07/07 C
A6021451658&OE1!& 24/07/07 APP
A6021451658&OE1!& 02/08/07 APP
A6021451658&OE1!& 10/08/07 MQI
A6021451658&OE1!& 01/09/07 SQO
A7072528906*6F.O&Kev 25/07/07 A
A7072528906*6F.O&Kev 25/07/07 APP
A7072528906*6F.O&Kev 31/07/07 MQO
A7072528906*6F.O&Kev 11/08/07 APP
A7072528906*6F.O&Kev 22/08/07 MQI
A7072528906*6F.O&Kev 19/09/07 SQO
A7072528906*6F.O&Kev 27/09/07 APP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 03:32:43

Can you explain how to get from the sample data to the output that you want ?

In your output, what is the meaning of "X = 1", "C = 1" etc ?

KH
[spoiler]Time is always against us[/spoiler]

M8KWR
Starting Member

26 Posts

Posted - 2007-07-25 : 03:42:40

I need to create a new column (Lets say it is called "NoCount").

I need to identify what order the APP RESULTCODE where gained, so hopefully the output would be something like this below. The reason why the first output result is a "0" is that is not an APP RESULTCODE.

ACCOUNTNO ONDATE RESULTCODE NoCount
A6021451658&OE1!& 16/07/07 C 0
A6021451658&OE1!& 24/07/07 APP 1
A6021451658&OE1!& 02/08/07 APP 2
A6021451658&OE1!& 10/08/07 MQI 2
A6021451658&OE1!& 01/09/07 SQO 2
A7072528906*6F.O&Kev 25/07/07 A 0
A7072528906*6F.O&Kev 25/07/07 APP 1
A7072528906*6F.O&Kev 31/07/07 MQO 1
A7072528906*6F.O&Kev 11/08/07 APP 2
A7072528906*6F.O&Kev 22/08/07 MQI 2
A7072528906*6F.O&Kev 19/09/07 SQO 2
A7072528906*6F.O&Kev 27/09/07 APP 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 03:57:42

[code]DECLARE @TABLE TABLE
(
ACCOUNTNO varchar(20),
ONDATE datetime,
RESULTCODE varchar(3)
)
SET dateformat dmy
INSERT INTO @TABLE
SELECT 'A6021451658&OE1!&', '16/07/07', 'C' UNION ALL
SELECT 'A6021451658&OE1!&', '24/07/07', 'APP' UNION ALL
SELECT 'A6021451658&OE1!&', '02/08/07', 'APP' UNION ALL
SELECT 'A6021451658&OE1!&', '10/08/07', 'MQI' UNION ALL
SELECT 'A6021451658&OE1!&', '01/09/07', 'SQO' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '25/07/07', 'A' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '25/07/07', 'APP' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '31/07/07', 'MQO' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '11/08/07', 'APP' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '22/08/07', 'MQI' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '19/09/07', 'SQO' UNION ALL
SELECT 'A7072528906*6F.O&Kev', '27/09/07', 'APP'

SELECT ACCOUNTNO, ONDATE, RESULTCODE,
[NOCOUNT] = (SELECT COUNT(*) FROM @TABLE x
WHERE x.ACCOUNTNO = t.ACCOUNTNO
AND x.ONDATE <= t.ONDATE
AND x.RESULTCODE <= t.RESULTCODE
AND RESULTCODE = 'APP')
FROM @TABLE t
ORDER BY ACCOUNTNO, ONDATE

/*
ACCOUNTNO ONDATE RESULTCODE NOCOUNT
-------------------- ---------- ---------- -----------
A6021451658&OE1!& 2007-07-16 C 0
A6021451658&OE1!& 2007-07-24 APP 1
A6021451658&OE1!& 2007-08-02 APP 2
A6021451658&OE1!& 2007-08-10 MQI 2
A6021451658&OE1!& 2007-09-01 SQO 2
A7072528906*6F.O&Kev 2007-07-25 A 0
A7072528906*6F.O&Kev 2007-07-25 APP 1
A7072528906*6F.O&Kev 2007-07-31 MQO 1
A7072528906*6F.O&Kev 2007-08-11 APP 2
A7072528906*6F.O&Kev 2007-08-22 MQI 2
A7072528906*6F.O&Kev 2007-09-19 SQO 2
A7072528906*6F.O&Kev 2007-09-27 APP 3
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

M8KWR
Starting Member

26 Posts

Posted - 2007-07-25 : 04:09:43

Having a bit of issues with the ORDER By clause getting this error message "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

But apart from that it work a dream, your great.. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 04:17:41

if you are creating a view out of this, you don't required the ORDER BY

KH
[spoiler]Time is always against us[/spoiler]

M8KWR
Starting Member

26 Posts

Posted - 2007-07-25 : 04:32:14

Just worked that out.... :)

Would it be possible fo ryou to explain in english what the query is doing just so i know, as i know it all works how i wanted it to, but i am trying to learn more complex sql statements and it would help me

Many thanks again for all your assistance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 04:42:44

[code] [NOCOUNT] = (SELECT COUNT(*) FROM @TABLE x
WHERE x.ACCOUNTNO = t.ACCOUNTNO
AND x.ONDATE <= t.ONDATE
AND x.RESULTCODE <= t.RESULTCODE
AND RESULTCODE = 'APP')[/code]

the [NOCOUNT] = (select . . ) is a sub-query. It counts the number of records with RESULTCODE = 'API' that is earlier or same date as the current record of the same ACCOUNTNO

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources