logo
down
shadow

SQL Query - Group consecutive items based on condition


SQL Query - Group consecutive items based on condition

Content Index :

SQL Query - Group consecutive items based on condition
Tag : sql , By : Paul
Date : November 24 2020, 03:01 PM

around this issue I have no idea what to name this question. Please forgive the title if it does not fit the question. I have data of production and unscheduled maintenance events, ordered by Equipment and then Moment. See below for table (see at the bottom of the question for text version): , Try this grouping
select min(Moment), Equipment, sum(duration)
from (
  select *,
    case DowntimeType when 'Unscheduled Maintenance' 
      then row_number() over(partition by Equipment, DowntimeType order by Moment) 
      else row_number() over(partition by Equipment order by Moment) - row_number() over(partition by Equipment, DowntimeType order by Moment) end r
   from myTable
) t
where r > 0 -- must start with 'Unscheduled Maintenance' 
group by Equipment, r
order by Equipment, r

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

sql query to return items based on text and group ID


Tag : sql , By : lili
Date : March 29 2020, 07:55 AM
this will help Assuming you're using SQL Server. since you know what columns your stored proc returns you can do this:
create #tempTable ( columnsSprocReturns )
insert into #tempTable
exec yourSproc

select columns 
from   #tempTable
where  someColumn = 'someValue'

drop table #tempTable

Condition based on COUNT(*) in query with GROUP BY clause


Tag : sql , By : user179271
Date : March 29 2020, 07:55 AM
around this issue I'm given a table Employee with 300,000 records that contains the following fields: , This is the query you're looking for:
SELECT T.Location_Id
     ,COUNT(T.Location_Id) AS [nbRecords]
FROM yourTable T
GROUP BY T.Location_Id
HAVING COUNT(T.Location_Id) > 75000

SQL Query Select based on a condition with Group By


Tag : mysql , By : Helpful Dude
Date : March 29 2020, 07:55 AM
To fix the issue you can do I have a Batch entity and a Job entity. A Batch can have many Jobs. , Use conditional aggregation to check your requirements:
SELECT
    b.id,
    b.name,
    b.user_id,
    CASE WHEN SUM(CASE WHEN j.status = 'REVIEW' THEN 1 ELSE 0 END) > 0
         THEN 'YES'
         ELSE 'NO' END AS status
FROM Batches b
INNER JOIN Jobs j
    ON j.batch_id = b.id
GROUP BY
    b.id;

query to count based on condition with group by in mongodb


Tag : mongodb , By : user185939
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I have data in mongodb like , you can use $cond at $group part like this.
 { $group: {
        "_id": "$_id.uid",
        "winnerCount": { $sum: {$cond: [{$gt: ['$winnings.coins', 0]}, 1, 0]} }, // count of winners who have more than 0 coins
        "looserCount": { $sum: {$cond: [{$gt: ['$winnings.coins', 0]}, 0, 1]} }  // count of loosers who dont have any coins
    } }

SQL query to remove some items based on condition


Tag : sql-server , By : jgood
Date : March 29 2020, 07:55 AM
With these it helps This appears to get the answer you're after, but I doubt it's the "best" (and it'll scale awfully):
WITH CTE AS(
    SELECT V.CC,
           COUNT(CASE V.CC WHEN 'Ind' THEN 1 END) OVER () AS Ind,
           COUNT(CASE V.CC WHEN 'UK' THEN 1 END) OVER () AS UK,
           COUNT(CASE V.CC WHEN 'FR' THEN 1 END) OVER () AS FR
    FROM (VALUES ('IND'),
                 ('UK'),
                 ('FR')
                 ) V(CC))
SELECT CASE WHEN UK > 0 AND FR > 0 AND C.CC = 'UK' THEN 'EU' ELSE C.CC END AS CC
FROM CTE C
WHERE (Ind > 0 AND UK > 0 AND FR > 0 AND C.CC IN ('UK','IND'))
   OR (Ind = 0 AND UK > 0 AND FR > 0 AND C.CC = 'UK')
   OR (Ind > 0 AND (UK = 0 OR FR = 0));
Related Posts Related QUESTIONS :
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • What is the difference between check and foreign key?
  • How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
  • SQL order by oldest from Unix Timestamps
  • How to convert decode function in oracle to standard big query
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com