Hiiya All,
Trying to work out how I can use SQL to tell me how many days an person has worked consecutively before a date.
Source
+----------+---------+-----------+------------+
| Forename | Surname | EMP_Num | Shift Date |
+----------+---------+-----------+------------+
| John | Kerry | 0111 | 2017-09-28 |
+----------+---------+-----------+------------+
| John | Kerry | 0111 | 2017-09-29 |
+----------+---------+-----------+------------+
| John | Kerry | 0111 | 2017-09-30 |
+----------+---------+-----------+------------+
| John | Kerry | 0111 | 2017-10-02 |
+----------+---------+-----------+------------+
| John | Kerry | 0111 | 2017-10-03 |
+----------+---------+-----------+------------+
| Bill | Kerry | 0112 | 2017-09-28 |
+----------+---------+-----------+------------+
| Bill | Kerry | 0112 | 2017-09-29 |
+----------+---------+-----------+------------+
| Bill | Kerry | 0112 | 2017-10-01 |
+----------+---------+-----------+------------+
| Bill | Kerry | 0112 | 2017-10-02 |
+----------+---------+-----------+------------+
| Bill | Kerry | 0112 | 2017-10-03 |
+----------+---------+-----------+------------+
so the results for today (2017-10-03) will be...
+----------+---------+-----------+-------------+
| Forename | Surname | EMP_Num | Days Worked |
+----------+---------+-----------+-------------+
| John | Kerry | 0111 | 2 |
+----------+---------+-----------+-------------+
| Bill | Kerry | 0112 | 3 |
+----------+---------+-----------+-------------+
or result for 2017-09-30 would be
+----------+---------+-----------+-------------+
| Forename | Surname | EMP_Num | Days Worked |
+----------+---------+-----------+-------------+
| John | Kerry | 0111 | 3 |
+----------+---------+-----------+-------------+
| Bill | Kerry | 0112 | 0 |
+----------+---------+-----------+-------------+
Any Ideas?
I'm running SQL 2008 so no lead or lag I'm afraid.
PS Thanks for http://www.tablesgenerator.com/text_tables
