Parameter month selection: Make a query that shows previous month, 12 months ago and last 12 months average
Tag : sql , By : user158220
Date : March 29 2020, 07:55 AM
help you fix your problem I wanted to play around with my Total_Sales table. This is how the data looks like (using SQL Server 2008 R2) SELECT
[this_month].*,
[last_month].Sales AS [prev_month_sales],
[last_year].Sales AS [month_last_year_sales],
[yearly].AverageSales AS [last_12_month_average]
FROM
Total_Sales AS [this_month]
LEFT JOIN
Total_Sales AS [last_month]
ON [last_month].Name = [this_month].Name
AND (
([last_month].Year = [this_month].Year AND [last_month].Month = [this_month].Month - 1)
OR ([last_month].Year = [this_month].Year - 1 AND [last_month].Month = 12 AND [this_month].Month = 1)
)
LEFT JOIN
TotalSales AS [last_year]
ON [last_year].Name = [this_month].Name
AND [last_year].Year = [this_month].Year - 1
AND [last_year].Month = [this_month].Month
CROSS APPLY
(
SELECT
AVG(Sales) AS AverageSales
FROM
Total_Sales
WHERE
Name = [this_month].Name
AND (
(Year = [this_month].Year AND Month <= [this_month].Month)
OR (Year = [this_month].Year - 1 AND Month > [this_month].Month)
)
)
AS [yearly]
Last Month : MonthStart = DATEADD(MONTH, -1, ThisMonth)
A Year Ago : MonthStart = DATEADD(YEAR, -1, ThisMonth)
Last Year : MonthStart > DATEADD(YEAR, -1, ThisMonth) AND MonthStart <= ThisMonth
|
T-SQL query to select last 3 months of data, last years month, averages of this month and average of all months
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , you need something along these lines .Not the optimum solution but will give you a start .This is a static solution but it looks like you may want a dynamic solution *not tested SELECT
Category
,[Sep 2012]=SUM(CASE WHEN YEAR(TranDate)= YEAR(GETDATE()) AND MONTH(TranDate)= MONTH(GETDATE()) THEN Amount ELSE NULL END)
,[Aug 2012]=SUM(CASE WHEN YEAR(TranDate)= YEAR(DATEADD(month,-1,GETDATE())) AND MONTH(TranDate)= MONTH(DATEADD(month,-1,GETDATE())) THEN Amount ELSE NULL END)
,[Jul 2012]=SUM(CASE WHEN YEAR(TranDate)= YEAR(DATEADD(month,-2,GETDATE())) AND MONTH(TranDate)= MONTH(DATEADD(month,-2,GETDATE())) THEN Amount ELSE NULL END)
,[AVG Sep 2012]=AVG(CASE WHEN YEAR(TranDate)= YEAR(GETDATE()) AND MONTH(TranDate)= MONTH(GETDATE()) THEN Amount ELSE NULL END)
,[AVG 12 months]=AVG(CASE WHEN TranDatee > CAST(DATEADD(year,-1,GETDATE()) AS DATE) THEN Amount ELSE NULL END)/12
FROM Table1
GROUP BY Category,Amount
|
Can I label months in a ggplot with the initial of each month and maintain the order?
Tag : r , By : John Bentley
Date : March 29 2020, 07:55 AM
will be helpful for those in need If your month values are numeric then you can do this at the plotting stage by modifying the labels. In your ggplot add the following: + scale_x_continuous(breaks = 1:12,
labels = c('J', 'F', 'M', 'A', 'M', 'J', 'J', 'A', 'S', 'O', 'N', 'D'))
|
Swift 4: Checking if week, month, 2 months, etc. has passed since initial launch using Calendar
Tag : ios , By : Matthew Steed
Date : March 29 2020, 07:55 AM
I wish did fix the issue. From what I understand of your question, you want a button to display if they have had the app for more than a certain amount of time. If that is the case, I've written some code that will be helpful to you: let timeSinceInstalled = -(installedDate ?? Date()).timeIntervalSinceNow
Button1.isHidden = timeSinceInstalled < 604800
Button2.isHidden = timeSinceInstalled < 2592000
Button3.isHidden = timeSinceInstalled < 5184000
override func viewDidLoad() {
super.viewDidLoad()
let timeSinceInstalled = -(installedDate ?? Date()).timeIntervalSinceNow
Button1.isHidden = timeSinceInstalled < 604800
Button2.isHidden = timeSinceInstalled < 2592000
Button3.isHidden = timeSinceInstalled < 5184000
}
var installedDate: Date? {
get {
return UserDefaults.standard.object(forKey: "installedDateKey") as? Date
}
set {
UserDefaults.standard.set(newValue, forKey: "installedDateKey")
}
}
override func viewDidLoad() {
super.viewDidLoad()
let timeSinceInstalled = -(installedDate ?? Date()).timeIntervalSinceNow
setTitle(Button1, timeSinceInstalled < 604800)
setTitle(Button2, timeSinceInstalled < 2592000)
setTitle(Button3, timeSinceInstalled < 5184000)
}
func setTitle(_ button: UIButton, _ statement: Bool) {
button.setTitle(statement ? "You have none available" : "Click me", for: .normal)
button.isEnabled = !statement
}
var installedDate: Date? {
get {
return UserDefaults.standard.object(forKey: "installedDateKey") as? Date
}
set {
UserDefaults.standard.set(newValue, forKey: "installedDateKey")
}
}
|
How to get next 4 months from current month in an array of month objects [{month:'Jan' id:'1},{month:'Feb', id:'2},...{m
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I tried array slice but its not giving expected result if starting month is one of the last 3 months of the year. , Simply double the array and then slice it var newArr = [ ...arr, ...arr ];
var index = 10;
var output = newArr.slice( index, index + 4 )
var arr = [{
month: 'Jan',
id: "1"
}, {
month: 'Feb',
id: "2"
}, {
month: 'Mar',
id: "3"
}, {
month: 'Apr',
id: "4"
}, {
month: 'May',
id: "5"
}, {
month: 'Jun',
id: "6"
}, {
month: 'Jul',
id: "7"
}, {
month: 'Aug',
id: "8"
}, {
month: 'Sep',
id: "9"
}, {
month: 'Oct',
id: "10"
}, {
month: 'Nov',
id: "11"
}, {
month: 'Dec',
id: "12"
}];
var newArr = [ ...arr, ...arr ];
//console.log( newArr );
var index = 10;
var output = newArr.slice(index, index+4);
console.log( output );
var newArr = arr.concat(arr.slice())
|
Boards Message :
Related QUESTIONS :