SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

find intime and out time

Last post 10-17-2014, 5:20 PM by eralper. 1 replies.
Sort Posts:
  •  03-01-2013, 1:28 AM 35108

    find intime and out time

    I have a  table Events

    Events containing

    cardno,time,id,name--each id has a cardno



    my requirement is every day each employee swipe the card several times

    i want to calculate first and last time of each card

    the output should be

    name     1                       2                             6        7  
               in       out            in        out                    holiday   holiday
    xxx      09:30     06:30      09:40   06:45
    where 1,2...... are dates for example january 1,2, etc. 6 and 7 are saturday and sunday

    how it is posssible
    Thanks in advance
    Baiju
  •  10-17-2014, 5:20 PM 39730 in reply to 35108

    Re: find intime and out time

    Hello Baiju,

    Below you can find a pivot table select statement with aggregation functions used together with PARTITITON BY clause. Since the format you provide is not a tabular format (date column is merged and for in and out) it is difficult to get a result as you mention

    But I hope below script will guide you for a solution.

    You should better you SQL Server Reporting Services (SSRS) reports for such display formats

    Here is the query with sample data


    /*
    Create Table [Events] (
    id int identity(1,1),
    cardno int,
    name varchar(50),
    time datetime
    )

    insert into [Events] select 1,'Kodyaz','20141016 08:44'
    insert into [Events] select 1,'Kodyaz','20141016 13:20'
    insert into [Events] select 1,'Kodyaz','20141017 09:30'
    insert into [Events] select 1,'Kodyaz','20141017 12:20'
    insert into [Events] select 1,'Kodyaz','20141017 13:30'
    insert into [Events] select 1,'Kodyaz','20141017 17:45'
    insert into [Events] select 2,'SQLAdmin','20141017 06:20'
    insert into [Events] select 2,'SQLAdmin','20141017 09:30'
    insert into [Events] select 2,'SQLAdmin','20141017 14:10'
    insert into [Events] select 2,'SQLAdmin','20141017 19:30'
    */


    -- pivot table query
    select
    name, 'in' tcode, [16],[17],[18]
    from (

    select
    name,
    DATEPART(dd, [date]) [date],
    CAST(DATEPART(hh, first_transaction) as varchar(2)) + ':' + CAST(DATEPART(mm, first_transaction) as varchar(2)) [in],
    CAST(DATEPART(hh, last_transaction) as varchar(2)) + ':' + CAST(DATEPART(mm, last_transaction) as varchar(2)) [out]
    from (
    select
    distinct cardno, name, CAST(time as DATE) [date],
    MIN(time) over (partition by cardno, name, CAST(time as DATE)) first_transaction,
    MAX(time) over (partition by cardno, name, CAST(time as DATE)) last_transaction
    from [Events]
    ) cte

    ) DataTable
    PIVOT
    (
    MAX([in])
    FOR [date]
    IN (
    [16],[17],[18]
    )
    ) PivotTable

    union all

    select
    name, 'out' tcode, [16],[17],[18]
    from (

    select
    name,
    DATEPART(dd, [date]) [date],
    CAST(DATEPART(hh, first_transaction) as varchar(2)) + ':' + CAST(DATEPART(mm, first_transaction) as varchar(2)) [in],
    CAST(DATEPART(hh, last_transaction) as varchar(2)) + ':' + CAST(DATEPART(mm, last_transaction) as varchar(2)) [out]
    from (
    select
    distinct cardno, name, CAST(time as DATE) [date],
    MIN(time) over (partition by cardno, name, CAST(time as DATE)) first_transaction,
    MAX(time) over (partition by cardno, name, CAST(time as DATE)) last_transaction
    from [Events]
    ) cte

    ) DataTable
    PIVOT
    (
    MAX([out])
    FOR [date]
    IN (
    [16],[17],[18]
    )
    ) PivotTable
    order by name, tcode


    Please refer to Transact-SQL tutorial Pivot Tables in SQL Server for pivot table usage in SQL Server
View as RSS news feed in XML
Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems