query question
-
Hello Guys,
It is raining and my brain is almost dead.
Anyway... Anyone has a hint how to retrieve the following:
I have a table:
select * from tbl_test gets the following resultset:
Ident User_Num Name Week_Ended Worked_in_Office
1 111 Agent Smith 2004-01-01 Y
2 111 Agent Smith 2004-01-08 N
3 111 Agent Smith 2004-01-15 N
4 111 Agent Smith 2004-01-22 N
5 111 Agent Smith 2004-01-29 Y
6 111 Agent Smith 2004-02-06 N
7 111 Agent Smith 2004-02-14 N
8 111 Agent Smith 2004-02-20 N
9 111 Agent Smith 2004-02-26 N
10 111 Agent Smith 2004-03-02 A
Of course it is more People in the office, but here is the scenario:
If Smith is NOT working in the office (N) for 2 weeks or more - result should get in the query.
It should display the first Week_Ended date and the last Week_Ended date before the week he again will be working in the office.
So the output might be the following:
Ident User_Num Name Week_Ended Worked_in_Office
2 111 Agent Smith 2004-01-08 N
4 111 Agent Smith 2004-01-22 N
...........................................................
6 111 Agent Smith 2004-02-06 N
9 111 Agent Smith 2004-02-26 N
..............................................................
TIA
-
This was removed by the editor as SPAM
-
OK so you want two kinds of row displayed in the query - type A: those that are the start of a 2+ week run of not-in-office, and type B: those that are the end of a 2+ week run of not-in-office.
Another way to characterise such rows is:
type A: row with N, preceding row has Y, following row has N
type B: row with N, preceding row has N, following row has Y
To be succinct, we can observe that we want all rows which with N, where the preceding and following rows have different out-of-office status (sneaky huh!)
So in T-SQL we would say
select * from tbl_test as T -- don't use select * in production environment, of course
where
Worked_in_Office = 'N'
and
(select Worked_in_Office from tbl_test as Tbefore where Tbefore.User_Num=T.User_Num and Tbefore.Week_Ended=T.Week_Ended-7)
<>
(select Worked_in_Office from tbl_test as Tafter where Tafter.User_Num=T.User_Num and Tafter.Week_Ended=T.Week_Ended+7)
Note, however, that our sneaky bit of succinctness makes this query fragile with regards to the '2 week' criterion being changed. You just know that the day after you proudly present this query, management will say 'oh, now we are only interested in THREE week out of office period'. For that you would have to spell out type A and type B separately:
type A: worked in office = N, in week before = Y, in week after = N, in week 2 after = N
type B: worked in office = N, in week 2 before = N, in week before = N, in week after = Y
No nice way of compacting those down
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply