On 2017/04/12 3:08 PM, Ron Barnes wrote:
Hello Ryan,
That Code below worked as you said it should. Awesome! And Thank you!
I now have the days difference for each row.
I have one other question if I may pose it to you, how do I count the number of
rows, less than a day, or a week or a year and so forth?
I tried this code and a few variants of it but I keep getting errors when
trying to execute.
Would you examine my code for errors?
SELECT category, COUNT(*) AS Expr1
FROM
((SELECT replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
AS ISO_Date, julianday('Now') - julianday(replace(substr(VI_Creation_Date,1,
10), '/','-')||' 12:00:00') AS DaysSince)
WHEN DaysSince < 2 THEN 'Under 1 Day'
WHEN DaysSince < 8 THEN 'Under 1 Week'
WHEN DaysSince < 32 THEN 'Under 1 Month'
WHEN DaysSince < 366 THEN 'Under 1 Year'
WHEN DaysSince < 366 THEN 'Under 1 Year'
WHEN DaysSince < 731 THEN 'Under 2 Year'
WHEN DaysSince < 1826 THEN 'Under 5 Years'
WHEN DaysSince < 3651 THEN 'Under 10 Years'
ELSE 'Over 10 Years' END) AS category
FROM Volume_Information) derivedtbl_1
GROUP BY category
Nothing much wrong with the idea, but I suppose the syntax is not clear.
This works by me:
SELECT G.cat, COUNT(*) AS qty
FROM (
SELECT C.days, CASE
WHEN C.days < 1 THEN '1. Under 1 Day'
WHEN C.days < 7 THEN '2. Under 1 Week'
WHEN C.days < 31 THEN '3. Under 1 Month'
WHEN C.days < 366 THEN '4. Under 1 Year'
WHEN C.days < 731 THEN '5. Under 2 Years'
WHEN C.days < 1826 THEN '6. Under 5 Years'
WHEN C.days < 3651 THEN '7. Under 10 Years'
ELSE '8. Over 10 Years'
END AS cat
FROM (
SELECT
julianday('now')-julianday(replace(substr(VI_Creation_Date,1,10),'/','-'))
AS days
FROM Volume_Information
) AS C
) AS G
GROUP BY G.cat
ORDER BY G.cat
;
I took the liberty of fixing the cut-offs a bit to better reflect the
truth and added a number to the category so ordering would make sense.
Note that these figures are not cumulative - i.e. if there are 25 items
this month, of which 10 items for this week and 2 of them are in the
last day, then the results will show:
2 Under 1 Day
8 Under 1 Week
15 Under 1 Month
While, technically, there are 25 items for the month and 10 items under
the last week...
This may be exactly as you need, but if not, let me know then we can try
another way.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
