I have two date columns in table (SQLITE)
Now what i need is to find number days appear in each quarter between these two dates
For example let's say,
start_Date = 12-Jan-2015
end_Date = 13-Jul-2015
So result should be like:
Quarter 1 = 79 days
Quarter 2 = 91 days
Quarter 3 = 13 days
Quarter 4 = 0 days
I have tried below query
SELECT CASE WHEN cast(strftime('%%m', end_date) as integer) BETWEEN 1 AND 3 THEN 'Q1' WHEN cast(strftime('%%m', end_date) as integer) BETWEEN 4 and 6 THEN 'Q2' WHEN cast(strftime('%%m', end_date) as integer) BETWEEN 7 and 9 THEN 'Q3' ELSE 'Q4' END as Quarter, sum(julianday(end_date) - julianday(start_date) + 1) AS Total FROM table WHERE end_date BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 00:00:00' GROUP BY Quarter
But problem is it will show all days in Quarter considering end_date. So if end_date's month is 4 then all days will be displayed in Quarter 3.
Any suggestions are welcomed. Either is sqlite query only, or in ios SDK or use of both. In any scenario possible.
Thanks....!!
Aucun commentaire:
Enregistrer un commentaire