使用Sql查询以格式化方式获取用户数据

I have a requirement to show formatted data in a report. I am using three tables where one adds new users, second one for their payments and the last one for if any user resigns. So here are the sample tables and default data:

我需要在报告中显示格式化数据。我使用三个表，其中一个添加新用户，第二个用于付款，最后一个用于任何用户辞职。以下是示例表和默认数据：

CREATE TABLE [dbo].[Entry]
(
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](40) NULL,
    [Address] [nvarchar](100) NULL,
    [Email] [nvarchar](20) NULL,
    [EntryDate] [datetime] NULL
)

INSERT [dbo].[Entry] ([EmpId], [EmpName], [Address], [Email], [EntryDate]) 
VALUES (1, N'John', N'On Earth', N'[e-mail]', CAST(0x0000A58000000000 AS DateTime)),
       (2, N'Jack', N'On Earth', N'[e-mail]', CAST(0x0000A5A800000000 AS DateTime)),
       (3, N'Jessi', N'On Earth', N'[e-mail]', CAST(0x0000A5CF00000000 AS DateTime)),
       (4, N'Jackson', N'On Earth', N'[e-mail]', CAST(0x0000A5E400000000 AS DateTime))

CREATE TABLE [dbo].[Payment]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NULL,
    [Payment] [float] NULL,
    [PayDate] [datetime] NULL
)

INSERT [dbo].[Payment] ([Id], [EmpId], [Payment], [PayDate]) 
VALUES (1, 1, 2000, CAST(0x0000A61800000000 AS DateTime)), (2, 1, 2000, CAST(0x0000A63600000000 AS DateTime)),
       (3, 1, 2000, CAST(0x0000A65500000000 AS DateTime)), (4, 1, 2000, CAST(0x0000A67400000000 AS DateTime)),
       (5, 2, 4000, CAST(0x0000A5DB00000000 AS DateTime)), (6, 2, 4000, CAST(0x0000A5F900000000 AS DateTime)),
       (7, 2, 4000, CAST(0x0000A61800000000 AS DateTime)), (8, 2, 4000, CAST(0x0000A63600000000 AS DateTime)),
       (9, 2, 4000, CAST(0x0000A65500000000 AS DateTime)), (10, 2, 4000, CAST(0x0000A67400000000 AS DateTime)),
       (11, 2, 4000, CAST(0x0000A69200000000 AS DateTime)), (12, 3, 6000, CAST(0x0000A65500000000 AS DateTime)),
       (13, 3, 6000, CAST(0x0000A67400000000 AS DateTime)), (14, 4, 8000, CAST(0x0000A7FF00000000 AS DateTime)),
       (15, 4, 8000, CAST(0x0000A98B00000000 AS DateTime))

CREATE TABLE [dbo].[Resign]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NULL,
    [ResignDate] [datetime] NULL,
    [Reason] [nchar](10) NULL
)

INSERT [dbo].[Resign] ([Id], [EmpId], [ResignDate], [Reason]) 
VALUES (1, 1, CAST(0x0000A69B00000000 AS DateTime), N'Resigned  '),
       (2, 2, CAST(0x0000A6C400000000 AS DateTime), N'Resigned')

The required output is as follows that'll be year-wise:

所需的输出如下：这将是逐年的：

Year 2015

Month - Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec
Opening - 10 - 10 - 10 - 12 - 12 - 12 - 12 - 12 - 12 - 12 - 8 - 8 //Total no. of employee
Add     -  0 -  0 -  2 -  0 -  0 -  0 -  0 -  0 -  0 -  0 - 0 - 0 //Newly added
Left    -  0 -  0 -  0 -  0 -  0 -  0 -  0 -  0 -  0 -  4 - 0 - 0 //Resigned
Closing - 10 - 10 - 12 - 12 - 12 - 12 - 12 - 12 - 12 -  8 - 8 - 8 //Closing Total

Year 2016

Month - Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec
Opening - 8 ----Goes on in the similar way
Add -----
Left ----
Closing ----

So this is clear how the data should be shown in the report. Though I am aware of the grouping year-wise in the report and have tried similar to show the data month-wise as follows:

因此，很清楚如何在报告中显示数据。虽然我知道报告中的年度分组，并尝试过类似的数据，按月显示数据如下：

SELECT 
    DATEPART(YYYY, Q.PayDate) [YEAR], 
    COUNT(m.EmpId) Total,
    SUM(CASE WHEN DATENAME(MONTH, m.EntryDate) = 'January' THEN 1 ELSE 0 END) [JAN],
    SUM(CASE WHEN DATENAME(MONTH, m.EntryDate) = 'February' THEN 1 ELSE 0 END) [FEB]  
FROM 
    Entry m 
INNER JOIN 
    Payment q ON Q.EmpId = M.EmpId 
LEFT JOIN
    Resign OUT ON OUT.EmpId = m.EmpId 
WHERE 
    m.EmpId NOT IN (SELECT DISTINCT p.EmpId FROM Resign p)
    AND m.EmpId IN (SELECT DISTINCT k.EmpId FROM Payment k)
GROUP BY 
    DATEPART(YYYY, Q.PayDate), DATEPART(MM, m.EntryDate);

The above query returns something as follows:

上面的查询返回如下内容：

YEAR  Total JAN FEB
--------------------
2016  2     0   0
2017  1     0   0
2018  1     0   0

But I am not sure how can I show the newly added as well resigned users with the opening in the same query row-wise as the sample given?

但我不确定如何在给出的样本中以相同的查询行显示新添加的以及已重新排列的用户？

N.B: I am open to any query languages. So expecting your ideas to implement it in an appropriate way.

N.B：我对任何查询语言都是开放的。因此，期待您的想法以适当的方式实施。

2 个解决方案

#1

You should definitely shift your effort over to a reporting tool such as SSRS if you plan to run many of these. It will simplify things many times over. That being said, here is one way that would produce the results you mentioned above.

如果您计划运行其中许多工具，那么您绝对应该将工作转移到SSRS等报告工具上。它会简化很多次。话虽如此，这是一种可以产生上述结果的方法。

DECLARE @Entry TABLE
(
    [EmpId] [int]  NOT NULL,
    [EmpName] [nvarchar](40) NULL,
    [Address] [nvarchar](100) NULL,
    [Email] [nvarchar](20) NULL,
    [EntryDate] [datetime] NULL
)

INSERT @Entry ([EmpId], [EmpName], [Address], [Email], [EntryDate]) 

VALUES
       (100, N'Early', N'On Earth', N'[e-mail]', '01/01/2015'),
       (1, N'John', N'On Earth', N'[e-mail]', CAST(0x0000A58000000000 AS DateTime)),
       (2, N'Jack', N'On Earth', N'[e-mail]', CAST(0x0000A5A800000000 AS DateTime)),
       (3, N'Jessi', N'On Earth', N'[e-mail]', CAST(0x0000A5CF00000000 AS DateTime)),
       (4, N'Jackson', N'On Earth', N'[e-mail]', CAST(0x0000A5E400000000 AS DateTime))

DECLARE @Payment TABLE 
(
    [Id] [int]  NOT NULL,
    [EmpId] [int] NULL,
    [Payment] [float] NULL,
    [PayDate] [datetime] NULL
)

INSERT @Payment ([Id], [EmpId], [Payment], [PayDate]) 
VALUES (1, 1, 2000, CAST(0x0000A61800000000 AS DateTime)), (2, 1, 2000, CAST(0x0000A63600000000 AS DateTime)),
       (3, 1, 2000, CAST(0x0000A65500000000 AS DateTime)), (4, 1, 2000, CAST(0x0000A67400000000 AS DateTime)),
       (5, 2, 4000, CAST(0x0000A5DB00000000 AS DateTime)), (6, 2, 4000, CAST(0x0000A5F900000000 AS DateTime)),
       (7, 2, 4000, CAST(0x0000A61800000000 AS DateTime)), (8, 2, 4000, CAST(0x0000A63600000000 AS DateTime)),
       (9, 2, 4000, CAST(0x0000A65500000000 AS DateTime)), (10, 2, 4000, CAST(0x0000A67400000000 AS DateTime)),
       (11, 2, 4000, CAST(0x0000A69200000000 AS DateTime)), (12, 3, 6000, CAST(0x0000A65500000000 AS DateTime)),
       (13, 3, 6000, CAST(0x0000A67400000000 AS DateTime)), (14, 4, 8000, CAST(0x0000A7FF00000000 AS DateTime)),
       (15, 4, 8000, CAST(0x0000A98B00000000 AS DateTime))

DECLARE  @Resign TABLE
(
    [Id] [int]  NOT NULL,
    [EmpId] [int] NULL,
    [ResignDate] [datetime] NULL,
    [Reason] [nchar](10) NULL
)

INSERT @Resign ([Id], [EmpId], [ResignDate], [Reason]) 
VALUES (1, 1, CAST(0x0000A69B00000000 AS DateTime), N'Resigned  '),
       (2, 2, CAST(0x0000A6C400000000 AS DateTime), N'Resigned')

DECLARE @StartDate DATETIME = '01/01/2015'
DECLARE @EndDate DATETIME = '12/01/2016'

;WITH Calendar as
 (
    SELECT CalendarDate = @StartDate, CalendarYear = DATEPART(YEAR, @StartDate), CalendarMonth = DATEPART(MONTH, @StartDate)
    UNION ALL
    SELECT CalendarDate = DATEADD(MONTH, 1, CalendarDate),  CalendarYear = DATEPART(YEAR, CalendarDate), CalendarMonth = DATEPART(MONTH, CalendarDate)
    FROM Calendar
    WHERE DATEADD (MONTH, 1, CalendarDate) <= @EndDate
 )
 ,Employees AS
 (
     SELECT
        E.EmpID,
        StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, EntryDate), 0),
        EndDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, ResignDate), 0)
    FROM     
        @Entry E
        LEFT OUTER JOIN @Resign R ON R.EmpId = E.EmpId
)
,NormalizedAndUnpivoted AS
(
    SELECT
        *,
        CalendarMonth = DATEPART(MONTH,CalendarDate),
        CalendarYear = DATEPART(YEAR,CalendarDate),
        GroupField = CAST(DATEPART(MONTH,CalendarDate) AS NVARCHAR(50))+'_'+CAST(DATEPART(YEAR,CalendarDate) AS NVARCHAR(50)),
        SortOrder = CASE 
            WHEN Property='Opening' THEN 1
            WHEN Property='Addition'  THEN 2
            WHEN Property='Subtraction'  THEN 3
            WHEN Property='Total'  THEN 4
        END
    FROM
    (
        SELECT 
            C.CalendarDate,
            Opening =  COUNT(DISTINCT EmpExisting.EmpID) ,
            Addition = COUNT(DISTINCT EmpStarted.EmpID),
            Subtraction = COUNT(DISTINCT EmpEnd.EmpID),
            Total = COUNT(DISTINCT EmpExisting.EmpID) + COUNT(DISTINCT EmpStarted.EmpID) - COUNT(DISTINCT EmpEnd.EmpID)
        FROM 
            Calendar C
            LEFT OUTER JOIN Employees EmpExisting ON EmpExisting.StartDate < C.CalendarDate AND (EmpExisting.EndDate IS NULL OR EmpExisting.EndDate >=  C.CalendarDate)
            LEFT OUTER JOIN Employees EmpStarted ON EmpStarted.StartDate = C.CalendarDate
            LEFT OUTER JOIN Employees EmpEnd ON EmpEnd.EndDate = C.CalendarDate
        GROUP BY
            C.CalendarDate      
    )AS X
    UNPIVOT(
        PivotValue FOR Property IN (Opening, Addition, Subtraction, Total)
    ) AS U
)
,Pivoted AS
(
    SELECT
        CalendarYear,
        Property,
        SortOrder,
        Janurary=SUM(CASE WHEN CalendarMonth=1 THEN PivotValue ELSE NULL END),
        Feburary=SUM(CASE WHEN CalendarMonth=2 THEN PivotValue ELSE NULL END),
        March=SUM(CASE WHEN CalendarMonth=3 THEN PivotValue ELSE NULL END),
        April=SUM(CASE WHEN CalendarMonth=4 THEN PivotValue ELSE NULL END),
        May=SUM(CASE WHEN CalendarMonth=5 THEN PivotValue ELSE NULL END),
        June=SUM(CASE WHEN CalendarMonth=6 THEN PivotValue ELSE NULL END),
        July=SUM(CASE WHEN CalendarMonth=7 THEN PivotValue ELSE NULL END),
        August=SUM(CASE WHEN CalendarMonth=8 THEN PivotValue ELSE NULL END),
        September=SUM(CASE WHEN CalendarMonth=9 THEN PivotValue ELSE NULL END),
        October=SUM(CASE WHEN CalendarMonth=10 THEN PivotValue ELSE NULL END),
        November=SUM(CASE WHEN CalendarMonth=11 THEN PivotValue ELSE NULL END),
        December=SUM(CASE WHEN CalendarMonth=12 THEN PivotValue ELSE NULL END)
    FROM
        NormalizedAndUnpivoted
    GROUP BY
        CalendarYear,
        Property,
        SortOrder
)

SELECT * FROM Pivoted
ORDER BY
    CalendarYear,
    SortOrder
OPTION (MAXRECURSION 1000)

Results:

结果：

CalendarYear Property                       SortOrder   Janurary    Feburary    March       April       May         June        July        August      September   October     November    December
------------ ------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2015         Opening                        1           0           1           1           1           1           1           1           1           1           1           1           1
2015         Addition                       2           1           0           0           0           0           0           0           0           0           0           0           0
2015         Subtraction                    3           0           0           0           0           0           0           0           0           0           0           0           0
2015         Total                          4           1           1           1           1           1           1           1           1           1           1           1           1
2016         Opening                        1           1           2           3           4           5           5           5           5           5           5           4           3
2016         Addition                       2           1           1           1           1           0           0           0           0           0           0           0           0
2016         Subtraction                    3           0           0           0           0           0           0           0           0           0           1           1           0
2016         Total                          4           2           3           4           5           5           5           5           5           5           4           3           3

#2

Oracle solution. In second line put report year.

Oracle解决方案。在二年级报告年度。

• mc counts new and resigning persons per months
• mc每月计算新人和辞职人员
• t is hierarchical subquery counting opening and closing values for each month. First part, anchor, counts entries in tables entry and resign for older years, this is our starting point. To this anchor we attach values month by month searching them in mc
• t是分层子查询，用于计算每个月的开始和结束值。第一部分，锚点，计算表格条目中的条目并在较旧年份辞职，这是我们的起点。对于这个锚，我们逐月附加值，在mc中搜索它们
• last step is pivot and ordering, added here only to present data as you requested.
• 最后一步是透视和排序，此处仅添加为您请求的当前数据。

Query:

查询：

with 
  y as (select 2016 yr from dual),
  mc as (   
    select mm, sum(a) a, sum(c) c
      from ( select extract (month from entrydate) mm, 1 a, 0 c
               from entry, y where extract(year from entrydate) = yr
             union all
             select extract (month from resigndate) mm, 0 a, 1 c
               from resign, y where extract(year from resigndate) = yr) 
      group by mm ),
  t(mm, o, a, r, c) as (
    select 0, 0, 0, 0, 
           ( (select count(1) from entry where extract(year from entrydate) < yr)
           - (select count(1) from resign where extract(year from resigndate) < yr) ) 
      from y
    union all
    select t.mm + 1, t.c, mc.a, mc.c, t.c + nvl(mc.a, 0) - nvl(mc.c, 0)
      from t left join mc on mc.mm = t.mm + 1 
      where t.mm + 1 <= 12)
select * 
    from (
        select * from t
            unpivot (cnt for type in (o, a, r, c)))
    pivot (sum(cnt) for mm in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
    order by case type when 'O' then 1 when 'A' then 2 
                       when 'R' then 3 when 'C' then 4 
             end

Result for provided data and year 2016:

提供的数据和2016年的结果：

TYPE      1      2      3      4      5      6      7      8      9     10     11     12
---- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
O         0      1      2      3      4      4      4      4      4      4      3      2
A         1      1      1      1                                         0      0 
R         0      0      0      0                                         1      1 
C         1      2      3      4      4      4      4      4      4      3      2      2

It's mostly standard SQL, you may need change functions like extract to their SQLServer equivalent. Hope this helps.

它主要是标准的SQL，您可能需要将提取功能更改为SQLServer等效项。希望这可以帮助。