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
ADVERTISEMENT
Nov 22, 2006
Can someone show me what I need to do to have a result set only displaying records from the specified date range. All the tables can relate on Date, shift, and ProductType (Besides UnprocessedProductTracking which can only relate on Date and Shift) I have a lot of trouble with joins...well I'm just not to experienced. Help!
CREATE Procedure dbo.spButterMarTotals
(
@fromdate smalldatetime,
@todate smalldatetime
)
AS
--
--Exec spButterMarTotals '11/15/06','11/15/06'
--
--
Select Distinct ButterballsThrownPounds, ReworkUsedPounds, ReworkMadePounds, CoolersUsedPounds, CoolersMadePounds, TrucksMadePounds,
ResidualFilmPounds, ResidualSqueezePounds, ProducedPounds, Blocks As ProcessingThrown, Weight AS NFCPounds, Pounds AS BirdsEyePounds,
T1AL50Type, T2Type, T3Type, T4Type, AL51Type, FillMach1Type
from tblproductionMaterialmetrics A
Inner Join tblNFCTracking B on
A.ProductType = B.ProductType
Inner Join tblProcessingDtl C on
C.ProductType = B.ProductType
Inner Join tblIngredientProduction D on
D.shift = C.shift
Inner Join tblUnprocessedProductTracking E on
D.shift = C.Shift
Where A.Date between @Fromdate and @todate
View 1 Replies
View Related
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
Aug 27, 2014
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
View 5 Replies
View Related
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
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
Jul 20, 2005
Hi guys,whilst working on a project which I thought was nearly complete I havecome across a problem which was some how over seen, which I am hopingone of you guys know how to resovle.Firstly here is the script to create the table and insert some sampledata:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventID 6006 represents a system shut down, and the EventID 6005represents a sytem start. Therefore from the above data you cans seethat the machine is not rebooted very often.My problem occurs when you select a date BETWEEN something where thereis no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired resultfrom this should return 15 days of uptime but as there is no data itis returning nothing:(Can anyone work this one out?ThanksSunny
View 7 Replies
View Related
Dec 12, 2014
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
[Code] ....
Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000
And so on….
View 4 Replies
View Related
Apr 23, 2008
I have a series of "Payroll Periods", called S1 thru S26 for the year. I want to create a parameter call "Pay Period" and have the user select a single pay period, i.e. S13. Elsewhere I know the beginning and ending date for each pay period, but it is in another dataset. So if the user selects S13 and I know the 13th pay period is April 1st thru April 15th, how do I use those dates as my beginning and end dates for my SQL query statement to return only the data from the selected pay period. The data itself has dates and not pay period references.
View 1 Replies
View Related
Oct 12, 2015
I have a table with the following data:
Type Date Subtype
1 1-1-2015 10
1 5-1-2015 10
1 6-1-2015 10
1 15-1-2015 11
1 20-1-2015 10
[Code] ....
I want to group by the data to the following while saving the first and last date within each type and subtype:
type startdate enddate subtype
1 1-1-2015 6-1-2015 10
1 15-1-2015 15-1-2015 11
1 20-1-2015 22-1-2015 10
1 25-1-2015 28-1-2015 12
2 2-1-2015 5-1-2015 11
2 6-1-2015 7-1-2015 12
2 14-1-2015 20-1-2015 11
2 23-1-2015 30-1-2015 13
I tried several approaches, but I can't fnd a solution to do this with T-SQL.
View 8 Replies
View Related
Jul 15, 2014
I have a table has following fields(Process, datestart,dateend,datedue,,,,)
Process datestart dateend ...
A 14.07.2017 20.07.2014
B 15.07.2014 21.07.2014
C 24.07.2014 30.07.2014
What i want when user enters from date and end date. i want to filter records which are fallen between those two input dates.
if user enters 17.07.2014 and 20.07.2014 as input range. query should show only A and B process as
A 14.07.2017 20.07.2014
B 15.07.2014 21.07.2014
View 3 Replies
View Related
Jun 16, 2015
I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:
DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,
[Code] ....
AGENTNAME BALANCE BEFORE BALANCE AFTER DATE
DOUGLAS 9738.75 9782.75 2015-05-11
DOUGLAS 9782.75 9804.75 2015-05-12
DOUGLAS 9804.75 9837.75 2015-05-13
In the sample data above, ideally I would like my query to return data as follow:
AGENTNAME BALANCE BEFORE BALANCE AFTER
DOUGLAS 9738.75 (from first activity) 9837.75 (from last activity)
Not sure how I can write sql query to accomplish this.
View 7 Replies
View Related
Aug 1, 2006
Hi Folks
Need a little help with a Date Range query;
Im using SQL 2000 and the field is set to datetime
I need to query from now to 30 days ago, its very basic I know...
The field is called - ArtDate
Any help appreciated !
Thanks
View 7 Replies
View Related
Apr 20, 2008
Hi there
I have a FileConsumptionDetail table which has [DataTime] every 30 minutes. Also, I have a schedule table with has range time and with certain condition.
Now I would like to query against these 2 tables what is the best approach to handle to do this due the process load is around 20,000 records. The end of result is sum of the Value1 of FileConsumptionDetail with the relevat schedule. I can probably using cursor and loop the 20,000 records of the data and check one by one whether that value is applied on that schedule table. Overkill .. I guess?
Here's the schema of the tables:
CREATE TABLE [dbo].[tnd_FileConsumptionDetail](
[FileConsumptionDetailID] [bigint] IDENTITY(1,1) NOT NULL,
[FileConsumptionID] [bigint] NULL,
[Time] [datetime] NULL,
[Value1] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value1] DEFAULT ((0)),
[Value2] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value2] DEFAULT ((0)),
[Value3] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value3] DEFAULT ((0)),
[Value4] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value4] DEFAULT ((0)),
[Value5] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value5] DEFAULT ((0)),
[DateCreated] [datetime] NULL CONSTRAINT [DF_UsageDetail_DateCreated] DEFAULT (getdate()),
[LastDateModified] [datetime] NULL CONSTRAINT [DF_UsageDetail_LastDateModified] DEFAULT (getdate()),
[IsActive] [bit] NULL
CREATE TABLE [dbo].[glb_Schedules](
[ScheduleID] [bigint] IDENTITY(1,1) NOT NULL,
[ScheduleGroupID] [bigint] NULL,
[ScheduleTypeID] [int] NULL,
[Name] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[StartTime] [smalldatetime] NULL,
[EndTime] [smalldatetime] NULL,
[Length] [int] NULL,
[IsMonday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsMonday] DEFAULT ((0)),
[IsTuesday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsTuesday] DEFAULT ((0)),
[IsWednesday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsWednesday] DEFAULT ((0)),
[IsThursday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsThursday] DEFAULT ((0)),
[IsFriday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsFirday] DEFAULT ((0)),
[IsSaturday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsSaturday] DEFAULT ((0)),
[IsSunday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsSunday] DEFAULT ((0)),
[IsJanuary] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsJanuary] DEFAULT ((0)),
[IsFebruary] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsFebruary] DEFAULT ((0)),
[IsMarch] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsMarch] DEFAULT ((0)),
[IsApril] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsApril] DEFAULT ((0)),
[IsMay] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsMay] DEFAULT ((0)),
[IsJune] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsJune] DEFAULT ((0)),
[IsJuly] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsJuly] DEFAULT ((0)),
[IsAugust] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsAugust] DEFAULT ((0)),
[IsSeptember] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsSeptember] DEFAULT ((0)),
[IsOctober] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsOctober] DEFAULT ((0)),
[IsNovember] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsNovember] DEFAULT ((0)),
[IsDecember] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsDecember] DEFAULT ((0)),
[DateCreated] [datetime] NULL CONSTRAINT [DF_GLB_ScheduleDetails_DateCreated] DEFAULT (getdate()),
[LastDateModified] [datetime] NULL CONSTRAINT [DF_GLB_ScheduleDetails_LastDateModified] DEFAULT (getdate()),
[IsActive] [bit] NULL CONSTRAINT [DF_GLB_ScheduleDetails_IsActive] DEFAULT ((1))
IsMonday - IsSunday is basically a flag for each day if it's a daily type of schedule.
IsJan - isDec is a flag for each month if it's a monthly type of schedule.
Regarding 2nd table design, i am not sure this is a best approach though and I am happy to change if it's easy to query.
Hope this clear and I am appreciated your comment.
Thanks
View 1 Replies
View Related
Mar 7, 2008
Hello,
I have a table called Event, this table has two fields called StartDate and FinishDate. On my application I want the user to be able to search for all the events for a certain date range. I am having some trouble getting my head around the code and was wondering if someone could offer some advice.
Some more details... the user can enter a from date, a to date or both. I can have separate sql code for each situation. The event.startdate and event.finishdate are never null and may contain a time value.
Thanks.
View 9 Replies
View Related
May 14, 2007
Hi,I have a query which works for one day:SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTableWHERE DATE = '2007-05-11' AND SOME_STAT 1Returnssomething something 21 23 4How do I get this to work for a date range (e.g. DATE '2007-05-09')where I get:date something something22007-05-09 1 22007-05-09 3 42007-05-10 1 22007-05-10 3 42007-05-11 1 22007-05-11 3 4Thanks in advance!
View 2 Replies
View Related
Nov 4, 2006
I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query,
I found some query sample like:
select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12')
or
WHERE DateField BETWEEN @StartDate AND @EndDate
But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help
View 1 Replies
View Related
Dec 18, 2001
I need SQL to determine what the date range for the previous month was, ie
Start date 11/01/01 for 11/30/01.
The results will populate a drop down box for user queries.
View 1 Replies
View Related
Jan 22, 2008
hi, i'm using sql sserver 2005 and i have a time that i need to change to a datetime query (from the start of the day to the end of the day), whats the syntax for that? thx
View 9 Replies
View Related
Apr 30, 2007
Hello
I was wondering if someone could tell me the syntax for a simple date range query. Basically:
select * from TABLE where start date= and end date =
Thanks
View 3 Replies
View Related
Jul 12, 2012
how to add a date range filter to my database query. I want to be able to enter a date into ("E,6") and have VBA pull data >,= to that date range according to my "OPENED_DATE" dates. My code is pasted below:
'Create header names
wkbOne.Worksheets("Sheet1").Cells(1, 1).Value = "PART_NUMBER"
wkbOne.Worksheets("Sheet1").Cells(1, 2).Value = "SFC"
wkbOne.Worksheets("Sheet1").Cells(1, 3).Value = "OPERATION"
wkbOne.Worksheets("Sheet1").Cells(1, 4).Value = "NC_CODE"
wkbOne.Worksheets("Sheet1").Cells(1, 5).Value = "OPENED_DATE"
[code]....
View 1 Replies
View Related
May 14, 2015
I have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
Code:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);
Instead if try this I have error:
Code:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);
[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
View 2 Replies
View Related
Oct 14, 2015
This one is making my head hurt! Trying to figure out how to query for records between date range. The records have a start_date and an end_date field. The end_date field maybe null.
For example, say you wanted to see the records of everyone checked into a hotel during a given date range. You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.
fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.
View 7 Replies
View Related
Jan 24, 2008
I have a DB with the folloing fields... ArticleID, ArticleTitle, PublishDate(DateTime Field), EndPublishDate(DateTime Field).... What I want to do is show in a list view only the articles where the date is on or after the PublishDate and before or on the EndPubishDate.
I thought I could simply do something like this
SELECT ArticleTitle, PublishDate, PublishEndDate, PublishedFROM UserArticlesWHERE (Published = @Published) AND (PublishDate >= @PublishDate) AND (PublishEndDate <= @PublishEndDate)
Well I can't... can someone explain how I can do this please... I understand the the PublishDate and EndPublsihDate need a variable... The info is in the DB I just don't know how to do what I need to.
Thanks,
View 4 Replies
View Related
Dec 26, 2013
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
View 3 Replies
View Related
Mar 8, 2008
Hi,
My tables:
Product
- productID
- name
- price
Inventory
- productID
- stockCount
- timestamp
So each day the Inventory table has a new row for each productID with its stock count.
How can I create a report for the total products sold from one day to another? Or from what a dateStart from a dateEnd (i.e. a range)
Example:
ProductID StockCount TimeStamp
1 10 2008/03/07
1 7 2008/03/08
So you can see that 3 products were sold in the last day.
View 4 Replies
View Related
Dec 2, 2015
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
View 5 Replies
View Related
Aug 2, 2007
Hi,
I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue:
I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
Many Thanks
View 2 Replies
View Related
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
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
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
May 21, 2015
I have a data structure like this
UID , Name, amount, start date End Date
1 A 10 2015-05-01 00:00:00 2015-05-01 23:59:59
2 A 10 2015-05-02 00:00:00 2015-05-02 23:59:59
3 A 10 2015-05-03 00:00:00 2015-05-03 23:59:59
4 A 10 2015-05-04 00:00:00 2015-05-04 23:59:59
5 B 10 2015-05-05 00:00:00 2015-05-05 23:59:59
[code]...
View 5 Replies
View Related
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
View Related
