Transact SQL :: Grouping Records With A Date Range Into Islands
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
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.
My company is unusual in that our accounting periods are not actual months. We have what we call "Red Fridays." These are spaced 3-5 weeks apart. So, my company doesn't care what happened in the month of April, but they care what happened between April 4 and May 2 because these are the Red Fridays.
So, I have created a database with a table called "RedFridays" with the dates for this year. I want to combine this with various tables in our ERP database. I use a Left Outer Join between the Red Friday Dates and the corresponding date in the ERP database.
I need to create a custom grouping formula which accomplishes the following:
1. Subtract a certain number of months from today's date to determine which Red Friday would be the correct starting date. 2. Group records by date between that Red Friday and the second one. This would be listed as something like "Month 1". 3. Continue grouping in this way to the present date and that Red Friday range.
Can anyone point me in the right direction? Any help would be greatly appreciated.
I have to find the records with in a paricular dates(from date and to date). In some cases @FromDate or @ToDate could be null. in the following query, when i am passing values @FromDate and @ToDate and execute the SP i am getting records which are not in the given range.
SELECT * FROM TABLE P WHERE CONVERT(VARCHAR(10), P.[FromDate], 101) BETWEEN CASE When @FromDate IS NULL Then CONVERT(VARCHAR(10),@minFromDate, 101) ELSE CONVERT(VARCHAR(10), @FromDate, 101) END AND CASE WHEN @ToDate IS NULL Then CONVERT(VARCHAR(10), @maxToDate, 101)
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:
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
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?
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.
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:
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.
If you have a table that has records which are applicable over a date range, is there a preferred design for dealing with the dates?
A simple example might be an employee table, which might have multiple employees, with each employee have multiple records, each record being applicable over a particular date range.
For instance:
Code: EmpID EmployeeName StartDate EndDate AdditionalFieldsOfData 00001 Jones, Jerry 4/6/2011 8/10/2011 00001 Jones, Jerry 8/11/2011 1/7/2012 00001 Jones, Jerry 1/8/2011 12/31/3000 00002 Fredricks, Fred 8/3/2011 10/15/2011 00002 Fredricks, Fred 10/16/2011 12/31/3000
One could model this table with an implied EndDate (of 12/31/3000), as in:
Code: EmpID EmployeeName StartDate AdditionalFieldsOfData 00001 Jones, Jerry 4/6/2011 00001 Jones, Jerry 8/11/2011 00001 Jones, Jerry 1/8/2011 00002 Fredricks, Fred 8/3/2011 00002 Fredricks, Fred 10/16/2011
Or, one could imply the beginning date and store the first record's beginning date elsewhere, in a date hired table, or in additional field on each record. As in:
Code: EmpID EmployeeName EndDate HireDate AdditionalFieldsOfData 00001 Jones, Jerry 8/10/2011 4/6/2011 00001 Jones, Jerry 1/7/2012 4/6/2011 00001 Jones, Jerry 12/31/3000 4/6/2011 00002 Fredricks, Fred 10/15/2011 8/3/2011 00002 Fredricks, Fred 12/31/3000 8/3/2011
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008' @EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
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?
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.
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)
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
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}
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
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
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID StudentName BirthDate 1 ABC 2002-12-25 2 VWX 2002-01-01 3 STU 2002-02-03 4 PQR 2002-03-05
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)' Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)
1.) Latest Product type PFE should be A (active) status for the particular ID 2.) At the same time ALL the latest PRI should be C(closed) status for the same ID
I have give example with 3 scenarios and desired output
1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result 2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result
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!!
I need to group data in a sql report by currency values. I have a report that shows part numbers and 4 columns making up manufacturing costs and then a 5th column showing total costs. I would like to add a 6th column that groups the total costs by ranges. The example would be, group all costs > 20000, group all costs 1000 > x > 20000 , and finally all costs < 1000.
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SAMPLE_TABLE]( [DESPATCH_ID] [nvarchar](30) NOT NULL, [SAMPLE_ID] [nvarchar](30) NOT NULL
