Partitioned View Fun! - SQL Server Forums

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

SQL Server Development (2000)

Partitioned View Fun!

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-08 : 12:00:49

I've tried this a few different ways, and can't seem to get it to work. We keep getting the following error message:


Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'V_CALLS' is not updatable because a partitioning column was not found.

Bottom line, I want to split my data by month on the colum ConnectedDate which is a DateTime field. It is not unique, so that's why we added the Call_ID GUID field.

Can anyone point us in the right direction?



CREATE TABLE Calls_JAN(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_JAN] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_FEB(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_FEB] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_MAR(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_MAR] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_APR(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_APR] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_MAY(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_MAY] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_JUN(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_JUN] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_JUL(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_JUL] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_AUG(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_AUG] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_SEP(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_SEP] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_OCT(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_OCT] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_NOV(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_NOV] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
CREATE TABLE Calls_DEC(Call_ID uniqueidentifier ROWGUIDCOL  NOT NULL, 
CalledNumber varchar(20) NOT NULL, ConnectedDate datetime NOT NULL, 
ExtendedCharge money NOT NULL, TotalTime float NOT NULL,
AccountCode varchar(50) NOT NULL, cre_date datetime DEFAULT (getdate()),
CONSTRAINT [PK_Calls_DEC] PRIMARY KEY  CLUSTERED 
 (
  [Call_ID],
  [ConnectedDate]
 )  ON [PRIMARY])
 
ALTER TABLE Calls_JAN ADD CONSTRAINT CK_JAN_DATE CHECK (MONTH(ConnectedDate) = '1')
ALTER TABLE Calls_FEB ADD CONSTRAINT CK_FEB_DATE CHECK (MONTH(ConnectedDate) = '2')
ALTER TABLE Calls_MAR ADD CONSTRAINT CK_MAR_DATE CHECK (MONTH(ConnectedDate) = '3')
ALTER TABLE Calls_APR ADD CONSTRAINT CK_APR_DATE CHECK (MONTH(ConnectedDate) = '4')
ALTER TABLE Calls_MAY ADD CONSTRAINT CK_MAY_DATE CHECK (MONTH(ConnectedDate) = '5')
ALTER TABLE Calls_JUN ADD CONSTRAINT CK_JUN_DATE CHECK (MONTH(ConnectedDate) = '6')
ALTER TABLE Calls_JUL ADD CONSTRAINT CK_JUL_DATE CHECK (MONTH(ConnectedDate) = '7')
ALTER TABLE Calls_AUG ADD CONSTRAINT CK_AUG_DATE CHECK (MONTH(ConnectedDate) = '8')
ALTER TABLE Calls_SEP ADD CONSTRAINT CK_SEP_DATE CHECK (MONTH(ConnectedDate) = '9')
ALTER TABLE Calls_OCT ADD CONSTRAINT CK_OCT_DATE CHECK (MONTH(ConnectedDate) = '10')
ALTER TABLE Calls_NOV ADD CONSTRAINT CK_NOV_DATE CHECK (MONTH(ConnectedDate) = '11')
ALTER TABLE Calls_DEC ADD CONSTRAINT CK_DEC_DATE CHECK (MONTH(ConnectedDate) = '12')
GO

CREATE VIEW V_CALLS
AS
    SELECT * FROM Calls_JAN
UNION ALL
    SELECT * FROM Calls_FEB
UNION ALL
    SELECT * FROM Calls_MAR
UNION ALL
    SELECT * FROM Calls_APR
UNION ALL
    SELECT * FROM Calls_MAY
UNION ALL
    SELECT * FROM Calls_JUN
UNION ALL
    SELECT * FROM Calls_JUL
UNION ALL
    SELECT * FROM Calls_AUG
UNION ALL
    SELECT * FROM Calls_SEP
UNION ALL
    SELECT * FROM Calls_OCT
UNION ALL
    SELECT * FROM Calls_NOV
UNION ALL
    SELECT * FROM Calls_DEC

GO
INSERT INTO 
[v_Calls](
[Call_ID], [CalledNumber], [ConnectedDate], [ExtendedCharge], 
[TotalTime], [AccountCode], [cre_date])
VALUES(newid(), '12516339252', '11/11/2003', .23, '15', '69', '11/11/2003')

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-08 : 12:29:23

When you created the table, did you set all of the various things that are required for a partitioned view? This is not included in your sample code, so I would guess not. Take a look at the page in BOL entitled Creating a Partitioned View for the requirements. There are a bunch of SET statements that you have to do.

Here is some information about the error:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9734

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-08 : 13:17:14

I didn't see anything about SET statements in the BOL under "partitioned views." Am I looking in the wrong place?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-08 : 13:24:07

I was thinking about indexed views, sorry about that. But is everything followed exactly according to the page that I mentioned in BOL.

What version of SQL Server 2000 are you using?:

"You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition"

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 13:27:57

I think you need to flip the PK


USE Northwind
GO

CREATE TABLE Orders2000
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_Orders2000
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_Orders2000_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_Orders2000_orderdate
      CHECK(orderdate >= '20000101' AND orderdate < '20010101'))

CREATE TABLE Orders2001
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_Orders2001
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_Orders2001_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_Orders2001_orderdate
      CHECK(orderdate >= '20010101' AND orderdate < '20020101'))

CREATE TABLE Orders2002
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime     NOT NULL,
  CONSTRAINT PK_Orders2002
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_Orders2002_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_Orders2002_orderdate
      CHECK(orderdate >= '20020101' AND orderdate < '20030101'))

GO
CREATE VIEW v_Orders
AS
SELECT * FROM Orders2000
UNION ALL
SELECT * FROM Orders2001
UNION ALL
SELECT * FROM Orders2002
GO


INSERT INTO v_Orders(orderId, customerId, Orderdate)
SELECT 1, 'BRETT', '2002/12/25'

SELECT * FROM v_Orders

SELECT * FROM Orders2000

SELECT * FROM Orders2001

SELECT * FROM Orders2002
GO

DROP VIEW v_Orders
DROP TABLE Orders2000
DROP TABLE Orders2001
DROP TABLE Orders2002
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 14:21:58

Did a google...every example I found had month as a separate int field

Tried you're method, but used datepart and that didn't work...

Its says the partiton can not be a derived column...

Even though it's not a derived column, I'm thinking that even though the contraint works...the contraint is derived, so it can't "see" the check, which is required for the partition...just a guess...seems kinda hokey though...

Maybe you can add a month int column, and use an instead of trigger to derive the month on the insert to the view....oh wait...never mind

There's got to be away...

the following works..though you have to code the date range like in my first sample...and it's only for 1 particular year....


USE Northwind
GO

CREATE TABLE OrdersJAN
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersJAN
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersJAN_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersJAN_orderdate
      CHECK(orderdate>= '01/01/2003' AND orderdate <= '01/31/2003'))
CREATE TABLE OrdersFEB
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersFEB
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersFEB_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersFEB_orderdate
      CHECK(orderdate>= '02/01/2003' AND orderdate <= '02/28/2003'))
CREATE TABLE OrdersMAR
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersMAR
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersMAR_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersMAR_orderdate
      CHECK(orderdate>= '03/01/2003' AND orderdate <= '03/31/2003'))
CREATE TABLE OrdersAPR
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersAPR
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersAPR_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersAPR_orderdate
      CHECK(orderdate>= '04/01/2003' AND orderdate <= '04/30/2003'))
CREATE TABLE OrdersMAY
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersMAY
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersMAY_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersMAY_orderdate
      CHECK(orderdate>= '05/01/2003' AND orderdate <= '05/31/2003'))
CREATE TABLE OrdersJUN
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersJUN
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersJUN_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersJUN_orderdate
      CHECK(orderdate>= '06/01/2003' AND orderdate <= '06/30/2003'))
CREATE TABLE OrdersJUL
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersJUL
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersJUL_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersJUL_orderdate
      CHECK(orderdate>= '07/01/2003' AND orderdate <= '07/31/2003'))
CREATE TABLE OrdersAUG
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersAUG
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersAUG_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersAUG_orderdate
      CHECK(orderdate>= '08/01/2003' AND orderdate <= '08/31/2003'))
CREATE TABLE OrdersSEP
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersSEP
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersSEP_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersSEP_orderdate
      CHECK(orderdate>= '09/01/2003' AND orderdate <= '09/30/2003'))
CREATE TABLE OrdersOCT
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersOCT
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersOCT_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersOCT_orderdate
      CHECK(orderdate>= '10/01/2003' AND orderdate <= '10/31/2003'))
CREATE TABLE OrdersNOV
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersNOV
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersNOV_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersNOV_orderdate
      CHECK(orderdate>= '11/01/2003' AND orderdate <= '11/30/2003'))
CREATE TABLE OrdersDEC
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersDEC
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersDEC_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersDEC_orderdate
      CHECK(orderdate>= '12/01/2003' AND orderdate <= '12/31/2003'))
GO

CREATE VIEW v_Orders
AS
SELECT * FROM OrdersJAN
UNION ALL
SELECT * FROM OrdersFEB
UNION ALL
SELECT * FROM OrdersMAR
UNION ALL
SELECT * FROM OrdersAPR
UNION ALL
SELECT * FROM OrdersMAY
UNION ALL
SELECT * FROM OrdersJUN
UNION ALL
SELECT * FROM OrdersJUL
UNION ALL
SELECT * FROM OrdersAUG
UNION ALL
SELECT * FROM OrdersSEP
UNION ALL
SELECT * FROM OrdersOCT
UNION ALL
SELECT * FROM OrdersNOV
UNION ALL
SELECT * FROM OrdersDEC
GO


INSERT INTO OrdersDEC(orderId, customerId, Orderdate)
SELECT 1, 'BRETT', '2003/12/25'

INSERT INTO v_Orders(orderId, customerId, Orderdate)
SELECT 1, 'BRETT', '2003/11/25'

SELECT * FROM v_Orders

SELECT * FROM OrdersJAN

SELECT * FROM OrdersFEB

SELECT * FROM OrdersMAR

SELECT * FROM OrdersAPR

SELECT * FROM OrdersMAY

SELECT * FROM OrdersJUN

SELECT * FROM OrdersJUL

SELECT * FROM OrdersAUG

SELECT * FROM OrdersSEP

SELECT * FROM OrdersOCT

SELECT * FROM OrdersNOV

SELECT * FROM OrdersDEC

GO

DROP VIEW v_Orders
DROP TABLE OrdersJAN
DROP TABLE OrdersFEB
DROP TABLE OrdersMAR
DROP TABLE OrdersAPR
DROP TABLE OrdersMAY
DROP TABLE OrdersJUN
DROP TABLE OrdersJUL
DROP TABLE OrdersAUG
DROP TABLE OrdersSEP
DROP TABLE OrdersOCT
DROP TABLE OrdersNOV
DROP TABLE OrdersDEC

GO


SELECT MONTH('01/01/2003')

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-08 : 14:29:24

Yeah, I added a "MonthNum" INT field and that seemed to make it work. I also flipped around the PK.

Thanks for you help!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 14:39:09

quote:
Originally posted by MichaelP

Yeah, I added a "MonthNum" INT field and that seemed to make it work. I also flipped around the PK.

Thanks for you help!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Great...but don't you think that's bogus?

How do you plan to keep it populated..an instead of trigger on every base table?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 15:04:32

Damn...I thought I had it...I figured if I decontruct the date, then reconstruct it for the range we'd want, that that would do it...

At least there goes the theory about the derived contraint...I bet it's because it's nondeterministic...

But I thought this was going to do it...


CREATE TABLE OrdersJAN
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersJAN
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersJAN_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersJAN_orderdate
      CHECK (orderdate >= CONVERT(datetime,'01/01/'+CONVERT(char(4),YEAR(orderdate))) 
	AND orderdate <= DATEADD(ms,-2,DATEADD(mm,1,CONVERT(datetime,'01/01/'+CONVERT(char(4),YEAR(orderdate)))))))

CREATE TABLE OrdersFEB
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_OrdersFEB
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_OrdersFEB_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_OrdersFEB_orderdate
      CHECK (orderdate >= CONVERT(datetime,'02/01/'+CONVERT(char(4),YEAR(orderdate))) 
	AND orderdate <= DATEADD(ms,-2,DATEADD(mm,1,CONVERT(datetime,'02/01/'+CONVERT(char(4),YEAR(orderdate)))))))

CREATE VIEW v_Orders
AS
SELECT * FROM OrdersJAN
UNION ALL
SELECT * FROM OrdersFEB


INSERT INTO v_Orders(orderId, customerId, Orderdate)
SELECT 1, 'BRETT', '2003/01/25'


DROP VIEW v_Orders
DROP TABLE OrdersJAN
DROP TABLE OrdersFEB

DECLARE @orderdate datetime
SELECT @orderdate = GetDate()
SELECT CONVERT(datetime,'01/01/'+CONVERT(char(4),YEAR(@orderdate)))
,DATEADD(ms,-2,DATEADD(mm,1,CONVERT(datetime,'01/01/'+CONVERT(char(4),YEAR(@orderdate)))))

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-08 : 15:35:22

Brett, I think we plan on getting that field populated in the INSERT stored proc. That's where we can do our datepart magic to get our "MonthNum" derived field.

It smells of hack, but it's the only way we've been able to get it to work. The queries against this thing are HELLA fast too!

Thanks for everyone's help on this!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-08 : 17:12:04

Out of curiousity, what happens when you get to the next year with this setup? Does Jan contain multiple years, all for January?

- Jeff

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-08 : 17:26:02

Well, I'm thinking each year we add more tables and add more tables to the view. I'm not sure that I like the solution that we have, but I don't think that we'll keep data around for a year.
I suspect that Jan 2003 and Jan 2004 will be in the same table, assuming that we do not archive any data. Running a report on this data spanning more than about three months would be totally insane :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources