基于具有不相关表的行值的SQL子查询 [英] SQL subquery based on row values with unrelated table

问题描述

我需要基于具有某些中等复杂联接的查询中的行值来获取不相关表中的记录数.所有数据都在单个SQL 2012数据库中的几个不同表上的一台服务器上.

I need to get a count of records in an unrelated table, based on the row values in a query with some moderately complex joins. All data is on one server in a single SQL 2012 database, on several different tables.

我正在一次从审核记录中重新创建一张票证的票证移动历史记录，并且需要计算联接所创建的行中跨度的工作日.票证在两个区域之间移动(分配)，并且有关于在任何一个区域应该保留多长时间的准则.每次重新开始计时后，票证可能会多次到达同一区域.

I am recreating ticket movement history for a single ticket at a time, from audit records and need to calculate business days for the spans in rows created by the joins. Tickets are moved around between areas (ASSIGNMENT), and there are guidelines on how long it should be at any one area. The ticket may go to the same area multiple times with each time restarting the time count.

我需要在工作日计算中考虑公司假期.在查看了SE上几个营业日计算解决方案后，我决定使用公司日历表(dbo.UPMCCALENDARM1)并计算跨度之间的日期.似乎是个好主意...

I need to consider company holidays in the business day calculations. After looking at several solutions for business day calculations on SE I decided to go with a company calendar table (dbo.UPMCCALENDARM1) and count the dates between spans. Seemed like a great idea...

我不知道如何将行值用作日期计数查询的参数.

I can't figure out how to use the row values as parameters for the date count query.

下面的查询具有使用变量和交叉联接的有效解决方案，但是它仅适用于硬编码日期，如果我尝试使用字段值，则它不起作用，因为它们不是子查询的一部分，并且不能束缚.

The query below has working solutions with a Variable and with a Cross Join, but it only works with hard coded dates, if I try to use the field values it does not work, because they are not part of the sub query and can not be bound.

-在DV_im_Audit_ASSIGNMENT.Time和Detail.RESOLVED_TIME之间

-- between DV_im_Audit_ASSIGNMENT.Time and Detail.RESOLVED_TIME

从理论上讲，我可能可以在子查询中使用此完整查询来获取日期计数，但这是我可以做到的，但仍然可以获取干净的数据.对于按需报告而言，这是一个沉重的负担，这将是我的最后选择.因此，我希望在每次出现DV_im_Audit_ASSIGNMENT.Time和Detail.RESOLVED_TIME时都与UPMCCALENDARM1联系.

In theory I could probably get there using this full query in the sub query to get the date count, but this is as short as I can make it and still get clean data. It is a pretty heavy lift for an on demand report, that would be my last option. So I want to reach out to UPMCCALENDARM1 as each occurrence of DV_im_Audit_ASSIGNMENT.Time and Detail.RESOLVED_TIME are listed.

可以做到吗?如果可以，怎么办?

Can it be done? If so how?

declare @NonBus integer 
set @NonBus = '0'
set @NonBus = (select Count(UPMCCALENDARM1.DATE) as NonBus
            from dbo.UPMCCALENDARM1
            where UPMC_BUSINESS_DAY = 'f'
            and UPMCCALENDARM1.DATE 
            between '2015-08-01' and '2015-08-31'
--          between DV_im_Audit_ASSIGNMENT.Time and Detail.RESOLVED_TIME
            )

select DV_im_Audit_ASSIGNMENT.Incident_ID
, DV_im_Audit_ASSIGNMENT.Old_ASSIGNMENT
, DV_im_Audit_ASSIGNMENT.New_ASSIGNMENT
, DV_im_Audit_ASSIGNMENT.Time as Assign_Time
, B.Time as Reassign_Time
, Detail.OPEN_TIME
, Cal.NonBus
, NonBus
, Detail.RESOLVED_TIME
, A.rownumA
, B.rownumB

from dbo.DV_im_Audit_ASSIGNMENT

--Get RownumA as a select join so I can work with it here, else get an invalid column name 'rownumA' error
left join(select Incident_ID
        , Old_ASSIGNMENT
        , New_ASSIGNMENT
        , [Time]
        , rownumA = ROW_NUMBER() OVER (ORDER BY  DV_im_Audit_ASSIGNMENT.Incident_ID, DV_im_Audit_ASSIGNMENT.Time)
        from dbo.DV_im_Audit_ASSIGNMENT
        where Incident_ID = ?
        ) as A
            on DV_im_Audit_ASSIGNMENT.Incident_ID = A.Incident_ID
            and DV_im_Audit_ASSIGNMENT.New_ASSIGNMENT = A.New_ASSIGNMENT
            and DV_im_Audit_ASSIGNMENT.Time = A.Time 

--Get time assigned to next group, is problomatic when assigned to the same group multiple times.
left join(select Incident_ID
        , Old_ASSIGNMENT
        , New_ASSIGNMENT
        , [Time]
        , rownumB = ROW_NUMBER() OVER (ORDER BY  DV_im_Audit_ASSIGNMENT.Incident_ID, DV_im_Audit_ASSIGNMENT.Time)
        from dbo.DV_im_Audit_ASSIGNMENT
        where Incident_ID = ?
        ) as B
            on DV_im_Audit_ASSIGNMENT.Incident_ID = B.Incident_ID
            and DV_im_Audit_ASSIGNMENT.New_ASSIGNMENT = B.Old_ASSIGNMENT
            and DV_im_Audit_ASSIGNMENT.Time < B.Time 
        and rownumA = (B.rownumB - 1)  

--Get current ticket info
left join (select Incident_ID
        , OPEN_TIME
        , RESOLVED_TIME
        from dbo.DV_im_PROBSUMMARYM1_Detail
        where Incident_ID = ?
        ) as Detail 
    on DV_im_Audit_ASSIGNMENT.Incident_ID = Detail.Incident_ID


--Count non-bussiness days.  This section is in testing and does not use dataview as a source.
-- this gets the date count for one group of dates, need a different count for each row based on assign time. 
cross join (Select Count(UPMCCALENDARM1.DATE) as NonBus
            from dbo.UPMCCALENDARM1
            where UPMC_BUSINESS_DAY = 'f'
            and UPMCCALENDARM1.DATE 
            between '2015-08-01' and '2015-08-30'
--          between DV_im_Audit_ASSIGNMENT.Time and Detail.RESOLVED_TIME
            ) as Cal


--Get data for one ticket
where DV_im_Audit_ASSIGNMENT.Incident_ID = ?

ORDER BY  DV_im_Audit_ASSIGNMENT.Incident_ID, DV_im_Audit_ASSIGNMENT.Time

结果

仅供参考-我正在通过BIRT 4.2运行此SQL，我相信很少有不能通过BIRT传递的SQL项

FYI - I am running this SQL through BIRT 4.2, I believe there are few SQL items that will not pass through BIRT

推荐答案

按照@Dominique的建议，我创建了一个自定义标量函数(使用SSMS中的向导)，我将默认值用于日期，就像从播放开始一样与存储过程，这使得测试更加容易.这个问题需要一个函数，因为它将每行返回一个值，而存储过程则不会.

Following the suggestion by @Dominique I created a custom scalar function (using the wizard in SSMS), I used default values for the dates as I had started by playing with stored procedure and that made it easier to test. This problem requires a function as it will return a value per row, where a stored procedure will not.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      James Jenkins
-- Create date: September 2015
-- Description: Counts Business Days for UPMC during a span of dates
-- =============================================
CREATE FUNCTION dbo.UPMCBusinessDayCount 
(
    -- Add the parameters for the function here
    @StartDate date = '2015-08-01',
    @EndDate date = '2015-08-31'
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @BusDay int

    -- Add the T-SQL statements to compute the return value here
    SELECT @BusDay = Count(UPMCCALENDARM1.DATE) 
        from dbo.UPMCCALENDARM1
        where UPMC_BUSINESS_DAY = 't'
        and UPMCCALENDARM1.DATE between @StartDate and @EndDate

    -- Return the result of the function
    RETURN @BusDay

END
GO

在数据库中创建函数之后，我将这两行添加到我的select语句中，并且运行良好.

After the function is created in the database I added these two lines to my select statement, and it works perfectly.

--Custom function counts business days on UPMCCALENDARM1
, dbo.UPMCBusinessDayCount(DV_im_Audit_ASSIGNMENT.Time, Detail.RESOLVED_TIME) as BusDay

我可以将此函数用于其中具有日期数据(或数据库中的任何查询)的任何范围.我可能会删除默认日期，并添加第三个参数以计算非工作日(UPMC_BUSINESS_DAY ='f').但问题就解决了.

I can use this function for any span that has date data in this (or any query on the database). I will probably be removing the default dates as well as adding a third parameter to count non-business days (UPMC_BUSINESS_DAY = 'f'). But as it is the problem is solved.

这篇关于基于具有不相关表的行值的SQL子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！