Adding fake rows as a result of a query

Question

Adding fake rows as a result of a query

I have a table (T1) with the following columns: department, dateofsale, totalsales. What I want to achieve is to sell the department per month for one year from the start and return dates of 1 year. Perhaps the following query will better show what I want to achieve.

-- Create the table T1 CREATE TABLE [dbo].[T1]( [department] [nvarchar](50) NULL, [dateofsale] [datetime] NULL, [totalsales] [decimal](18, 5) NULL ) ON [PRIMARY] -- Add some data INSERT [dbo].[T1] ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29B00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5))) INSERT [dbo].[T1] ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A27D00000000 AS DateTime), CAST(300.00000 AS Decimal(18, 5))) INSERT [dbo].[T1] ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29C00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5))) -- The query declare @dataBegin datetime declare @dataEnd datetime set @dataEnd = '21/12/2013' set @dataBegin = DATEADD(month,-11, @dataEnd) - (DAY(@dataEnd)-1) set @dataEnd = DATEADD(month,1, @dataEnd) - (DAY(@dataEnd)) SELECT department,SUM(totalsales) AS totsales, MONTH(dateofsale) as month, YEAR(dateofsale) as year FROM T1 WHERE dateofsale >= @dataBegin AND dateofsale< @dataEnd GROUP BY department,MONTH(dateofsale), YEAR(dateofsale) ORDER BY department,MONTH(dateofsale), YEAR(dateofsale)

With the data added before the query result, there will be the following:

  department /totsales/ month /year 0001/ 300.00000 /11 /2013 0001/ 400.00000 /12 /2013

The problem is that I also want months that have a value of 0 as totals. Thus, the result should be:

 department /totsales/ month /year 0001/ 0 /1 /2013 0001/ 0 /2 /2013 0001/ 0 /3 /2013 0001/ 0 /4 /2013 0001/ 0 /5 /2013 0001/ 0 /6 /2013 0001/ 0 /7 /2013 0001/ 0 /8 /2013 0001/ 0 /9 /2013 0001/ 0 /10 /2013 0001/ 300.00000 /11 /2013 0001/ 400.00000 /12 /2013

How can i do this?

+6

sql-server tsql

Evald.i Feb 22 '13 at 13:17

source share

6 answers

Rednaxel · Answer 1 · 2013-02-22T13:38:32+0000

you can create a table "Months" and make a left link with it

 SELECT * FROM Months M LEFT JOIN T1 T ON M.month = T.Month

Dalex · Answer 2 · 2013-02-22T14:14:42+0000

You do not need to model the missing lines, just get the correct values for it.

Note: data must be rotated not only by year-month, but also by department. Otherwise, you will get a NULL value

  -- Create the table T1 DECLARE @T1 TABLE( [department] [nvarchar](50) NULL, [dateofsale] [datetime] NULL, [totalsales] [decimal](18, 5) NULL ) -- Add some data INSERT @T1([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29B00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5))) INSERT @T1([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A27D00000000 AS DateTime), CAST(300.00000 AS Decimal(18, 5))) INSERT @T1([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29C00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5))) INSERT @T1([department], [dateofsale], [totalsales]) VALUES (N'0003', CAST(0x0000A29C00000000 AS DateTime), CAST(100.00000 AS Decimal(18, 5))) -- The query DECLARE @dataBegin DATETIME DECLARE @dataEnd DATETIME SET @dataEnd = '20140101' SET @dataBegin = DATEADD(month, - 11, @dataEnd) - (DAY(@dataEnd) - 1) SET @dataEnd = DATEADD(month, 1, @dataEnd) - (DAY(@dataEnd)); WITH Months ( MonthNr ,Year ,Department ) AS ( SELECT MonthNr ,Y.Year ,D.department FROM ( VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) M(MonthNr) CROSS JOIN ( SELECT DISTINCT T.department FROM @T1 T ) D CROSS JOIN ( SELECT year FROM ( VALUES (2013) --insert as many years as you need ) T(year) ) Y ) SELECT M.department ,ISNULL(T.totsales, 0) totalSales ,M.MonthNr month ,M.year FROM Months M LEFT JOIN ( SELECT department ,SUM(totalsales) AS totsales ,MONTH(dateofsale) AS month ,YEAR(dateofsale) AS year FROM @T1 WHERE dateofsale >= @dataBegin AND dateofsale < @dataEnd GROUP BY department ,MONTH(dateofsale) ,YEAR(dateofsale) ) T ON T.month = M.MonthNr and T.department = M.Department ORDER BY department ,M.MonthNr ,M.Year

Result:

 department totalSales month year --------------- --------------------- ----------- ----------- 0001 0.00000 1 2013 0001 0.00000 2 2013 0001 0.00000 3 2013 0001 0.00000 4 2013 0001 0.00000 5 2013 0001 0.00000 6 2013 0001 0.00000 7 2013 0001 0.00000 8 2013 0001 0.00000 9 2013 0001 0.00000 10 2013 0001 300.00000 11 2013 0001 400.00000 12 2013 0003 0.00000 1 2013 0003 0.00000 2 2013 0003 0.00000 3 2013 0003 0.00000 4 2013 0003 0.00000 5 2013 0003 0.00000 6 2013 0003 0.00000 7 2013 0003 0.00000 8 2013 0003 0.00000 9 2013 0003 0.00000 10 2013 0003 0.00000 11 2013 0003 100.00000 12 2013

Tim · Answer 3 · 2013-02-22T13:35:33+0000

You can create two queries and UNION, or create the missing lines using CTE. I understand that you mean that you have no data until November.

 WITH months AS ( SELECT 2013 as yr, 1 as mnth UNION ALL SELECT 2013 as yr, mnth+1 as mnth FROM months WHERE mnth < 12 ) select months.yr, months.mnth, department, isnull(totsales,0.00) as totsales from months left join sales on sales.yr = months.yr and sales.month = months.mnth

Just use the datepart function to retrieve the month from the sale date. The above query is just to show you how to get months that you do not have in your data.

Matthew warman · Answer 4 · 2013-02-22T14:09:23+0000

How I managed to get around this problem when I ran into it was to create a temporary table that creates all the necessary dates, and then execute UNION between the temporary table and the data request in the select statement:

 -- Create the table T1 CREATE TABLE #T1( [department] [nvarchar](50) NULL, [dateofsale] [datetime] NULL, [totalsales] [decimal](18, 5) NULL ) ON [PRIMARY] -- Add some data INSERT #T1 ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29B00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5))) INSERT #T1 ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A27D00000000 AS DateTime), CAST(300.00000 AS Decimal(18, 5))) INSERT #T1 ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29C00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5))) --Solution Start DECLARE @dataBegin datetime DECLARE @dataEnd datetime DECLARE @CurrentDate DATETIME SET @dataEnd = '2013-12-23' SET @dataBegin = DATEADD(month,-11, @dataEnd) - (DAY(@dataEnd)-1) SET @dataEnd = DATEADD(month,1, @dataEnd) - (DAY(@dataEnd)) SET @CurrentDate = @dataBegin -- Create Temporary Table CREATE TABLE #calDate (calDate DATETIME) -- Populate Table INSERT INTO #calDate (calDate) SELECT @CurrentDate WHILE DATEADD(MONTH, 1, @CurrentDate) <= @dataEnd BEGIN INSERT INTO #calDate (calDate) SELECT DATEADD(MONTH, 1, @CurrentDate) SET @CurrentDate = DATEADD(MONTH, 1, @CurrentDate) END -- Query Data SELECT department , sum(totsales) , month , year FROM( SELECT '0001' as 'department',0 AS totsales, MONTH(calDate) as month, YEAR(calDate) as year FROM #calDate UNION SELECT department,SUM(totalsales) AS totsales, MONTH(dateofsale) as month, YEAR(dateofsale) as year FROM #T1 WHERE dateofsale >= @dataBegin AND dateofsale< @dataEnd GROUP BY department,MONTH(dateofsale), YEAR(dateofsale) )a GROUP BY department,month, year ORDER BY department,month, year DROP table #calDate DROP table #T1

Only the problem with the above is that the department is hard-coded in temporary table creation, can be passed as a parameter.

Ben thul · Answer 5 · 2013-02-22T15:17:15+0000

One of the great uses for a number table:

 -- Populate numbers table; keep this around, you'll find uses for it! ;WITH Pass0 as (select 1 as C union all select 1), --2 rows Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows Tally as (select row_number() over(order by C) as Number from Pass5) select Number into dbo.Numbers from Tally where Number <= 1000000 -- The query declare @dataBegin datetime declare @dataEnd datetime set @dataEnd = '2013-12-21' set @dataBegin = DATEADD(month,-11, @dataEnd) - (DAY(@dataEnd)-1) set @dataEnd = DATEADD(month,1, @dataEnd) - (DAY(@dataEnd)); with sales as ( SELECT department,SUM(totalsales) AS totsales, MONTH(dateofsale) as month, YEAR(dateofsale) as year FROM T1 WHERE dateofsale >= @dataBegin AND dateofsale< @dataEnd GROUP BY department,MONTH(dateofsale), YEAR(dateofsale) ), all_months as ( select distinct department, Number as [month], 2013 as [year] from T1 as t cross join dbo.Numbers as n where n.Number <= 12 ) select m.department, coalesce(s.totsales, 0), m.[month], m.[year] from all_months as m left join sales as s on m.department = s.department and m.[year] = s.[year] and m.[month] = s.[month] ORDER BY m.department, m.[month], m.[year]

Scotty Boy · Answer 6 · 2013-02-22T15:08:53+0000

Insert a zero value for each month or date and each department. Now your data is clear and your requests are simplified.

Assuming the lack of data means that a value of zero is not good data practice.

Adding fake rows as a result of a query

More articles: