Menu
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'[email protected]', CAST(0x0000A58000000000 AS DateTime)),
(2, N'Jack', N'On Earth', N'[email protected]', CAST(0x0000A5A800000000 AS DateTime)),
(3, N'Jessi', N'On Earth', N'[email protected]', CAST(0x0000A5CF00000000 AS DateTime)),
(4, N'Jackson', N'On Earth', N'[email protected]', 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.
226
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.
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'[email protected]', '01/01/2015'),
(1, N'John', N'On Earth', N'[email protected]', CAST(0x0000A58000000000 AS DateTime)),
(2, N'Jack', N'On Earth', N'[email protected]', CAST(0x0000A5A800000000 AS DateTime)),
(3, N'Jessi', N'On Earth', N'[email protected]', CAST(0x0000A5CF00000000 AS DateTime)),
(4, N'Jackson', N'On Earth', N'[email protected]', 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
Oracle solution. In second line put report year.
mc counts new and resigning persons per monthst 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
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:
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.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
