May 3, 2019 at 12:58 pm
Here min Calendar year is 2010 and max Calendar year is 2013
How to add Zero to employes who does not have that year
Input sample:
SalesamountCalendarYearEmployee
6370002010282
34400002011282
44150002012282
32940002013282
18980002011291
49850002012291
36310002013291
4780002012296
12090002013296
780002012297
Output required :
SalesamountCalendarYearEmployee
6370002010282
34400002011282
44150002012282
32940002013282
02010291
18980002011291
49850002012291
36310002013291
02010296
02011296
4780002012296
12090002013296
02010297
02011297
780002012297
02013297
May 3, 2019 at 1:30 pm
Do a left join from a table, or virtual, dynamic table, which contains all of the years you wish to display.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2019 at 2:03 pm
Thanks !
Tried creating left join , but did not work as expected.
Second tried with tally table with running number in it and found the missing record, but it did not match with my requirement.
More of my table have some missing records need to identify them and say as Zero (0).
May 3, 2019 at 2:52 pm
I understand your requirement, but unless you post the SQL you have tried, it's difficult to help you any further.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2019 at 3:02 pm
Maybe not the best query, but here's a test. Query embedded in the --ACT section.
CREATE OR ALTER PROCEDURE tsqltests.[test calendarsales]
AS
BEGIN
---------------
-- Assemble
---------------
DECLARE
@expected INT
, @actual INT;
EXEC tsqlt.faketable @TableName = 'CalendarSales', @SchemaName = 'dbo';
INSERT dbo.CalendarSales
(SalesAmount
, CalendarYear
, Employee)
VALUES
(637000, 2010, 282)
, (3440000, 2011, 282)
, (4415000, 2012, 282)
, (3294000, 2013, 282)
, (1898000, 2011, 291)
, (4985000, 2012, 291)
, (3631000, 2013, 291)
, (478000, 2012, 296)
, (1209000, 2013, 296)
, (78000, 2012, 297);
CREATE TABLE #Expected
( SalesAmount INT
, CalendarYear int
, Employee INT);
INSERT #Expected
(SalesAmount, CalendarYear, Employee)
VALUES
(637000, 2010, 282)
, (3440000, 2011, 282)
, (4415000, 2012, 282)
, (3294000, 2013, 282)
, (0, 2010, 291)
, (1898000, 2011, 291)
, (4985000, 2012, 291)
, (3631000, 2013, 291)
, (0, 2010, 296)
, (0, 2011, 296)
, (478000, 2012, 296)
, (1209000, 2013, 296)
, (0, 2010, 297)
, (0, 2011, 297)
, (78000, 2012, 297)
, (0, 2013, 297);
SELECT TOP 0 SalesAmount, CalendarYear, Employee INTO #Actual FROM
#Expected;
---------------
-- Act
---------------
WITH cteYear (minyear, maxyear)
AS
(SELECT MIN(calendarYEar ) AS minyear
, MAX(cs.CalendarYear) AS maxyear
FROM dbo.CalendarSales AS cs
)
, cteTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
)
, cteYears (yearnum)
AS (
SELECT n
FROM cteTally
WHERE n >= (SELECT minyear FROM cteYear)
AND n <= (SELECT maxyear FROM cteYear)
)
, cteEmpYearList (Employee, CalendarYear)
AS
(
SELECT DISTINCT cs.Employee
, y.yearnum
FROM dbo.CalendarSales AS cs
CROSS JOIN cteYears y
)
INSERT #Actual
SELECT
COALESCE(cs2.SalesAmount, 0) AS SalesAmount
, cey.CalendarYear
, cey.Employee
FROM cteEmpYearList cey
LEFT OUTER JOIN dbo.CalendarSales AS cs2
ON cey.Employee = cs2.Employee
AND cs2.CalendarYear = cey.CalendarYear
--WHERE cs2.CalendarYear IS NOT null
ORDER BY cey.Employee, cey.CalendarYear;
---------------
-- Assert
---------------
EXEC tSQLt.AssertEqualsTable
@Expected = N'#expected'
, @Actual = N'#actual'
, @Message = N'The query doesn''t work';
END;
GO
EXEC tsqlt.run 'tsqltests.[test calendarsales]';
May 5, 2019 at 9:51 am
Thanks Steve ,
Here is the code i have tried. Knew that it can be done in better way.
create table #CalendarSales (salesamount decimal(20,4),Calendaryear int,Employee int)
create table #CalendarSales_final (salesamount decimal(20,4),Calendaryear int,Employee int)
INSERT #CalendarSales
(SalesAmount
, CalendarYear
, Employee)
VALUES
(637000, 2010, 282)
, (3440000, 2011, 282)
, (4415000, 2012, 282)
, (3294000, 2013, 282)
, (1898000, 2011, 291)
, (4985000, 2012, 291)
, (3631000, 2013, 291)
, (478000, 2012, 296)
, (1209000, 2013, 296)
, (78000, 2012, 297);
Select Distinct Identity(Int,1,1) Rid,Employee into #temp from #CalendarSales
Select Distinct Calendaryear into #dates from #CalendarSales
Declare @i int,@j int=1,@Emp int
Select @i =Rid from #temp
While @j<=@i
Begin
Select @emp=Employee from #temp where Rid=@j
insert into #CalendarSales_final
Select ISNULL(c.salesamount,0)salesamount, d.*,@emp from #CalendarSales c Right join #Dates as d on c.Calendaryear=d.Calendaryear and Employee in (Select t. Employee from #temp as t where Rid=@j)
Set @j=@j+1
end
Select * from #CalendarSales_final
Drop table #temp
drop table #CalendarSales
drop table #CalendarSales_final
Drop table #dates
May 6, 2019 at 8:36 am
Maybe not the best query, but here's a test. Query embedded in the --ACT section.
CREATE OR ALTER PROCEDURE tsqltests.[test calendarsales]
AS
BEGIN
---------------
-- Assemble
---------------
DECLARE
@expected INT
, @actual INT;
EXEC tsqlt.faketable @TableName = 'CalendarSales', @SchemaName = 'dbo';
INSERT dbo.CalendarSales
(SalesAmount
, CalendarYear
, Employee)
VALUES
(637000, 2010, 282)
, (3440000, 2011, 282)
, (4415000, 2012, 282)
, (3294000, 2013, 282)
, (1898000, 2011, 291)
, (4985000, 2012, 291)
, (3631000, 2013, 291)
, (478000, 2012, 296)
, (1209000, 2013, 296)
, (78000, 2012, 297);
CREATE TABLE #Expected
( SalesAmount INT
, CalendarYear int
, Employee INT);
INSERT #Expected
(SalesAmount, CalendarYear, Employee)
VALUES
(637000, 2010, 282)
, (3440000, 2011, 282)
, (4415000, 2012, 282)
, (3294000, 2013, 282)
, (0, 2010, 291)
, (1898000, 2011, 291)
, (4985000, 2012, 291)
, (3631000, 2013, 291)
, (0, 2010, 296)
, (0, 2011, 296)
, (478000, 2012, 296)
, (1209000, 2013, 296)
, (0, 2010, 297)
, (0, 2011, 297)
, (78000, 2012, 297)
, (0, 2013, 297);
SELECT TOP 0 SalesAmount, CalendarYear, Employee INTO #Actual FROM
#Expected;
---------------
-- Act
---------------
WITH cteYear (minyear, maxyear)
AS
(SELECT MIN(calendarYEar ) AS minyear
, MAX(cs.CalendarYear) AS maxyear
FROM dbo.CalendarSales AS cs
)
, cteTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
)
, cteYears (yearnum)
AS (
SELECT n
FROM cteTally
WHERE n >= (SELECT minyear FROM cteYear)
AND n <= (SELECT maxyear FROM cteYear)
)
, cteEmpYearList (Employee, CalendarYear)
AS
(
SELECT DISTINCT cs.Employee
, y.yearnum
FROM dbo.CalendarSales AS cs
CROSS JOIN cteYears y
)
INSERT #Actual
SELECT
COALESCE(cs2.SalesAmount, 0) AS SalesAmount
, cey.CalendarYear
, cey.Employee
FROM cteEmpYearList cey
LEFT OUTER JOIN dbo.CalendarSales AS cs2
ON cey.Employee = cs2.Employee
AND cs2.CalendarYear = cey.CalendarYear
--WHERE cs2.CalendarYear IS NOT null
ORDER BY cey.Employee, cey.CalendarYear;
---------------
-- Assert
---------------
EXEC tSQLt.AssertEqualsTable
@Expected = N'#expected'
, @Actual = N'#actual'
, @Message = N'The query doesn''t work';
END;
GO
EXEC tsqlt.run 'tsqltests.[test calendarsales]';
The problem with this code is that it will produce the full output of the tally CTE before filtering it, in this case 10000 rows.
😎
Here is an example on how to limit the tally CTE output to the exact number of rows needed
USE TEEST;
GO
SET NOCOUNT ON;
--Input sample:
;WITH SAMPLE_DATA(Salesamount,CalendarYear,Employee) AS
(
SELECT 637000,2010,282 UNION ALL
SELECT 3440000,2011,282 UNION ALL
SELECT 4415000,2012,282 UNION ALL
SELECT 3294000,2013,282 UNION ALL
SELECT 1898000,2011,291 UNION ALL
SELECT 4985000,2012,291 UNION ALL
SELECT 3631000,2013,291 UNION ALL
SELECT 478000,2012,296 UNION ALL
SELECT 1209000,2013,296 UNION ALL
SELECT 78000,2012,297
)
,T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
,YEARSTAT(STY,NY) AS
(
SELECT
MIN(SD.CalendarYear) AS STY
,1 + (MAX(SD.CalendarYear) - MIN(SD.CalendarYear)) AS NY
FROM SAMPLE_DATA SD
)
,CALENDAR(YR) AS
(
SELECT
TOP((SELECT YS.NY FROM YEARSTAT YS))
YS.STY + (ROW_NUMBER() OVER (ORDER BY @@VERSION) - 1)
FROM T T1,T T2
CROSS APPLY YEARSTAT YS
)
,EMP_LIST AS
(
SELECT DISTINCT
SD.Employee EMP
FROM SAMPLE_DATA SD
)
,EMP_YEAR_LIST(EMP,YR) AS
(
SELECT
EL.EMP
,CR.YR
FROM CALENDAR CR
CROSS APPLY EMP_LIST EL
)
SELECT
ISNULL(SD.Salesamount,0) AS Salesamount
,EYL.YR AS CalendarYear
,EYL.EMP AS Employee
FROM EMP_YEAR_LIST EYL
LEFT OUTER JOIN SAMPLE_DATA SD
ON EYL.EMP = SD.Employee
AND EYL.YR = SD.CalendarYear
ORDER BY EYL.EMP ASC
,EYL.YR ASC
;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply