mercredi 4 mars 2015

Find days between two dates in each Quarter

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