October 15, 2015 at 12:52 am
There is nothing wrong with this DDL, works perfectly. Just a hunch, have you run DBCC CHECKDB on that database recently? Also is this script generated from the table set that is failing the inserts?
😎
Quick test script modification of your DDL, ran it on 4 different instances without a glitch.
USE TEEST;
GO
SET NOCOUNT ON;
GO
/*
-- http://www.sqlservercentral.com/Forums/Topic1727429-2799-1.aspx
-- [SQLSTATE 42000] (Error 4444)
- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
*/
--===================================
--=== CREATE TABLES
--===================================
IF OBJECT_ID(N'dbo.RptReportData') IS NOT NULL DROP VIEW dbo.RptReportData;
IF OBJECT_ID(N'dbo.RptReportData_201401') IS NOT NULL DROP TABLE dbo.RptReportData_201401;
IF OBJECT_ID(N'dbo.RptReportData_201402') IS NOT NULL DROP TABLE dbo.RptReportData_201402;
IF OBJECT_ID(N'dbo.RptReportData_201403') IS NOT NULL DROP TABLE dbo.RptReportData_201403;
IF OBJECT_ID(N'dbo.RptReportData_201404') IS NOT NULL DROP TABLE dbo.RptReportData_201404;
IF OBJECT_ID(N'dbo.RptReportData_201405') IS NOT NULL DROP TABLE dbo.RptReportData_201405;
IF OBJECT_ID(N'dbo.RptReportData_201406') IS NOT NULL DROP TABLE dbo.RptReportData_201406;
IF OBJECT_ID(N'dbo.RptReportData_201407') IS NOT NULL DROP TABLE dbo.RptReportData_201407;
IF OBJECT_ID(N'dbo.RptReportData_201408') IS NOT NULL DROP TABLE dbo.RptReportData_201408;
IF OBJECT_ID(N'dbo.RptReportData_201409') IS NOT NULL DROP TABLE dbo.RptReportData_201409;
IF OBJECT_ID(N'dbo.RptReportData_201410') IS NOT NULL DROP TABLE dbo.RptReportData_201410;
IF OBJECT_ID(N'dbo.RptReportData_201411') IS NOT NULL DROP TABLE dbo.RptReportData_201411;
IF OBJECT_ID(N'dbo.RptReportData_201412') IS NOT NULL DROP TABLE dbo.RptReportData_201412;
IF OBJECT_ID(N'dbo.RptReportData_201501') IS NOT NULL DROP TABLE dbo.RptReportData_201501;
IF OBJECT_ID(N'dbo.RptReportData_201502') IS NOT NULL DROP TABLE dbo.RptReportData_201502;
IF OBJECT_ID(N'dbo.RptReportData_201503') IS NOT NULL DROP TABLE dbo.RptReportData_201503;
IF OBJECT_ID(N'dbo.RptReportData_201504') IS NOT NULL DROP TABLE dbo.RptReportData_201504;
IF OBJECT_ID(N'dbo.RptReportData_201505') IS NOT NULL DROP TABLE dbo.RptReportData_201505;
IF OBJECT_ID(N'dbo.RptReportData_201506') IS NOT NULL DROP TABLE dbo.RptReportData_201506;
IF OBJECT_ID(N'dbo.RptReportData_201507') IS NOT NULL DROP TABLE dbo.RptReportData_201507;
IF OBJECT_ID(N'dbo.RptReportData_201508') IS NOT NULL DROP TABLE dbo.RptReportData_201508;
IF OBJECT_ID(N'dbo.RptReportData_201509') IS NOT NULL DROP TABLE dbo.RptReportData_201509;
IF OBJECT_ID(N'dbo.RptReportData_201510') IS NOT NULL DROP TABLE dbo.RptReportData_201510;
IF OBJECT_ID(N'dbo.RptReportData_201511') IS NOT NULL DROP TABLE dbo.RptReportData_201511;
CREATE TABLE [dbo].[RptReportData_201401]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201401] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201402]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201402] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201403]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201403] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201404]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201404] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201405]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201405] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201406]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201406] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201407]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201407] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201408]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201408] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201409]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201409] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201410]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201410] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201411]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201411] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201412]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201412] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201501]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201501] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201502]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201502] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201503]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201503] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201504]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201504] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201505]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201505] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201506]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201506] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201507]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201507] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201508]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201508] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201509]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201509] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201510]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201510] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201511]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201511] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
GO
--===================================
--=== CREATE CHECK Constraints
--===================================
ALTER TABLE [dbo].[RptReportData_201401] WITH CHECK ADD CONSTRAINT [chkRptReportData_201401_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-01-01' AND [PeriodEndDate]<'2014-02-01'))
ALTER TABLE [dbo].[RptReportData_201402] WITH CHECK ADD CONSTRAINT [chkRptReportData_201402_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-02-01' AND [PeriodEndDate]<'2014-03-01'))
ALTER TABLE [dbo].[RptReportData_201403] WITH CHECK ADD CONSTRAINT [chkRptReportData_201403_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-03-01' AND [PeriodEndDate]<'2014-04-01'))
ALTER TABLE [dbo].[RptReportData_201404] WITH CHECK ADD CONSTRAINT [chkRptReportData_201404_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-04-01' AND [PeriodEndDate]<'2014-05-01'))
ALTER TABLE [dbo].[RptReportData_201405] WITH CHECK ADD CONSTRAINT [chkRptReportData_201405_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-05-01' AND [PeriodEndDate]<'2014-06-01'))
ALTER TABLE [dbo].[RptReportData_201406] WITH CHECK ADD CONSTRAINT [chkRptReportData_201406_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-06-01' AND [PeriodEndDate]<'2014-07-01'))
ALTER TABLE [dbo].[RptReportData_201407] WITH CHECK ADD CONSTRAINT [chkRptReportData_201407_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-07-01' AND [PeriodEndDate]<'2014-08-01'))
ALTER TABLE [dbo].[RptReportData_201408] WITH CHECK ADD CONSTRAINT [chkRptReportData_201408_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-08-01' AND [PeriodEndDate]<'2014-09-01'))
ALTER TABLE [dbo].[RptReportData_201409] WITH CHECK ADD CONSTRAINT [chkRptReportData_201409_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-09-01' AND [PeriodEndDate]<'2014-10-01'))
ALTER TABLE [dbo].[RptReportData_201410] WITH CHECK ADD CONSTRAINT [chkRptReportData_201410_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-10-01' AND [PeriodEndDate]<'2014-11-01'))
ALTER TABLE [dbo].[RptReportData_201411] WITH CHECK ADD CONSTRAINT [chkRptReportData_201411_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-11-01' AND [PeriodEndDate]<'2014-12-01'))
ALTER TABLE [dbo].[RptReportData_201412] WITH CHECK ADD CONSTRAINT [chkRptReportData_201412_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-12-01' AND [PeriodEndDate]<'2015-01-01'))
ALTER TABLE [dbo].[RptReportData_201501] WITH CHECK ADD CONSTRAINT [chkRptReportData_201501_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-01-01' AND [PeriodEndDate]<'2015-02-01'))
ALTER TABLE [dbo].[RptReportData_201502] WITH CHECK ADD CONSTRAINT [chkRptReportData_201502_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-02-01' AND [PeriodEndDate]<'2015-03-01'))
ALTER TABLE [dbo].[RptReportData_201503] WITH CHECK ADD CONSTRAINT [chkRptReportData_201503_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-03-01' AND [PeriodEndDate]<'2015-04-01'))
ALTER TABLE [dbo].[RptReportData_201504] WITH CHECK ADD CONSTRAINT [chkRptReportData_201504_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-04-01' AND [PeriodEndDate]<'2015-05-01'))
ALTER TABLE [dbo].[RptReportData_201505] WITH CHECK ADD CONSTRAINT [chkRptReportData_201505_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-05-01' AND [PeriodEndDate]<'2015-06-01'))
ALTER TABLE [dbo].[RptReportData_201506] WITH CHECK ADD CONSTRAINT [chkRptReportData_201506_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-06-01' AND [PeriodEndDate]<'2015-07-01'))
ALTER TABLE [dbo].[RptReportData_201507] WITH CHECK ADD CONSTRAINT [chkRptReportData_201507_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-07-01' AND [PeriodEndDate]<'2015-08-01'))
ALTER TABLE [dbo].[RptReportData_201508] WITH CHECK ADD CONSTRAINT [chkRptReportData_201508_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-08-01' AND [PeriodEndDate]<'2015-09-01'))
ALTER TABLE [dbo].[RptReportData_201509] WITH CHECK ADD CONSTRAINT [chkRptReportData_201509_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-09-01' AND [PeriodEndDate]<'2015-10-01'))
ALTER TABLE [dbo].[RptReportData_201510] WITH CHECK ADD CONSTRAINT [chkRptReportData_201510_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-10-01' AND [PeriodEndDate]<'2015-11-01'))
ALTER TABLE [dbo].[RptReportData_201511] WITH CHECK ADD CONSTRAINT [chkRptReportData_201511_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-11-01' AND [PeriodEndDate]<'2015-12-01'))
GO
--===================================
--=== CHECK Constraints
--===================================
ALTER TABLE [dbo].[RptReportData_201401] CHECK CONSTRAINT [chkRptReportData_201401_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201402] CHECK CONSTRAINT [chkRptReportData_201402_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201403] CHECK CONSTRAINT [chkRptReportData_201403_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201404] CHECK CONSTRAINT [chkRptReportData_201404_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201405] CHECK CONSTRAINT [chkRptReportData_201405_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201406] CHECK CONSTRAINT [chkRptReportData_201406_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201407] CHECK CONSTRAINT [chkRptReportData_201407_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201408] CHECK CONSTRAINT [chkRptReportData_201408_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201409] CHECK CONSTRAINT [chkRptReportData_201409_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201410] CHECK CONSTRAINT [chkRptReportData_201410_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201411] CHECK CONSTRAINT [chkRptReportData_201411_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201412] CHECK CONSTRAINT [chkRptReportData_201412_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201501] CHECK CONSTRAINT [chkRptReportData_201501_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201502] CHECK CONSTRAINT [chkRptReportData_201502_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201503] CHECK CONSTRAINT [chkRptReportData_201503_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201504] CHECK CONSTRAINT [chkRptReportData_201504_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201505] CHECK CONSTRAINT [chkRptReportData_201505_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201506] CHECK CONSTRAINT [chkRptReportData_201506_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201507] CHECK CONSTRAINT [chkRptReportData_201507_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201508] CHECK CONSTRAINT [chkRptReportData_201508_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201509] CHECK CONSTRAINT [chkRptReportData_201509_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201510] CHECK CONSTRAINT [chkRptReportData_201510_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201511] CHECK CONSTRAINT [chkRptReportData_201511_PeriodEndDate]
GO
--===================================
--=== CREATE Partitioned View
--===================================
CREATE VIEW [dbo].[RptReportData] AS
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201401
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201402
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201403
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201404
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201405
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201406
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201407
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201408
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201409
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201410
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201411
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201412
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201501
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201502
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201503
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201504
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201505
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201506
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201507
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201508
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201509
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201510
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201511
GO
DECLARE @FIRST_DATE DATE = '2014-01-01';
DECLARE @LAST_DATE DATE = '2015-12-01';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) X(N))
, NUMS(N) AS (SELECT TOP(DATEDIFF(DAY,@FIRST_DATE,@LAST_DATE)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,FIVE_A_DAY(NX) AS (SELECT NX FROM (VALUES (1),(2),(3),(4),(5)) AS X(NX))
INSERT INTO [dbo].[RptReportData]
([Amount]
,[Credits]
,[CurrencyCode]
,[Deposits]
,[NoOfRefs]
,[OrganizationId]
,[PeriodEndDate]
,[ReferenceTypeId]
,[RptPeriodId]
,[SubAccountId]
,[SystemAccountId]
,[VatCategoryId])
SELECT
CONVERT(decimal(38,4),NM.N,0)
,CONVERT(decimal(38,4),NM.N,0)
,'USD'
,CONVERT(decimal(38,4),NM.N,0)
,FAD.NX
,FAD.NX
,DATEADD(DAY,NM.N,@FIRST_DATE)
,FAD.NX
,FAD.NX
,FAD.NX
,FAD.NX
,FAD.NX
FROM NUMS NM
CROSS APPLY FIVE_A_DAY FAD;
SELECT
RD.Amount
,RD.Credits
,RD.CurrencyCode
,RD.Deposits
,RD.NoOfRefs
,RD.OrganizationId
,RD.PeriodEndDate
,RD.ReferenceTypeId
,RD.RptPeriodId
,RD.SubAccountId
,RD.SystemAccountId
,RD.VatCategoryId
FROM dbo.RptReportData RD;
October 15, 2015 at 1:52 am
Thank you very much for taking the time to run the scripts and test the resulting structure. It feels good to know that it works on other systems even though it does not work here.
This is a brand new database so I didn't think of running DBCC CHECKDB on it before, however, I have now done that and it shows no errors.
"CHECKDB found 0 allocation errors and 0 consistency errors in database 'DBName'."
The scripts I attached were generated from the failing environment, yes.
I guess that I'll just have to tear down that structure and re-build it from the generated scripts. Seems like an exercise in futility but that's the only way out I can think of now.
I'll keep you posted on how it worked.
Daniel
October 15, 2015 at 7:00 am
What I ended up doing was to create the logic in the SP that inserts/deletes data to know which partition it should insert into or delete from. I did not have the time to find out why this would not work on my servers.
Thanks again for the help
Daniel
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply