help with creating a complicated view.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with creating a complicated view.

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-03-02 : 13:43:20

I have a table that has 3 columns with data that I would like create a view on.
If there is a better way to achieve the results, please let me know.
The columns are - 'division', 'complete_date' & 'avg_wrk_days'.
The columns hold the avg working days for a specific month/year by a division.

The view I would like to create will take the current month (ie: 3/1/2006), plus the previous
12 months (2/1/2006 - 3/1/2005) and average that total 'avg_wrk_days' while keeping the
current month as the date stamp. I would like the view to do this for every month.

So for example:
Selected Month : Previous 12 months
3/1/2006 : 2/1/2006 - 3/1/2005)
2/1/2006 : 1/1/2006 - 2/1/2005)
1/1/2006 : 12/1/2005 - 1/1/2005)
12/1/2005 : 11/1/2005 - 12/1/2004)
... etc

Here is a view I created that shows the result I'm looking for, but I'm not sure how to write
it to have it continue through all the months. Also, keep the results to their own division.


SELECT     TOP 100 PERCENT division, SUM(avg_wrk_days) / COUNT(division) AS avg_total, CAST(STR(MONTH(GETDATE())) + '/' + STR(01) 
                      + '/' + STR(YEAR(GETDATE())) AS DateTime) AS total_dt
FROM         dbo.xx_tbl_temp
WHERE     (complete_date >= DATEADD(yyyy, - 1, DATEADD(mm, - 1, GETDATE())))
GROUP BY division

DDL & Sample code (DML) below:


-- Export From Server version: Microsoft SQL Server Version 8.0.255

-- Table structure for table 'xx_tbl_temp'
IF EXISTS (SELECT * FROM sysobjects WHERE (name = 'xx_tbl_temp')) DROP TABLE xx_tbl_temp
GO
CREATE TABLE xx_tbl_temp (
division varchar(10),
complete_date smalldatetime,
avg_wrk_days decimal(18,0))
GO


-- Data for table 'xx_tbl_temp'
--
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jan 1 2004 12:00AM', 8.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Feb 1 2004 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Mar 1 2004 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Apr 1 2004 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'May 1 2004 12:00AM', 9.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jun 1 2004 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jul 1 2004 12:00AM', 3.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Aug 1 2004 12:00AM', 4.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Sep 1 2004 12:00AM', 7.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Oct 1 2004 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Nov 1 2004 12:00AM', 3.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Dec 1 2004 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jan 1 2005 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Feb 1 2005 12:00AM', 7.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Mar 1 2005 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Apr 1 2005 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'May 1 2005 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jun 1 2005 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jul 1 2005 12:00AM', 8.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Aug 1 2005 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Sep 1 2005 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Oct 1 2005 12:00AM', 5.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Nov 1 2005 12:00AM', 6.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Dec 1 2005 12:00AM', 7.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Jan 1 2006 12:00AM', 9.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Feb 1 2006 12:00AM', 7.0)
GO
INSERT INTO xx_tbl_temp (division, complete_date, avg_wrk_days)
VALUES('SC', 'Mar 1 2006 12:00AM', 7.0)
GO

Thanks in adv. for your help!
Jose

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 20:45:30

There you go.

select	m.division, m.complete_date, avg(y.avg_wrk_days) as avg_total, 
	dateadd(month, -12, m.complete_date) as start_period, dateadd(month, - 1, m.complete_date) as end_period
from	xx_tbl_temp m inner join xx_tbl_temp y
	on	m.division	= y.division
	and	y.complete_date	>= dateadd(month, -12, m.complete_date)
	and	y.complete_date	<= dateadd(month, - 1, m.complete_date)
group by m.division, m.complete_date
order by m.division, m.complete_date

----------------------------------
'KH'

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-03-03 : 16:07:51

You are awesome! That worked perfectly.

Thanks alot KH.

Jose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources