spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Grouping rows with a date range


A Left Outer Join and DateDiff Function should help you to filter the records. Finally Use Window Function to create GroupID's

create table #test 
(ADM_ID int,WH_PID int,WH_IN_DATETIME
DATETIME,WH_OUT_DATETIME  DATETIME)

INSERT #test
VALUES ( 1,9,'2014-10-12 00:00:00','2014-10-13
15:00:00'),
       (2,9,'2014-10-14 14:00:00','2014-10-15
15:00:00'),
       (3,9,'2014-10-16 14:00:00','2014-10-17
15:00:00'),
       (1,10,'2014-10-16 14:00:00','2014-10-17
15:00:00'),
       (2,10,'2014-10-18 14:00:00','2014-10-19
15:00:00')

SELECT Row_number()OVER(partition by a.WH_PID
ORDER BY a.WH_IN_DATETIME) Group_Id,
       a.WH_PID,
       a.WH_IN_DATETIME,
       b.WH_OUT_DATETIME
FROM   #test a
       LEFT JOIN #test b
              ON a.WH_PID = b.WH_PID
                 AND a.ADM_ID <> b.ADM_ID
where  Datediff(hh, a.WH_OUT_DATETIME,
b.WH_IN_DATETIME)BETWEEN 0 AND 24 

OUTPUT :

Group_Id    WH_PID  WH_IN_DATETIME     
    WH_OUT_DATETIME
--------    ------  -----------------------
-----------------------
1           9       2014-10-12 00:00:00.000
2014-10-15 15:00:00.000
2           9       2014-10-14 14:00:00.000
2014-10-17 15:00:00.000
1           10      2014-10-16 14:00:00.000
2014-10-19 15:00:00.000

Categories : SQL

Related to : Grouping rows with a date range
preg_split regex for splitting on a range but retaining some of that range as the string suffix
Use a lookbehind to match the boundary which exists after newline character. <?php $str = <<<EOT This is some text which I am working on. This text has whitespace before the new line but after this word Another line. EOT; $splits = preg_split("~(?<= )|p{Z}+(?! )~", $str); print_r($splits); ?> Output: Array ( [0] => This [1] => is [2] => some [3] =&

Categories : PHP
Check if a range of numbers are within another range in PHP, or MySQL
Uhm... with BETWEEN in MySQL: SELECT num1, num2 FROM table WHERE (num1 BETWEEN num3 AND num4) AND (num2 BETWEEN num3 AND num4); If you get a result from the query, the numbers are inside the range...

Categories : PHP
Grouping by two values in same table
You have to COUNT DISTINCT user ids (and ORDER BY ... DESC if you want the provided result): SELECT ship_type, COUNT(DISTINCT userid) as cnt FROM db1.MessageType5 GROUP BY ship_type ORDER BY cnt DESC See this fiddle.

Categories : SQL
Likert grouping with different levels in R
Your underlying levels are in reality the same, you just have to tell your data frame that they exist: foo[2:4] <- lapply(foo[2:4], factor, levels=1:9) Then you can plot. (But how the grouping argumnent works remains a mystery - it's not clear from the help of that package. likt <- likert(foo[,c(2:4)]) plot(likt)

Categories : R
Grouping dates by Periods in R
dat <-read.table(text="Date 09/01/2008 10/01/2008 11/01/2008 12/01/2008 01/01/2009 02/01/2009 03/01/2009 04/01/2009 05/01/2009 06/01/2009 07/01/2009 08/01/2009",head=T) dat$dt <- with( dat, as.Date(Date,format="%m/%d/%Y")) with(dat, c("2008", "2008.2009", "NA")[ findInterval( dt, c( as.Date("2008/09/01") ,

Categories : R
Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.