Last Date Used

I am trying to generate a report listing data base and tables within the
data base when last accessed, updated or modified. Does anyone know of
a tool that performs this function?
Ernie
A Plan without Action is a DayDream
Action without a Plan is a Nightmare
*** Sent via Developersdex http://www.codecomments.com ***
Hi,
SQL Server will not store this information. For doing this you need to
enable the profiler, store the output into a table and then do the
analysis manually to get the report.
Thanks
Hari
SQL Server MVP
"Ernie" <nospan@.devdex.com> wrote in message
news:OUTJ%23INUFHA.584@.TK2MSFTNGP15.phx.gbl...
> I am trying to generate a report listing data base and tables within the
> data base when last accessed, updated or modified. Does anyone know of
> a tool that performs this function?
> Ernie
> A Plan without Action is a DayDream
> Action without a Plan is a Nightmare
> *** Sent via Developersdex http://www.codecomments.com ***

Last 90 Days For a Variable

I triyng to generate a table with the last 90 days for a Part number, I have
a table with data for the last 7 years, and I need to complie the last 90
days of data for all the parts, but if I use the date as a criteria it will
only give the last 90 days, so I need to generate a query that will give all
the parts with only the last 90 days of data for all parts.
If you have any idea or suggestion are more than welcome
Thanks
Can you post DDL (CREATE TABLE statements), some sample data (INSERT
statements), and some sample output?
I'm confused by why using the date to get the last 90 days will not give you
all of the data for all of the parts over the last 90 days.
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> I triyng to generate a table with the last 90 days for a Part number, I
have
> a table with data for the last 7 years, and I need to complie the last 90
> days of data for all the parts, but if I use the date as a criteria it
will
> only give the last 90 days, so I need to generate a query that will give
all
> the parts with only the last 90 days of data for all parts.
> If you have any idea or suggestion are more than welcome
> Thanks
|||SELECT <columns> FROM <table>
WHERE <datetime_column> >= GETDATE()-90
If you want 90 days from midnight this AM:
SELECT <columns> FROM <table>
WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8), GETDATE(),
112))
If you want 90 days from midnight tomorrow AM:
SELECT <columns> FROM <table>
WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8),
GETDATE()+1, 112))
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> I triyng to generate a table with the last 90 days for a Part number, I
have
> a table with data for the last 7 years, and I need to complie the last 90
> days of data for all the parts, but if I use the date as a criteria it
will
> only give the last 90 days, so I need to generate a query that will give
all
> the parts with only the last 90 days of data for all parts.
> If you have any idea or suggestion are more than welcome
> Thanks
|||Aaron,
Some parts did not ran for the last 90 days, so I need to create a query
that will search the last 90 days for any part, here is an example assuming
the following results
Part Start Date End Date
ABC 1/4/04 7/3/04
CDB 11/1/03 5/4/04
DCB 5/4/04 8/8/04
So as you could see some parts ran in a differnts period of time and I need
to collect information for the latest 90 days for all parts regarding of the
date they ran.
Thanks
"Aaron [SQL Server MVP]" wrote:

> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= GETDATE()-90
> If you want 90 days from midnight this AM:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8), GETDATE(),
> 112))
> If you want 90 days from midnight tomorrow AM:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8),
> GETDATE()+1, 112))
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
> news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> have
> will
> all
>
>
|||Adam,
If I request the last 90 days, it'll only give those parts that ran in that
time interval, and I need the last 90 days for all the parts, regarding the
dates that they ran.
Thaks
"Adam Machanic" wrote:

> Can you post DDL (CREATE TABLE statements), some sample data (INSERT
> statements), and some sample output?
> I'm confused by why using the date to get the last 90 days will not give you
> all of the data for all of the parts over the last 90 days.
>
> "sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
> news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> have
> will
> all
>
>
|||Can you tell us which of these rows satisfy your requirements? Can you
please let us know if those dates are m/d/y or d/m/y?
Please see http://www.aspfaq.com/5006 for information on giving us usable
specifications to help solve your issue...
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:7D125639-DDFA-4836-A755-BC35E7969B78@.microsoft.com...
> Aaron,
> Some parts did not ran for the last 90 days, so I need to create a query
> that will search the last 90 days for any part, here is an example
assuming
> the following results
> Part Start Date End Date
> ABC 1/4/04 7/3/04
> CDB 11/1/03 5/4/04
> DCB 5/4/04 8/8/04
> So as you could see some parts ran in a differnts period of time and I
need
> to collect information for the latest 90 days for all parts regarding of
the[vbcol=seagreen]
> date they ran.
> Thanks
> "Aaron [SQL Server MVP]" wrote:
GETDATE(),[vbcol=seagreen]
I[vbcol=seagreen]
90[vbcol=seagreen]
give[vbcol=seagreen]
|||Your narrative does not make sense. Please supply real requirements with
CREATE TABLE statements, sample data in the form of INSERT statements, and
desired output.
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:B1666113-0BA6-4893-9C1F-A7ED897FA7A1@.microsoft.com...
> Adam,
> If I request the last 90 days, it'll only give those parts that ran in
that
> time interval, and I need the last 90 days for all the parts, regarding
the[vbcol=seagreen]
> dates that they ran.
> Thaks
> "Adam Machanic" wrote:
you[vbcol=seagreen]
I[vbcol=seagreen]
90[vbcol=seagreen]
give[vbcol=seagreen]
|||Aaron,
Here is
Insert <Table Name>
Select actcycle,actcav,actqty,actlab,slot,part,hrsran,hrs ava,hrsava-hrsran
as downtime
From Tbl_History
Where Pdate >= getdate()-90
by doing this I'll only get those parts that have ran in the last 90 days,
and I need the last 90 days of history for every part that we have in the
table Tbl_History. So in others words I need the last 2160 hours of ran data
for every part in the Tbl_History table and then insert this data into
different table (90*24)
"sanvaces" wrote:
[vbcol=seagreen]
> Aaron,
> Some parts did not ran for the last 90 days, so I need to create a query
> that will search the last 90 days for any part, here is an example assuming
> the following results
> Part Start Date End Date
> ABC 1/4/04 7/3/04
> CDB 11/1/03 5/4/04
> DCB 5/4/04 8/8/04
> So as you could see some parts ran in a differnts period of time and I need
> to collect information for the latest 90 days for all parts regarding of the
> date they ran.
> Thanks
> "Aaron [SQL Server MVP]" wrote:
|||More narrative doesn't help. I have no idea what "last 2160 hours of ran
data for every part" means. Please see http://www.aspfaq.com/5006 and give
us REAL REQUIREMENTS.
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:79E6CB0F-501C-4643-B3AD-F9DA40E15190@.microsoft.com...
> Aaron,
> Here is
> Insert <Table Name>
> Select actcycle,actcav,actqty,actlab,slot,part,hrsran,hrs ava,hrsava-hrsran
> as downtime
> From Tbl_History
> Where Pdate >= getdate()-90
> by doing this I'll only get those parts that have ran in the last 90 days,
> and I need the last 90 days of history for every part that we have in the
> table Tbl_History. So in others words I need the last 2160 hours of ran
data[vbcol=seagreen]
> for every part in the Tbl_History table and then insert this data into
> different table (90*24)
> "sanvaces" wrote:
query[vbcol=seagreen]
assuming[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
GETDATE(),[vbcol=seagreen]
number, I[vbcol=seagreen]
last 90[vbcol=seagreen]
it[vbcol=seagreen]
give[vbcol=seagreen]