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 |
|
avanrijn
Starting Member
3 Posts |
 Posted - 2011-09-08 : 09:32:05
|
| Emplid FunctionId Count 1------- 2 1 ------ 3 ---------- 1 1 ------ 4 ---------- 2 1 ------ 4 1 ------ 5 ---------- 3 1 ------ 6 ---------- 4 1 ------ 3 ---------- 5 2 ------ 3 2 ------ 3 2 ------ 1 ---------- 1 2 ------ 2 ---------- 2 H&R is looking for a measure to count the flexibility/mobility in the company. When an Employee is changing from job/function a FunctionID is stored in the DWH empl dim. See the example above how this table looks like (6000 employee records with lots of mutations as wel. So I need a count only when an employee is going to do something else (another function). The example above is showing you how the count output should be. How can do it with T-SqL or in a SSIS package (foreach loop ?) Hope the light is going to shine again.Hope to hear from you. Regards from Amsterdam, Arthur |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-09-08 : 11:50:45
|
| This would be a running total of sorts. To be able to do this, you need one additional piece of information - which probably is in your table already - and that is a column or criterion that will enable you to order the rows.By definition, rows in a database table are unordered. So unless you have some criterion or column - a datestamp column for example - that would allow you to do this ordering, you cannot predict the order in which the rows for a given employee would be returned.One other thing I want to mention is that, SQL is notoriously bad at computing running totals. So if you have only a few thousand rows, it might be fine, but if you get into the millions range, computing this in T-SQL would be very resource intensive. |
 |
|
|
avanrijn
Starting Member
3 Posts |
Posted - 2011-09-09 : 08:12:28
|
quote:
Originally posted by sunitabeck
This would be a running total of sorts. To be able to do this, you need one additional piece of information - which probably is in your table already - and that is a column or criterion that will enable you to order the rows.By definition, rows in a database table are unordered. So unless you have some criterion or column - a datestamp column for example - that would allow you to do this ordering, you cannot predict the order in which the rows for a given employee would be returned.One other thing I want to mention is that, SQL is notoriously bad at computing running totals. So if you have only a few thousand rows, it might be fine, but if you get into the millions range, computing this in T-SQL would be very resource intensive.
|
|
|
|
avanrijn
Starting Member
3 Posts |
Posted - 2011-09-09 : 08:13:46
|
| This is the 'solution' I have. Might be useful...This SQL can do the job. WITH CTE AS (SELECT Emplid, FunctionId, DaDay, row_number() over (partition by Emplid order by DaDay asc) as rowNr, 0 as nr FROM #DaTable ), RecCTE AS (SELECT Emplid, FunctionId, DaDay, rowNr, nr FROM CTE WHERE rowNr = 1 UNION ALL SELECT Cur.Emplid, Cur.FunctionId, Cur.DaDay, Cur.rowNr, CASE WHEN Cur.FunctionId = Prev.FunctionId THEN Prev.nr ELSE Prev.nr + 1 END as nr FROM CTE as Cur INNER JOIN RecCTE as Prev ON Cur.Emplid = Prev.Emplid AND Cur.rowNr = Prev.rowNr + 1 ) SELECT Emplid, FunctionId, DaDay, NULL FROM RecCTE WHERE rowNr = 1 UNION ALL SELECT Cur.Emplid, Cur.FunctionId, Cur.DaDay, CASE WHEN Cur.nr = 0 THEN NULL WHEN Cur.nr = Prev.nr THEN NUll Else Cur.nr END as Count_ FROM RecCTE as Cur INNER JOIN RecCTE as Prev ON Cur.Emplid = Prev.Emplid AND Cur.rowNr = Prev.rowNr + 1 ORDER BY 1, 3 CREATE TABLE #DaTable(Emplid INT NOT NULL,FunctionId INT NOT NULL,DaDay DATE NOT NULL) INSERT INTO #DaTable(Emplid, FunctionId, DaDay) VALUES (1, 2, '20000101'),(1, 3, '20001001'),(1, 4, '20010101'),(1, 4, '20020101'),(1, 5, '20030101'),(1, 6, '20040101'),(1, 3, '20050101'),(2, 3, '20000101'),(2, 3, '20001001'),(2, 1, '20010101'),(2, 2, '20020101') SELECT * FROM #DaTable |
|
|
|
|
|
|
|
|
conditional count