Scripting issue - 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

General SQL Server Forums

Script Library

Scripting issue

Author

Topic

falzar
Starting Member

2 Posts

Posted - 2010-03-10 : 06:46:24

Hello,

Pretty new to the forum so please bear with me. I have been writing a script for our server which records holds all our call records. Each call number has various fields such as FLS_FAULT_NUMBER, FLS_FAULT DATE, FLS_FAULT_ACTION held on table FLS0105M. I need to write a script where the two most recent FLS_FAULT_ACTION = 'CALLBACK'. However, then the call back is recorded on the system, there may be blank line entries after a call back e.g

FLS_FAULT_NUMBER FLS_FAULT_NOTES_SEQ FLS_FAULT_ACTION
1234567 1 LOGGED
1234567 2 ASSIGN
1234567 3 ACTIONED
1234567 4 ONHOLD
1234567 5
1234567 6 ONHOLD
1234567 7 CALLBACK
1234567 8
1234567 9 CALLBACK
1234567 10

Although notes_seq is 8 and 10 are null, I am after the double callback, as these are the most recent actions that = CALLBACK and are not null.

I have tried the below script, which works for a single fault number. However, when you remove the where clause for the fault number the script returns nothing. Even though it should search the entire database:

SELECT * FROM(
SELECT
FLS0105M.FLS_FAULT_NOTES_SEQ AS SEQ,
FLS0105M.FLS_FAULT_NUMBER,
FLS0105M.FLS_FAULT_ACTION AS ACTION,
LAG(FLS0105M.FLS_FAULT_ACTION,1) over (order by FLS0105M.FLS_FAULT_NOTES_SEQ) AS ACTION_1,
MAX(FLS0105M.FLS_FAULT_NOTES_SEQ) over (order by FLS0105M.FLS_FAULT_NUMBER) AS MAX_SEQ
FROM
FLS0105M
WHERE
(
FLS0105M.FLS_FAULT_NUMBER = '1465632'
AND FLS0105M.FLS_FAULT_ACTION IS NOT NULL
))
WHERE ACTION_1 = ACTION AND
SEQ = MAX_SEQ
AND ACTION = 'ONHOLD'
/

As you can see, I have tried a lag but this has not worked. The above script will work for one call. The below script does not work. Any suggestions would be great.

SELECT * FROM(
SELECT
FLS0105M.FLS_FAULT_NOTES_SEQ AS SEQ,
FLS0105M.FLS_FAULT_NUMBER,
FLS0105M.FLS_FAULT_ACTION AS ACTION,
LAG(FLS0105M.FLS_FAULT_ACTION,1) over (order by FLS0105M.FLS_FAULT_NOTES_SEQ) AS ACTION_1,
MAX(FLS0105M.FLS_FAULT_NOTES_SEQ) over (order by FLS0105M.FLS_FAULT_NUMBER) AS MAX_SEQ
FROM
FLS0105M
WHERE
(
FLS0105M.FLS_FAULT_ACTION IS NOT NULL
))
WHERE ACTION_1 = ACTION AND
SEQ = MAX_SEQ
AND ACTION = 'CALLBACK'
/

falzar
Starting Member

2 Posts

Posted - 2010-03-10 : 09:05:56

Not sure if this is in the forum sectiion. Sorry if it's not.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources