CODEWITHSUNDEEP
sqlite
KlauRu
KlauRu

Reputation: 11

SQLite: Select and count cases where part of the string (year) is lowar than...?

I did already search in past answers for nearly two days but found no solution. I have a table with a column 'projects', column 'country' and a column 'timeframe'. I want to count the number of projects, grouped by country, which did start before 2017. The data looks like

ProjectID   CountryID   Time 
  5             3       Enero/2011 - Diciembre/2020
  6             3       June 2017 - December 2020 
  7             3       June 2017 - December 2030 
  8             5       NULL 
  9             11      July 2017 - December 2020 
  10            11      7/2017 - 12/2020 
  11            5       2017 June - 2020 January

The problem is the format of the Time, but as it is a large dataset I cannot change the format manually. I tried my best to do something like

SELECT *, COUNT (*)
WHERE (Time LIKE '%XX% - %' AND XX < 2018);

but I cannot find the right way to include the parameter XX in the query. Is there any way to get this done without re-writing all data manually?

Thanks a lot!

Upvotes: 0

Views: 24

Answers (1)

KlauRu
KlauRu

Reputation: 11

Thanks Tripehound - substrings did help! For the Start year 

SELECT ID, substr(TimeFrame, instr((TimeFrame),'20'),4) AS ProjectStart
    , Count(*) AS NumberProjects
    FROM Timeframe
    WHERE ProjectStart>"2017";

did do the job; for the end year it was 

SELECT substr(TimeFrame, instr(TimeFrame,'-')-1+instr(substr(TimeFrame, instr(TimeFrame,'-')), '20'), 4) AS ProjectEnd, 
Count(*) AS ProjectsNumber
FROM Timeframe
WHERE ProjectEnd<"2030";

Done!

Upvotes: 1

Related Questions