Query/View: The 2 Newest Periods For Each Indicator
Mar 24, 2006
Hi,
I'm working on a simple performance-program, where I need to extract
information from the 2 newest periods for every performance-indicator
- And from there calculate a trend between these results.
The problem is, that I can't find a simple way to extract the 2 latest
results.
The Table (Table1) looks like this:
kpiIDperiodIDActual
Acceleration23
Acceleration54
Speed1100
Speed4200
Speed7220
Speed9180
Weight122
Weight332
Weight721
Weight1033
If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):
SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);
BUT - how how do I get the second-newest period as well?
Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest
Alternatively a View with 2 posts for each performace-indicator.
I want to create a report based on above three tables It looks like as follows;
Program Summary
Yesterday/Current MTD Previous Month YTD
Amount Number Amount Number Amount Number Amount Number
Portfolio
Loans Commenced
$ # $ # $ # $ #
Loans Closed
$ # $ # $ # $ #
CDs Commenced
$ # $ # $ # $ #
CDs Closed
$ # $ # $ # $ #
Loan table - Amount, State CD - Amount, State Account - CommencedOnDate(date time), closeondate(date time)
Yesterday - Loans/CDs started yesterday(calander date..not last 24 hours) MTD - " " First of this month to yesterday Previous Month - " " during previous month YTD - " " Loans/CDs started from 1 of this year to yesterday
Loan and cd states are Commenced and Closed
Based on this I have to create a report. But this time format seems to be really complex. Can anyone help me to write the query pls?
I'm working on SQL Server 2005 & Reporting services to create a Projet model to deploy it on the server.
To explain me, i 'll recreate simply my problem on the the database "AdventureWorksDW".
I woudl like to see information on my product dimension.
In a matrix tab, i put "Product name" in row value and "customer name" on column.
I would naturally know the "Sales Amount" for my product by customers.
But, as i had not create aggregate, i can't put my numerique value on the cells indicators. It just allowed me to put the SUM(SALES_AMOUNT) by exemple, but it's not what i want.
I have data as below, I want the indicator to be displayed by using timestamps
Id task_id tsk_complete_ts task_due_date Id_end_ts tsk_indicator id_indicator
50 2001 01-01-2015 02-01-2015 10-01-2015 GREEN
50 2002 03-01-2015 04-01-2015 10-01-2015 AMBER
50 2003 07-01-2015 06-01-2015 10-01-2015 RED
50 2004 NULL 09-01-2015 10-01-2015 GREEN
I need to calculate the indicator for the whole id considering all the tasks by using the below conditions,
1) if tsk_complete_ts > id_end_ts, display id_indicator as RED 2) if any task is in RED or AMBER and id got completed before id_end_ts, the display id_indicator as AMBER 3) Else GREEN
I want the id_indicator to be populate dynamically in view without using stored procedure, id_indicator should be common for whole id.
Task indicator also getting populated in the view using the case statement.
I hope you can help as I am really scratching my head on this one. I am pulling together an assessment of the Disaster Recovery readiness for an organisation I am working at. Part of the assessment I am doing is the recovery model of each of the databases.
I have scripts that are already pulling lots of data from 40+ servers and 500+ databases. However, I cannot seem to find anywhere within the MASTER or MSDB or the database itself where the Recovery Model flag is held. Obviously I can right click on the database and click properties and it is there, but I need to automate this task (as it will probably be a weekly assessment).
I have checked sysdatabases and almost every other table, but nothing obvious as to where this flag is.
I am in the process of training a Neural Networks, which could take significant iterations in the process of getting trained. While using other tools like I can visually see the convergence (in terms of error for the model). Is there a way to see any progress while training while using Analysis Server - Neural Network training? It would be useful to see the accuracy, interation number and timeout while in the process of training etc...
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
I am building from the Time Tracker Start Kit, trying to get a better feel for how MS thinks we should do things.
In my editing, I have built a new Stored Procedure, trying to pull the newest entries for a specified person.
I have the following that will return all entries for a specific person, sorted by date, newest first:
SELECT EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID, TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName, ManagerUserID, TT_Categories.Abbreviation AS CatShortName FROM TT_EntryLog INNER JOIN TT_Categories ON TT_EntryLog.CategoryID = TT_Categories.CategoryID INNER JOIN TT_Projects ON TT_EntryLog.ProjectID = TT_Projects.ProjectID WHERE UserID = @UserID ORDER BY EntryDate Desc
This will return something like:
EntryLogId Description Duration EntryDate ProjectID CategoryID CategoryName ProjectName ManagerUserID CatShortName 14Can type date in date... .00 2004-02-04 10:28:00116Pros ITS Project Management App 3 Pros 12Changed "Entry Date"... .00 2004-02-03 13:28:00116Pros ITS Project Management App 3 Pros 13Added default button ... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros 11Removed hours per p... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros 6Isn't this cool .00 2004-02-02 00:00:00229Pros Knowledge Base 3 Pros 9Added week-by-week... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros 10Fixed Update comma... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros 1Built initial framewor... 6.00 2004-01-30 00:00:00116Pros ITS Project Management App 3 Pros 5Adding up to 8 hours... 2.00 2004-01-30 00:00:00229Pros Knowledge Base 3 Pros 3Debugged - fixed a f... 1.00 2004-01-23 00:00:00229Pros Knowledge Base 3 Pros
What I would like to accomplish is to return only the newest entry for each ProjectID (so in the above example, there would only be 2 entries, EntryID 14 and 6)
I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help
Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?
I just discovered this cool forum. I am fairly new to T-SQL, so please bear with me.
Here is my problem. I am trying to return 1 record for each distinct MachineName, based on the most current CreateTime.
I have tried a bunch of different things, but the follwing query seems to get me close, except for all of the records, per MachineName, that aren't the newest CreateTime. Can anyone offer a suggestion I may be missing?
Here is my query:
SELECT rcv.Name AS MachineName, r.CreateTime, rpv.Path + rpv.Name AS ResourcePool
FROM dbo.Result_View AS r WITH (NOLOCK) INNER JOIN dbo.ResourceConfiguration_View AS rcv WITH (NOLOCK) ON r.ResourceConfigurationId = rcv.Id INNER JOIN dbo.Resource_View AS rv WITH (NOLOCK) ON rcv.ResourceId = rv.Id INNER JOIN dbo.ResourcePool_View AS rpv WITH (NOLOCK) ON rv.ResourcePoolId = rpv.Id
WHERE (rpv.Name NOT LIKE 'Archive') AND (r.CreateTime > '2/1/2008') AND (r.CreateTime = (SELECT TOP (1) r.CreateTime FROM dbo.Result_View))
ORDER BY MachineName
I have added the '2/1/2008' filter, so as not to hammer the DB too hard. It goes back for some time.
Hi!I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.Can I do that?/Johan Ch
I have just started to use Integration Services Catalog with SQL Server Agent. After I deploy a new version of the project the any Server Agent job that uses any of dstx still uses the old one. How do I fix this?
Hello everybody,-------------------------------------------------CREATE TABLE [T1] ([IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DateEvt] [datetime] NOT NULL,[Value] [varchar] (10) NOT NULL ,[DataX] [varchar] (10) NULL ,CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED([IDX]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOinsert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00','0000000001', 'AAAAAAAAAA')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01','0000000002', 'AAAAAAAAAA')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02','0000000003', 'AAAAAAAAAA')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00','0000000001', 'BBBBBBBBBB')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00','0000000001', 'CCCCCCCCCC')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00','0000000001', 'DDDDDDDDDD')GO-------------------------------------------------and the question is:In which fastes and best for the preformance way, get the last IDX ofspecified Value.I could do this like this:-------------------------------------------------declare @nIDX numericdeclare @sValue varchar(10)select top 1 @nIDX = IDX from T1where Value = @sValueorder by DateEVT desc-------------------------------------------------But I know, this is not fast (even if I have index on DateEVT field),and I'm quite sure, that there is better way to get this IDX.Anyway, this table can be big (like 20 milions records).I could take the max of IDX, but is it a sure way?Any help? Thanks in advanceMatik
I have the following Table:DataIDuserIDTimeStampI need to create a query that selects the "newest" records for all distinct users. I'm hoping to get this done in one query. I know I could write multiple queries to find all distinct users, and then the newest records based on that... and so on... but I'm not sure that's the most efficient. I have the following so far, but is it the most efficient (I have indexing on most of my columns)? SELECT DATA.DataID, DATA.UserID, UN.vchName, UN.vchImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName' FROM [tblData] DATA INNER JOIN [tblUnits] UN ON DATA.UserID = Un.UserID INNER JOIN [tblusers] U ON U.UserID = UN.UserID WHERE GPS.intGPSDataID IN (SELECT MAX([tbLData].DataID) FROM [tbLData] GROUP BY [tbLData].UserID)
First I'm a big NEWBIE, so it'd be great if you can provide a little explanation as to how this should be done...
Here's what I want to do, but have no idea how to approach it.
I have a table with Quotes (table Quote) in them (for a Sale's Team). Each has a quote number (qtQN) and this number is sequencial but sometimes revised where a letter is added at the end. Like so:
The first 3 digit are company codes and pretty unimportant to this problem. As you can see, 111q0002 has three versions. A, B, and no letter. The most up to date is B. So in this list I want to list only the most up to date quotes. So the resulting list would be:
111q0001 111q0002B 222q0005
Get it? Good, cuz I have no idea how to query that... any help at all is appreciated!
I'm using MS SQL Server 2k and scripting with ASP 3.0
Hi, this is my first post here, i'm working on a view wich I have to display the results by period. 1st period - Current month 2nd period - Last month 3rd period - Month before last and so on.
How can I achieve this? of course I have a column wich has the date I want to use, I was thinking on semthing like:
case when DATEDIFF(M, TRXDATE, getdate()) <= 0 then ammount END as Period1 (TRXDATE and ammount are columns on my table)
I have to do alot of date calculations. For some calculations, I can use the datediff or dateadd function to get a Time Period between 2 dates.
Now for some dates I need to calculate the time between 2 dates BUT:
a week = 5 days starting from Monday to Friday a day starts at 8AM and ends at 6PM (so a day is 10 hours)
You can probably calculate this manually but what about summer to winter hour and the month Februari when it has 29 days etc.
So I was thinking ... is it possible to calculate the Time Period for weeks with 24h a day / 7 days AND then transform that Time Period to a time period for weeks with 10h a day / 5 days ?
If anyone has an idea to solve this, either with functions or an other way, please let me know! Thanks
I want to create a report based on above three tables It looks like as follows;
Program Summary
Yesterday/Current MTD Previous Month YTD
Amount Number Amount Number Amount Number Amount Number
Portfolio
Loans Commenced
$ # $ # $ # $ #
Loans Closed
$ # $ # $ # $ #
CDs Commenced
$ # $ # $ # $ #
CDs Closed
$ # $ # $ # $ #
Loan table - Amount, State CD - Amount, State Account - CommencedOnDate(date time), closeondate(date time)
Yesterday - Loans/CDs started yesterday(calander date..not last 24 hours) MTD - " " First of this month to yesterday Previous Month - " " during previous month YTD - " " Loans/CDs started from 1 of this year to yesterday
Loan and cd states are Commenced and Closed
Based on this I have to create a report. But this time format seems to be really complex. Can anyone help me to write the query pls?
Guys Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number of periods betwen the startdate and enddate so for example
-- Initial data insert @Periods ( StartDate , Enddate , NumberOfMonthsInPeriod , TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below )
select '30-Sep-2005', '10-Apr-2009', 1 -- 1 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 3 -- 3 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 6 -- 6 month period 0
The following rules regarding periods apply Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie 01-Jan - 31 Jan 01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...) and so on to December
Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year 01-Jan - 31-Mar 01-Apr - 30-Jun 01-Jul - 30-Sep 01-Oct - 31-Dec
Each 6 (NumberOfMonthsInPeriod) month period is one of the following 01-Jan - 30-Jun 01-Jul - 31-Dec
I need to derive TotPeriods as follows:
For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44
For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16
For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8
I have a Column in my data that gives a financial period value in the YYYYMM format. i.e. an asset was re-valued in a particular period for example 201301. I need to find out the number(count) of periods(months) between another given period for example current period (201403) and the period provided in the table i.e. 201301.
Your help is greatly appreciated. How do you query each one of the following:
1) SELECT FieldValue WHERE DateTime is from 8:30AM - 12:00PM and 6:00PM - 9:30PM on Mondays thru Fridays (i.e. exclude Sat & Sun) for the whole month of January 2008.
2) SELECT FieldValue WHERE DateTime is from 9:30PM - 8:30AM on Mondays thru Fridays, AND ALL-DAY on Saturdays & Sundays for the whole month of January 2008.
I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:
Period Sum
May '07 41
April '07 14
Q2 '07 55
March '07 36
February '07 28
January '07 22
Q1 '07 86
June '07 N/A
YTD '07 141
December '06 33
November '06 27
October '06 42
Q4 '06 102
September '06 58
August '06 84
July '06 52
Q3 '06 194
June '06 40
May '06 41
April '06 14
Q2 '06 95
March '06 67
February '06 38
January '06 N/A
Q1 '06 105
YTD '06 496
For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?
I am trying to tweak some code which is used to display the newest comments left on photos created by my members.
The existing code is this:
SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC
So the latest comment left was for photo #210879 from user "Cla" (redacted user names). The 2nd newest comment would be for photo #211072 from a member named "mo". pdate is a date field
However for the script I have coded I don't want all of the photo comments to show up. This is because I use access levels based on the type of location (higher levels mean more restricted galleries). I check the access levels as I go through the recordsets.
I use this method to get the top 15 comments:
SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC
Now I have to use two other tables to determine the access level. Since PHOTOCOMMENTS is just a list of photo #'s and the people who left comments for those photos, I need to:
a) determine what location the photo is from and b) determine the access level of that location
I use: select creator,access from locations where id=(select dir from photos where id="&pnumber&")"
This is a two step process as you can see. The first part is:
select dir from photos where id=(pnumber)
ID is the same value as pnumber seen in PHOTOCOMMENTS. That is to say PHOTOS.ID = PHOTOCOMMENTS.PNUMBER
If I haven't confused you yet, the executed code for the first example would be:
select dir from photos where id=210879
which would get me a value for DIR. DIR is the location number which would be:
select creator,access from locations where id=(dir value)
Just to simplify it a bit....
There are three tables (shown below)
PHOTOCOMMENTS PHOTOS LOCATIONS
I need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)
but then also
select creator,access from locations (The last table shown) where id=(select dir from photos where id="&pnumber&")"
So the first table PHOTOCOMMENTS has to also join PHOTOS table where PHOTOS.DIR = PHOTOCOMMENTS.PNUMBER in order to get the value of "DIR" and then DIR is joined to the LOCATIONS tables where PHOTOS.DIR = LOCATIONS.ID
Here is the actual code, which I am trying to make into a single SQL command
strSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC" set ors = oconn.Execute(strSQL) tl = 0 do until ors.eof or tl > 15 ' until we have 15 results because not every recordset will be of the proper security level
[Code] ....
Bonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2