Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Talking Unanswered: conditional count

    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. (Remember the con/csa tables (Zorgrapp/Talrapp) ?)

    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.

    You can do it with T-SqL or in a SSIS package (foreach loop ?)

    Anyway, if you can tackle this one, I will raise a new project for you and another $100,-
    are going your way.

    Hope to hear from you.

    Regards,

    Arthur

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You will need an extra column that holds the date of the event. This should work:
    Code:
    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
    
    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
    If you could live with duplicate values in the count_ column, the query would be simpler.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    Talking Unbelievable

    Hello

    This is great stuff. Never saw this #DaTable.
    You are making me very happy with your reply and so quick.

    Thanks and happy coding

    Regards,

    Arthur

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Anyway, if you can tackle this one, I will raise a new project for you and another $100,- are going your way.
    Err ... aren't you forgetting something?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2011
    Posts
    3

    I got it...



    I got it...

    Anyway, if you can tackle this one, I will raise a new project for you and another $100,- are going your way.

    Can I do the payment through Elance ... Or paypall ... Mail ...

    Send me you details and 'the salt of the earth' is on your way...

    Regards,

    Arthur

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Wow! Thanks for your prompt payment!

    Can I keep the rest of those $ 1000? Or was it a typo? I'll refund you those $ 900 if it was a mistake, or I'll solve another 9 problems for you. Just indicate what you prefer.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •