Adding fake rows on a result of a query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Adding fake rows on a result of a query

Adding fake rows on a result of a query

Rate Topic

Display Mode

Topic Options

Author

Message

evald

Posted Friday, February 22, 2013 6:11 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, March 07, 2013 8:14 AM
Points: 66, Visits: 243

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

Post #1423014

Sean Lange

Posted Friday, February 22, 2013 8:11 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 8,605, Visits: 8,246

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

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

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1423084

Erin Ramsay

Posted Friday, February 22, 2013 8:29 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 421, Visits: 777

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/

Post #1423100

evald

Posted Friday, February 22, 2013 9:59 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, March 07, 2013 8:14 AM
Points: 66, Visits: 243

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

Post #1423142

Sean Lange

Posted Friday, February 22, 2013 10:06 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 8,605, Visits: 8,246

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1423148

« Prev Topic | Next Topic »

Permissions