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

How to generate 6 months average spend prior current date


Try this query:

SELECT q.year, avg(
o.item_extended_actual_price_amt )  
FROM (
   SELECT EXTRACT(YEAR FROM ord_submitted_date) as
year,
          min( ord_submitted_date ) as
first_order_date
   FROM ORDERS
   GROUP BY year
) q
JOIN ORDERS o
ON o.ord_submitted_date 
   BETWEEN q.first_order_date - INTERVAL '6
months'
       AND q.first_order_date + INTERVAL '6
months'
GROUP BY q.year
ORDER BY q.year
;

Demo: http://sqlfiddle.com/#!15/3e570/29


Categories : SQL

Related to : How to generate 6 months average spend prior current date
How to add 60 months to current date in data stage jobs?
this issue is there in datastage till FP8.x version. This issue is fixed in later versions. If you want to work in older versions, you need to install patch given by IBM to fix this or you need to write your own logic to get the date after adding months.

Categories : Misc
SQL - Months and Dates
Ok, I don't know VectorWise or how ansidate is stored, however, if it's stored as an integer value I assume it represents a certain amount of time, measured on a specific time unit since a starting time point. If it's true, you can convert your 3 months to the time unit in which ansidate is being stored and add the converted value.

Categories : SQL
How to get months and days between two date
i think directly u can not get like z day y hr x min but you can get separate day min sec and all like below - long diff = EFFDAT.getTime() - CURRDATE.getTime(); long diffSeconds = diff / 1000 % 60; long diffMinutes = diff / (60 * 1000) % 60; long diffHours = diff / (60 * 60 * 1000); int diffInDays = (int) ((dt2.getTime() - dt1.getTime()) / (1000 * 60 * 60 * 24)); UPDATED

Categories : Java
Subset in R between 2 Months over a Range of Days
As @JoshuaUlrich said, the month of one observation can't be two values at the same time. Something like that would work: vf1 <- vf [ which( vf$Month==4 & vf$Day>19 | vf$Month ==5 & vf$Day <32 ), ] So you are saying if Month is 4, Day must be larger 19 or if Month is 5, Day must be smaller 32.

Categories : R
perl datetime add months fails in dst timezone
Your solution is recommended in the documentation, so it should be OK. Also, when I ran your original code, I got an error back: Invalid local time for date in time zone: America/Sao_Paulo

Categories : Perl
Recently Add
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
SQL Server 2008 Agent jobs. Two procedures and a view
Can't create table in Oracle Developer
Trigger error "ORA-01403: no data found" when It is not fire
SQL: alternatives and substitutions for GROUPING SETS and PIVOT
Excel Vlookup too Slow
Is WHERE field IS NULL quicker than >0
BFILE error on oracle
SQL Server : Select from two tables with default value 0
Use of uninitialized value after fetchrow()
Why did this UPDATE change all rows instead of just one row?
Oracle / SQL Counting Different values from one Column and storing it in a variable
How can I calculate the percentage of occurrences of a value in a column?
To compute sum regarding to a constraint
Enterprise Architect - Execute SQL Issue
First business day of the current month - SQL Server
Incorrect no. of rows fetched in SQL
SQL query design with configurable variables
Grouping dates by dateadd and datediff
SQLServer get top 1 row from subquery
SQL Using CONTAINS() doesn't work for AND & OR
SQL Server: Persisting computed column
Improve SQL Server query
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.