logo
down
shadow

database db2: Group by custom date range


database db2: Group by custom date range

By : Tartan Stag
Date : November 19 2020, 01:01 AM
Hope that helps You need a function that maps the creation_time to your wanted intervals. Something like:
code :
select monthname(CREATION_TIMESTAMP)
     , day(CREATION_TIMESTAMP) + 1 - mod(day(CREATION_TIMESTAMP),5)
     , count(1)
from ...
group by monthname(CREATION_TIMESTAMP)
    , day(CREATION_TIMESTAMP) + 1 - mod(day(CREATION_TIMESTAMP),5)
with t(n) as  ( values date('2014-12-01') 
                union all 
                select n + 1 day from t 
                where n < '2014-12-20'
              ) select distinct day(n) + 1 - mod(day(n),5), day(n) + 5 - mod(day(n),5) 
                from t;

1           2          
----------- -----------
SQL0347W  The recursive common table expression "LELLE.T" may contain an 
infinite loop.  SQLSTATE=01605

      1           5
      6          10
     11          15
     16          20
     21          25
with t(n) as  ( 
    values date('2014-12-01') 
    union all 
    select n + 1 day 
    from t where n < '2014-12-20' 
) 
select day(n)
     , mod(day(n),5)
     , day(n) + 1 - mod(day(n),5) 
from t

      1           1           1
      2           2           1
      3           3           1
      4           4           1
      5           0           6
      6           1           6
      7           2           6
      8           3           6
      9           4           6
     10           0          11
     11           1          11
     12           2          11
     13           3          11
     14           4          11
     15           0          16
     16           1          16
     17           2          16
     18           3          16
     19           4          16
     20           0          21


Share : facebook icon twitter icon
Query a MySQL Database and Group By Date Range to Create a Chart

Query a MySQL Database and Group By Date Range to Create a Chart


By : Mehmet Akif Alper
Date : March 29 2020, 07:55 AM
Hope that helps Ok so if you are trying to get a count of records in a certain state at a certain time, I think a stored proc might be necessary.
code :
CREATE PROCEDURE spStatesAtDate
    @Date datetime,
    @StateId int
AS
BEGIN
   SET NOCOUNT ON;

   SELECT COUNT(*) as Count
   FROM ticket_table t1
   WHERE to_state = @StateId AND status_changed_date < @Date
        AND status_changed_date = (SELECT MAX(status_changed_date) FROM ticket_table t2 where t2.ticket_num=t1.ticket_num AND status_changed_date < @Date)
END
EXEC spStatesAtDate @Date='2011-01-02 12:00:00', @StateId=2
Pick Date Range, Count multiple Records in range based off condition, group by shift

Pick Date Range, Count multiple Records in range based off condition, group by shift


By : Nayying Kapolo
Date : March 29 2020, 07:55 AM
seems to work fine So I am new to SQL World, so please forgive how ignorant this question might be. I will likely need to do a stored procedure for this eventually but trying to get it to work in a query before I go to the next step. , I'm still confused what you want but I think you need this
code :
SELECT COUNT(CASE WHEN ( [B] BETWEEN 1 AND 9 )
                       AND ( [Time] BETWEEN '2014-01-01 00:00:00' AND '2014-02-01 00:00:00' ) THEN 1
                  ELSE NULL
             END) AS '6f'
       ,COUNT(CASE WHEN ( [B] = 10 )
                        AND ( [Time] BETWEEN '2014-01-01 00:00:00' AND '2014-02-01 00:00:00' ) THEN 1
                   ELSE NULL
              END) AS '5E'
       ,COUNT(CASE WHEN ( [B] BETWEEN 11 AND 13 )
                        AND ( [Time] BETWEEN '2014-01-01 00:00:00' AND '2014-02-01 00:00:00' ) THEN 1
                   ELSE NULL
              END) AS '4D'
       ,COUNT(CASE WHEN ( [B] = 14 )
                        AND ( [Time] BETWEEN '2014-01-01 00:00:00' AND '2014-02-01 00:00:00' ) THEN 1
                   ELSE NULL
              END) AS '1A'
       ,COUNT(CASE WHEN ( [A Step] BETWEEN 1 AND 25 )
                        AND ( [Time] BETWEEN '2014-01-01 00:00:00' AND '2014-02-01 00:00:00' ) THEN 1
                   ELSE NULL
              END) AS '2B'
       ,COUNT(CASE WHEN [B Step] = 0
                        AND [ A Step] = 0
                        AND ( [Time] BETWEEN '2014-01-01 00:00:00' AND '2014-02-01 00:00:00' ) THEN 1
                   ELSE NULL
              END) AS '3C'
    FROM dbo.[1_Data]
mysql - select count multiple tables, group by week, custom date range

mysql - select count multiple tables, group by week, custom date range


By : Jack Dell
Date : March 29 2020, 07:55 AM
around this issue For example, I have 2 tables and a date range (1 dec 2015 - 10 jan 2016). , I would start off with something like this:
code :
select ((week(dateb) - week('2015-12-01')) + 1) as week_number, count(a.dateb) as userdates 
from users as a
where dateb between '2015-12-01' and '2016-01-01'
group by week(dateb)
order by week(dateb);
select ((week(dateb2) - week('2015-12-01')) + 1) as week_number, count(dateb2) as birthdays 
from birthdays
where dateb2 between '2015-12-01' and '2016-01-01'
group by week(dateb2)
order by week(dateb2);
Laravel - PHP/MySQL date range filter against date range in database

Laravel - PHP/MySQL date range filter against date range in database


By : alexcvc
Date : March 29 2020, 07:55 AM
With these it helps I am working on an application using Laravel and I am trying to filter records from the database. Here is the criteria: , Your initial excluded_period can't be between your start and end:
code :
          ->whereNotBetween('excluded_period_start', [$start, $end])
          ->whereNotBetween('excluded_period_end', [$start, $end])
How to group Pandas DataFrame dates into custom date range bins using groupby/cut

How to group Pandas DataFrame dates into custom date range bins using groupby/cut


By : Rob F
Date : March 29 2020, 07:55 AM
I hope this helps you . This works if you convert all your dates form dtype string to datetime.
code :
df1['date'] = pd.to_datetime(df1['date'])

df2['End Dates'] = pd.to_datetime(df2['End Dates'])

df2['Start Dates'] = pd.to_datetime(df2['Start Dates'])

df1['HDD'] = df1['HDD'].astype(float)

df1.groupby(pd.cut(df1['date'], df2['Start Dates'])).agg({'HDD': sum})
                           HDD
date                          
(2015-01-01, 2015-02-15]   7.5
(2015-02-15, 2015-03-14]  36.0
(2015-03-14, 2015-04-14]   NaN
df1.groupby(pd.cut(df1['date'], df2['Start Dates'], labels=df2.iloc[:-1,1])).agg({'HDD': sum})
        HDD
date       
One     7.5
Two    36.0
Three   NaN
Related Posts Related Posts :
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org