创建一个对某些数据进行分组但不对其他数据进行分组的视图 [英] Create a view that groups some data, but doesn't group other data

问题描述

我正在尝试编写查询以填充报告。

I am attempting to write a query to populate a report.

SQL Fiddle- http://sqlfiddle.com /＃！3 / 920eb

SQL Fiddle - http://sqlfiddle.com/#!3/920eb

查询依赖于几个不同的表

The query relies on several different tables

HardwareSupportRequest
tblCHSRLaborPerCHSR
LaborTypes
tblMaterialUsed
tblMaterial

在 HardwareSupportRequest 里面，我有很多字段，但是相关的字段是

Inside of HardwareSupportRequest, I have a huge array of fields, but the pertinent ones are

CHSRNumber - Basically the ID/unique identifier
Building - Used for grouping by location
WorkType - Can be 'Electric Install','Electric Removal', if it's something else, it'll be ignored and a differ field will be used to group.

在 tblCHSRLaborPerCHSR 内是

[CHSR#] - fk
[Hours Worked]
[Hourly CHSR Labor Rate]
LaborTypeId

在 tblCHSRLabor内部

Inside of 'tblCHSRLabor`

id
LaborType

在<$ c $内部c> tblMaterialUsed

MaterialId -fk
[CHSR#] - fk
[Amount Used]

在 tblMaterial内部

Inside of 'tblMaterial'

id - pk
[Item Cost]

我需要一种生成视图的方法，该方法执行一些相当复杂的操作。首先，如果记录的LaborType是 Install ， Testing 或 Build 。必须将它们视为一行（用于人工成本）。任何其他LaborType将有其自己的行。

I need a method of generating a view that does some rather complex operations. First, if the LaborType of a record is Install, Testing, or Build. They must be treated as a single row (for labor cost). Any other LaborType will have its own row.

它需要按 Building 和 LaborType 分组。我还必须计算每个记录有多少个CHSR。

It needs to be grouped by Building and LaborType. I also have to count how many CHSRs there are for each record.

在这一点上，我是如此的迷茫，以至于我的头都撞在键盘上了。我尝试过的每一种方法都失败了，或者让高层人士摇了摇头。

I am so beyond lost at this point that I'm bashing my head against the keyboard. Every method I have tried has either failed or had the high-ups shake their head at it.

我最近的尝试：

USE Facilities_Database
DECLARE @minimumDate DATE
DECLARE @maximumDate DATE

SET @minimumDate = '2014/12/11'
SET @maximumDate = '2014/12/15' 

SELECT  CHSRs.WorkType
        ,LaborTypes.TypeName AS 'Labor Type'
        ,CHSRs.Building
        ,CHSRNumber
        ,Count(CHSRs.CHSRNumber) AS 'Number of CHSRs'
        ,ISNULL(SUM(matUsed.cost),0) AS 'Total Material Cost'
        ,ISNULL(SUM(Labor.[Hour Worked] * Labor.[Hourly CHSR Labor Rate]),0) AS 'Total Labor Cost'
FROM    [Facilities].[HardwareSupportRequest] CHSRs
    JOIN Facilities.tblCHSRLaborPerCHSR Labor
        ON CHSRs.CHSRNumber = Labor.[CHSR #]
    JOIN (SELECT ROUND(SUM(matU.[Amount Used] * mat.[Item Cost] * 1.05417 * 1.15),2) AS cost, [CHSR #]
        FROM Facilities.tblMaterialUsed matU
        JOIN Facilities.tblMaterial mat ON
        matU.MaterialId = mat.Id
        GROUP BY matU.[CHSR #]) matUsed ON
        matUsed.[CHSR #] = CHSRs.CHSRNumber
    JOIN Facilities.LaborTypes LaborTypes
        ON Labor.LaborTypeId = LaborTypes.Id
WHERE CHSRs.ActualCompleteDate BETWEEN @minimumDate AND @maximumDate AND LaborTypes.TypeName IS NOT NULL
GROUP BY    CHSRs.CHSRNumber,CHSRs.WorkType,LaborTypes.TypeName,Building
ORDER BY    ChsrNumber,Building,LaborTypes.TypeName

示例SQL Fiddle- http://sqlfiddle.com/#!3/920eb

Example SQL Fiddle - http://sqlfiddle.com/#!3/920eb

编辑

示例输出看起来类似于

Building    LaborType   NumberOfCHSRs   MaterialCost    LaborCost
D2          Admin       6               0               300
D2          Install     20              10349.42        32400
D2          Removal     2               350.42          1000
D2          Database    4               0               22000
...

非常重要的一点是要注意材料是链接的

It's very important to note that the materials are linked to a CHSR, but they should only be summed for Installs and Removals.

推荐答案

您肯定有很多测试数据，但是它太窄了。最好只使用几个CHSR条目并使用许多不同的LaborType，而不是全部 Electric Install 。所以我没有做很多测试。但这看起来将使您非常接近您想要的东西：

You certainly have a lot of test data, but it is too narrow. It would have been better to have only a couple of CHSR entries and a lot of different LaborTypes used rather than all Electric Installs. So I didn't do a whole lot of testing. But this looks like it will get you pretty close to what you want:

select  hsr.Building,
        case when lt.TypeName in( 'Install', 'Testing', 'Build' )
             then 'Labor'
             else lt.TypeName end as LaborType,
        hsr.CHSRNumber,
        Count( * ) as [Number of CHSRs],
        Cast( Sum( case when lt.TypeName in( 'Install', 'Removal' )
                        then mu.[Amount Used] * mat.[Item Cost] * 1.2122955
                        else 0 end
                 ) as Money )as MaterialCost,
        Cast( Sum( lpc.[Hour Worked] * lpc.[Hourly CHSR Labor Rate] )as Money )as LaborCost
from    HardwareSupportRequest hsr
join    tblCHSRLaborPerCHSR    lpc
    on  lpc.CHSR = hsr.CHSRNumber
join    LaborTypes             lt
    on  lt.ID    = lpc.LaborTypeID
join    tblMaterialUsed        mu
    on  mu.CHSR  = hsr.CHSRNumber
join    tblMaterial            mat
    on  mat.Id = mu.MaterialId
where   hsr.ActualCompleteDate BETWEEN @minimumDate AND @maximumDate
group by hsr.Building,
         case when lt.TypeName in( 'Install', 'Testing', 'Build' )
              then 'Labor'
              else lt.TypeName end,
         hsr.CHSRNumber;

您想要合并劳动类型为安装，测试和构建的行，所以 case 语句可以。请注意， case 语句定义了LaborType字段，并且 group by 子句中的一个必须相同。

You wanted to merge the rows with labor types Install, Testing and Build, so that is what the case statements do. Note the case statement that defines the LaborType field and the one in the group by clause must be identical.

MaterialCost字段中埋藏的另一个 case 语句允许您仅计算安装和拆卸成本。不需要强制转换为Money。我只是把它炫耀。 ;）

The other case statement buried in the MaterialCost field allows you to count only the Install and Removal costs. The cast to Money isn't necessary. I just threw that in to show off. ;)

查看 SQL Fiddle 。

LaborTypes.TypeName的测试不为空不必要，因为NULL不会通过联接实现。

The test for LaborTypes.TypeName IS NOT NULL is not necessary because a NULL would not make it through the join.

这篇关于创建一个对某些数据进行分组但不对其他数据进行分组的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！