我需要在报告中显示格式化数据.我使用三个表,其中一个添加新用户,第二个用于付款,最后一个用于任何用户辞职.以下是示例表和默认数据：
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'john@abc.com',CAST(0x0000A58000000000 AS DateTime)),(2,N'Jack',N'jack@abc.com',CAST(0x0000A5A800000000 AS DateTime)),(3,N'Jessi',N'jessi@abc.com',CAST(0x0000A5CF00000000 AS DateTime)),(4,N'Jackson',N'jackson@abc.com',CAST(0x0000A5E400000000 AS DateTime))
CREATE TABLE [dbo].[Payment]
(
[Id] [int] IDENTITY(1,[EmpId] [int] NULL,[Payment] [float] NULL,[PayDate] [datetime] NULL
)
INSERT [dbo].[Payment] ([Id],[EmpId],[Payment],[PayDate])
VALUES (1,1,2000,CAST(0x0000A61800000000 AS DateTime)),CAST(0x0000A63600000000 AS DateTime)),CAST(0x0000A65500000000 AS DateTime)),CAST(0x0000A67400000000 AS DateTime)),(5,2,4000,CAST(0x0000A5DB00000000 AS DateTime)),(6,CAST(0x0000A5F900000000 AS DateTime)),(7,(8,(9,(10,(11,CAST(0x0000A69200000000 AS DateTime)),(12,3,6000,(13,(14,4,8000,CAST(0x0000A7FF00000000 AS DateTime)),(15,CAST(0x0000A98B00000000 AS DateTime))
CREATE TABLE [dbo].[Resign]
(
[Id] [int] IDENTITY(1,[ResignDate] [datetime] NULL,[Reason] [nchar](10) NULL
)
INSERT [dbo].[Resign] ([Id],[ResignDate],[Reason])
VALUES (1,CAST(0x0000A69B00000000 AS DateTime),N'Resigned '),CAST(0x0000A6C400000000 AS DateTime),N'Resigned')
所需的输出如下：这将是逐年的：
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 ----
因此,很清楚如何在报告中显示数据.虽然我知道报告中的年度分组,并尝试过类似的数据,按月显示数据如下：
SELECT
DATEPART(YYYY,Q.PayDate) [YEAR],COUNT(m.EmpId) Total,SUM(CASE WHEN DATENAME(MONTH,m.EntryDate) = 'January' THEN 1 ELSE 0 END) [JAN],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);
YEAR Total JAN FEB -------------------- 2016 2 0 0 2017 1 0 0 2018 1 0 0
但我不确定如何在给出的样本中以相同的查询行显示新添加的以及已重新排列的用户？
N.B：我对任何查询语言都是开放的.因此,期待您的想法以适当的方式实施.
解决方法
如果您计划运行其中许多工具,那么您绝对应该将工作转移到SSRS等报告工具上.它会简化很多次.话虽如此,这是一种可以产生上述结果的方法.
DECLARE @Entry TABLE
(
[EmpId] [int] NOT NULL,[EntryDate] [datetime] NULL
)
INSERT @Entry ([EmpId],[EntryDate])
VALUES
(100,N'Early','01/01/2015'),(1,CAST(0x0000A5E400000000 AS DateTime))
DECLARE @Payment TABLE
(
[Id] [int] NOT NULL,[PayDate] [datetime] NULL
)
INSERT @Payment ([Id],CAST(0x0000A98B00000000 AS DateTime))
DECLARE @Resign TABLE
(
[Id] [int] NOT NULL,[Reason] [nchar](10) NULL
)
INSERT @Resign ([Id],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,CalendarDate),CalendarDate)
FROM Calendar
WHERE DATEADD (MONTH,CalendarDate) <= @EndDate
),Employees AS
(
SELECT
E.EmpID,StartDate = DATEADD(MONTH,DATEDIFF(MONTH,EntryDate),0),EndDate = DATEADD(MONTH,ResignDate),0)
FROM
@Entry E
LEFT OUTER JOIN @Resign R ON R.EmpId = E.EmpId
),NormalizedAndUnpivoted AS
(
SELECT
*,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,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
