logo
Tags down

shadow

selecting data for the first 12 months


By : Pulkesh Jindal
Date : October 18 2020, 01:08 AM
This might help you Another option is to unpivot a targeted subset and then pivot
Example
code :
;with cte as (
        Select A.Year
              ,Mnth   = DateName(MONTH,B.Per)
              ,B.Per 
              ,B.Value
         From  YourTable A
         Cross Apply ( values (1 ,cast(str(Year)+'-01-01' as date),[January])
                             ,(2 ,cast(str(Year)+'-02-01' as date),[February])
                             ,(3 ,cast(str(Year)+'-03-01' as date),[March])
                             ,(4 ,cast(str(Year)+'-04-01' as date),[April])
                             ,(5 ,cast(str(Year)+'-05-01' as date),[May])
                             ,(6 ,cast(str(Year)+'-06-01' as date),[June])
                             ,(7 ,cast(str(Year)+'-07-01' as date),[July])
                             ,(8 ,cast(str(Year)+'-08-01' as date),[August])
                             ,(9 ,cast(str(Year)+'-09-01' as date),[September])
                             ,(10,cast(str(Year)+'-10-01' as date),[October])
                             ,(11,cast(str(Year)+'-11-01' as date),[November])
                             ,(12,cast(str(Year)+'-12-01' as date),[December])
                     ) B (Mnth,Per,Value)
) , cte2 as ( Select FirstPer = max(Per) from cte where Value is null and Year=(Select min(Year) from cte) )
Select *
 From (
        Select OutPut = case when Per > DateAdd(MONTH,12,FirstPer) then 2 else 1 end
               ,Year,Mnth,Value 
         From  cte 
         Cross Join cte2
      ) A
 Pivot (sum([Value]) For [Mnth] in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]) ) p


Share : facebook icon twitter icon

Selecting data from a period of full months based on the current date


By : user2392660
Date : March 29 2020, 07:55 AM
Does that help You can calculate an arbitrary date based on the current date using PHP functions mktime and strtotime.
So instead of using awkward, unreadable SQL functions I'd rather calculate the desired time period in PHP directly:
code :
$period_end = mktime(0,0,0, date('n'), 1, date('Y')); // first of current month, time 00:00:00
$period_begin = strtotime('-3 months', $period_end);
$query = "SELECT * FROM table WHERE publish_up BETWEEN '" .
         date('Y-m-d H:i:s', $period_begin) ."' AND '" .
         date('Y-m-d H:i:s', $period_end) ."' AND " 
         "created_by = '$editorID' AND state = 1 AND sectionid in (1,2,3)";

Selecting Data from Last X Months


By : Andy Turner
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I want to select data from the last 4 months. I would want to start from the beginning of the month, so if it is currently July 28, I would want data from March1-July28. , You can use pd.tseries.offsets.MonthBegin.
code :
import pandas as pd

# simulate some data
# =================================
np.random.seed(0)
date_rng = pd.date_range('2015-01-01', '2015-07-28', freq='D')
df = pd.DataFrame(np.random.randn(len(date_rng)), index=date_rng, columns=['col'])
df

               col
2015-01-01  1.7641
2015-01-02  0.4002
2015-01-03  0.9787
2015-01-04  2.2409
2015-01-05  1.8676
2015-01-06 -0.9773
2015-01-07  0.9501
2015-01-08 -0.1514
...            ...
2015-07-21 -0.2394
2015-07-22  1.0997
2015-07-23  0.6553
2015-07-24  0.6401
2015-07-25 -1.6170
2015-07-26 -0.0243
2015-07-27 -0.7380
2015-07-28  0.2799

[209 rows x 1 columns]

# processing
# ===============================
start_date = df.index[-1] - pd.tseries.offsets.MonthBegin(5)
# output: Timestamp('2015-03-01 00:00:00')

df[start_date:]

               col
2015-03-01 -0.3627
2015-03-02 -0.6725
2015-03-03 -0.3596
2015-03-04 -0.8131
2015-03-05 -1.7263
2015-03-06  0.1774
2015-03-07 -0.4018
2015-03-08 -1.6302
...            ...
2015-07-21 -0.2394
2015-07-22  1.0997
2015-07-23  0.6553
2015-07-24  0.6401
2015-07-25 -1.6170
2015-07-26 -0.0243
2015-07-27 -0.7380
2015-07-28  0.2799

[150 rows x 1 columns]

How to bind current months data to calendar bootstrap, cause only previous months are binding


By : nelson_2013
Date : March 29 2020, 07:55 AM
wish of those help So the issue was that the .calendar events doesnt see single dates as a valid date so for example 01/12/2018 it must 1/12/2018. Otherwise the .calendar wont read it as calendar event

Selecting all the previous 6 months data records from occurrence of a particular value in a column in pandas


By : Harrison Howard
Date : March 29 2020, 07:55 AM
around this issue Idea is created Series of datetimes filtered by Moved and shifted by MonthOffset, last filter by Series.map values less like this offsets:
EDIT: Get all datetimes for each Moved values:
code :
df['Transaction_Date'] = pd.to_datetime(df['Transaction_Date'])
df = df.sort_values(['Cust_ID','Transaction_Date'])
df['g'] = df['Description'].iloc[::-1].eq('Moved').cumsum()

s = (df[df['Description'].eq('Moved')]
        .set_index(['Cust_ID','g'])['Transaction_Date'] - pd.offsets.MonthOffset(6))

mask = df.join(s.rename('a'), on=['Cust_ID','g'])['a'] < df['Transaction_Date']
df1 = df[mask].drop('g', axis=1)
print (df)
   Cust_ID Transaction_Date  Amount Description
0        1       10/01/2017      12           X
1        1       01/23/2017      15       Moved
2        1       03/01/2017       8           Y
3        1       08/08/2017      12       Moved
4        2       10/01/2018       6       Moved
5        2       02/01/2018      12           Z

#convert to datetimes
df['Transaction_Date'] = pd.to_datetime(df['Transaction_Date'])

#mask for filter Moved rows
mask = df['Description'].eq('Moved')
#filter and sorting this rows
df1 = df[mask].sort_values(['Cust_ID','Transaction_Date'])
print (df1)
   Cust_ID Transaction_Date  Amount Description
1        1       2017-01-23      15       Moved
3        1       2017-08-08      12       Moved
4        2       2018-10-01       6       Moved
#get duplicated filtered rows in df1
mask = df1.duplicated('Cust_ID')
#create Series for map
s = df1[~mask].set_index('Cust_ID')['Transaction_Date'] - pd.offsets.MonthOffset(6)
print (s)
Cust_ID
1   2016-07-23
2   2018-04-01
Name: Transaction_Date, dtype: datetime64[ns]

#create mask for filter out another Moved (get only first for each group)
m2 = ~mask.reindex(df.index, fill_value=False)
df1 = df[(df['Cust_ID'].map(s) < df['Transaction_Date']) & m2]
print (df1)
   Cust_ID Transaction_Date  Amount Description
0        1       2017-10-01      12           X
1        1       2017-01-23      15       Moved
2        1       2017-03-01       8           Y
4        2       2018-10-01       6       Moved
#get last duplicated filtered rows in df1
mask = df1.duplicated('Cust_ID', keep='last')
#create Series for map
s = df1[~mask].set_index('Cust_ID')['Transaction_Date'] 
print (s)
Cust_ID
1   2017-08-08
2   2018-10-01
Name: Transaction_Date, dtype: datetime64[ns]

m2 = ~mask.reindex(df.index, fill_value=False)
#filter by between Moved and next 6 months
df3 = df[df['Transaction_Date'].between(df['Cust_ID'].map(s), df['Cust_ID'].map(s + pd.offsets.MonthOffset(6))) & m2]
print (df3)
   Cust_ID Transaction_Date  Amount Description
3        1       2017-08-08      12       Moved
0        1       2017-10-01      12           X
4        2       2018-10-01       6       Moved

How to generate dynamic expiry dates like 3 months, 6 months, 9 months and 12 months from the today date in php


By : Antoine Précigout
Date : March 29 2020, 07:55 AM
I wish this help you You could use the DateTime class and the associated methods available such as add and diff
code :
$interval=new DateInterval('P3M');
$now = new DateTime();
$start=new DateTime();
$end=new DateTime( date( DATE_ATOM, strtotime('+1 year') ) );
$end->add( $interval );


while( $start->add( $interval ) <= $end ){
    echo $diff = $start->diff( $now )->format('%a') . '<br>';
}
92
182
274
366
Related Posts Related Posts :
  • Create New Field QGIS Depending on Another Field Using Case
  • How can you find the beginning of a week for a given date in Netezza?
  • Automatically assigning a primary key to an entry in MS-Access via Netbeans
  • Convert OUTER APPLY to LEFT JOIN
  • I need DateTime in SELECT, but if in GROUP BY provides unusable report
  • Concatenate with string but exclude when null
  • Copy null data to not null in a column
  • ORA-01861: literal does not match format string 01861
  • Postgres query for calendar
  • Error in Join query with pagination for Oracle 10g
  • Replace %20 string in URL with Underscore using SQL Server Database Table
  • SQL how to replace multiple characters in a single field of the same table with single query
  • Why does this Postgres Select Query work in the local Docker Container but not on Amazon RDS Postgres?
  • Combine two Oracle SQL queries with one LEFT OUTER JOIN and two INNER JOINS from four tables
  • How to generate unique id based on start date and end date for the next 40 years in SQL table
  • Single SELECT Statement to Retrieve All Possible Combinations of 1 Columns Values Oracle
  • Stored procedure fails, but works correctly when it runs manually
  • T-SQL Null comparisons in stored procedure
  • SQL query returning error only when CASE WHEN is used
  • Last value based on criteria
  • SELECT assigns a value to local variable and refers it again. Are the results guaranteed by SQL Server?
  • Do 'set operations' have an prescribed order of execution, or do they execute in order of evaluation?
  • SQL Select with distinct last 10 characters
  • Delete duplicate records based on timestamps
  • PostgreSQL Find all matches for given player
  • oracle regular expression issue
  • MS Access VBA query multiple tables
  • SQL update table with distinct
  • Summing a Case Statement with a check for Value in Table Variable
  • How to convert Big Int into Datetime style 109
  • SELECT the record (time) of a specific change for one ID
  • Should I put a row number filter in join condition or in a prior CTE?
  • Nest a select statement inside array_to_json(array_agg(row_to_json())) in PostgreSQL
  • Unable to pull Custom Dimension for a specific ID using BigQuery Standard SQL
  • Count of id per day using window function
  • WHERE clause with the same field name
  • How to choose 1 record from 2 tables based on record existence?
  • How to do join only with distinct items with latest date
  • Convert MAC address to integer in Vertica
  • Add if else condition in stored procedures and return hard coded values?
  • Subquery to return multiple columns from newest record?
  • SQL Server : Insert Values into a Table with one Dynamic Query, constants, etc
  • Merge multiple columns from different tables
  • AS400 - Using Merge to add a new record to a table and prevent from a duplicate key error
  • How can I add a dataframe converted to an array as an element of another dataframe based on a key value
  • How much will the transactionlog grow when making a delete given number of rows
  • Finding Mismatch Between Two SQL DataTables
  • DocumentDB filter documents on multiple items in a child array
  • Combining Columns from different tables
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com