Showing posts with label stuff. Show all posts
Showing posts with label stuff. Show all posts

Sunday, March 25, 2012

Creating User Defined Function for Rolling Calendar Year

Has anyone ever created a UDF for a rolling calendar year? I would like to
use that instead of writing this kind of stuff all the time:
if(datepart(month,@.CurrentDate) =12)
Begin
set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
end
if(datepart(month,@.CurrentDate) !=12)
Begin
set @.PastDate = dateadd(month,1,@.PastDate)
End
set @.CurrentDate = dateadd(month,1,@.Currentdate)
if(datepart(day, @.CurrentDate) <= 30)
Begin
set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
Begin
set @.currentDate = @.CurrentDateStep
end
if datepart(month, @.CurrentDate) = 3
Begin
set @.CurrentDate = dateadd(day,2,@.CurrentDate)
end
end
Thanks in advance for any assistance.
Thanks,
SPHow about a calendar table?
http://www.aspfaq.com/2519
"SP" <SP@.discussions.microsoft.com> wrote in message
news:8E1056B9-3011-4523-894A-1087FE936B1A@.microsoft.com...
> Has anyone ever created a UDF for a rolling calendar year? I would like to
> use that instead of writing this kind of stuff all the time:
> if(datepart(month,@.CurrentDate) =12)
> Begin
> set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
> end
> if(datepart(month,@.CurrentDate) !=12)
> Begin
> set @.PastDate = dateadd(month,1,@.PastDate)
> End
> set @.CurrentDate = dateadd(month,1,@.Currentdate)
> if(datepart(day, @.CurrentDate) <= 30)
> Begin
> set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
> if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
> Begin
> set @.currentDate = @.CurrentDateStep
> end
> if datepart(month, @.CurrentDate) = 3
> Begin
> set @.CurrentDate = dateadd(day,2,@.CurrentDate)
> end
> end
> Thanks in advance for any assistance.
> Thanks,
> SP
>|||Hi,
Pardon me for my ignorance, can you please elucidate with an example what
you are trying to achieve?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sure. Sorry for the sparse information.
At the company that I work for, they are always interested in a rolling
calendar year. When I write reports with Reporting Services, they may run
them for say 2006. The end date would always change depending on what month
the report is run. For example, if the report is run today, they would want
the results to show 6/1/2005 to 5/31/2006. If it gets run next month,
7/1/2005 to 6/30/2006, etc.
I always have to wrote some funky code to consider February, etc.
I just thought if someone had written a UDF, that might help. Or send me in
the right direction.
I hope this clarifies things a little and I will definitely take a look at
the Date table. I have done similiar things with an Access database, but
didn't think about it for work.
Thanks to all for looking.
SP
"Omnibuzz" wrote:

> Hi,
> Pardon me for my ignorance, can you please elucidate with an example wha
t
> you are trying to achieve?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Maybe, this should work.. If I understood it wrong.. correct me..
declare @.a datetime
set @.a = getdate() -- Date on which you are running the report
select
dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of th
e month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS 'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS 'End_Dt'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message news:30BC1C01-180E-442C-8D
BE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
>
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
>
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>|||I think it would be cleaner to use the next month's start instead of 3 ms
earlier, and use < on the end_dt instead of <= (and obviously, don't use
between).
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of
the month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
'End_Dt'
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Agreed.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhBC5SIlGHA.984@.TK2MSFTNGP05.phx.gbl...
>I think it would be cleaner to use the next month's start instead of 3 ms
>earlier, and use < on the end_dt instead of <= (and obviously, don't use
>between).
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
> As written, the End_dt is midnight on the last day of the month,
> effectively leaving out all entries for that day.
> I would add one more dateadd step to include ALL times on the last day of
> the month (up to 23:59:59.997)
> DECLARE @.Today datetime
> SET @.Today = getdate() -- Date on which you are running the report
> SELECT
> dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
> 'Begin_Dt'
> , dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
> 'End_Dt'
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
>sql

Monday, March 19, 2012

creating SQLDataSource programmatically?

Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.

heres my code (Master.master.vb):

Protected Sub Page_Init(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Init lblUser.Text = Page.User.Identity.NameDim PUserAs New ControlParameter PUser.ControlID ="lblUser" PUser.Name ="LoginName" PUser.PropertyName ="Text" PUser.Type = TypeCode.String PUser.DefaultValue = Page.User.Identity.NameDim SQLDS_LoginAs New SqlDataSource SQLDS_Login.ID ="SQLDS_Login" SQLDS_Login.ConnectionString ="I put conection string here. How do I use the one on my web.config?" SQLDS_Login.SelectCommand ="SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @.LoginName)" SQLDS_Login.SelectParameters.Add(PUser) SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text GridView1.DataSource = SQLDS_Login GridView1.DataBind()End Sub

When i run, i get this error message:

The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind.

I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically.

Can you help me with this?

as the error states you need to add it to the page i.e. me.controls.add(SQLDS_Login) [or if you're doing it from a page that uses that for a master then me.master.controls.add(SQLDS_login)

Wednesday, March 7, 2012

Creating own Unique Identifier

Hi:
This is pretty basic stuff but I forget how to do this with one update.
I want to create a key for a table. I have two fields:
TableName: MyWorkingTable
PlayerID (int)
Time (DateTime)
My_New_ID (int)
PlayerIDs might be duplicated. But a combination of PlayerID and Time
will always be unique. To begin with, "My_New_ID" values will all be
NULL.
I want to grab an ID value from another table (the max value of all
the IDs from final staging table) and start assigning 'My_New_ID's in
'MyWorkingTable' starting with a number one higher that result. So if
that value is 230, then I start assigning My_New_IDs with 231 and on up
incrementing by one until I run out of records. Then I write all these
records to the final staging table.
How do I do this?
Thanks,
KaydaKayda,
I haven't tested this, it might require adjusting the initial 1 to get
it right.
UPDATE MyWorkingTable
SET My_New_ID = 1 +
(SELECT MAX(OldId) FROM SomeOtherTable) +
(SELECT COUNT(*) FROM MyWorkingTable as T
WHERE T.PlayerID < MyWorkingTable.PlayerID
OR (T.PlayerID = MyWorkingTable.PlayerID
AND T.DateTime < MyWorkingTable.DateTime)
Roy
On 15 Feb 2006 10:40:16 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>This is pretty basic stuff but I forget how to do this with one update.
>I want to create a key for a table. I have two fields:
>TableName: MyWorkingTable
>PlayerID (int)
>Time (DateTime)
>My_New_ID (int)
>PlayerIDs might be duplicated. But a combination of PlayerID and Time
>will always be unique. To begin with, "My_New_ID" values will all be
>NULL.
>I want to grab an ID value from another table (the max value of all
>the IDs from final staging table) and start assigning 'My_New_ID's in
>'MyWorkingTable' starting with a number one higher that result. So if
>that value is 230, then I start assigning My_New_IDs with 231 and on up
>incrementing by one until I run out of records. Then I write all these
>records to the final staging table.
>How do I do this?
>Thanks,
>Kayda