trying to return data with only the latest date

when i run this i get data for the last day. i'm trying to get it to
return only for the latest date.

for example the instance name column contains all the drive letters. i
would like it to return one row for each machinename and drive letter.
tried adding distinct but didn't work

with free_hd_space_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename,
a.objectname, a.countername, a.instancename, b.countervalue,
convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead
from counterdetails a inner join counterdata b
on a.counterid = b.counterid
where b.counterid in (select CounterID
from counterdetails
where objectname = 'logicaldisk'
and countername in ('Free Megabytes', '% Free Space')
and instancename != '_Total')
and convert(datetime, substring(b.counterdatetime,1, 19)) > getdate()
-1
group by a.machinename, a.instancename, a.counterid, a.objectname,
a.countername,b.countervalue, b.counterdatetime
--order by a.machinename, a.instancename, a.counterid
)
select MachineName, free_hd_space_cte.ObjectName,
free_hd_space_cte.CounterName, free_hd_space_cte.InstanceName,
free_hd_space_cte.CounterValue, max(TimeRead) as TimeRead
from free_hd_space_cte
group by MachineName, ObjectName, CounterName, InstanceName,
CounterValue, timeread
0
Alen
3/4/2010 3:24:49 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
747 Views

Similar Articles

[PageSpeed] 37

Alen
Take a look at ROW_NUMBER () function to partititon and order by date column






"Alen Teplitsky" <alent1234@gmail.com> wrote in message 
news:a508392a-a956-4965-94e5-d0d54e7e23d6@j27g2000yqn.googlegroups.com...
> when i run this i get data for the last day. i'm trying to get it to
> return only for the latest date.
>
> for example the instance name column contains all the drive letters. i
> would like it to return one row for each machinename and drive letter.
> tried adding distinct but didn't work
>
> with free_hd_space_cte
> as
> (
> select a.counterid, substring(a.machinename,3,20) as machinename,
> a.objectname, a.countername, a.instancename, b.countervalue,
> convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead
> from counterdetails a inner join counterdata b
> on a.counterid = b.counterid
> where b.counterid in (select CounterID
> from counterdetails
> where objectname = 'logicaldisk'
> and countername in ('Free Megabytes', '% Free Space')
> and instancename != '_Total')
> and convert(datetime, substring(b.counterdatetime,1, 19)) > getdate()
> -1
> group by a.machinename, a.instancename, a.counterid, a.objectname,
> a.countername,b.countervalue, b.counterdatetime
> --order by a.machinename, a.instancename, a.counterid
> )
> select MachineName, free_hd_space_cte.ObjectName,
> free_hd_space_cte.CounterName, free_hd_space_cte.InstanceName,
> free_hd_space_cte.CounterValue, max(TimeRead) as TimeRead
> from free_hd_space_cte
> group by MachineName, ObjectName, CounterName, InstanceName,
> CounterValue, timeread 


0
Uri
3/4/2010 3:33:52 PM
On Mar 4, 10:33=A0am, "Uri Dimant" <u...@iscar.co.il> wrote:
> Alen
> Take a look at ROW_NUMBER () function to partititon and order by date col=
umn
>
> "Alen Teplitsky" <alent1...@gmail.com> wrote in message
>
> news:a508392a-a956-4965-94e5-d0d54e7e23d6@j27g2000yqn.googlegroups.com...
>
> > when i run this i get data for the last day. i'm trying to get it to
> > return only for the latest date.
>
> > for example the instance name column contains all the drive letters. i
> > would like it to return one row for each machinename and drive letter.
> > tried adding distinct but didn't work
>
> > with free_hd_space_cte
> > as
> > (
> > select a.counterid, substring(a.machinename,3,20) as machinename,
> > a.objectname, a.countername, a.instancename, b.countervalue,
> > convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead
> > from counterdetails a inner join counterdata b
> > on a.counterid =3D b.counterid
> > where b.counterid in (select CounterID
> > from counterdetails
> > where objectname =3D 'logicaldisk'
> > and countername in ('Free Megabytes', '% Free Space')
> > and instancename !=3D '_Total')
> > and convert(datetime, substring(b.counterdatetime,1, 19)) > getdate()
> > -1
> > group by a.machinename, a.instancename, a.counterid, a.objectname,
> > a.countername,b.countervalue, b.counterdatetime
> > --order by a.machinename, a.instancename, a.counterid
> > )
> > select MachineName, free_hd_space_cte.ObjectName,
> > free_hd_space_cte.CounterName, free_hd_space_cte.InstanceName,
> > free_hd_space_cte.CounterValue, max(TimeRead) as TimeRead
> > from free_hd_space_cte
> > group by MachineName, ObjectName, CounterName, InstanceName,
> > CounterValue, timeread

got it. used Itzik Ben-Gan's method from chapter 4 of SQL 2005 T-SQL
Querying book. now i have to pivot the data

with free_hd_space_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename,
a.objectname, a.countername, a.instancename, b.countervalue,
convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead
from counterdetails a inner join counterdata b
on a.counterid =3D b.counterid
where b.counterid in (select CounterID
from counterdetails
where objectname =3D 'logicaldisk'
and countername in ('Free Megabytes', '% Free Space')
and instancename !=3D '_Total')
and convert(datetime, substring(b.counterdatetime,1, 19)) > getdate()
-1
group by a.machinename, a.instancename, a.counterid, a.objectname,
a.countername,b.countervalue, b.counterdatetime
--order by a.machinename, a.instancename, a.counterid
)
select MachineName, ObjectName, CounterName, InstanceName,
CounterValue, TimeRead
from free_hd_space_cte
where TimeRead in (select max(timeread) from  free_hd_space_cte)
group by MachineName, ObjectName, CounterName, InstanceName,
CounterValue, timeread
--having timeread > (select timeread
0
Alen
3/4/2010 4:05:40 PM
Reply:

Similar Artilces:

Looking for an excel function which can mirror opposite the data
Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB...

Convert weekno to a date
I have a week no. (say week 2) in cell A4 and I want to convert that to the Fiday of that week (in cell B7) in the format Fri 14th Jan 2005. Could anybody tell me how to achieve this please. See if this is something you can work with: A4: 2 (The week number) A5: 2005 (The year..you didn't mention if the formula might apply to other years) B7: =DATE(A5,1,CHOOSE(WEEKDAY(DATE(A5,1,1),2),5,4,3,2,1,7,6)+(A4-1)*7) Does that help? •••••••••• Regards, Ron "Box666" wrote: > I have a week no. (say week 2) in cell A4 and I want to convert that > to the Fiday of that week ...

Any word on CRM 1.2 release date?
Has anyone heard a firm release date yet for CRM version 1.2? And will CRM 1.2 work with SBS2003 that was just released? -kw "Jim" <jim@nospam.com> wrote in message news:ehxGoPvnDHA.644@TK2MSFTNGP11.phx.gbl... > Has anyone heard a firm release date yet for CRM version 1.2? > > This depends on where you are based. I've just got home from an MS course, and we told that in Australia it will release to the partners at the conference late next month, and will be released to customers on 12/1/04. It was also confirmed that it will run on SBS2003 - it will ru...

date problem #10
Thanks a lot, Dave This piece of code works great. M P Redd -- mpredd ----------------------------------------------------------------------- mpreddy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1327 View this thread: http://www.excelforum.com/showthread.php?threadid=26358 ...

Averaging weekly data into Months
Ive got a string of weekly data ranging from 1993-2010, which i need to convert from weekly to monthly. Id like to do this so that in the future new weekly data can be automatically converted to its monthly average. Whats the best way to do this? Maybe this will be useful to you... Step1: (If your weekly-date is in column B) Make a new column near your data, with formula in each line as "=eomonth(b1,0)" This will bring month value of each data-line, so later you can summarise/condense your data through this column value. Step2: In your monthly summary, use refe...

Drop line on only one data point in series
Is it possible to add a drop line to only one data point in a series? If so, how? I believe attaching an autoshape line to a single data point would also meet my needs. I would like to be able to have a single data point marked with a line that doesn't require repositioning every time I add new points to the end of the series, which is daily. Any ideas? An autoshape would move anytime a heavy truck drove by. You could add custom negative error bars to the series, where the range containing the values contained zero for each point except for the one you want the drop line for. Easie...

Pulling data for a report from a form
Hi, I have a product report list that want to show the on hand qty. so i have this formula in a report field: =onHand([pkProductID],Forms!FrmMyDate!InvDateQty) but when i preview the report it shows #name? instead. Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 To refer to a textbox on another (open) form, use: =Forms!FrmMyDate!InvDateQty -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "josros60 via AccessMonster.com" <u56159@uw...

copy date in a cell if within a date range
Column M is a listing of percentages Column A is various dates, anywhere from Jan 1, 1998 to the present. I need to copy the contents of let's say M3 into cell T3 is the date in cell A3 is any date in the year 2010. If the date is in another year, leave cell T3 blank Thanks "carrerapaolo" wrote: > Column M is a listing of percentages > > Column A is various dates, anywhere from Jan 1, 1998 to the present. > > I need to copy the contents of let's say M3 into cell T3 is the date in cell > A3 is any date in the year 2010. If the ...

Custom Formatting a Chart Data Label
I want to create a chart which will display a zero value on the data label where there is in fact a zero value, and show N/A on the data label where it didn't apply. The formula I am using in the spreadsheet uses a nested formula to make this determination: =IF(AND(B22+C22>0,C22>0),B22/C22,NA()) Presently, it works but displays the #N/A on the chart where I would prefer it simply displayed as N/A. Can I do this with a custom format on the data label?? Thanks so much! I don't know how you all do it but your amazing! -- If you can read this, thank a Teacher... If your read...

pivot table changes when data is refreshed.
Hi, I have a pivot table with grouping based on a field called "period which is of date type. I have grouped it into months and quarters However when i refresh the pivot table whenever the data (which is in seperate sheet) is changed, the groupings are going out. I want t preserve the groupings as they are like a template. How do i stop th changing the format and layout of the pivot table whenever i refres the data? any ideas? please help. regards Kiran:mad ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages d...

Bill Summary skips Apay due dates
I know I saw something about this some time ago but I can't find the info anymore. Microsoft Money 2007 Deluxe Version 16.0.120.1303 Bank - Wachovia Issue: I have 2 monthly Apays that I set up on Wachovia's website which were downloaded into Money's Bill Summary. I looked today and, though the next payment that hadn't posted to the register should be due 10/20/2008 the Due Date showing in Bill Summary was 3/20/2009. I've canceled the Apays and re-created them but I know this is a chronic issue. I already went through the full file repair process a while ago and ...

Redefine field data type
Hi, I am trying to write a function in a module in MS Access 2000 that will change the data type of a field called 'Start' in table 'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I run it nothing happens...no errors or changes to the table. The code finds the table and field, creates a new field called 'temp' then copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The field 'Start' has data type dbDouble. Any help would be great!! Public Function ChangeFieldType() 'Purpose: Changes a f...

sp_columns does not return NVARCHAR data type columns
Hi, our SQL Server Version is 2000 SP4. For several days the SP "sp_columns" does not return NVARCHAR data type columns any more. I reviewd the SP "sp_columns" and it seems, that the used SP "spt_datatype_info" does not return that type any more. A second production server returns that column type. The SP "sp_columns" is used by the ODBC-Driver to determine the table columns. Any suggestions? Thanks a lot, Nils Ok, my fault, it seems that "spt_datatype_info" is a table and there is no NVARCHAR row. After insert...

Charts not recognizing source data if original linked data is changed.
I am very frustrated by Excel (2003) at the moment. I'm relatively new to using Excel in depth. I have always been able to work my way through most intricacies and pitfalls. But now I have been working in a workbook with linked sheets (and with linked workbooks too) and have two problems that I cannot solve. Right now, I have about 47 sheets that are set up as follows: detail data: this is the raw data the the user enters daily totals: this is calculated data from the detail sheets summary sheets: further sums from the daily totals and a chart object that charts these sums (a line or sc...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

Date Problem #2
Hi All Please can someone advise me what to do, I have a date in a textbox where I am using the AddDate method to increment the day , but I am getting strange results. Is this because the US date? if so how do I change this to work for the UK date? Any help would be greatly appreciated regards Subs Hiya- i'm not sure what formula you're using- I put a random date in A1 and pasted the following formula in B1 & C1: =DATE(YEAR(A1),MONTH(A1),DAY(A1)+1) it increased the day by 1 in both cells. I then changed the format to UK and it worked just fine. Is this what you're l...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <NoSpam-ZQLi@GMail.com> wrote in message news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Where has the data map tool gone to in Excel 2003?
Cannot find it in 2003. Was there in earlier versions of Excel. It's gone as of Exel 2002. Here is MS's statement and a link to another program for map making. http://office.microsoft.com/en-us/assistance/HA010346591033.aspx tj "macray" wrote: > Cannot find it in 2003. Was there in earlier versions of Excel. If it still resides on the hard drive, you can use it from later Excel versions, through the Insert Object dialog. But I think uninstallation is pretty good at wiping it out. In my case, I have multiple versions of Excel installed on one machine, so Excel 2003...

Text to Date
I have been given a database dump (thousands of rows) that put the dates in a text format like this: Aug/11/2009 Is there any way to put this into a date format? Even if it means creating a new field breaking it up and doing some extra steps. Thanks. Change to appropriate cell reference: =DATEVALUE(SUBSTITUTE(A1,"/",", ")) -- Best Regards, Luke M "PAL" <PAL@discussions.microsoft.com> wrote in message news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com... >I have been given a database dump (thousands of rows) that put the da...

Expiration Date on Trial Period
Is there a way to determine what the expiration date of a trial period installation is? ------=_NextPart_0001_3829F12C Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Cindy, Thanks for posting in. This is Ken in Online Partner Support. The trial program will be expired in 90 days from installation date so why don't you confirm the installation date? If you use Windows Server 2008, you are able to confirm the date from Programs and Features and if you use Windows Server 2003, how about check the create date of install folder? Kind Regards, Ken Microsoft Online Partne...

Finding data in grouped sheets
When using the 'find' option whilst workbook pages are grouped together I am getting data from the last page first, then the last but one etc. Can anyone tell me whether it is possible to change this so that it finds the data in the first possible page, ie it looks at page 3 before page 4 etc? What I am trying to do is set up a spreadsheet which finds the first 'vacancy' in a childrens nursery. As I have set it up at the moment it is finding the last vacancy first! All help greatly received! Thanks in advance. Susie Vaughan This may not quite fit, but you may find it very us...

Importing data from several sheets, to one chart
I need data from many sheet into one chart (In its own sheet). I know how to do it when its from from sheet (In this example, sheet named Pernille) : =Pernille!$AS$10 I tried this : =Pernille!$AS$10;Aase!$AS$10 But the reference was not valid. Please help, I havent been able to find anything, not even in my E-learning. Martin, Probably the easiest way to chart data from multiple sheets is to create a summary range in a single sheet that references the other sheets with formulas. If you use this technique then all you have to do is generate the chart using the summary range as your sourc...

Date formats, culture and globalization
Hi, ASP.NET 3.5 We are creating a 3-tier ASP.NET Web Application that needs just to understand dates as UK shortdate format (dd/MM/yyyy). It will only be used in the UK on intranet. I often come across web server and client machine that are incorrectly set up so I want to protect the app from these incorrectly set up hosts. I understand the safe date formats for passing through to the database. SQLParameters pass through a non-ambiguous format and also that 'yyyyMMdd' is also a safe format. So ignore the database side of things - I get that. At the moment all the bou...

Result of date as Month-2 digit Year
I have columns of dates that the users must enter as actual dates. I can format to display month and two-digit year...but when creating pivot tables it reconizes the entire date. Since I want to summarize only by month and year or by year only, is there a formula or statement that will change the date to month-year or year status only? Thanks. -- Linda Hi Linda Sure. Rightclick the date field header in the Pivot table, choose menu "Group and show detail" > "Group"... . You will get a list offering Seconds, minutes, hours, days, months, quarters, years. Try sel...

Outlook is trying to think for me again and getting it wrong (again!)
Please dear lord tell me there is a way to turn this off... I recently composed an email using Microsoft Outlook (2003 SP2) that was supposed to be a list of items, each on a single line. line 1 line 2 line 3 Well, for some reason unknown to me, when the email was sent Outlook reformatted my email and put all the individual lines into a single paragraph with commas and spaces between them. line1, line2, line3 NO NO NO NO NO!!! Not only did I never get a warning that this was going to happen or a chance to reject these changes, but I wanted it the way I freaking typed it! Is there an ...