February 22, 2013 at 6:11 am
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
February 22, 2013 at 8:11 am
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/
February 22, 2013 at 8:29 am
I've done the same thing using the more generic Tally or Numbers table.
Here's a good article by Jeff about it.
February 22, 2013 at 9:59 am
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
February 22, 2013 at 10:06 am
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