Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding fake rows on a result of a query Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 6:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:38 AM
Points: 66, Visits: 245
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
Posted Friday, February 22, 2013 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1423084
Posted Friday, February 22, 2013 8:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
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
Posted Friday, February 22, 2013 9:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:38 AM
Points: 66, Visits: 245
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
Posted Friday, February 22, 2013 10:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1423148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse