Adding fake rows on a result of a query

  • Hello everybody,

    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?

    Thank you

  • You need to use a calendar table for this type of thing.

    http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I've done the same thing using the more generic Tally or Numbers table.

    Here's a good article by Jeff about it.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

  • thank you for your replies. I wiil read them.

    how about something like the following(using CTE):

    declare @dataBegin datetime;

    declare @dataEnd datetime;

    set @dataEnd = '21/05/2013';

    set @dataBegin = DATEADD(month,-11, @dataEnd) - (DAY(@dataEnd)-1);

    set @dataEnd = DATEADD(month,1, @dataEnd) - (DAY(@dataEnd));

    WITH dates AS

    (

    SELECT CAST(@dataBegin AS DATETIME) 'date'

    UNION ALL

    SELECT DATEADD(MONTH, 1, t.date)

    FROM dates t

    WHERE DATEADD(month, 1, t.date) <= @dataEnd

    )

    select month(dates.date) as mese, YEAR(dates.date) as anno,department

    from dates

    ,

    (

    SELECT distinct department

    FROM T1

    ) t2

    the result is that i have all the dates i need with the departments but i need to join this result with

    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)

    the problem is that i cant do this join...

    any idea?

    thank's again

  • A tally table will perform far better for this than a recursive cte. The biggest challenge at this point is that we can't help much because we don't know what your tables look like. Please take a few minutes and read the article in my signature about best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply