Getting all weeks data of a month using inner join

I have the following query:

select distinct 
    v.DriverName,t.KM_W1,t.KM_W2,t.KM_W3,t.KM_W4,t.KM_W5,(t.KM_W1+t.KM_W2+t.KM_W3+t.KM_W4+t.KM_W5) Total
from 
    (select 
         DriverName,
         SUM( case when TodayDate >= '01-07-2018' and TodayDate <= '07-07-2018' then PaidKM else 0 end) KM_W1,
         SUM( case when TodayDate >= '07-07-2018' and TodayDate <= '14-07-2018' then PaidKM else 0 end) KM_W2,
         SUM( case when TodayDate >= '14-07-2018' and TodayDate <= '21-07-2018' then PaidKM else 0 end) KM_W3,
         SUM( case when TodayDate >= '21-07-2018' and TodayDate <= '28-07-2018' then PaidKM else 0 end) KM_W4,
         SUM( case when TodayDate >= '28-07-2018' and TodayDate <= '31-07-2018' then PaidKM else 0 end) KM_W5
     from 
         Traveling
     group by 
         DriverName) t 
inner join
    Traveling v on t.DriverName = v.DriverName
where 
    TodayDate >= '01-07-2018' and TodayDate <= '31-07-2018'

Above query explanation: I’m getting all weeks data PaidKM which driver gets in a whole month. The month for example starting date is 01-07-2018 and ending date is 31-07-2018 which covers whole month # 07. But when I change the month it gives me the same results all time.

Above GIF’s query implementation: I have implemented this query in Java and when I change the month from combo box but back end query all time gives the result from all records instead of different month.

Above image is the Traveling table in database.

How I can achieve the results when I change a different month? And how can I correct this query to work this?

Answer

Your issue is that the comparisons are string based not date based. Days have the higher precedence than months, and months have higher precedence than years.

e.g. 31-01-1970 would be considered greater than 01-01-2018 as 31 is greater than 01.

You should consider using a recognised date format, which is directly sortable/comparable e.g. YYYY-MM-DD whenever working with/storing dates.

You may wish to consider looking at SQL As Understood By SQLite – Date And Time Functions – Time Strings.

You could convert the dates to be sortable/easily compared using

substr(datevalue,7,4)||substr(datevalue,3,4)||substr(datevalue,1,2)

e.g. instead of :-

SUM( case when TodayDate >= '01-07-2018' and TodayDate <= '07-07-2018' then PaidKM else 0 end) KM_W1, ....

you could use :-

SUM( case when substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-01' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-07' then PaidKM else 0 end) KM_W1, ....

The whole query could be :-

select distinct 
    v.DriverName,t.KM_W1,t.KM_W2,t.KM_W3,t.KM_W4,t.KM_W5,(t.KM_W1+t.KM_W2+t.KM_W3+t.KM_W4+t.KM_W5) Total
from 
    (select 
         DriverName,
         SUM( case when substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-01' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-07' then PaidKM else 0 end) KM_W1,
         SUM( case when substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-08' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-14' then PaidKM else 0 end) KM_W2,
         SUM( case when substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-15' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-21' then PaidKM else 0 end) KM_W3,
         SUM( case when substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-22' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-28' then PaidKM else 0 end) KM_W4,
         SUM( case when substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-29' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-31' then PaidKM else 0 end) KM_W5
     from 
         Traveling
     group by 
         DriverName) t 
inner join
    Traveling v on t.DriverName = v.DriverName
where 
    substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) >= '2018-07-01' and substr(TodayDate,7,4)||substr(Todaydate,3,4)||substr(Todaydate,1,2) <= '2018-07-31'

• Note The above is in-principle code and has not been tested, it may therefore contain some errors.