当选择Monthly-Day of Week的时候，右边会出现两个Dropdownlist， 意思是要在每个 月选择一天，这一天是第几周的周几。 所一第一个Dropdownlist表示的是要选择第几 周，后一个是表示周几。所以报表就要列出，本Planyear中每个月份的这一天。
当选择Manual的时候， 计划日期同FactPlanDate
上边是数据库表结构的简化版 代码如下
USE [master] GO /****** 对象: Database [Help] 脚本日期: 08/20/2008 16:08:24 ******/ IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Help') BEGIN CREATE DATABASE [Help] ON PRIMARY ( NAME = N'Help', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Help.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Help_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Help_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) END
GO EXEC dbo.sp_dbcmptlevel @dbname=N'Help', @new_cmptlevel=90 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [Help].[dbo].[sp_fulltext_database] @action = 'disable' end GO ALTER DATABASE [Help] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [Help] SET ANSI_NULLS OFF GO ALTER DATABASE [Help] SET ANSI_PADDING OFF GO ALTER DATABASE [Help] SET ANSI_WARNINGS OFF GO ALTER DATABASE [Help] SET ARITHABORT OFF GO ALTER DATABASE [Help] SET AUTO_CLOSE OFF GO ALTER DATABASE [Help] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [Help] SET AUTO_SHRINK OFF GO ALTER DATABASE [Help] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [Help] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [Help] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [Help] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [Help] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [Help] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [Help] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [Help] SET ENABLE_BROKER GO ALTER DATABASE [Help] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [Help] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [Help] SET TRUSTWORTHY OFF GO ALTER DATABASE [Help] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [Help] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [Help] SET READ_WRITE GO ALTER DATABASE [Help] SET RECOVERY FULL GO ALTER DATABASE [Help] SET MULTI_USER GO ALTER DATABASE [Help] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [Help] SET DB_CHAINING OFF
USE [Help] GO /****** 对象: Table [dbo].[Employer] 脚本日期: 08/20/2008 16:08:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employer]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Employer]( [RowID] [int] IDENTITY(1,1) NOT NULL, [EmployerName] [nvarchar](50) NULL, CONSTRAINT [PK_Employer] PRIMARY KEY CLUSTERED ( [RowID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employer', @level2type=N'COLUMN', @level2name=N'RowID'
GO /****** 对象: Table [dbo].[PlanYear] 脚本日期: 08/20/2008 16:08:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanYear]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[PlanYear]( [RowID] [int] IDENTITY(1,1) NOT NULL, [PlanYearName] [nvarchar](50) NULL, CONSTRAINT [PK_PlanYear] PRIMARY KEY CLUSTERED ( [RowID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PlanYear', @level2type=N'COLUMN', @level2name=N'RowID'
GO /****** 对象: Table [dbo].[PlanInfo] 脚本日期: 08/20/2008 16:08:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanInfo]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[PlanInfo]( [RowID] [int] IDENTITY(1,1) NOT NULL, [EmployerID] [int] NULL, [PlanYearID] [int] NULL, CONSTRAINT [PK_PlanInfo] PRIMARY KEY CLUSTERED ( [RowID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PlanInfo', @level2type=N'COLUMN', @level2name=N'RowID'
GO /****** 对象: Table [dbo].[PlanDates] 脚本日期: 08/20/2008 16:08:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanDates]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[PlanDates]( [RowID] [int] IDENTITY(1,1) NOT NULL, [PlanInfoID] [int] NULL, [FactPlanDate] [datetime] NULL, CONSTRAINT [PK_PlanDates] PRIMARY KEY CLUSTERED ( [RowID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PlanDates', @level2type=N'COLUMN', @level2name=N'RowID'
GO /****** 对象: Table [dbo].[PlanDateRules] 脚本日期: 08/20/2008 16:08:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanDateRules]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[PlanDateRules]( [RowID] [int] IDENTITY(1,1) NOT NULL, [PlanInfoID] [int] NULL, [Rule] [nvarchar](50) NULL, [RuleDate1] [nvarchar](50) NULL, [RuleDate2] [nvarchar](50) NULL, CONSTRAINT [PK_PlanDateRules] PRIMARY KEY CLUSTERED ( [RowID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PlanDateRules', @level2type=N'COLUMN', @level2name=N'RowID'
GO USE [Help] GO USE [Help] GO USE [Help] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PlanInfo_Employer]') AND parent_object_id = OBJECT_ID(N'[dbo].[PlanInfo]')) ALTER TABLE [dbo].[PlanInfo] WITH CHECK ADD CONSTRAINT [FK_PlanInfo_Employer] FOREIGN KEY([EmployerID]) REFERENCES [dbo].[Employer] ([RowID]) GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PlanInfo_PlanYear]') AND parent_object_id = OBJECT_ID(N'[dbo].[PlanInfo]')) ALTER TABLE [dbo].[PlanInfo] WITH CHECK ADD CONSTRAINT [FK_PlanInfo_PlanYear] FOREIGN KEY([PlanYearID]) REFERENCES [dbo].[PlanYear] ([RowID]) GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PlanDates_PlanInfo]') AND parent_object_id = OBJECT_ID(N'[dbo].[PlanDates]')) ALTER TABLE [dbo].[PlanDates] WITH CHECK ADD CONSTRAINT [FK_TBL_PlanDates_PlanInfo] FOREIGN KEY([PlanInfoID]) REFERENCES [dbo].[PlanInfo] ([RowID]) GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PlanDateRules_PlanInfo]') AND parent_object_id = OBJECT_ID(N'[dbo].[PlanDateRules]')) ALTER TABLE [dbo].[PlanDateRules] WITH CHECK ADD CONSTRAINT [FK_TBL_PlanDateRules_PlanInfo] FOREIGN KEY([PlanInfoID]) REFERENCES [dbo].[PlanInfo] ([RowID])
DECLARE @PlanYearName varchar(100) SELECT @PlanYearName = PlanYearName FROM PlanYear WHERE RowID = 1 DECLARE @RULE varchar(255) DECLARE @RuleDate1 varchar(255) DECLARE @RuleDate2 varchar(255) SELECT TOP 1 @RULE = [RULE] , @RuleDate1 =RuleDate1,@RuleDate2 =RuleDate2 FROM PlanDateRules WHERE PlanInfoID =( SELECT RowID FROM PlanInfo WHERE PlanYearID =@PlanYearID AND EmployerID =@EmployerID) IF @RULE <> 'Manual' BEGIN DECLARE @tempTableB TABLE(FactPlanDate datetime,row_num int) INSERT @tempTableB SELECT * FROM [dbo].[fn_ReturnDate](year(@PlanYearName),@RULE,@RuleDate1,@RuleDate2)
SELECT EmployerName,PlanYearName,FactPlanDate,Actual FROM (SELECT ROW_NUMBER()Over(ORDER BY PlanDates.FactPlanDate) as row_num, Employer.EmployerName AS EmployerName , PlanYear.PlanYearName AS PlanYearName ,PlanDates.FactPlanDate AS Actual FROM PlanInfo INNER JOIN PlanDates ON PlanDates.PlanInfoID = PlanInfo.RowID INNER JOIN Employer ON Employer.RowID =PlanInfo.EmployerID INNER JOIN PlanYear ON PlanYear.RowID = PlanInfo.PlanYearID WHERE PlanInfo.PlanYearID = @PlanYearID AND PlanInfo.EmployerID = @EmployerID AND YEAR(PlanDates.FactPlanDate) =@PlanYearName) AS tempTableA INNER JOIN (SELECT* FROM @tempTableB) AS tempTableB ON tempTableB.row_num = tempTableA.row_num END ELSE BEGIN SELECT EmployerName,PlanYearName,Actual AS FactPlanDate,Actual FROM (SELECT ROW_NUMBER()Over(ORDER BY PlanDates.FactPlanDate) as row_num, Employer.EmployerName AS EmployerName , PlanYear.PlanYearName AS PlanYearName ,PlanDates.FactPlanDate AS Actual FROM PlanInfo INNER JOIN PlanDates ON PlanDates.PlanInfoID = PlanInfo.RowID INNER JOIN Employer ON Employer.RowID =PlanInfo.EmployerID
INNER JOIN PlanYear ON PlanYear.RowID = PlanInfo.PlanYearID WHERE PlanInfo.PlanYearID = @PlanYearID AND PlanInfo.EmployerID = @EmployerID AND YEAR(PlanDates.FactPlanDate) =@PlanYearName) AS tempTableA END -- END --sp_Show_Employer_PlanDate 1,2
