Calculate date range between two rows

by

Can you try with this?

WITH TEMP AS
(
    SELECT 
        RANK() OVER ( PARTITION BY MATERIAL ORDER BY DATEFROM ASC) AS ASC_RANK,
        RANK() OVER ( PARTITION BY MATERIAL ORDER BY DATEFROM DESC) AS DES_RANK,
        MATERIAL, COST, DATEFROM, DATETO
    FROM 
)
SELECT MATERIAL, COST, DATEFROM, DATETO
FROM TEMP
WHERE DES_RANK = 1 

UNION ALL 

SELECT T1.MATERIAL, T1.COST, T1.DATEFROM, (T2.DATETO - 1) AS DATETO
FROM TEMP T1
JOIN TEMP T2 ON T1.MATERIAL = T2.MATERIAL AND T1.ASC_RANK = (T2.ASC_RANK + 1)

More Related Contents:

  1. Error Code: 2013. Lost connection to MySQL server during query
  2. MySQL Workbench: How to keep the connection alive
  3. mySQL query to search all tables within a database for a string?
  4. INSERT INTO SELECT statement with more columns which is not in another table
  5. need to return two sets of data with two different where clauses
  6. SQL statement is ignoring where parameter
  7. quick selection of a random row from a large table in mysql
  8. Column calculated from another column?
  9. Error in MySQL when setting default value for DATE or DATETIME
  10. How does MySQL process ORDER BY and LIMIT in a query?
  11. MySQL: How to allow remote connection to mysql
  12. Generating a random & unique 8 character string using MySQL
  13. SQL: Repeat a result row multiple times, and number the rows
  14. sql join two table
  15. Compare dates in MySQL
  16. Cast int to varchar
  17. Invalid default value for ‘dateAdded’
  18. Import SQL file into mysql
  19. MySQL SELECT last few days?
  20. “where 1=1” statement [duplicate]
  21. Split a string and loop through values in MySQL stored procedure
  22. How to create dummy variable columns for thousands of categories in Google BigQuery?
  23. how to cast the hexadecimal to varchar(datetime)?
  24. MySQL Select Query – Get only first 10 characters of a value
  25. Most efficient way to store IP Address in MySQL [duplicate]
  26. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  27. Postgres LEFT JOIN with WHERE condition
  28. mysql select id and name from other table and join query
  29. SQL query with avg and group by
  30. Optimistic locking in MySQL

Leave a Comment