Help determining an end date

Help determining an end date

Post by Matt Este » Thu, 26 Jul 2001 00:54:26



I have a start_date(datetime) and a length(numeric(8,2)) of time in days for
courses that I need to find an end_date(datetime) for.  The length column
can have decimals and if it does I need the end_date column to round up and
show the full day (3.1 would show as 4 days).  Some of these courses last
for 90 days or more and I need the end_date generated but I have no idea how
to exclude weekends.

for example:

start_date = 07/09/2001       length = 10

I need the end_date to be = 07/20/2001

Thanks,
Matt

 
 
 

Help determining an end date

Post by BP Margoli » Thu, 26 Jul 2001 03:00:42


Matt,




-- option #1

-- option #2

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I have a start_date(datetime) and a length(numeric(8,2)) of time in days
for
> courses that I need to find an end_date(datetime) for.  The length column
> can have decimals and if it does I need the end_date column to round up
and
> show the full day (3.1 would show as 4 days).  Some of these courses last
> for 90 days or more and I need the end_date generated but I have no idea
how
> to exclude weekends.

> for example:

> start_date = 07/09/2001       length = 10

> I need the end_date to be = 07/20/2001

> Thanks,
> Matt


 
 
 

Help determining an end date

Post by Matt » Thu, 26 Jul 2001 04:06:06


This works great when the date and length are set like below, but how do I
get it to pull from existing data in the columns?  Here is what I am trying
to get it to do.  I am getting errors saying that "The column prefix
'tbltrnrec' does not match with a table name or alias name used in the
query."

use tr_db




Thanks.


> Matt,




> -- option #1

> -- option #2

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I have a start_date(datetime) and a length(numeric(8,2)) of time in days
> for
> > courses that I need to find an end_date(datetime) for.  The length
column
> > can have decimals and if it does I need the end_date column to round up
> and
> > show the full day (3.1 would show as 4 days).  Some of these courses
last
> > for 90 days or more and I need the end_date generated but I have no idea
> how
> > to exclude weekends.

> > for example:

> > start_date = 07/09/2001       length = 10

> > I need the end_date to be = 07/20/2001

> > Thanks,
> > Matt

 
 
 

Help determining an end date

Post by BP Margoli » Thu, 26 Jul 2001 08:18:59


Matt,

Sorry that the code wasn't self-explanatory. However when requesting
assistance with SQL, it is best to post (simplified) table schemas (CREATE
TABLEs), sample data (INSERTs), and the results you want using the sample
data. If you look at other posts on this newsgroup, it should give you a
better idea of exactly what is being requested.

If you post the above, then I can customize the query to your particular
application. Without the above, you are asking me to take a guess at what
your SQL skills are and what I need to post so that it makes sense to you. I
tried this once and failed. Rather than having a series of posts, if you
post the information requested above, I, or someone else, can provide
exactly what you need in a timely manner.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> This works great when the date and length are set like below, but how do I
> get it to pull from existing data in the columns?  Here is what I am
trying
> to get it to do.  I am getting errors saying that "The column prefix
> 'tbltrnrec' does not match with a table name or alias name used in the
> query."

> use tr_db




> Thanks.



> > Matt,




> > -- option #1

> > -- option #2

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I have a start_date(datetime) and a length(numeric(8,2)) of time in
days
> > for
> > > courses that I need to find an end_date(datetime) for.  The length
> column
> > > can have decimals and if it does I need the end_date column to round
up
> > and
> > > show the full day (3.1 would show as 4 days).  Some of these courses
> last
> > > for 90 days or more and I need the end_date generated but I have no
idea
> > how
> > > to exclude weekends.

> > > for example:

> > > start_date = 07/09/2001       length = 10

> > > I need the end_date to be = 07/20/2001

> > > Thanks,
> > > Matt

 
 
 

Help determining an end date

Post by Matt » Fri, 27 Jul 2001 01:01:43


Sorry about that BP.  Hopefully this will help.  I'm trying to create a
trigger so that when the user inputs the StartDate and Length, the EndDate
is generated for them.  I also need to create the EndDate for about 600
records that already exist in the DB, but that I can do from the trigger
code.  The problem is that some of the courses last for several weeks and I
want the EndDate to only take into account weekdays.  I have added some
insert data below the table setup, but have only included the StartDate and
Length insert data.  Please let me know if you need more.

CREATE TABLE [dbo].[tblTrnRec] (
 [TRID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
 [INumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [CourseID] [int] NULL ,
 [StartDate] [datetime] NULL ,
 [EndDate] [datetime] NULL ,
 [Length] [numeric](8, 2) NULL ,
 [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [TypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Instr] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [CourseDesc] [nchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL
) ON [PRIMARY]
GO

Insert Into tblTrnRec (StartDate,Length)
Values ('07/02/2001', '20')

Once this is entered, I want the trigger to generate the EndDate and it
should be:  07/27/2001

Thanks again,
Matt


> Matt,

> Sorry that the code wasn't self-explanatory. However when requesting
> assistance with SQL, it is best to post (simplified) table schemas (CREATE
> TABLEs), sample data (INSERTs), and the results you want using the sample
> data. If you look at other posts on this newsgroup, it should give you a
> better idea of exactly what is being requested.

> If you post the above, then I can customize the query to your particular
> application. Without the above, you are asking me to take a guess at what
> your SQL skills are and what I need to post so that it makes sense to you.
I
> tried this once and failed. Rather than having a series of posts, if you
> post the information requested above, I, or someone else, can provide
> exactly what you need in a timely manner.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > This works great when the date and length are set like below, but how do
I
> > get it to pull from existing data in the columns?  Here is what I am
> trying
> > to get it to do.  I am getting errors saying that "The column prefix
> > 'tbltrnrec' does not match with a table name or alias name used in the
> > query."

> > use tr_db




> > Thanks.



> > > Matt,




> > > -- option #1

> > > -- option #2

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > I have a start_date(datetime) and a length(numeric(8,2)) of time in
> days
> > > for
> > > > courses that I need to find an end_date(datetime) for.  The length
> > column
> > > > can have decimals and if it does I need the end_date column to round
> up
> > > and
> > > > show the full day (3.1 would show as 4 days).  Some of these courses
> > last
> > > > for 90 days or more and I need the end_date generated but I have no
> idea
> > > how
> > > > to exclude weekends.

> > > > for example:

> > > > start_date = 07/09/2001       length = 10

> > > > I need the end_date to be = 07/20/2001

> > > > Thanks,
> > > > Matt

 
 
 

Help determining an end date

Post by oj » Fri, 27 Jul 2001 15:20:50


matt,

try this...


> Sorry about that BP.  Hopefully this will help.  I'm trying to create a
> trigger so that when the user inputs the StartDate and Length, the EndDate
> is generated for them.  I also need to create the EndDate for about 600
> records that already exist in the DB, but that I can do from the trigger
> code.  The problem is that some of the courses last for several weeks and
I
> want the EndDate to only take into account weekdays.  I have added some
> insert data below the table setup, but have only included the StartDate
and
> Length insert data.  Please let me know if you need more.

> CREATE TABLE [dbo].[tblTrnRec] (
>  [TRID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
>  [INumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [CourseID] [int] NULL ,
>  [StartDate] [datetime] NULL ,
>  [EndDate] [datetime] NULL ,
>  [Length] [numeric](8, 2) NULL ,
>  [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [TypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Instr] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [CourseDesc] [nchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL
> ) ON [PRIMARY]
> GO

> Insert Into tblTrnRec (StartDate,Length)
> Values ('07/02/2001', '20')

> Once this is entered, I want the trigger to generate the EndDate and it
> should be:  07/27/2001

> Thanks again,
> Matt



> > Matt,

> > Sorry that the code wasn't self-explanatory. However when requesting
> > assistance with SQL, it is best to post (simplified) table schemas
(CREATE
> > TABLEs), sample data (INSERTs), and the results you want using the
sample
> > data. If you look at other posts on this newsgroup, it should give you a
> > better idea of exactly what is being requested.

> > If you post the above, then I can customize the query to your particular
> > application. Without the above, you are asking me to take a guess at
what
> > your SQL skills are and what I need to post so that it makes sense to
you.
> I
> > tried this once and failed. Rather than having a series of posts, if you
> > post the information requested above, I, or someone else, can provide
> > exactly what you need in a timely manner.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > This works great when the date and length are set like below, but how
do
> I
> > > get it to pull from existing data in the columns?  Here is what I am
> > trying
> > > to get it to do.  I am getting errors saying that "The column prefix
> > > 'tbltrnrec' does not match with a table name or alias name used in the
> > > query."

> > > use tr_db




> > > Thanks.



> > > > Matt,




> > > > -- option #1

> > > > -- option #2

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > I have a start_date(datetime) and a length(numeric(8,2)) of time
in
> > days
> > > > for
> > > > > courses that I need to find an end_date(datetime) for.  The length
> > > column
> > > > > can have decimals and if it does I need the end_date column to
round
> > up
> > > > and
> > > > > show the full day (3.1 would show as 4 days).  Some of these
courses
> > > last
> > > > > for 90 days or more and I need the end_date generated but I have
no
> > idea
> > > > how
> > > > > to exclude weekends.

> > > > > for example:

> > > > > start_date = 07/09/2001       length = 10

> > > > > I need the end_date to be = 07/20/2001

> > > > > Thanks,
> > > > > Matt

 
 
 

Help determining an end date

Post by oj » Fri, 27 Jul 2001 15:21:31


oops...hit enter before finish

create trigger tr_insert_tbl on tblTrnRec
for insert
as
begin
 update tblTrnRec
 set enddate=dateadd(dd, ceiling(i.length), i.startdate)
 from tblTrnRec t, inserted i
 where t.trid=i.trid
end
go

-oj


> matt,

> try this...



> > Sorry about that BP.  Hopefully this will help.  I'm trying to create a
> > trigger so that when the user inputs the StartDate and Length, the
EndDate
> > is generated for them.  I also need to create the EndDate for about 600
> > records that already exist in the DB, but that I can do from the trigger
> > code.  The problem is that some of the courses last for several weeks
and
> I
> > want the EndDate to only take into account weekdays.  I have added some
> > insert data below the table setup, but have only included the StartDate
> and
> > Length insert data.  Please let me know if you need more.

> > CREATE TABLE [dbo].[tblTrnRec] (
> >  [TRID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
> >  [INumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >  [CourseID] [int] NULL ,
> >  [StartDate] [datetime] NULL ,
> >  [EndDate] [datetime] NULL ,
> >  [Length] [numeric](8, 2) NULL ,
> >  [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >  [TypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >  [Instr] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >  [CourseDesc] [nchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >  [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL
> > ) ON [PRIMARY]
> > GO

> > Insert Into tblTrnRec (StartDate,Length)
> > Values ('07/02/2001', '20')

> > Once this is entered, I want the trigger to generate the EndDate and it
> > should be:  07/27/2001

> > Thanks again,
> > Matt



> > > Matt,

> > > Sorry that the code wasn't self-explanatory. However when requesting
> > > assistance with SQL, it is best to post (simplified) table schemas
> (CREATE
> > > TABLEs), sample data (INSERTs), and the results you want using the
> sample
> > > data. If you look at other posts on this newsgroup, it should give you
a
> > > better idea of exactly what is being requested.

> > > If you post the above, then I can customize the query to your
particular
> > > application. Without the above, you are asking me to take a guess at
> what
> > > your SQL skills are and what I need to post so that it makes sense to
> you.
> > I
> > > tried this once and failed. Rather than having a series of posts, if
you
> > > post the information requested above, I, or someone else, can provide
> > > exactly what you need in a timely manner.

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > This works great when the date and length are set like below, but
how
> do
> > I
> > > > get it to pull from existing data in the columns?  Here is what I am
> > > trying
> > > > to get it to do.  I am getting errors saying that "The column prefix
> > > > 'tbltrnrec' does not match with a table name or alias name used in
the
> > > > query."

> > > > use tr_db




> > > > Thanks.



> > > > > Matt,




> > > > > -- option #1

> > > > > -- option #2

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
etc.)
> > > which
> > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > I have a start_date(datetime) and a length(numeric(8,2)) of time
> in
> > > days
> > > > > for
> > > > > > courses that I need to find an end_date(datetime) for.  The
length
> > > > column
> > > > > > can have decimals and if it does I need the end_date column to
> round
> > > up
> > > > > and
> > > > > > show the full day (3.1 would show as 4 days).  Some of these
> courses
> > > > last
> > > > > > for 90 days or more and I need the end_date generated but I have
> no
> > > idea
> > > > > how
> > > > > > to exclude weekends.

> > > > > > for example:

> > > > > > start_date = 07/09/2001       length = 10

> > > > > > I need the end_date to be = 07/20/2001

> > > > > > Thanks,
> > > > > > Matt

 
 
 

Help determining an end date

Post by Matt » Sat, 28 Jul 2001 02:23:16


Thanks for the help OJ, but I still have a problem with it counting the
weekends.  I'll give you an example of an insert and what I actually need
below.

ex.
Insert into tbltrnrec (StartDate, Length)
Values ('05/14/2001', '40')
    This gives me an EndDate of: 06/23/2001
The actual EndDate was 07/06/2001.

Is it even possible to do this?

Thanks,
Matt


> oops...hit enter before finish

> create trigger tr_insert_tbl on tblTrnRec
> for insert
> as
> begin
>  update tblTrnRec
>  set enddate=dateadd(dd, ceiling(i.length), i.startdate)
>  from tblTrnRec t, inserted i
>  where t.trid=i.trid
> end
> go

> -oj


> > matt,

> > try this...



> > > Sorry about that BP.  Hopefully this will help.  I'm trying to create
a
> > > trigger so that when the user inputs the StartDate and Length, the
> EndDate
> > > is generated for them.  I also need to create the EndDate for about
600
> > > records that already exist in the DB, but that I can do from the
trigger
> > > code.  The problem is that some of the courses last for several weeks
> and
> > I
> > > want the EndDate to only take into account weekdays.  I have added
some
> > > insert data below the table setup, but have only included the
StartDate
> > and
> > > Length insert data.  Please let me know if you need more.

> > > CREATE TABLE [dbo].[tblTrnRec] (
> > >  [TRID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
> > >  [INumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >  [CourseID] [int] NULL ,
> > >  [StartDate] [datetime] NULL ,
> > >  [EndDate] [datetime] NULL ,
> > >  [Length] [numeric](8, 2) NULL ,
> > >  [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > >  [TypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >  [Instr] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >  [CourseDesc] [nchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > >  [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL
> > > ) ON [PRIMARY]
> > > GO

> > > Insert Into tblTrnRec (StartDate,Length)
> > > Values ('07/02/2001', '20')

> > > Once this is entered, I want the trigger to generate the EndDate and
it
> > > should be:  07/27/2001

> > > Thanks again,
> > > Matt



> > > > Matt,

> > > > Sorry that the code wasn't self-explanatory. However when requesting
> > > > assistance with SQL, it is best to post (simplified) table schemas
> > (CREATE
> > > > TABLEs), sample data (INSERTs), and the results you want using the
> > sample
> > > > data. If you look at other posts on this newsgroup, it should give
you
> a
> > > > better idea of exactly what is being requested.

> > > > If you post the above, then I can customize the query to your
> particular
> > > > application. Without the above, you are asking me to take a guess at
> > what
> > > > your SQL skills are and what I need to post so that it makes sense
to
> > you.
> > > I
> > > > tried this once and failed. Rather than having a series of posts, if
> you
> > > > post the information requested above, I, or someone else, can
provide
> > > > exactly what you need in a timely manner.

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > This works great when the date and length are set like below, but
> how
> > do
> > > I
> > > > > get it to pull from existing data in the columns?  Here is what I
am
> > > > trying
> > > > > to get it to do.  I am getting errors saying that "The column
prefix
> > > > > 'tbltrnrec' does not match with a table name or alias name used in
> the
> > > > > query."

> > > > > use tr_db




> > > > > Thanks.



> > > > > > Matt,




> > > > > > -- option #1

> > > > > > -- option #2

> > > > > > -------------------------------------------
> > > > > > BP Margolin
> > > > > > Please reply only to the newsgroups.
> > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
> etc.)
> > > > which
> > > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > > I have a start_date(datetime) and a length(numeric(8,2)) of
time
> > in
> > > > days
> > > > > > for
> > > > > > > courses that I need to find an end_date(datetime) for.  The
> length
> > > > > column
> > > > > > > can have decimals and if it does I need the end_date column to
> > round
> > > > up
> > > > > > and
> > > > > > > show the full day (3.1 would show as 4 days).  Some of these
> > courses
> > > > > last
> > > > > > > for 90 days or more and I need the end_date generated but I
have
> > no
> > > > idea
> > > > > > how
> > > > > > > to exclude weekends.

> > > > > > > for example:

> > > > > > > start_date = 07/09/2001       length = 10

> > > > > > > I need the end_date to be = 07/20/2001

> > > > > > > Thanks,
> > > > > > > Matt

 
 
 

Help determining an end date

Post by Matt » Sun, 29 Jul 2001 00:10:13


OJ,

You are the man! That worked like a charm. Thanks for sticking with it and
helping me out.

Thanks again,

Matt

here you go, matt...

create trigger tr_insert_tbl on tblTrnRec

for insert

as

begin

update tblTrnRec

set

enddate=dateadd(dd,convert(int,i.length-1)%5,dateadd(wk,convert(int,i.length

-1)/5,i.startdate))

from tblTrnRec t, inserted i

where t.trid=i.trid

end

hth.

-oj


> oops...hit enter before finish

> create trigger tr_insert_tbl on tblTrnRec
> for insert
> as
> begin
>  update tblTrnRec
>  set enddate=dateadd(dd, ceiling(i.length), i.startdate)
>  from tblTrnRec t, inserted i
>  where t.trid=i.trid
> end
> go

> -oj


> > matt,

> > try this...



> > > Sorry about that BP.  Hopefully this will help.  I'm trying to create
a
> > > trigger so that when the user inputs the StartDate and Length, the
> EndDate
> > > is generated for them.  I also need to create the EndDate for about
600
> > > records that already exist in the DB, but that I can do from the
trigger
> > > code.  The problem is that some of the courses last for several weeks
> and
> > I
> > > want the EndDate to only take into account weekdays.  I have added
some
> > > insert data below the table setup, but have only included the
StartDate
> > and
> > > Length insert data.  Please let me know if you need more.

> > > CREATE TABLE [dbo].[tblTrnRec] (
> > >  [TRID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
> > >  [INumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >  [CourseID] [int] NULL ,
> > >  [StartDate] [datetime] NULL ,
> > >  [EndDate] [datetime] NULL ,
> > >  [Length] [numeric](8, 2) NULL ,
> > >  [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > >  [TypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >  [Instr] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >  [CourseDesc] [nchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > >  [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL
> > > ) ON [PRIMARY]
> > > GO

> > > Insert Into tblTrnRec (StartDate,Length)
> > > Values ('07/02/2001', '20')

> > > Once this is entered, I want the trigger to generate the EndDate and
it
> > > should be:  07/27/2001

> > > Thanks again,
> > > Matt



> > > > Matt,

> > > > Sorry that the code wasn't self-explanatory. However when requesting
> > > > assistance with SQL, it is best to post (simplified) table schemas
> > (CREATE
> > > > TABLEs), sample data (INSERTs), and the results you want using the
> > sample
> > > > data. If you look at other posts on this newsgroup, it should give
you
> a
> > > > better idea of exactly what is being requested.

> > > > If you post the above, then I can customize the query to your
> particular
> > > > application. Without the above, you are asking me to take a guess at
> > what
> > > > your SQL skills are and what I need to post so that it makes sense
to
> > you.
> > > I
> > > > tried this once and failed. Rather than having a series of posts, if
> you
> > > > post the information requested above, I, or someone else, can
provide
> > > > exactly what you need in a timely manner.

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > This works great when the date and length are set like below, but
> how
> > do
> > > I
> > > > > get it to pull from existing data in the columns?  Here is what I
am
> > > > trying
> > > > > to get it to do.  I am getting errors saying that "The column
prefix
> > > > > 'tbltrnrec' does not match with a table name or alias name used in
> the
> > > > > query."

> > > > > use tr_db




> > > > > Thanks.



> > > > > > Matt,




> > > > > > -- option #1

> > > > > > -- option #2

> > > > > > -------------------------------------------
> > > > > > BP Margolin
> > > > > > Please reply only to the newsgroups.
> > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
> etc.)
> > > > which
> > > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > > I have a start_date(datetime) and a length(numeric(8,2)) of
time
> > in
> > > > days
> > > > > > for
> > > > > > > courses that I need to find an end_date(datetime) for.  The
> length
> > > > > column
> > > > > > > can have decimals and if it does I need the end_date column to
> > round
> > > > up
> > > > > > and
> > > > > > > show the full day (3.1 would show as 4 days).  Some of these
> > courses
> > > > > last
> > > > > > > for 90 days or more and I need the end_date generated but I
have
> > no
> > > > idea
> > > > > > how
> > > > > > > to exclude weekends.

> > > > > > > for example:

> > > > > > > start_date = 07/09/2001       length = 10

> > > > > > > I need the end_date to be = 07/20/2001

> > > > > > > Thanks,
> > > > > > > Matt

 
 
 

Help determining an end date

Post by oj » Sun, 29 Jul 2001 02:43:50


ya welcome.

-oj

"Matt" <matthew.es...@sap.com> wrote in message

news:9js08m$9ou$1@news1.wdf.sap-ag.de...
> OJ,

> You are the man! That worked like a charm. Thanks for sticking with it and
> helping me out.

> Thanks again,

> Matt

> here you go, matt...

> create trigger tr_insert_tbl on tblTrnRec

> for insert

> as

> begin

> update tblTrnRec

> set

enddate=dateadd(dd,convert(int,i.length-1)%5,dateadd(wk,convert(int,i.length

- Show quoted text -

> -1)/5,i.startdate))

> from tblTrnRec t, inserted i

> where t.trid=i.trid

> end

> hth.

> -oj

> "oj" <nospam.oj...@home.com> wrote in message
> news:uGl6KsZFBHA.1480@tkmsftngp05...
> > oops...hit enter before finish

> > create trigger tr_insert_tbl on tblTrnRec
> > for insert
> > as
> > begin
> >  update tblTrnRec
> >  set enddate=dateadd(dd, ceiling(i.length), i.startdate)
> >  from tblTrnRec t, inserted i
> >  where t.trid=i.trid
> > end
> > go

> > -oj

> > "oj" <nospam.oj...@home.com> wrote in message news:...
> > > matt,

> > > try this...

> > > "Matt" <matthew.es...@sap.com> wrote in message
> > > news:9jmqh9$7dk$1@news1.wdf.sap-ag.de...
> > > > Sorry about that BP.  Hopefully this will help.  I'm trying to
create
> a
> > > > trigger so that when the user inputs the StartDate and Length, the
> > EndDate
> > > > is generated for them.  I also need to create the EndDate for about
> 600
> > > > records that already exist in the DB, but that I can do from the
> trigger
> > > > code.  The problem is that some of the courses last for several
weeks
> > and
> > > I
> > > > want the EndDate to only take into account weekdays.  I have added
> some
> > > > insert data below the table setup, but have only included the
> StartDate
> > > and
> > > > Length insert data.  Please let me know if you need more.

> > > > CREATE TABLE [dbo].[tblTrnRec] (
> > > >  [TRID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
> > > >  [INumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > >  [CourseID] [int] NULL ,
> > > >  [StartDate] [datetime] NULL ,
> > > >  [EndDate] [datetime] NULL ,
> > > >  [Length] [numeric](8, 2) NULL ,
> > > >  [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> > > >  [TypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > >  [Instr] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > >  [CourseDesc] [nchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> > > >  [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL
> > > > ) ON [PRIMARY]
> > > > GO

> > > > Insert Into tblTrnRec (StartDate,Length)
> > > > Values ('07/02/2001', '20')

> > > > Once this is entered, I want the trigger to generate the EndDate and
> it
> > > > should be:  07/27/2001

> > > > Thanks again,
> > > > Matt

> > > > "BP Margolin" <bpma...@attglobal.net> wrote in message
> > > > news:#dSXXZJFBHA.1604@tkmsftngp05...
> > > > > Matt,

> > > > > Sorry that the code wasn't self-explanatory. However when
requesting
> > > > > assistance with SQL, it is best to post (simplified) table schemas
> > > (CREATE
> > > > > TABLEs), sample data (INSERTs), and the results you want using the
> > > sample
> > > > > data. If you look at other posts on this newsgroup, it should give
> you
> > a
> > > > > better idea of exactly what is being requested.

> > > > > If you post the above, then I can customize the query to your
> > particular
> > > > > application. Without the above, you are asking me to take a guess
at
> > > what
> > > > > your SQL skills are and what I need to post so that it makes sense
> to
> > > you.
> > > > I
> > > > > tried this once and failed. Rather than having a series of posts,
if
> > you
> > > > > post the information requested above, I, or someone else, can
> provide
> > > > > exactly what you need in a timely manner.

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
etc.)
> > > which
> > > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > "Matt" <matthew.es...@sap.com> wrote in message
> > > > > news:9jkguv$plc$1@news1.wdf.sap-ag.de...
> > > > > > This works great when the date and length are set like below,
but
> > how
> > > do
> > > > I
> > > > > > get it to pull from existing data in the columns?  Here is what
I
> am
> > > > > trying
> > > > > > to get it to do.  I am getting errors saying that "The column
> prefix
> > > > > > 'tbltrnrec' does not match with a table name or alias name used
in
> > the
> > > > > > query."

> > > > > > use tr_db
> > > > > > declare @start_date datetime, @length decimal (8,2)
> > > > > > select @start_date = tbltrnrec.start_date,
> > > > > >             @length = tbltrnrec.length

> > > > > > select dateadd(dd, ceiling(@length), @start_date)

> > > > > > Thanks.

> > > > > > "BP Margolin" <bpma...@attglobal.net> wrote in message
> > > > > > news:OXZLhnGFBHA.1340@tkmsftngp07...
> > > > > > > Matt,

> > > > > > > declare @start_date datetime, @length decimal (8,2)
> > > > > > > select @start_date = '07/09/2001',
> > > > > > >             @length = 10.6

> > > > > > > -- option #1
> > > > > > > select dateadd(dd, ceiling(@length), @start_date)

> > > > > > > -- option #2
> > > > > > > select @start_date + ceiling(@length)

> > > > > > > -------------------------------------------
> > > > > > > BP Margolin
> > > > > > > Please reply only to the newsgroups.
> > > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
> > etc.)
> > > > > which
> > > > > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > > > "Matt Estes" <matthew.es...@sap.com> wrote in message
> > > > > > > news:9jk5nj$k2o$1@news1.wdf.sap-ag.de...
> > > > > > > > I have a start_date(datetime) and a length(numeric(8,2)) of
> time
> > > in
> > > > > days
> > > > > > > for
> > > > > > > > courses that I need to find an end_date(datetime) for.  The
> > length
> > > > > > column
> > > > > > > > can have decimals and if it does I need the end_date column
to
> > > round
> > > > > up
> > > > > > > and
> > > > > > > > show the full day (3.1 would show as 4 days).  Some of these
> > > courses
> > > > > > last
> > > > > > > > for 90 days or more and I need the end_date generated but I
> have
> > > no
> > > > > idea
> > > > > > > how
> > > > > > > > to exclude weekends.

> > > > > > > > for example:

> > > > > > > > start_date = 07/09/2001       length = 10

> > > > > > > > I need the end_date to be = 07/20/2001

> > > > > > > > Thanks,
> > > > > > > > Matt

 
 
 

1. ((Current Date - Start Date)/(End Date - Start Date))*Contract Amt

We have a cube consisting of a fact table with a measure of Contract Amt ($)
plus some other accounting numbers related to projects and a couple of
dimensions (actually more but that should be enough information for this
question) - Time and Projects with a project number as a key.

We are trying to create the following calculated member:

((Current Date - Start Date)/(End Date - Start Date))*Contract Amt

Where

  a.. Current Date is a member of Time dimension (either default or
explicitly created)
  b.. Start and End Dates are properties of a project number (should they be
defined as custom properties or some other way?)

What is the correct syntax for this calculation?

Can it be a member of measures dimension?

Thanks much for any helpJ

Alex Pitko

2. Create a new DSN in VB ?

3. Calculate Start Date from End Date Help

4. Creating temporary tables that only exists during the query.

5. Help on How to remove overllaping records base on a begin and end date

6. Rushmore

7. End Date Calculation Help

8. How do I get the start date and end date of week #35

9. Between 2 dates not returning data for the end date

10. Multiple Dates by choosing Start and End Date

11. Calculate difference between start date and time and end date and time

12. How do you determine the end of file.