That worked!!!! Thank you very Much!!
-Ron -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, April 12, 2017 10:12 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
