Combine date ranges in a table

This question already has an answer here:

  • How do I group on continuous ranges 5 answers

I have the following table

id price startdate enddate product 1 112 12/05/2015 12/05/2015 A 2 112 13/05/2015 13/05/2015 A 3 112 14/05/2015 14/05/2015 A 4 112 15/05/2015 15/05/2015 A 5 732 16/05/2015 16/05/2015 A 6 732 17/05/2015 17/05/2015 A 7 112 18/05/2015 18/05/2015 A 8 112 19/05/2015 19/05/2015 A 9 112 20/05/2015 20/05/2015 A

I need to get the following result

id price startdate enddate product 1 112 12/05/2015 15/05/2015 A 2 732 16/05/2015 17/05/2015 A 3 112 18/05/2015 20/05/2015 A

-------------Problems Reply------------

You can use DATEDIFF with ROW_NUMBER(). Something like this

Query

;WITH CTE as
(
SELECT DATEDIFF(day, 0, startdate) - ROW_NUMBER()OVER(partition by price order by startdate) r,*
from prod
)
SELECT ROW_NUMBER()OVER(ORDER BY MIN(id) ASC) as id,price,product,MIN(startdate) startdate,MAX(enddate) enddate
FROM cte c1
GROUP BY price,product,r

Output

id price product startdate enddate
1 112 A 2015-05-12 2015-05-15
2 732 A 2015-05-16 2015-05-17
3 112 A 2015-05-18 2015-05-20

SQL Fiddle

Using Common Table Expression like this How do I group on continuous ranges surely can achive what you ask, but you might want to take a look at below discussion: Date Range for set of same data

Category:sql Views:4 Time:2019-01-04
Tags: sql sql server

Related post

  • Query - Comparing a date to a date range in another table 2013-09-21

    Don't know if this can be done or not. I have a table to define accounting months (there are 14 records in the table). I have a query that has selected certain data based on a date range. I want to create a query that compares the date in the query t

  • Need help with a SQL query selecting date ranges from a table of period quarters 2010-07-07

    I have a table called Periods that looks like this PeriodID | PeriodYear | PeriodQuarter 7 | 2009 | 1 8 | 2009 | 2 9 | 2009 | 3 10 | 2009 | 4 11 | 2010 | 1 12 | 2010 | 2 Each row in the table represents 1 of the 4 quarters of the year (like 3-monthly

  • SQL Server 2008: Count Number of Keys In Multiple Date Ranges Across Multiple Tables 2010-01-18

    Thanks to the people who answered my question this morning: http://stackoverflow.com/questions/2087664/sql-server-2008-count-number-of-keys-in-multiple-date-ranges This is an extension of it and I'm not clear how to generalize the previous solutions

  • Combining Data from two MySQL tables 2010-04-30

    I'm trying to combine data from two tables in MySQL with PHP. I want to select all the data (id, title, post_by, content and created_at) from the "posts" table. Then I would like to select the comment_id COUNT from the "comments" table IF the comment

  • How to find sum of a column between a given date range, where the table has only start date and end date 2012-04-19

    I have a postgresql table userDistributions like this : user_id, start_date, end_date, project_id, distribution I need to write a query in which a given date range and user id the output should be the sum of all distributions for every day for that g

  • How do I pull up dates in a specific date range from a table that has multiple columns of dates? 2014-03-26

    I am working with 1 table: Rows: Employee Columns (all cells contain different due dates, 9 columns total): DL Renew CPR Renew FA Renew etc. I want to be able to pull up a query or report that will show what is due within a specified date range. I am

  • Delete data range in pivot table/chart 2014-08-10

    If I have a table of data. Create a pivot table from that data Then create a chart from the pivot table. It seems sometimes you can select one bar (if it is a bar chart), and click delete. and the data will be taken out of the graph. But sometimes yo

  • Filter by date range in pivot table 2012-04-24

    Hi, I have a pivot table with sales data by product and country which currently has "sales date" in the report filter field. The limitation this has is that in order to filter by a 6-month range from 5 years of data, each date has to be either checke

  • Combining Data from Two Access Tables 2012-08-30

    I have two tables in Access; one contains sales data, the other contains forecast data. These both contain an item number field. There can be sales without a forecast and vice versa. I need to combine so I have sales and forecast information for each

  • Vlookup a value within a date range in another table with a different date range 2013-05-03

    Hi! I have a worksheet in which I have to bring an amount from another table but there are multiple criterias that have to be met. ie: this is what I am looking for Contract id dates price? 12345 3/1/2009 3/31/2009 this is the data that I am looking

  • How to select data between date ranges from 2 tables 2011-07-11

    Let suppose I have two table Taxes Products I want to select sum of product values multiplied by their respective VAT value in taxes table as a percentage where the date of the product is less than date of tax and mDate is less than product date, gro

  • Combine data from multiple Access tables to produce address label, preferring PO box to Physical Address 2012-02-01

    I have an Access 2007 database that has 3 tables, each the same. Each of these tables is for a different telemarketer to enter leads. It is necessary for there to be multiple tables as each marketer must be treated as a separate entity. Each of these

  • Combining multiple data ranges into one pivot table 2012-05-06

    Hello, I am trying to combine multiple data ranges from four different worksheets within the same workbook into one master pivot table. Each data range has the same column headers and measures. My issue is that when I combine these data ranges, the p

  • Combining split date ranges in a SQL query 2008-09-26

    I'm working on a query that needs to have some data rows combined based on date ranges. These rows are duplicated in all the data values, except the date ranges are split. For example the table data may look like StudentID StartDate EndDate Field1 Fi

  • Comparing two date ranges within the same table 2010-04-05

    I have a table with sales per store as follows: SQL> select * from sales; ID ID_STORE DATE TOTAL ---------- -------- ---------- ------------------------------- 1 1 2010-01-01 500.00 2 1 2010-01-02 185.00 3 1 2010-01-03 135.00 4 1 2009-01-01 165.00

  • What database table layout should I use for quick retrieval of aggregated / distinct data for a date range? 2011-10-05

    I am writing a web app to analyze my web server log. I plan to have an SQL job, run daily, to denormalize my web server log in a SQL database, so that the web app does not read the raw web server log. I would like for the web app user to input a date

  • Select records where date not in table of multiple date ranges SQL Server 2012-02-02

    I have a table in SQL Server which holds various date ranges. Table has a start date and an end date as below:- start_date end_date ======================= ======================= 2011-02-21 00:00:00.000 2011-02-25 00:00:00.000 2011-04-18 00:00:00.00

  • Finding complement date ranges? 2009-12-28

    I have two tables both of which have columns StartDate and EndDate. I'm trying to return a single resultset that contains all date ranges from one table (TableA), and all complement date ranges from the other one (TableB). CREATE TABLE [dbo].[TableA]

  • Date range/query problem 2010-06-12

    I have a table with 3 fields: id datestart dateend I need to query this to find out if a pair of dates from a form are conflicting i.e table entry 1, 2010-12-01, 2010-12-09 from the form 2010-12-08, 2010-12-15 (edited, typo) select id from date_table

  • ORACLE SQL Date range intersections 2010-08-13

    I have a table T1, it contains a NAME value (not unique), and a date range (D1 and D2 which are dates) When NAME are the same, we make a union of the date ranges (e.g. B). But as a result (X), we need to make intersection of all the date ranges Edit:

  • How do I use PHP and MYSQL to fill fields with in a form when the data comes from multiple tables? 2012-03-14

    Im working on a project where i want the user to enter his/her username and password and if it is correct have a php script fill in fields of a table below the small login section. Is this possible? This is what i have so far and im kinda stuck any s

  • How can I select data from multiple mysql tables in this scenario? 2012-04-05

    Ok I have a bit of a weird scenario, and I can't change the database structure, because I don't have the permission for that. For the problem, i have three tables table 1- id(int) | name(varchar) | link_id(int) | link_to(tinyint) ....................

  • Data adjacent to piviot table can't be shown in a pivot chart 2015-01-01

    I'm a long time user of Excel. Ever since upgrading to Office 2010, I have had difficulty with pivot charts. I used to be able to use a chart to show continuity in trend by graphing data within a pivot table and continuing beyond the pivot table inco

  • How do I combine multiple data ranges as sources for a single pivot table? 2012-08-12

    I need to combine multiple data ranges as sources in a single pivot table. For example, one tab lists buildings with street addresses, square footages, etc. (one row per building). Another tab lists building operating expenses for maintenance, cleani

  • Combine overlapping date ranges in SQL Server 2011-08-10

    I have a table with millions of rows in it that require that date spans with a certain "contract number" and "service code" not overlap. There are typically 2-4 rows with dates that overlap that must be combined into a single record. Records which do

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.164 (s). 11 q(s)