April 28, 2011 at 2:14 pm
Hello
i need help in writing a query, i want to insert monthid (YYYYMM) in a table. My table would initailly have monthid inserted as 201101
MonthID TimeStamp
201101 2011/01/02 00:00:0000
it could be done by using the below query
(SELECT CONVERT(VARCHAR(4),YEAR(GETDATE())) + CONVERT(VARCHAR(4),DATEPART(WK,GETDATE())))
i actually want to keep track of the process ran every month, my table. If i use the above query to insert the monthid it would be fine and working well, but the problem i had is this query will not insert the monthid properly if i executed in april istead of march
Eg: March - 201103 April - 201104
irrespective to the month i want to insert the monthid's sequentially in my table. i think i have to use a case statement. please help me with this query for the scenario i illustrated.
April 28, 2011 at 2:45 pm
First, you say that you want a monthID, but your formula actually calculates a WEEK ID.
Second, you don't say how the timestamp field relates to the month ID, if at all. If they are related, then you're better off calculating your month ID from your timestamp rather than trying to calculate your timestamp from your month ID.
Third, your formula can be greatly simplified. (I'm using the timestamp field instead of the getdate() function.)
Convert(varchar(6), timestamp, 112)
If you want sequential records, you'll need to query your existing table. This query adds a month to the largest existing timestamp field.
SELECT DateAdd(m, Max(timestamp))
FROM YourTable
You may want to look at using a tally table/cte to insert all missing months between the last existing month and the current month.
You may also want to look using a calendar table.
You can search this site for more information about tally tables or calendar tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 29, 2011 at 3:11 am
Hello Drew,
timestamp column is used to records the time & date at whcih the record is added to the table, it has a default of getdate.
let me explain you again clearly. Intially my table will have a value in monthid column (lets assume).
If i ran my process in march and inserted the monthid for march as 201103. Again i have to run process in next month, if i execute my process in april i can insert the month 201104 without any problem. the problem here is the executoin of my process may not be done exaclty in one particluar month/month end, it may be prolonged to next month also. if the april process is executed in may the value for month id becomes as 201105 if i use the below code. i want to insert the monthid as 201104 even if i ran the process in may or in june.
SELECT Convert(varchar(6), getdate(), 112) will give me a result of 201104.
i also tried with the below code.
SELECT MAX(MONTHID)+1 FROM MY table.
the problem with the above query is it works fine till december. till decemebr it goes on adding 1 to the month and give me the next monthid after december it is going as 201113 which is invalid. after decemebr the value should be inserted as 201201.
My table should get the monthid values as below. (irrespective of month the monthid should be inserted sequentailly)
MonthIDMonth of ExecutionUpdatetimeStamp
201004 April 4/30/2010
201005 June 6/08/2010
201006 June 6/25/2010
201007 July 7/30/2010
201008 Aug 8/29/2010
201009 Oct 10/10/2010
201010 Oct 10/31/2010
201011 Nov 11/30/2010
201012 Dec 12/25/2010
201101 Jan 1/31/2010
May 3, 2011 at 9:41 am
Let me be clear.
* It's best to use a datetime field to store and manipulate datetime data.
* Adding a month is manipulating datetime data.
* Your MonthID is not a datetime field, which is why you are running into problems performing datetime manipulations on it.
* You need to use a datetime field that corresponds to your last MonthID. You can achieve this by
-- Adding a datetime field to your table and using that to derive your MonthID
-- Converting your MonthID to datetime data. (This is horribly inefficient.)
-- Looking up a datetime field in a calendar table using your MonthID
* Once you have this datetime field, it is easy to find the next month using the DateAdd() function.
* Once you have the next month, it's easy to retrieve/calculate the MonthID that corresponds to it.
* You may want to look into using a calendar table.
* You may want to look into using a calendar table.
* You may want to look into using a calendar table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 4, 2011 at 4:16 am
Hi is this what you needed
declare @Temp table(MonthID int, Month_Execution varchar(25) ,UpdatetimeStamp date)
insert @Temp values
(201004, 'April','4/30/2010'),
(201005, 'June', '6/08/2010'),
(201006, 'June', '6/25/2010'),
(201007, 'July', '7/30/2010'),
(201008, 'Aug', '8/29/2010'),
(201009, 'Sep', '9/10/2010'),
(201010, 'Oct', '10/31/2010'),
(201011, 'Nov', '11/30/2010'),
(201012, 'Dec', '12/25/2010')
SELECT CASE WHEN RIGHT(MAX(MONTHID)+1,2)=13 THEN cast(LEFT(MAX(MONTHID),4)+1as varchar(4))+''+cast('01'as varchar(2)) ELSE MAX(MONTHID)+1 END from @Temp
---- 2011 value
insert @Temp values
(201104, 'April','4/30/2011'),
(201105, 'June', '6/08/2011'),
(201106, 'June', '6/25/2011'),
(201107, 'July', '7/30/2011'),
(201108, 'Aug', '8/29/2011'),
(201109, 'Sep', '9/10/2011'),
(201110, 'Oct', '10/31/2011'),
(201111, 'Nov', '11/30/2011'),
(201112, 'Dec', '12/25/2011')
SELECT CASE WHEN RIGHT(MAX(MONTHID)+1,2)=13 THEN cast(LEFT(MAX(MONTHID),4)+1as varchar(4))+''+cast('01'as varchar(2)) ELSE MAX(MONTHID)+1 END from @Temp
---- 2012 value
insert @Temp values
(201201, 'Jan', '01/15/2012')
SELECT CASE WHEN RIGHT(MAX(MONTHID)+1,2)=13 THEN cast(LEFT(MAX(MONTHID),4)+1as varchar(4))+''+cast('01'as varchar(2)) ELSE MAX(MONTHID)+1 END from @Temp
Thanks
Parthi
May 4, 2011 at 4:20 am
parthi-1705 (5/4/2011)
Hi is this what you needed--
Hmm, I don't agree - drew.allen's post describes what is needed IMO.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2011 at 4:29 am
Phil Parkin (5/4/2011)
parthi-1705 (5/4/2011)
Hi is this what you needed--
Hmm, I don't agree - drew.allen's post describes what is needed IMO.
Im Humm..
What is wrong with the above code i have just derived upon which the Questioner has given for him it is going as 13 so for that i have written case statement. If any wrong in the case statement or logic please correct me.
Thanks
Parthi
May 4, 2011 at 4:36 am
parthi-1705 (5/4/2011)
Phil Parkin (5/4/2011)
parthi-1705 (5/4/2011)
Hi is this what you needed--
Hmm, I don't agree - drew.allen's post describes what is needed IMO.
Im Humm..
What is wrong with the above code i have just derived upon which the Questioner has given for him it is going as 13 so for that i have written case statement. If any wrong in the case statement or logic please correct me.
I didn't even check the code, TBH. Whether it's logically correct or not, my comment stands.
As you'll know, this forum is about giving advice as well as unquestioningly providing exactly what posters ask for and drew.allen's post pointed out some shortcomings of using this method at all.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 5, 2011 at 3:53 am
Hello,
as I understand you are trying to add the next MonthID in your table, irrespective of the current month.
And there is another problem, you are trying to use date functions without use datetime fields.
My solution to your problem is:
- take the max MonthId on your table
- convert it to datetime
- add a month
- convert it to varchar (or whatever type your field is)
- insert it into your table
You can do it following this code
declare @Temp table(MonthID char(6))
insert INTO @Temp values ('201010')
insert INTO @Temp values ('201011')
insert INTO @Temp values ('201012')
INSERT INTO @TEMP
select convert(char(6), dateadd(m, 1, cast(max(MonthID) + '01' AS datetime)), 112)
FROM @Temp
SELECT * FROM @Temp
Regards,
Francesc
May 5, 2011 at 7:22 am
frfernan (5/5/2011)
You can do it following this code
declare @Temp table(MonthID char(6))
insert INTO @Temp values ('201010')
insert INTO @Temp values ('201011')
insert INTO @Temp values ('201012')
INSERT INTO @TEMP
select convert(char(6), dateadd(m, 1, cast(max(MonthID) + '01' AS datetime)), 112)
FROM @Temp
SELECT * FROM @Temp
You want to be careful here. The OP never stated what datatype the MonthID field was, but, unless he was doing an implicit conversion in his original code, it looks like it might be integer, in which case your max(MonthID) + '01' won't give you the result that you're expecting.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 5, 2011 at 8:15 am
Hello,
You want to be careful here. The OP never stated what datatype the MonthID field was, but, unless he was doing an implicit conversion in his original code, it looks like it might be integer, in which case your
max(MonthID) + '01'won't give you the result that you're expecting.
you are right, if MonthId is an integer the calculation sentence should be corrected with additional castings.
I'm curious about the use of this field; I never designed a table with a field like that (or don't remember), and when I worked with fields like that I was facing poorly designed applications, not only due to this kind of field. Maybe jagadeeps can comment us what's the meaning of this MonthID column.
Francesc
May 5, 2011 at 9:02 am
frfernan (5/5/2011)
I'm curious about the use of this field; I never designed a table with a field like that (or don't remember), and when I worked with fields like that I was facing poorly designed applications, not only due to this kind of field. Maybe jagadeeps can comment us what's the meaning of this MonthID column.
I've mostly seen it used in data warehouses, OLAP databases, and BI applications where the granularity of the data is only specified to the month level. For instance, the corporate headquarters may only receive monthly sales summaries from each of its local stores. They may not have details at the daily or even weekly level, so it doesn't make sense to assign a specific date.
It doesn't really make sense to use this kind of a field in an OLTP database.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply