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:
Salesamount CalendarYear Employee
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
Output required :
Salesamount CalendarYear Employee
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
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]';
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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