locked
how to -count on the same day RRS feed

  • Question

  • need help

    count in the same day like this if the employee have 2 shifts in the same day

    i use for shift the value 7,8,9

     

    empID      fname          val_shift        date_shift

    --------------------------------------------------------------------

    1111         aaaaa           7               01/05/2008

    1111         aaaaa           8               01/05/2008

    2222         bbbbb           7               01/05/2008

    3333         ccccc           7               01/05/2008

    4444         ddddd           8               01/05/2008

    4444         ddddd           9               01/05/2008

     

    1111         aaaaa           7               02/05/2008

    2222         bbbbb           8               02/05/2008

    2222         bbbbb           7               02/05/2008

    3333         ccccc           7               02/05/2008

    3333         ddddd           8               02/05/2008

    4444         ddddd           9               02/05/2008

     

    need to do like this count in new field if the employee have 2 shifts

    if not than null

     

     

    empID      fname          val_shift        date_shift              double_shift_same_day_remark

    ----------------------------------------------------------------------------------------------------------------------------------------------------

    1111         aaaaa           7               01/05/2008                 you have 2 shifts in the date 01/05/2008

    1111         aaaaa           8               01/05/2008                  you have 2 shifts in the date 01/05/2008

    2222         bbbbb           7               01/05/2008

    3333         ccccc           7               01/05/2008

    4444         ddddd           8               01/05/2008                    you have 2 shifts in the date 01/05/2008

    4444         ddddd           9               01/05/2008                     you have 2 shifts in the date 01/05/2008

     

    1111         aaaaa           7               02/05/2008

    2222         bbbbb           8               02/05/2008                       you have 2 shifts in the date 02/05/2008

    2222         bbbbb           7               02/05/2008                        you have 2 shifts in the date 02/05/2008

    3333         ccccc           7               02/05/2008

    3333         ddddd           8               02/05/2008                      you have 2 shifts in the date 02/05/2008

    4444         ddddd           9               02/05/2008                       you have 2 shifts in the date 02/05/2008

     

    TNX

    Monday, May 12, 2008 11:10 PM

Answers

  • Code Snippet

    with cteShifts

    as

    (SELECT EmpID, date_shift, Shifts=count(*)

    FROM TableX

    GROUP BY EmpID, date_shift

    ORDER BY EmpID, date_shift;

    )

    SELECT a.EmpID, a.Fname, a.val_shift, a.Date_shift,

    case

    when Shifts = 2 then 'You have 2'

    else NULL

    end

    FROM TableX a

    join cteShifts cte

    on a. EmpID = cte.EmpID

    and a.date_shift=cte.date_shift

     

     

    Let us now if worked.

    Tuesday, May 13, 2008 8:39 AM
  • Affirmative. You cannot put ORDER BY into a CTE.

     

    Tuesday, May 13, 2008 12:51 PM

All replies

  • Code Snippet

    SELECT ID=identity(int,1,1),  EmpID, date_shift

    into #DoubleShifters

    FROM TableX

    GROUP BY EmpID, date_shift

    HAVING count(*) > 1

    ORDER BY EmpID, date_shift;

     

    SELECT * FROM #DoubleShifters;

     

     

     

    Tuesday, May 13, 2008 12:54 AM
  • tnx

    but i  must show all the emplyee

    not only how is    count(*) > 1

    like this

     

    empID      fname          val_shift        date_shift               count_remark

    ----------------------------------------------------------------------------------------------------------------------------------------------------

    1111         aaaaa           7               01/05/2008                 you have 2

    1111         aaaaa           8               01/05/2008                 you have 2

    2222         bbbbb           7               01/05/2008                  null

    3333         ccccc           7               01/05/2008                    null

    4444         ddddd           8               01/05/2008                   you have 2

    4444         ddddd           9               01/05/2008                    you have 2

     

    1111         aaaaa           7               02/05/2008                     null

    2222         bbbbb           8               02/05/2008                     you have 2

    2222         bbbbb           7               02/05/2008                      you have 2

    3333         ccccc           7               02/05/2008                        null

    3333         ddddd           8               02/05/2008                      you have 2

    4444         ddddd           9               02/05/2008                       you have 2

     

     

    tnx

    Tuesday, May 13, 2008 5:49 AM
  • i try do do this

    (it is from sqlusa idea)

     

    Code Snippet

    SELECT empID, date_shift,fname

    CASE empID

    WHEN

    (HAVING (COUNT(empID) = 3) THEN 3 empID END AS aaaaa

    WHEN

    (HAVING (COUNT(empID) = 2) THEN 2 empID END AS aaaaa

    WHEN

    (HAVING (COUNT(empID) = 1) THEN 1 empID END AS aaaaa

     

     

    but i get error

    can someone help

     

    tnx

    Tuesday, May 13, 2008 7:20 AM
  • Code Snippet

    with cteShifts

    as

    (SELECT EmpID, date_shift, Shifts=count(*)

    FROM TableX

    GROUP BY EmpID, date_shift

    ORDER BY EmpID, date_shift;

    )

    SELECT a.EmpID, a.Fname, a.val_shift, a.Date_shift,

    case

    when Shifts = 2 then 'You have 2'

    else NULL

    end

    FROM TableX a

    join cteShifts cte

    on a. EmpID = cte.EmpID

    and a.date_shift=cte.date_shift

     

     

    Let us now if worked.

    Tuesday, May 13, 2008 8:39 AM
  •  SQLUSA wrote:

    Code Snippet

    with cteShifts

    as

    (SELECT EmpID, date_shift, Shifts=count(*)

    FROM TableX

    GROUP BY EmpID, date_shift

    -----ORDER BY EmpID, date_shift;

    )

    SELECT a.EmpID, a.Fname, a.val_shift, a.Date_shift,

    case

    when Shifts = 2 then 'You have 2'

    else NULL

    end

    FROM TableX a

    join cteShifts cte

    on a. EmpID = cte.EmpID

    and a.date_shift=cte.date_shift

     

     

    Let us now if worked.

    tnx sqlusa

    it work but like this

    if i put this line -----ORDER BY EmpID, date_shift;

     i get error

    Msg 1033, Level 15, State 1, Line 11

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

     

    so i do this and it work like this wonderful,

     

     

    Code Snippet

    with cteShifts

    as

    (SELECT EmpID, date_shift, Shifts=count(*)

    FROM TableX

    GROUP BY EmpID, date_shift

     

    )

    SELECT a.EmpID, a.Fname, a.val_shift, a.Date_shift,

    case

    when Shifts = 2 then 'You have 2'

    else NULL

    end

    FROM TableX a

    join cteShifts cte

    on a. EmpID = cte.EmpID

    and a.date_shift=cte.date_shift

    ORDER BY EmpID, date_shift

     

    SQLUSA tnx for the help and the support

     

    Tuesday, May 13, 2008 12:01 PM
  • Affirmative. You cannot put ORDER BY into a CTE.

     

    Tuesday, May 13, 2008 12:51 PM