How To Get Latest Temperature Reading For Each Date In A Date Range

Sep 28, 2012

I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:

Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.

I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable

How could I get the temperature if I did a sub-query here?

View 5 Replies


ADVERTISEMENT

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?

Nov 26, 2015

CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )

[Code] .....

I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3.  

If the declined date is filled in.  Then I want to get the row, when the Datein column value is greater then the declined date only.

I tried grouping it by max date, but   i got an error message when trying this out.  Against the code  

WHERE MAX(Datein) > Declined

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.  What do I need to do to get both my outputs working? 

View 9 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID          StartDate
1            2014-01-01
1            2014-02-01
1            2014-05-01-------After Gap Restarted
1            2014-06-01
1            2014-09-01---------After last gap restarted
1            2014-10-01
1            2014-11-01
2            2014-01-01
2           2014-02-01
2            2014-03-01
2            2014-04-01
2            2014-05-01
2            2014-06-01
2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id             Startdate
1             2014-10-01
2             2014-01-01

View 4 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

Aug 11, 2013

Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?

View 18 Replies View Related

Reporting Services :: Searching By Single Date Or Date Range

Apr 22, 2015

I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.

View 5 Replies View Related

{RESOLVED} Date Logic - Calculating A Date Range

Jan 23, 2007

I have a report that I need to run on 2 different date ranges.

Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE

The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)

So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007

My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007

Any help would be greatly appriciated!

View 2 Replies View Related

Query Help - Giving A Date Range Given The Start Date, Thanks!

Jul 23, 2005

Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)

View 2 Replies View Related

Date Picker Controls - Anyway To Limit Date Range

Jul 26, 2007

Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?

View 4 Replies View Related

Finding Where My Date Falls In Date Range

Oct 25, 2007

Hi;

We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.


I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------


with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

View 11 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

Help With Getting Row With Latest Date

Sep 3, 2007

guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?

Ex:

TransactionID | Customer ID | TrasanctionDate

1 | 2 | 9/3/2007 12:00:00 AM
2 | 3 | 9/4/2007 12:00:00 AM
3 | 2 | 9/5/2007 12:00:00 AM
4 | 2 | 9/6/2007 12:00:00 AM

Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2

4 | 2 | 9/6/2007 12:00:00 AM

Any help will be greatly appreciated. Thanks in advance!

View 3 Replies View Related

Getting The Most Latest Date

Jun 14, 2006

I have a column which stores dates (datetime data type). I would liketo fetch one data which is the most latest date among them. So if thereare 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one Ineed to output.how can I write this in sql stmt?select datebeginfrom testtablewhere datebegin = ?????I don't think it is simple as I thought. I have no idea what I need towrite in where clause to make this work.thanks.

View 1 Replies View Related

Getting The LATEST DATE In The Table

Mar 5, 2007

How to modify the below procedure in order to get the latest date ( the biggest value ) in the column “Deadline�, type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer “UserName� ?
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE GetLatestDate
(@UserName VARCHAR(50)
AS
SELECT Deadline
FROM CustomerItems
WHERE UserName = @UserName

View 2 Replies View Related

Get Record With Latest Date

Jun 4, 2007

 if there are 2 records with different date  how to write query for ----> get record with latest date 

View 4 Replies View Related

Select Latest Date Only

May 24, 2005

Hello,

If I have a TransactionTableT including with fields like this:
TransactionDateDT
QuantityIN
ItemID,
ItemCodeCH
ItemNameCH
PriceFL

Each time the user make an entry a new record is inserted.
How can I pull only the latest (TransactionDateDT) record for each item. I've tried MAX(TransactionDateDT), but how can I get ItemCodeCH, ItemNameCH and PriceFL.

Basically I need to create a report with the latest price for each item.

Any feedback welcome!

Edi

View 2 Replies View Related

Selecting Latest Date

May 8, 2008

Hello all. I ma using the following query to pull back data. The MergeHistory table has a column named DateMerged. I am looking to pull back the one record with the most recent DateMerged. I have managed to get the query as far as below but not sure how to select the most recent one. Can anyone help with this? I was told it may be along the line of SELECT TOP 1 or something?


INSERT INTO @List (IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate)
SELECT DISTINCT
RT.IndexId,
isnull(dbo.ufn_GetBestIdentifier(RT.IndexId), dbo.ufn_GetBestVirtualIdentifier(RT.IndexId)),
dbo.ufn_GetEntitySystemName(RT.IndexId),
RT.Status,
CONVERT(varchar, RT.DateCreated, 106) as DateCreated,
RT.CreatedBy,
RT.DataTag,
MH.MergedDate
FROM @resulttable AS RT, MergeHistory AS MH
WHERE RT.IndexId = MH.EntityID

View 4 Replies View Related

Latest Date To Be Returned

Dec 10, 2013

Aim – Get the latest [hst_date_processed] for every [hst_merchnum]

My query so far is

SELECT top 5
[hst_merchnum]
,[hst_date_processed]
FROM [FDMS].[dbo].[Fact_Financial_History]
where hst_merchnum = '878001897'
group by [hst_merchnum],hst_date_processed

which return the following results

hst_merchnumhst_date_processed
8780018972011-12-01
8780018972012-07-01
8780018972011-11-01
8780018972011-02-01
8780018972011-05-01

However results i need are:

hst_merchnumhst_date_processed
8780018972012-07-01

View 2 Replies View Related

How To Get Latest Date Record

Apr 10, 2014

I want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved

table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 1 Replies View Related

Return Latest Date

Jan 20, 2006

I am trying to return the latest date in a stored procedure as shown below, but get the error 'incorrect syntax near @strLastDate'

CREATE PROCEDURE spRB_GetLastDateBookingDates

@strLastDate datetime OUTPUT

AS

SELECT max(BD_DateRequired) as @strLastDate
FROM tblRB_BookingDates
GO

RETURN

View 8 Replies View Related

Get Rows For Latest Date

Sep 27, 2007

Hello!

I have a table something like this:

ID INTEGER
Info VARCHAR (actually several columns but that is not important here)
DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info
1 - 2007-02-01 - Info1
1 - 2007-02-01 - Info2
1 - 2006-02-01 - Info3
2 - 2007-05-05 - Info4
2 - 2007-02-01 - Info5
2 - 2006-02-01 - Info6

I would like to get:

Info1
Info2
Info4

This has to be done in one Query. Can anybody help me?

View 6 Replies View Related

How To Get The Latest &&amp; Earliest Date?

Mar 1, 2007

Hi,

How to get the latest date from ColumnDateTime?

and the earlier as well ?

View 5 Replies View Related

Select Latest Date Based On ID

Dec 3, 2012

I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.

I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.

Here is my code which does not work

select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
where userID = '1'
group by productID, basketID, SessionID, UserID

View 2 Replies View Related

Unable To Get Latest Date Record?

Apr 10, 2014

i want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------

idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 2 Replies View Related

Getting List With Only One Of Each Item And Latest Date

Mar 26, 2015

I have the following table:

Item Date
A 2015-01-02
A 2015-03-01
A 2015-02-17
B 2015-01-07
B 2015-03-20
C 2015-01-02
C 2015-03-17
C 2015-01-14
C 2015-02-02
D 2015-03-25
E 2015-02-09
E 2015-02-09

Is it possible to get a list with only one of each item and then the latest date possible?

View 2 Replies View Related

Searching For Latest Date And Time Only

Jul 20, 2005

> This is what I want to do with the data table below.[color=blue]>> I only want it to show one id for each issue. ( ie, 4001 only once,> 4002 only once, etc. But only the one with the latest date and time)>> Ie. 4001 should only be in the table once, with the latest date -> which should be :> 400109/12/200317:12:09 (as I only want to show the last time> the id was updated - do you get me now )>> Ie. 4002 should show only 400211/12/200315:25:13>[/color][color=blue]>>> idhs_change_datehs_change_time> 400127/10/200310:38:27> 400109/12/200314:43:58> 400109/12/200317:12:09> 400227/10/200310:56:28> 400209/12/200314:44:11> 400211/12/200315:25:13> 400327/10/200311:13:12> 400309/12/200314:44:21> 400310/12/200310:48:02> 400310/12/200313:25:09> 400427/10/200311:28:09> 400409/12/200314:44:29> 400409/12/200317:18:28> 400527/10/200315:55:40> 400528/10/200310:18:24> 400627/10/200315:59:47> 400628/10/200310:18:38> 400609/12/200314:44:40> 400609/12/200316:47:15>[/color]

View 3 Replies View Related

Selecting The Latest Date From Different Columns

Jul 20, 2005

I have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip

View 5 Replies View Related

How To Know Latest Update Date Of Each Stored Procedure ?

Aug 3, 2006

on SQL Server 2000
They show only Create date
but I need know update date
because I install my system on customer's site and solve problem on customer site
and I can't bring all stored procedure back to my office and restore all stored
because of my database have two projects.
 
Please Help me.....

View 2 Replies View Related

Select Latest Row When ID Has One Or More Rows And Each ID Unique From And To Date

Dec 8, 2013

I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES).

ID
NAME
FROM_DATE
TO_DATE

[code]...

View 1 Replies View Related

How To Enter A Check Constraint With Latest Date

Nov 7, 2013

Without having to update it every day.

This is so no one enters a value higher than the current date.

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Closed_Date>)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved