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

如果您计划运行其中的许多工具,那么您肯定应该将工作转移到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'john@abc.com', '01/01/2015'),
       (1, N'John', N'On Earth', N'john@abc.com', CAST(0x0000A58000000000 AS DateTime)),
       (2, N'Jack', N'On Earth', N'jack@abc.com', CAST(0x0000A5A800000000 AS DateTime)),
       (3, N'Jessi', N'On Earth', N'jessi@abc.com', CAST(0x0000A5CF00000000 AS DateTime)),
       (4, N'Jackson', N'On Earth', N'jackson@abc.com', 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)

结果:

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

Oracle解决方案。在第二行的报告年度。

• mc 每月统计新员工和辞职人员
• t 是分层子查询计数 opening 和 closing 每个月的值。第一部分,锚定,统计表中的条目 entry 和 resign 对于老年人来说,这是我们的起点。对于这个锚,我们按月附加值,在中搜索它们 mc公司
• 最后一步是 pivot 以及订购,添加到这里只是为了按照您的要求提供数据。

查询:

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

提供数据和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

它主要是标准的sql,您可能需要更改如下函数 extract 与他们的sqlserver等价。希望这有帮助。