logo
down
shadow

SQL first date for the first occurrence of a value


SQL first date for the first occurrence of a value

By : user2172485
Date : October 23 2020, 08:10 PM
I hope this helps you . I have a table with clients and the history of their rating (low, medium, high) which gets assessed quarterly. , Here's one way to tackle your problem:
code :
CREATE TABLE #T (ClientID INT, RATING VARCHAR (20), eff_From DATE)

INSERT INTO #T VALUES
(111, 'high',  '20180930'  ), 
(111, 'high',  '20180630'  ) ,
(111, 'medium', '20180331' ) ,
(111, 'high',  '20171231'  ) ,
(111, 'high', '20170930'      ) ,
(111, 'low', '20170630'   ); 

WITH CTE AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY  eff_From) AS RowNumb
    FROM #T
)

SELECT C.*, 
      C2.RATING AS C2Rating,
      C2.eff_From AS C2EffFrom,
      CASE WHEN C.RATING <> C2.RATING THEN 1 ELSE 0 END AS RatingChanged 
INTO #T2
FROM CTE AS C
LEFT JOIN CTE AS C2 ON C.RowNumb = C2.RowNumb - 1

SELECT ClientID, MAX (C2EffFrom) AS MaxEffFrom
FROM #T2
WHERE RatingChanged = 1
GROUP BY ClientID


Share : facebook icon twitter icon
Mysql Find occurrence of start and end date range between two date columns

Mysql Find occurrence of start and end date range between two date columns


By : user3912915
Date : March 29 2020, 07:55 AM
I wish this help you From the above table, I will search between start and end date. For Ex., '2011-12-21' to '2011-12-23'. From the above image, 5th and 6th row will come under this date range. ie., 5th row will comprise of 21st and 22nd[2 dates] and 6th row comprise of 23rd[1 dates].
code :
SELECT
  SUM(
    IF(DATEDIFF(least(validto, '2011-12-23'),
                greatest(validfrom, '2011-12-21')) >= 0,
       DATEDIFF(least(validto, '2011-12-23'),
                greatest(validfrom, '2011-12-21')) + 1,
       NULL) * rateperday
 ) as total
FROM thedata;
How Occurrence date of an event is different from the start date?

How Occurrence date of an event is different from the start date?


By : Juan José García Lóp
Date : March 29 2020, 07:55 AM
Get all uid that first occurrence in some date

Get all uid that first occurrence in some date


By : Iok-Lim Ng
Date : March 29 2020, 07:55 AM
it fixes the issue Let's say you have the following table: , Assuming you have the right indexes:
code :
select t.*
from t
where time >= '2016-07-19' and time < '2016-07-20' and
      not exists (select 1
                  from t t2
                  where t2.uid = t.uid and
                        t2.time < '2016-07-19'
                 );
select uid
from t
group by uid
having min(time) >= '2016-07-19' and min(time) < '2016-07-20';
Getting the date of the first occurrence of value

Getting the date of the first occurrence of value


By : yuzhenliu
Date : March 29 2020, 07:55 AM
it should still fix some issue I hope this is what you are looking for,
code :
=IF(A2<>A3,IF(INDEX(A:B,MATCH(A2,$A$1:$A2,0),2)-B2=0,1,INDEX(A:B,MATCH(A2,$A$1:$A2,0),2)-B2),"")
T-SQL Grouping with LESS THAN {date} that breaks off on each occurrence of date

T-SQL Grouping with LESS THAN {date} that breaks off on each occurrence of date


By : Tama Solomon
Date : March 29 2020, 07:55 AM
With these it helps First - this is ignoring the question I raised in the comments above, and just allocates all rows to the expiry date on or after the use by date. You would need to rethink this if you need to split one use among multiple expiry dates
First, allocate an expiry date to each PointsUsed row. This is done by joining to all OrderPoints rows with an expiry date on or after the UseDate, then taking the minimum date.
code :
WITH allocatedPoints as 
(
    Select U.CustomerID, U.OrderPointsUsed, MIN(P.OrderPointsExpiry) as OrderPointsExpiry
    from CustomerOrderPointsUsed U 
    inner join CustomerOrderPoints P on P.CustomerID = U.CustomerID and P.OrderPointsExpiry >= U.OrderPointsUseDate
GROUP BY U.CustomerID, U.OrderPointsUseDate, U.OrderPointsUsed
)

Select P.CustomerID, P.OrderPoints, P.OrderPointsExpiry, 
    ISNULL(SUM(AP.OrderPointsUsed), 0) as used,
    P.OrderPoints - ISNULL(SUM(AP.OrderPointsUsed), 0) as remaining
from CustomerOrderPoints P
left outer join allocatedPoints AP on AP.CustomerID = P.CustomerID and AP.OrderPointsExpiry = P.OrderPointsExpiry
GROUP BY P.CustomerID, P.OrderPoints, P.OrderPointsExpiry
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org