Post reply

Displaying Multiple rows clock event Under a different Column view

  • October 26, 2016 at 3:07 pm

    #1909013

    philand3 (10/26/2016)

    Hello Chris, Thanks i did coding in ASP.NET itself.

    By the way i am trying to bring query with the last clock_in where no clock_out for the same details ascending order by preferred_name,

    how to achieve with your codings ?

    this query should give current date clock details.

    BR

    I think at this point I have more questions than answers for you on this issue. Is there separate tables that have the people and department information in them? Are you looking for people who are currently clocked in or are you looking for the most recent past occurrence of a missing clock_out event for each person?

    Something like this would find all occurrences of CLOCK_IN events without a CLOCK_OUT, if there's a separate person table I'd probably write this differently to only find the most recent.

    SELECT ci.person_num, ci.full_name, ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,

    CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,

    co.event_name, CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,

    DATEDIFF(minute, ci.creation_date, co.creation_date) AS mintues

    FROM TEST ci

    OUTER APPLY (SELECT TOP 1 creation_date, event_name FROM TEST t WHERE t.person_num = ci.person_num AND t.creation_date > ci.creation_date ORDER BY t.creation_date) co

    WHERE ci.event_name = 'CLOCK_IN'

    AND (co.event_name IS NULL OR co.event_name = 'CLOCK_IN')

  • October 27, 2016 at 4:14 am

    #1909071

    Hello Chris,

    Thanks , Glad to see more hit in our Question and Answer. Sorry if i ask more question . Is it possible i can ask many or do i have to limit myself with limited questions.Please confirm since i am new to this website plus i always think with new questions with different requirement.

    Not sure about this website rule. please advise .

    BR

  • October 27, 2016 at 6:58 am

    #1909107

    It's probably best to start a new topic in the forums if the question is different. At this point I think we're into data cleansing (determining mismatched CHECK_IN and CHECK_OUT events) than the original question which was about pivoting multiple rows into columns. Please try to keep a topic in the right forum too, I noticed this was put in "Administration - SQL Server 2014" but should have been in "Development - SQL Server 2014"

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply