Description
Changes in the list price of a product over time.
Table properties
| name | value |
|---|
| name | [Production].[ProductListPriceHistory] |
| created | Mar 19 2009 9:09PM |
| modified | Mar 19 2009 9:10PM
|
| ansi nulls | on
|
| quoted identifier | on
|
| row count | 395 |
| Size of data | 24 kb |
| Size of indexes | 16 kb |
| Maximum size of a single row | 36 bytes |
Columns
| column | datatype | length | bytes | default | nulls | PK | FK | UQ | computed | comment |
|---|
| ProductID | int | 10 | 4 | | no
| composite PK
| Product.ProductID
|
| no
| Product identification number. Foreign key to Product.ProductID
|
| StartDate | datetime | 23 | 8 | | no
| composite PK
| |
| no
| List price start date.
|
| EndDate | datetime | 23 | 8 | | yes
|
| |
| no
| List price end date
|
| ListPrice | money | 19 | 8 | | no
|
| |
| no
| Product list price.
|
| ModifiedDate | datetime | 23 | 8 | (getdate()) | no
|
| |
| no
| Date and time the record was last updated.
|
Indexes
References
Foreign key graph
Foreign keys
| name | columns | foreign columns | type | comment |
|---|
| FK_ProductListPriceHistory_Product_ProductID | ProductID | Product.ProductID |
| Foreign key constraint referencing Product.ProductID.
|
Check constraints
Defaults
Dependency graph
Objects that depend on [Production].[ProductListPriceHistory]
Sample rows
| ProductID | StartDate | EndDate | ListPrice | ModifiedDate |
|---|
| 707
| 7/1/2001 12:00:00 AM
| 6/30/2002 12:00:00 AM
| 33.6442
| 6/30/2002 12:00:00 AM
|
| 707
| 7/1/2002 12:00:00 AM
| 6/30/2003 12:00:00 AM
| 33.6442
| 6/30/2003 12:00:00 AM
|
| 707
| 7/1/2003 12:00:00 AM
| NULL
| 34.9900
| 6/10/2003 12:00:00 AM
|
| 708
| 7/1/2001 12:00:00 AM
| 6/30/2002 12:00:00 AM
| 33.6442
| 6/30/2002 12:00:00 AM
|
| 708
| 7/1/2002 12:00:00 AM
| 6/30/2003 12:00:00 AM
| 33.6442
| 6/30/2003 12:00:00 AM
|
| 708
| 7/1/2003 12:00:00 AM
| NULL
| 34.9900
| 6/10/2003 12:00:00 AM
|
| 709
| 7/1/2001 12:00:00 AM
| 6/30/2002 12:00:00 AM
| 9.5000
| 6/30/2002 12:00:00 AM
|
| 710
| 7/1/2001 12:00:00 AM
| 6/30/2002 12:00:00 AM
| 9.5000
| 6/30/2002 12:00:00 AM
|
| 711
| 7/1/2001 12:00:00 AM
| 6/30/2002 12:00:00 AM
| 33.6442
| 6/30/2002 12:00:00 AM
|
| 711
| 7/1/2002 12:00:00 AM
| 6/30/2003 12:00:00 AM
| 33.6442
| 6/30/2003 12:00:00 AM
|
Code
CREATE TABLE [Production].[ProductListPriceHistory](
[ProductID] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[ListPrice] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_ProductListPriceHistory_ProductID_StartDate] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[StartDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Production].[ProductListPriceHistory] WITH CHECK ADD CONSTRAINT [FK_ProductListPriceHistory_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[ProductListPriceHistory] CHECK CONSTRAINT [FK_ProductListPriceHistory_Product_ProductID]
ALTER TABLE [Production].[ProductListPriceHistory] WITH CHECK ADD CONSTRAINT [CK_ProductListPriceHistory_EndDate] CHECK (([EndDate]>=[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [Production].[ProductListPriceHistory] CHECK CONSTRAINT [CK_ProductListPriceHistory_EndDate]
ALTER TABLE [Production].[ProductListPriceHistory] WITH CHECK ADD CONSTRAINT [CK_ProductListPriceHistory_ListPrice] CHECK (([ListPrice]>(0.00)))
ALTER TABLE [Production].[ProductListPriceHistory] CHECK CONSTRAINT [CK_ProductListPriceHistory_ListPrice]
ALTER TABLE [Production].[ProductListPriceHistory] ADD CONSTRAINT [DF_ProductListPriceHistory_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]