Transact SQL :: Select First And Last Record For Certain Date Range
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
ADVERTISEMENT
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
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
Apr 7, 2006
I HAVE THIS SQL STATEMENT WHICH GET THE MIN(AMOUNT) and MAX(AMOUNT) and DISPLAY IT IN A SINGLE ROW.
HOW CAN I GET THE RECORD BEFORE THE MAX (AMOUNT) INBETWEEN DATES IN CASE THERE ARE 4 RECORDS WITH SAME EMPLOYEENO IN THE DATE RANGE SPECIFICIED ? THIS IS SORTED BY EMPLOYEENO AND STARTDATE DESC
SELECT EmployeeNo, StartDate AS StartDate, MIN(Amount) AS oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2001-06-01' AND startdate <= '2007-06-02') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND startdate >= '2001-06-01' AND startdate <= '2007-06-02'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate DESC
THANK YOU SO MUCH ...
View 2 Replies
View Related
Aug 31, 2006
I am currently using this SQL code to capture some records over the last 2 months and it has been working great. I am now being asked if I can change this code with specifications:
1) Scan the records in the system until the count (*) as Volume reaches 30 because they prefer that as a denominator when figuring an average
2) Only run the scan for a maximum of 6 months.
So, there will most likely be some records that do not reach a volume number of 30 in this date range. In this instance we will just take the maximum volume number reached at 6 months.
So, how can I write this so it will build the file each time a record has reached the maximum of 30 and keep scanning back until we reach 6 months? If someone could lead me in the right direction on the proper order of the methodology in my code to accomplish these results it would be greatly appreciated. Desperate!
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (mm, -2, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (mm, 2 , @startdate)
select distinct
pe1.patev_loc_id as LocID,
pp_cproc_id_r as ProcID,
count (*) as Volume,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time)) as Minutes,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time))/count(*) as AvgMin
from
risdb_rch08_stag..performed_procedure (index pp_serv_time_r_ndx),
risdb_rch04_stag..patient_event pe1,
risdb_rch04_stag..patient_event pe2
where
pp_service_time_r between @Startdate and @Enddate
and pp_asn_req_no = pe1.patev_asn_req_no
and pp_asn_seq_no = pe1.patev_asn_seq_no
and pp_status_v = 'CP'
and pp_rep_id > 0
and pe1.patev_event_code = 'PB'
and (pp_asn_req_no = pe2.patev_asn_req_no
and pp_asn_seq_no = pe2.patev_asn_seq_no
and pe2.patev_event_code = 'PL')
and datediff (mi, pe1.patev_event_time, pe2.patev_event_time) > 0
group by
pe1.patev_loc_id , pp_cproc_id_r
View 1 Replies
View Related
Mar 25, 2008
I need to create records based on date range on monthly basis, please help.
Here is an example:
ID Startdate EndDate
1 20070301 20070522
and I need to create the following data set based on start and end date range
ID Startdate EndDate
1 20070301 20070331
1 20070301 20070430
1 20070301 20070522
View 8 Replies
View Related
Jul 31, 2015
I'm trying to move some logic that I have currently within a program and putting it into SQL instead.
My table has the following 3 fields that are of interest to me: StartDate (DateTime), StopDate(DateTime), Length (int)
Length is calculated based on StopDate - StartDate and is expressed to the nearest minute.
What I want to do is query the Db giving a start date and end date and return all records that fall within that date range. I then want to present that data such that the earliest date is set to the start date criteria, the last to the end date criteria and the length recalculated.
Say for example I query for results between 01/02/2015 07:00:00 and 01/02/2015 19:00:00 and I get the following:
Start Stop Length
01/02/2015 06:30:00 01/02/2015 07:05:00 35
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:15:00 555
I would like the output to show as
Start Stop Length
01/02/2015 07:00:00 01/02/2015 07:05:00 5
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:00:00 540
Is there a way to do this?
View 5 Replies
View Related
May 26, 2015
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
View 9 Replies
View Related
Aug 20, 2015
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
View 14 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
Jul 23, 2015
I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:
declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,
[code]....
Others says I need to use exec sp_executesql N'' but how do I use it using my code above?
View 7 Replies
View Related
Jun 29, 2015
I've data like below:
Now, I've to get active data for a particular date range. Let me explain the active data definition as below:
StartDate : 01-Jul-2015
EndDate : 31-Dec-2015
It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.
I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.
View 4 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
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
Nov 18, 2015
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL
[code]...
View 7 Replies
View Related
Nov 17, 2015
My scenario is: a person has many events, all based on a date. I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.
DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE
[Code] ....
I would expect the groups to look something like below:
Is this where a recursive CTE may be used?
View 6 Replies
View Related
Oct 8, 2015
I need to recognize only one month in a date range e to make a proportion of the quantity. Practically, period 31-08-2015 - 30-09-2015 is 31 days, 1 belonging August and 30 belonging September so 3.2258% of the quantity must belong August and 96.7742% September. The quantity 200, so 193.54 belong September (That's what I need to achieve). Range 01-09-2015 / 30-09-2015 Qty 500, all 500 belong September.
Range 01-07-2015 / 20-08-2015 Qty 2500 0 belong September. A little bit more complicated if I got 25-06-2015 / 16-12-2015. it 30 day for September and with a datediff I can count the days and make a proportion. I can write piece by piece the code but I'd prefer of course to have only one query for this.
The DDL:
create table forum (idd int, byfrom date, byto date, qty int)
insert into forum values
(1,'2015-06-15','2015-08-18',300),(2,'2015-09-16','2015-10-04',400),(3,'2015-07-28','2015-09-27',1000),
(4,'2015-09-01','2015-09-30',500),(5,'2015-09-03','2015-09-03',300),(6,'2015-08-02','2015-09-02',100),
(7,'2015-07-01','2015-07-30',500),(8,'2015-06-03','2015-12-08',500),(9,'2015-09-01','2015-09-30',500),
(10,'2015-08-04','2015-09-04',300)
View 14 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
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
Oct 7, 2015
how to hard code mention date range in my SP to get expected results in my query 01/01/2012 to 12/31/2012
DECLARE @ACCOUNT AS INT
DECLARE @POSTING_DATE AS DATETIME
DECLARE @FIRST_POSTING_DATE AS DATETIME
SET @POSTING_DATE = {?POSTING_DATE}
SET @ACCOUNT = {?ACCOUNT}
[code]...
View 3 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
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
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
Oct 20, 2015
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
View 2 Replies
View Related
May 4, 2015
I have records like this
id address1 date
1 xyz 01/01/2013
1 abd 01/01/2014
2 dfg 01/03/2015
Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.
So here result should be
1 abd 01/01/2014
2 dfg 01/03/2015
View 12 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
Sep 22, 2015
I have one table :
file _target
which has below records..
file_target_ID is identity column which will repeat per files_ID
Now, i just shown Target log for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date column
Like this ..
Target Date New Column
2015-09-09 00:00:00.000 2015-09-16 00:00:00.000
2015-09-16 00:00:00.000 2015-09-25 00:00:00.000
2015-09-25 00:00:00.000 New Target date after 25-9-2015
how to do this in SQL Server ??
using Cross Apply ?? Row_Number() ??
View 3 Replies
View Related
Aug 31, 2015
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
View 2 Replies
View Related
Sep 21, 2015
I have created a sample Table with inserting below data,
ID Subject CreatedDate
1 Test 2015-09-20 22:59:07.373
2 Test 2015-09-21 09:16:58.290
3 Test 2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?
View 14 Replies
View Related
Aug 4, 2015
I built the following query to add the position of a record based on a certain date of the transaction of a customer:
select
CustomerID
, Product
, purchasedate
[code]....
View 6 Replies
View Related
Aug 20, 2015
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
View 6 Replies
View Related
Jun 29, 2015
I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?
View 8 Replies
View Related
Sep 16, 2015
My tables look like this:
Users //
table
UserID // pk
UserName // varchar
UserFamilyName // varchar
User_Friends //
table
FriendsID // pk
UserID // fk
FamilyName // varchar
MY query:
SELECT
U.UserFamilyName, F.FamilyName
FROM
Users U LEFT
JOIN User_Friends
F ON U.UserID =
F.UserID
WHERE
U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
View 10 Replies
View Related
