Many to One with Max Date Query?

I have a master table that has all my accounts in it.
In a 2nd table I have update notes per each account, so there are
multiple notes entries for each single ID account in teh first table.

How can I get the OLDEST dated entry in teh second table, for every
account that's had a not entered into if by joining on the ID of the
first table?

This has been driving me nuts, does that make sense to you?

Here:

Table 1 FIELDS
ACCOUNTID

TABLE 2 FIELDS
NOTEID
ACCOUNTID (Foreign Key)
DATE_ENTERED

I need to join those two tables on the ACCOUNTID, but ONLY show the most
recent date for the record I pull from Table 2, since it's got multiple
entries in table 2, it's screwing my query up. Any help/ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SELECT T1.accountid, T2.noteid, T2.date_entered
FROM Table1 AS T1
JOIN Table2 AS T2
ON T1.accountid = T2.accountid
AND T2.date_entered =
(SELECT MIN(date_entered)
FROM Table2
WHERE accountid = T2.accountid)

--
David Portas
SQL Server MVP
--

Many thanks in advance! - Simple Date function - Please Help!

Hi All,

Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)

Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.

Many thanks in advance!!
Brad

--------------
declare @.today DateTime
Select @.today = GetDate()
print @.todaylook at the Convert function...something like...

select convert(varchar, getdate(), 6)

|||Heres a tutorial that I thought was helpful
http://www.easerve.com/developer/tutorials/asp-net-tutorials-dates.aspx

Manipulating dates in SQL

I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?

Start Date... This is the first day of the month, one year ago... in VB I worked it out as...

dateadd("yyyy",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

End Date... This is the last day of the previous month... in VB I worked this one out as...

dateadd("d",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

eg. for today 18/01/2007 I would get a Start date of 01/01/2006 and an End date of 31/12/2006

Any help would be appreciated.

I managed to work out a solution... I get my Start date by using the following...

(select dateadd(yyyy,-1,(select stuff(stuff((convert(varchar,
convert(varchar,datepart(year,getdate()))+
convert(varchar,datepart(Month,getdate()))+
convert(varchar,(convert(int,datepart(day,getdate())))- (convert(int,datepart(day,getdate()))-1))))
,5 ,0, '-'),7,0, '-'))))

|||

Hey Jon,

This should do what you want it to.

select dateadd(mm, datediff(mm, 0, dateadd(yy, -1, getdate())), 0) as StartDate,
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, getdate()), 0)) as EndDate

Hope this helps.

Jarret

|||

That's great... thanks Jarret.

A much better solution than mine... especially since I noticed mine only works if the month is a single digit.

Manipulating dates in an expression

Does anyone know a way to subtract a date from a date
parameter (e.g. = Parameters!Date.Value - 1, which of
course does not work)
thanksYou might want to read the MSDN documentation for the DateTime class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
E.g. subtracting one day would be =Parameters!Date.Value.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks|||Here is how I subtracted one date from another to get a number of days
between them:
=Fields!DESIRED_SHIP_DATE.Value.ToOADate() -
Fields!ORDER_DATE.Value.ToOADate()
This information below is not a direct solution for your problem, but the
information may help you with other things data/time related.
Here is how I get the current hour and manipate it:
The DateAndTime.Hour(Now) return a 24 hour clock output for the hour, so
this code turns hour 13 to 1 (like 1pm).
Dim cHour As Integer = DateAndTime.Hour(Now)
If cHour > 12 Then
StartHourBox.Text = cHour - 12
Else
StartHourBox.Text = cHour
End If
Look at all the different options available to you with "DateAndTime",
including Day, Month, and year.
Later,
Ed Hammond
--
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated
"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA
|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005
.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/200
5.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:
> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
> >Hi
> >
> >I have a field called paid to date and need to calculate the next date with
> >the same day of the month from getdate(). For example, paid to date of
> >13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> > However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> >
> >Any help in achieving this would be greatly appreciated
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulate Dates Need Help ASAP! (Please)

I have a date like 12/1/2004 12:00:00 AM. I need to subtract one month and
display the three character month name so I can concatenate it. It should
look like this:
Nov Act 2004
Please help. Thanks in advance.Try this expression:
=MonthName(Month(CDate(Fields!Date.Value).AddMonths(-1))) & " Act " &
Year(CDate(Fields!Date.Value).AddMonths(-1))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:62F306AB-6B30-4B14-916B-10492ACE4155@.microsoft.com...
>I have a date like 12/1/2004 12:00:00 AM. I need to subtract one month and
> display the three character month name so I can concatenate it. It should
> look like this:
> Nov Act 2004
> Please help. Thanks in advance.
>

Managing DATE ?

Hi,

I have a page that shows some problem indexes (cards ? I don't know the word :/ ) and I want to show only those that are from the current day.

I'm using SQL server 2000. I have a date field inside my table (datetime type). So I tried to put another condition in my WHERE clause. This is:

WHERE something = something else AND mydate = DATEPART('dd', getdate())

or

WHERE something = something else AND mydate = DAY(getdate())

Both don't work..

I wonder if I can really use this in a WHERE clause...of if I'm using them correctly.try:

WHERE mydate BETWEEN CONVERT(datetime,CONVERT(nvarchar(20),GetDate(),101)) AND
CONVERT(datetime,CONVERT(nvarchar(20),DATEADD(day,1,GetDate()),101))

This will get any date/time between midnight last night and midnight tonight.|||That's it, thanks a lot.

But if it is not too much, can I ask why do I have to convert the whole thing (I thought DATE functions could handle datetime type ?) and what does 101 stand for ?|||i'll let douglas answer your question, but I'd be more comfortable with..

" ... WHERE something = something else AND mydate BETWEEN @.StartDate AND @.EndDate"

commandobject.Parameters.Add("@.StartDate", SqlDbType.DateTime);
commandobject.Parameters.Add("@.EndDate", SqlDbType.DateTime);

commandobject.Parameters["@.StartDate"].Value = thestartdatevar;
commandobject.Parameters["@.EndDate"].Value = theenddatevar;|||I presumed that this particular bit of coding was all on the SQL Server box itself. The only reason for using the two dates is so that any entry <b>on a particular day</a> is selected. In the example, he wanted records for today. Thus, no parameters would need to be sent (unless this needs to be generalized).

101 means that the date is formatted mm/dd/yyyy. In thinking about it, 112 (yyyymmdd) would have been better. A DateTime type has both a date AND a time. You are looking to get just a particular date (today in your example).|||Ok.

But is there a way to write a DateTime (like 2004-01-01) and use it with the code you gave me. Because if I want to do an archive of all entries, I have to use specific date (something like clickable month).

I think I should use KraGie code, but how do I initialise DateTime variable ?

For instance, if I want thestartdatevar to be June 1st 2004 (for the moment, because I'll eventually pass the value through a DropDownList or something).

thestartdatevar = ?

PS: Is this C# (the ; ?) ? Because I don't know C#.