从1的多行获取数据 [英] get data from many rows on 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

推荐答案

我对SQL的一般经验是，当您变得过于复杂时，它几乎是不可能做的，更重要的是不可能维护.

我会选择一个超集，就像将所有的休息和完成都放入数据集中一样，然后在C#中进行筛选，直到达到所需的答案.

只要您的记录不是数以百万计，它就会足够快.

My general experience with SQL is when you get too complicated its almost impossible to do and more importantly impossible to maintain.

I''d select a superset, like all your breaks and finishes into a dataset and then do your filtering down to your desired answer in C#.

As long as your records aren''t in the millions then it will probably be fast enough.

这篇关于从1的多行获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！