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