Create table, how to set PK as MMYYYY?

Question

Create table, how to set PK as MMYYYY?

mario17

SSCertifiable

Points: 6079
More actions
November 22, 2013 at 1:40 pm

#395880

Hi, all
I need to create table with time history data for each month,
now I have PK below, but there is a chance I will need to rerun my script, in this case I will need to do upsert, as later I'll need to stat report selecting only single dataset for each product for month.
create table01
[ID_PRODUCT] [varchar](255) NOT NULL,
[ID_PERIOD] [datetime] NOT NULL, -- datetime or 201311 (YYYYMM as month period)
---
[RUNDATE] [datetime],
[COUNTERS] [INT]
Or probably if I keep ID_PERIOD AS [DATETIME] I can select the latest set for the same month??
What is the best approach? to make it reliable and simple for next generations.
Tx
Mario

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mario17 (11/22/2013)
Hi, all
I need to create table with time history data for each month,
now I have PK below, but there is a chance I will need to rerun my script, in this case I will need to do upsert, as later I'll need to stat report selecting only single dataset for each product for month.
create table01
[ID_PRODUCT] [varchar](255) NOT NULL,
[ID_PERIOD] [datetime] NOT NULL, -- datetime or 201311 (YYYYMM as month period)
---
[RUNDATE] [datetime],
[COUNTERS] [INT]
Or probably if I keep ID_PERIOD AS [DATETIME] I can select the latest set for the same month??
What is the best approach? to make it reliable and simple for next generations.
Tx
Mario

I am not really sure what the question is here. The title of your thread is something setting a primary key but I don't see anything like that in the actual post. So what exactly are you trying to do and what is the question???

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I'm not sure if I understand your requirement, but if you want to have a PK by month, you could create an additional CHECK constraint to validate for the first day of the month. You can easily convert it to YYYYMM on the front end.

CREATE TABLE #Test ( mydate datetime PRIMARY KEY CHECK (DAY(myDate) = 1))

--ALTER TABLE #Test

--ADD CONSTRAINT chkDate CHECK (DAY(myDate) = 1);

INSERT INTO #Test VALUES( '20131101');

GO

INSERT INTO #Test VALUES( '20131001');

GO

INSERT INTO #Test VALUES( '20131105');

GO

INSERT INTO #Test VALUES( '20130901');

GO

INSERT INTO #Test VALUES( '20131001');

GO

SELECT *

FROM #Test

DROP TABLE #Test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TomThomson SSC Guru Points: 104773 More actions · Answer 3

I would keep it as date (not datetime).

Then add a table constraint as suggested by Luis and fix the day part of the date either by an instead of trigger or by code wrapped around any insert statement, for example

create table table01 (

[ID_PRODUCT] [varchar](255) NOT NULL,

[ID_PERIOD] [date] NOT NULL check (datepart(DD,ID_PERIOD) = 1),

[RUNDATE] [datetime],

[COUNTERS] [INT] );

GO

create trigger table01insert ON table01 INSTEAD OF insert AS

INSERT table01 SELECT ID_PRODUCT,

dateadd(DD, 1-datepart(DD,ID_PERIOD),ID_PERIOD),

RUNDATE, COUNTERS

FROM inserted

GO

It is possible to keep all the data select the latest date for the product and month, roughly like this:

select top 1 * from table01 where ID_PRODUCT = @product_id order by datediff(D,ID_PERIOD,@month_id) desc

but that's not particularly tidy and stores unwanted rows which may cause a space problem and/or a performance problem in the long term, plus it means that everyone reading from the table has to remember to do this.

Tom

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

If you're going to store it as MMYYYY, I would store it as an int, not a date or datetime. Otherwise, it will be converted to a different format.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Robert Davis (11/24/2013)
If you're going to store it as MMYYYY, I would store it as an int, not a date or datetime. Otherwise, it will be converted to a different format.

MMYYYY is a bad format if it's stored as an decimal-coded date because it would mean that <, >, and BETWEEN all delivered bizarre results; YYYYMM on the other hand would work fine with those operators. A decimal coded date in an int is used by Microsoft in a few of the tables in MSDB so wouldn't be something new. Of course a check constraint to enforce the constraint that it must be valid year and month numbering would be interesting.

Tom