QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:

> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >=
(SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...[vbcol=seagreen]
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project id.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project id.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >= (SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>> I am using the last updated date field to give me the dates I want, but I
>> want to narrow that search to give me only the greatest date per project
>> id.
>> Here is my query:
>> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
>> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
>> POReqHdr.CuryReqTotal, POReqHdr.User2,
>> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
>> FROM POReqHdr LEFT OUTER JOIN
>> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
>> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID =>> 'HE017')
>> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
>> I know I need to use "max(lupd_datetime)" somewhere but I am not sure
>> how.
>> The is in a sql report, by the way.
>>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
>> I am using the last updated date field to give me the dates I want, but I
>> want to narrow that search to give me only the greatest date per project
>> id. Here is my query:
>> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
>> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
>> POReqHdr.CuryReqTotal, POReqHdr.User2,
>> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
>> FROM POReqHdr LEFT OUTER JOIN
>> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
>> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID =>> 'HE017') AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
>> I know I need to use "max(lupd_datetime)" somewhere but I am not sure
>> how. The is in a sql report, by the way.
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Query to count holidays

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.I did something similar in a previous life. I created a master calendar table with columns for the date plus flag (bit) columns for weekends and holidays (and a third flag as I recall called working day). I then created a user-defined function which would take two dates as an input and return the number of "work" days elapsed between the two.

Perhaps not elegant, but it did work.

The master calendar table was also useful for reporting purposes. In your case, you might want a front-end interface to edit the calendar and identify which days are working versus non-working.

Regards,

hmscott

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.|||Only way since holidays are uniqu to countries...

works well though

Query timeout problems

Front End: Access 2000 Project (.adp)
Back End: SQL Server 2000

I have an application that keeps timing out. I have opened the
DataLink properties in the front end (File, Connection, Advanced tab)
and set the timeout to 999. But the connection still occasionally
times out.

Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
etc.)

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk"Edward" <teddysnips@.hotmail.com> wrote in message
news:25080b60.0501310730.3f8e6ed9@.posting.google.c om...
> Front End: Access 2000 Project (.adp)
> Back End: SQL Server 2000
> I have an application that keeps timing out. I have opened the
> DataLink properties in the front end (File, Connection, Advanced tab)
> and set the timeout to 999. But the connection still occasionally
> times out.
> Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
> etc.)
> TIA
> Edward
> --
> The reading group's reading group:
> http://www.bookgroup.org.uk

The timeout you changed is the connection timeout, so it won't affect
timeouts caused by long-running queries. Depending on what connection
library you're using (ODBC, ADO), you should be able to change the general
timeout value, but I have no idea how - perhaps an Access group might give
more help.

In ay event, if the timeouts occur during different queries, or the same
queries with different parameters, you'll probably have to try to identify
the problem queries more exactly - Profiler would be a good place to start.

Simon