June 13, 2010 at 5:18 am
I've as T-SQL as follow,
declare @FromDate datetime
set @FromDate='1/1/2011'
declare @ToDate Datetime
set @ToDate='12/1/2011'
Declare @StartingMon as int , @EndingMon as int, @SqlStmt as Varchar(8000)
Set @StartingMon = Cast(year(@FromDate)as varchar) + Left(Convert(varchar,@FromDate,101),2)
Set @EndingMon = Cast(year(@ToDate) as varchar) + Left(Convert(varchar,@ToDate,101),2)
While @StartingMon <= @EndingMon
Begin
IF not EXISTS(select name from sysobjects where name='DERInfo_' + Cast(@StartingMon as varchar) + '' and xtype='U')
Begin
Set @SqlStmt ='CREATE TABLE [dbo].[DERInfo_' + Cast(@StartingMon as varchar) +'](
[trnxid] [int] IDENTITY(1,1) NOT NULL,
[DDate] [datetime] NOT NULL,
[TID] [varchar](20) NOT NULL,
[RID] [varchar](20) NOT NULL,
[TripN] [varchar](10) NOT NULL,
[Busn] [varchar](10) NOT NULL,
[dtGrp] [datetime] NOT NULL,
CONSTRAINT [PK_DERInfo_' + Cast(@StartingMon as varchar) +'_P01] PRIMARY KEY CLUSTERED
(
[TID] ASC,
[dtGrp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DERInfo_' + Cast(@StartingMon as varchar) +'_P01] UNIQUE NONCLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
--Exec (@SqlStmt)
print 'DERInfo_' + Cast(@StartingMon as varchar) + ' created ...'
End
Else
Begin
print 'DERInfo_' + Cast(@StartingMon as varchar) + ' exit ...'
End
Set @StartingMon = @StartingMon + 1
End
If
declare @FromDate datetime
set @FromDate='1/1/2011'
declare @ToDate Datetime
set @ToDate='12/1/2011'
my T-SQL running perfectly, and the output as follow,
DERInfo_201101 created ...
DERInfo_201102 created ...
DERInfo_201103 created ...
DERInfo_201104 created ...
DERInfo_201105 created ...
DERInfo_201106 created ...
DERInfo_201107 created ...
DERInfo_201108 created ...
DERInfo_201109 created ...
DERInfo_201110 created ...
DERInfo_201111 created ...
DERInfo_201112 created ...
But if me change my value as follow,
declare @FromDate datetime
set @FromDate='1/1/2011'
declare @ToDate Datetime
set @ToDate='2/1/2012'
the output was incorrect as follow,
DERInfo_201101 created ...
DERInfo_201102 created ...
DERInfo_201103 created ...
DERInfo_201104 created ...
DERInfo_201105 created ...
DERInfo_201106 created ...
DERInfo_201107 created ...
DERInfo_201108 created ...
DERInfo_201109 created ...
DERInfo_201110 created ...
DERInfo_201111 created ...
DERInfo_201112 created ...
DERInfo_201113 created ...
DERInfo_201114 created ...
DERInfo_201115 created ...
DERInfo_201116 created ...
DERInfo_201117 created ...
DERInfo_201118 created ...
DERInfo_201119 created ...
DERInfo_201120 created ...
DERInfo_201121 created ...
DERInfo_201122 created ...
DERInfo_201123 created ...
DERInfo_201124 created ...
DERInfo_201125 created ...
DERInfo_201126 created ...
DERInfo_201127 created ...
DERInfo_201128 created ...
DERInfo_201129 created ...
DERInfo_201130 created ...
DERInfo_201131 created ...
DERInfo_201132 created ...
DERInfo_201133 created ...
DERInfo_201134 created ...
DERInfo_201135 created ...
DERInfo_201136 created ...
DERInfo_201137 created ...
DERInfo_201138 created ...
DERInfo_201139 created ...
DERInfo_201140 created ...
DERInfo_201141 created ...
DERInfo_201142 created ...
DERInfo_201143 created ...
DERInfo_201144 created ...
DERInfo_201145 created ...
DERInfo_201146 created ...
DERInfo_201147 created ...
DERInfo_201148 created ...
DERInfo_201149 created ...
DERInfo_201150 created ...
DERInfo_201151 created ...
DERInfo_201152 created ...
DERInfo_201153 created ...
DERInfo_201154 created ...
DERInfo_201155 created ...
DERInfo_201156 created ...
DERInfo_201157 created ...
DERInfo_201158 created ...
DERInfo_201159 created ...
DERInfo_201160 created ...
DERInfo_201161 created ...
DERInfo_201162 created ...
DERInfo_201163 created ...
DERInfo_201164 created ...
DERInfo_201165 created ...
DERInfo_201166 created ...
DERInfo_201167 created ...
DERInfo_201168 created ...
DERInfo_201169 created ...
DERInfo_201170 created ...
DERInfo_201171 created ...
DERInfo_201172 created ...
DERInfo_201173 created ...
DERInfo_201174 created ...
DERInfo_201175 created ...
DERInfo_201176 created ...
DERInfo_201177 created ...
DERInfo_201178 created ...
DERInfo_201179 created ...
DERInfo_201180 created ...
DERInfo_201181 created ...
DERInfo_201182 created ...
DERInfo_201183 created ...
DERInfo_201184 created ...
DERInfo_201185 created ...
DERInfo_201186 created ...
DERInfo_201187 created ...
DERInfo_201188 created ...
DERInfo_201189 created ...
DERInfo_201190 created ...
DERInfo_201191 created ...
DERInfo_201192 created ...
DERInfo_201193 created ...
DERInfo_201194 created ...
DERInfo_201195 created ...
DERInfo_201196 created ...
DERInfo_201197 created ...
DERInfo_201198 created ...
DERInfo_201199 created ...
DERInfo_201200 created ...
DERInfo_201201 created ...
DERInfo_201202 created ...
If i'm using
declare @FromDate datetime
set @FromDate='1/1/2011'
declare @ToDate Datetime
set @ToDate='2/1/2012'
How to to adjust my T-SQL to built output as follow,
DERInfo_201101 created ...
DERInfo_201102 created ...
DERInfo_201103 created ...
DERInfo_201104 created ...
DERInfo_201105 created ...
DERInfo_201106 created ...
DERInfo_201107 created ...
DERInfo_201108 created ...
DERInfo_201109 created ...
DERInfo_201110 created ...
DERInfo_201111 created ...
DERInfo_201112 created ...
DERInfo_201201 created ...
DERInfo_201202 created ...
June 13, 2010 at 5:28 am
You should rethink your concept of generating months.
Instead of dealing with integer data type for your variables you should use datetime (or smalldatetime) together with SET @runningmonth = DATEADD(mm,1,@runningmonth).
To get year and month as character, use CONVERT(CHAR(6),@runningmonth ,112).
June 13, 2010 at 5:37 am
I'm sorry, missDelinda... You provided ready to use sample data so you should expect a coded and tested answer...
Edit: I decided to use a different style for FromDate and ToDate to avoid any conflict with DATEFORMAT settings...
DECLARE @FromDate DATETIME
SET @FromDate='20110101'
DECLARE @ToDate DATETIME
SET @ToDate='20120201'
DECLARE @SqlStmt AS VARCHAR(8000) --,@StartingMon as int , @EndingMon as int
--Set @StartingMon = Cast(year(@FromDate)as varchar) + Left(Convert(varchar,@FromDate,101),2)
--Set @EndingMon = Cast(year(@ToDate) as varchar) + Left(Convert(varchar,@ToDate,101),2)
DECLARE @RunningMonth DATETIME
SET @RunningMonth = @FromDate
WHILE @RunningMonth <= @ToDate
BEGIN
IF NOT EXISTS(SELECT name FROM sysobjects WHERE name='DERInfo_' + CONVERT(CHAR(6),@RunningMonth ,112) + '' AND xtype='U')
BEGIN
SET @SqlStmt ='CREATE TABLE [dbo].[DERInfo_' + CONVERT(CHAR(6),@RunningMonth ,112) +'](
[trnxid] [int] IDENTITY(1,1) NOT NULL,
[DDate] [datetime] NOT NULL,
[TID] [varchar](20) NOT NULL,
[RID] [varchar](20) NOT NULL,
[TripN] [varchar](10) NOT NULL,
[Busn] [varchar](10) NOT NULL,
[dtGrp] [datetime] NOT NULL,
CONSTRAINT [PK_DERInfo_' + CONVERT(CHAR(6),@RunningMonth ,112) +'_P01] PRIMARY KEY CLUSTERED
(
[TID] ASC,
[dtGrp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DERInfo_' + CONVERT(CHAR(6),@RunningMonth ,112) +'_P01] UNIQUE NONCLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
--Exec (@SqlStmt)
PRINT 'DERInfo_' + CONVERT(CHAR(6),@RunningMonth ,112) + ' created ...'
END
ELSE
BEGIN
PRINT 'DERInfo_' + CONVERT(CHAR(6),@RunningMonth ,112) + ' exit ...'
END
SET @RunningMonth = DATEADD(mm,1,@RunningMonth)
--Set @StartingMon = @StartingMon + 1
END
June 13, 2010 at 5:42 am
When thinking about it again:
Why do you need to have separate tables per month?
Do you have to deal with such a large data volume? Did you consider horizontal partitioning?
June 13, 2010 at 6:02 am
lmu92 (6/13/2010)
When thinking about it again:Why do you need to have separate tables per month?
Do you have to deal with such a large data volume? Did you consider horizontal partitioning?
Yes sir. Some of large dta volume.
After table have been segmented, i will built the partion views.
June 14, 2010 at 8:14 am
If you dont want to switch to using a date counter, does this work?
Set @StartingMon = @StartingMon + CASE
WHEN RIGHT(CONVERT(CHAR(6),@StartingMon),2) = '12'
THEN 89
ELSE 1
END
June 15, 2010 at 6:41 am
lemsip (6/14/2010)
If you dont want to switch to using a date counter, does this work?
Set @StartingMon = @StartingMon + CASE
WHEN RIGHT(CONVERT(CHAR(6),@StartingMon),2) = '12'
THEN 89
ELSE 1
END
I think the problem is what Lutz pointed out, that fundamentally the dates should be handled as dates. You could do the above, but you would be adding another layer of functions to convert and grab characters from a string that doesn't need to be a string.
In other words, your end result would be doing this:
1 - declare two datetime variables and populate (@FromDate / @ToDate)
2 - declare two more variables of type int and populate by:
3 - find the year using a function
4 - cast the date to varchar (another function)
5 - find the left ten characters using a function
6 - concatenate the two to create an integer value
7 - go through the loop and when you reach the end (here's where your solution would come in)
8 - use a CASE statement to:
9 - convert the integer value of our date to char(6)
10 - take the right two characters using a function
11 - decide if those characters equal string '12' and either
12 - add 89 or 1
OR, we could do what Lutz suggested:
1 - declare three datetime variables and populate (@FromDate/@ToDate/@RunningMonth)
2 - compare them
3 - add one month for the loop using a dateadd function
Personally, I'd go for the second method. 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply