从1的多行获取数据

本文介绍了从1的多行获取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表，其中包含有关员工在工作中的事件.

事件日志
============
radioNumber，开始(日期时间)，状态(varchar)

大约有50个广播，并且每天为每个广播插入一个结束"事件，该事件指定员工的结束时间.在员工轮班期间，我们还需要捕获并显示4个休息"事件.结束事件是我们跟踪感兴趣的每个范围的开始和结束的方式.

我想做的是在一行显示事件摘要的输出上产生输出.我需要4个break事件，并在每行上指定1个finish事件.这让我很困惑，我不知道该怎么办！我有一个查询，返回两个中断时间，但我的代码是一团糟.必须有一种更简单的方法.

到目前为止，这里是我的代码:

I have a table which contains events about an employee while working.

Event Log
=============
radioNumber, start(datetime), status(varchar)

There are 50 or so radio''s, and each day a "finish" event is inserted for each radio, which specifies the finish time of the employee. There are also 4 "break" events that we need to capture and display during an employee''s shift. The finish event is how we track the beginning and end of each range that we''re interested in.

What I''m trying to do, is to produce output on one row that shows a summary of the events. I need 4 break events, and 1 finish event specified on each row. It''s boggling my mind, i don''t know what to do! I''ve got a query that returns two break times, but my code is a mess. There must be an easier way.

Here my code so far:

SELECT RADIONUMBER,
(SELECT TOP 1
(CASE WHEN NOT ISNULL([start],'') = '' THEN
    LEFT(CONVERT(nvarchar,[start],108),5) + ' - ' +
        CASE WHEN NOT ISNULL([finish],'') = '' THEN
            LEFT(CONVERT(nvarchar,[finish],108),5)
        ELSE
            '00:00'
        END
        + ' (' +
            CONVERT(nvarchar,
                DATEDIFF(n,'00:00',
                    dbo.getTimeDiff([start],ISNULL([finish],GETDATE())
                    )
                )
            ) + ' mins)'
    ELSE
    ''
    END)
FROM CREW_LOG a WHERE status = 'brk' and [radio] = RADIONUMBER AND datestamp >= DATEADD(hh,-14,GETDATE()) AND a.id > dbo.getLast1019(RADIONUMBER)
) AS break1summary,
(SELECT TOP 1
[start]
FROM CREW_LOG a WHERE status = 'brk' and [radio] = RADIONUMBER AND datestamp >= DATEADD(hh,-14,GETDATE()) AND a.id > dbo.getLast1019(RADIONUMBER)
) AS break1time,

(SELECT TOP 1
(CASE WHEN NOT ISNULL([start],'') = '' THEN
    LEFT(CONVERT(nvarchar,[start],108),5) + ' - ' +
        CASE WHEN NOT ISNULL([finish],'') = '' THEN
            LEFT(CONVERT(nvarchar,[finish],108),5)
        ELSE
            '00:00'
        END
    + ' (' + CONVERT(nvarchar,DATEDIFF(n,'00:00', dbo.getTimeDiff(LEFT(CONVERT(nvarchar,[start],108),5),LEFT(CONVERT(nvarchar,ISNULL([finish],GETDATE()),108),5)))) + ' mins)'
    ELSE
    ''
    END)
FROM CREW_LOG a WHERE not a.[start] =
    (SELECT top 1 [start] FROM CREW_LOG a WHERE status = 'brk' and [radio] = RADIONUMBER AND datestamp >= DATEADD(hh,-14,GETDATE()) AND a.id > dbo.getLast1019(RADIONUMBER))
     and status = 'brk' and [radio] = RADIONUMBER AND datestamp >= DATEADD(hh,-14,GETDATE()) AND a.id > dbo.getLast1019(RADIONUMBER)
) AS break2summary,

    (SELECT CONVERT(DATETIME,sum(CONVERT(NUMERIC(9,2),
        CONVERT(DATETIME,dbo.timepart(ISNULL([finish],getdate()))) - CONVERT(DATETIME,dbo.timepart([start]))
    ))) FROM CREW_LOG a WHERE status = 'stby' and [radio] = RADIONUMBER AND datestamp >= DATEADD(hh,-14,GETDATE()) AND a.id > dbo.getLast1019(RADIONUMBER)
) as totalstby,

(SELECT TOP 1
(CASE WHEN NOT ISNULL([start],'') = '' THEN
    LEFT(CONVERT(nvarchar,[start],108),5) + ' - ' +
        CASE WHEN NOT ISNULL([finish],'') = '' THEN
            LEFT(CONVERT(nvarchar,[finish],108),5)
        ELSE
            '00:00'
        END
    + ' (' + CONVERT(nvarchar,DATEDIFF(n,'00:00', dbo.getTimeDiff(LEFT(CONVERT(nvarchar,[start],108),5),LEFT(CONVERT(nvarchar,ISNULL([finish],GETDATE()),108),5)))) + ' mins)'
    ELSE
    ''
    END)
FROM CREW_LOG a WHERE status = 'stby' and [radio] = RADIONUMBER AND datestamp >= DATEADD(hh,-14,GETDATE()) AND a.id > dbo.getLast1019(RADIONUMBER)
) AS laststbysummary

FROM [dbo].[getCurrentCrewNumbers] tblCrewsList
ORDER BY RADIONUMBER

finish

从1的多行获取数据

问题描述

推荐答案