Query with MAX Date

I have the following situation:
The name of a product can change with time. These changes are stored in a
table with 3 columns: Product_id, Date and ProductName with Product_id and
Date forming the Primary Key.
I want to run a query that returns the product_id and each product's latest
name. For the life of me, I can't get my head rould what such a query would
look like. I can get a query that gives me just the product_id and the Max
of Date with a group by on the Product_id but when I introduce the
ProductName, it returns ALL names.
I have a gut feeling that there may be a subquery involved, or am I barking
up the wrong tree?
Any help much appreciated.
PeteYou can either use a subquery:
SELECT Product_id, ProductName
FROM Your_table t1
WHERE Date = (SELECT MAX(Date) FROM Your_table t2 WHERE t1.Product_id =
t2.Product_id)
or a derived table:
SELECT t1.Product_id, t1.ProductName
FROM Your_table t1
INNER JOIN
(SELECT Product_id, MAX(Date) AS max_date
FROM Your_table
GROUP BY Product_id) t2
ON t1.Product_id = t2.Product_id
AND t1.Date = t2.max_date
Jacco Schalkwijk
SQL Server MVP
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
>I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
> latest
> name. For the life of me, I can't get my head rould what such a query
> would
> look like. I can get a query that gives me just the product_id and the
> Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
> barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||First, please change your column name 'Date' to something more meaningful
and something that doesn't use a reserved word. Also, you should be
consistent in your column naming. Why does Product_id have an underscore,
but ProductName not? Finally, in the future, please post DDL, sample data,
and desired results. See http://www.aspfaq.com/5006
In the meantime, you can try this:
SELECT o.Product_id, i.MaxDate, o.ProductName
FROM Products o
INNER JOIN
(
SELECT Product_id, MaxDate = MAX([Date])
FROM Products
GROUP BY Product_id
) i
ON o.Product_id = i.Product_id
AND o.[Date] = i.MaxDate
http://www.aspfaq.com/
(Reverse address to reply.)
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
> I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
latest
> name. For the life of me, I can't get my head rould what such a query
would
> look like. I can get a query that gives me just the product_id and the
Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||Here is a solution based on guesswork:
SELECT t1.product_id, t1.product_name
FROM tbl t1
WHERE ( SELECT MAX( t2.dtcol )
FROM tbl t2
WHERE t2.product_id = t1.product_id ) = t1.dtcol ;
If this is not what you are looking for, refer to www.aspfaq.com/5006 and
provide required information.
Anith

Query with date question

I need to return all records in which the date 'lastmodified' +
'responsible' which is a number (of days) is the same as today's date.
I have the following condition in my sql but even though it should return
some records it doesnt:
WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
getdate())
Activities.responsible = Its the number of days being added
Activities.lasmodified = Its a date say (01/01/2005)
If the SUM of both is todays date the recordset should be returned, I don't
know if this has to do with the fact that seconds and minutes might be
involved ?
Any help is appreciated.
AleksThis should do it:
WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
AND GETDATE()
... Unfortunately, I believe this will force a table or index scan; I'm not
sure how to get around it given your current schema. If you can, instead of
keeping the number of days, keep the "end" date. Then you'll be able to
write a query that's capable of using an index.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Much more efficient query (assuming an index on LastModified):
DECLARE @.d SMALLDATETIME
SET @.d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= (@.d - a.responsible)
AND a.LastModified < (@.d +1 - a.responsible)
You always want the column with the index on its own on one side of the
equation. This will allow for an index s instead of a scan. And yes,
this is because LastModified has minutes and seconds, presumably, and
GETDATE() certainly does. The DATEADD/DATEDIFF trick I did up there
converted it to a time of midnight, which makes it easy to find date values
anywhere >= that day and < that day + 1. The query also takes advantage of
implicit integer math with datetime/smalldatetime values, but the purists
might want this instead:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
http://www.aspfaq.com/
(Reverse address to reply.)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Did not work
The sum of lastworked and responsible should be today's date. I tried that
sql and returned nothing.
Aleks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||This seems to work though.
(Activities.LastModified+responsible) between (GETDATE()-1) and
(getdate())
Would that be alright ?
A
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||"Aleks" <arkark2004@.hotmail.com> wrote in message
news:egjIvv2IFHA.3568@.TK2MSFTNGP10.phx.gbl...
> This seems to work though.
> (Activities.LastModified+responsible) between (GETDATE()-1) and
> (getdate())
> Would that be alright ?
Personally -- if you can't change the schema -- I would go with Aaron's
second query:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Alek,
To explain your problem, You must understand theat getDate() function
returns date and Time, so your query will only return records where the
expression
DATEADD(day,Activities.responsible , Activities.Lastmodified )
is EXACTLY equal to the current date AND time Value, It is very unlikely
that any records will satisfy that...
The only way to do this is to write your predicate as a between, (or as a >=
AND < ), where all records are returned where the value in the dataabase
table is between 2 calculated BOUNDARY values, based on the current date and
time...
so you would either have
Activities.Lastmodified Between @.LowDate And @.HighDate, or
Activities.Lastmodified > @.LowDate And Activities.Lastmodified <= @.HighDate
(NOTE: BETWEEN implies >= AND <= )
Two questions need to be answered, to determine what those
1) When you say "= getdate()" What, exactly do you mean? - do you want,
a) ALl the records that occurred on that specific Calendar DAY?, or
b) All the records that occurred within exactly 12/(24?) hours of a
specific Date and Time?
If it's the former (which I suspect is the case), than your Boundary dates
will be Midnight, in the am, on a specific calculated date :
DateAdd(day, - Responsible, getdate()), converted to strip off the time...
Convert(VarChar(8), DateAdd(day, - Responsible, getdate()), 112)
and the high date would be one day later, again at midnigjt...
Convert(VarChar(8), DateAdd(day, 1 - Responsible, getdate()), 112)
or.
Where Activities.Lastmodified >= Convert(VarChar(8), DateAdd(day, -
Responsible, getdate()), 112)
AND Activities.Lastmodified < Convert(VarChar(8), DateAdd(day, 1 -
Responsible, getdate()), 112)
You have to split off the time portion if you only want those records from a
specific calendar day...
cannot "Aleks" wrote:

> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day ,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I don'
t
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>
>

query w/ case help

Hey all, here is my problem: I want to run a query to grab all the 'next dates' from a column after the user inputs a date. .. and then repeat accordingly.

Here is some simplified example code: This works fine (but doesn't grab all rows, which varies, with the Date. It only grabs the first, hence the 'top 1')

select top 1 datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and purchord = '4112'
order by datewrk

Soo, then this grabs all the rows with the Date, but doesn't 'skip' correctly. If you pick the date right before a valid row, as in there are rows of data for Date 6/18/2004 and you pick 6/17/2004 it will bring up the next date fine. BUT if you pick 6/15/2004 it will not 'skip ahead'. Any ideas? Thanks

select datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and 1 = (case when Datewrk = (select min(Datewrk) from Hours where Datewrk is not null and Datewrk > '06/15/2004') then 1 else 0 end) and purchord = '4112'
order by datewrkDoes this work:SELECT datewrk
FROM hours
WHERE datewrk IS NOT NULL
AND datewrk > '06/15/2004'
AND purchord = '4112'
AND 1 = (case when Datewrk = (select min(Datewrk)
FROM Hours
WHERE Datewrk IS NOT NULL
AND purchord = '4112'
AND Datewrk > '06/15/2004') THEN 1 ELSE 0 END)
ORDER BY datewrk-PatP|||Yes, that works! THANK YOU.

Query using datetime datatype

hello,
so i have a table with column name Date with datetime as it's datatype. I'm
trying to run a select statement on it that will give me all rows where my
Date column has a datetime of 30 days or more. Can anyone help? I tried using
datediff but can't get it to work.
Thanks in advance!"FS" <FS@.discussions.microsoft.com> wrote in message
news:6A6874E0-38C5-4257-BA2A-4EEFF4D3A181@.microsoft.com...
> hello,
> so i have a table with column name Date with datetime as it's datatype.
> I'm
> trying to run a select statement on it that will give me all rows where my
> Date column has a datetime of 30 days or more. Can anyone help? I tried
> using
> datediff but can't get it to work.
> Thanks in advance!
WHERE dt <= DATEADD(DAY,-30,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

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 xml question

I have a table that contains 3 columns SiteID, Results and date. the table has 4 rows. I want to query the table and end up with 1 row that combines all the field into in the Results Column.

so in table form it looks like

627 test 3/3/7
627 bob 3/3/7
627 tom 3/9/7
627 rob 3/8/7

I want the resulting query to bring back one row:

test,bob,tom,rob

the following query will do 90% of what I want,

SELECT test +','
FROM #temp1
FORXMLPATH('')

BUT I cannot figure out how to provide a column name for the query result. instead I appear to get a guid of XML_F52E2B61-18A1-11d1-B105-00805F49916B

Q - is there a way to name the column, or is there a different way to create this query result without using XML?

I am us

Jim:

The best way is to make your select statement into a "derived table" or a correlated subquery; For example:

create table #temp1
( SiteID integer,
Results varchar(10),
date datetime
)
insert into #temp1 values (627, 'billy joe', '3/3/7')
insert into #temp1 values (627, 'bob', '3/3/7')
insert into #temp1 values (627, 'tom', '3/9/7')
insert into #temp1 values (627, 'rob', '3/8/7')

select distinct
siteId,
replace(replace(
( select replace (x.results, ' ', '~') as [data()]
from #temp1 x
where x.siteId = x.siteId
order by date
for xml path ('')
), ' ', ','), '~', ' ') as dataLabel
from #temp1 a

-- siteId dataLabel
-- --
-- 627 billy joe,bob,rob,tom

go

drop table #temp1
go

|||

selectcast((SELECT test +','FROM #temp1 FORXMLPATH(''))asvarchar(max))as YourName

|||I think I like Konstantin's better.|||

Thanks to both of you for the quick reply, they both work, but think I will use Konstantin's

|||

Actually I now have a different problem:

I am using

select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1

this does work, sort of.... But it creates the result for all records in the table. I need it to create a seperate record for each siteid, otherwise all the resulting data is the same for all siteids?

any ideas?

|||Just add filter to subquery:
select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 where siteid=t.siteid FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1 t
sql

Query to return multiple date rows

HI there,

Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:

1636 1073746475 342 2005-12-30 00:00:00.000
1636 1073746475 359 2006-03-10 00:00:00.000

This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?

Thanks!!!!!

Sselect security,
count(distinct datevalue) as datecount
from [yourtable]
group by security
having count(*) > 1|||You handle all the tough questions...time to shovel snow|||Thanks that's a great help, much appreciated!!!

S

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 the last access data/time

Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB
|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:

>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>
|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:

Query the last access data/time

Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||This is a multi-part message in MIME format.
--040107090904080005040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>
>
--040107090904080005040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Or alternately, you could create a profiler trace (with
sp_trace_create and the other trace procs) and set it to run when SQL
server starts (with sp_procoption). Then you could query the output of
that trace (if you wanted to query it with T-SQL you'd import the trace
output file (open it in Profiler and then SaveAs... a Trace Table...)
into a table and query that table). This is a little kludgey and
fairly expensive (the running profiler trace that is) in terms of
resources on the SQL box but it would work as long as you set up your
trace appropriately (it would take a bit of fine tuning).<br>
<br>
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.<br>
<br>
HTH.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<em><font face="Tahoma" size="2"> mallesons</font><font face="Tahoma"> </font><font
face="Tahoma" size="2">stephen</font><font face="Tahoma"> </font><font
face="Tahoma" size="2"> jaques</font></em><font face="Tahoma"><br>
</font><font face="Tahoma" size="2">blog:</font><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a></font></span> </p>
</div>
<br>
<br>
Hari Prasad wrote:
<blockquote cite="mid%23LLkY7teFHA.900@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:jbngspam@.yahoo.com"><jbngspam@.yahoo.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:42bf9544$0$18637$14726298@.news.sunsite.dk">news:42bf9544$0$18637$14726298@.news.sunsite.dk</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040107090904080005040400--|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>>Hi,
>>SQL Server will not store these information. But for new object creation you
>>can see the CRDATE column in sysobjects table.
>>But for Insert/update and delete you need to write trigger to populate a
>>audit table. Later you could use the audit table.
>>Thanks
>>Hari
>>SQL Server MVP
>>"John B" <jbngspam@.yahoo.com> wrote in message
>>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>>
>>

Query the last access data/time

Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:

>Hi,
>SQL Server will not store these information. But for new object creation yo
u
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>