Reputation: 99

Adding fake rows on a result of a query

I have a table(T1) with the following columns: department,dateofsale,totalsales. What I want to achieve is to have the sales for department per month in one year from a start date and going backward 1 year. Maybe the following query will show better 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 result of the query will be the following:

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

The problem is that I want also the months that has a value of zero as totalsales. So the result must 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 that?

Upvotes: 7

Answers (6)

Ben Thul

Reputation: 32717

One of the great uses for a numbers 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]

Upvotes: 1

Scotty Boy

Reputation: 336

Insert a value of zero for each month or date and each department. Now your data is explicit and your queries are simplified.

Assuming the absence of data implies a value of zero is not a good data practice.

Upvotes: 0

Matthew Warman

Reputation: 3442

The way I've managed to get round this problem when I've encountered it is to create a temporary table creating all the required dates then performing a UNION between the temporary table and the data query within a 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 problem with the above is that department is hard coded in temporary table create, could be passed as parameter though.

Upvotes: 1

Dalex

Reputation: 3625

You do not need to simulate missing rows, just get right values for it.

Note: data need to be rotated not only by Year-Month but by department too. Otherwise, you will get 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

Upvotes: 2

Tim

Reputation: 8919

You could create two queries and UNION them, or to fabricate the missing rows, use a CTE. I understand you to mean that you have no data before 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 extract the month from your sales date. The query above is just to show you how to get months you don't have in your data.

Upvotes: 1

Rednaxel

Reputation: 968

you could create a table Months and do a Left Join with it

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

Upvotes: 2

Adding fake rows on a result of a query

Answers (6)

Related Questions