Order by today day

[Henson4004]

How can i order below query by whichday so that whichday should start from today which is 19th onwards in the order, any help please:

SELECT MIN(entered_date) as edate, entered_date, SUM(case when entered_date>=‘2020-02-19’ and entered_date<‘2020-03-19’ then 1 else 0 end) AS counter_prev, SUM(case when entered_date>=‘2020-03-19’ and entered_date<=‘2020-04-19’ then 1 else 0 end) AS counter_current, day(entered_date) AS whichday FROM contacts WHERE profile_categories IN(1) and (entered_date>=‘2020-02-19’ and entered_date<=‘2020-04-19’) GROUP BY day(entered_date) ORDER BY whichday ASC

[r937]

some problems with your query

your dates seem to have been enclosed in microsoft word smart quotes, so i changed them to ordinary single quotes

you had entered_date in your SELECT clause alongside MIN(entered_date), but not in your GROUP BY clause, so i removed it

you had unnecessary parentheses in the WHERE clause, so i removed them

finally, you are grouping the day of the month, and both the 29th of february and the 29th of march will be grouped into the same group

so i’m not sure what you actually wanted to do with the day business, and i especially don’t know how you want it sorted

SELECT MIN(entered_date) as edate
     , SUM(CASE WHEN entered_date >= '2020-02-19' 
                 AND entered_date  < '2020-03-19' 
                THEN 1 ELSE 0 END) AS counter_prev
     , SUM(CASE WHEN entered_date >= '2020-03-19' 
                 AND entered_date <= '2020-04-19' 
                THEN 1 ELSE 0 END) AS counter_current
     , DAY(entered_date) AS whichday 
  FROM contacts 
 WHERE profile_categories IN(1) 
   AND entered_date >= '2020-02-19' 
   AND entered_date <= '2020-04-19' 
GROUP 
    BY DAY(entered_date) 
ORDER 
    BY whichday ASC

perhaps you could show some sample results to demonstrate how you want this stuff sorted

[Henson4004]

@r937

2020-04-19

I want to select record counts of 2 months for the performance comparison against each day in 2 date ranges.

Result of my query is, below is the sample of few days only:

edate counter_prev counter_current whichday
2020-03-10 21 3 10
2020-03-11 5 3 11
2020-03-12 11 11 12
2020-03-13 3 10 13
2020-03-14 12 11 14
2020-03-15 21 4 15
2020-03-16 17 5 16
2020-03-17 4 4 17
2020-03-18 9 5 18
2020-02-19 8 24 19
2020-02-20 13 4 20
2020-02-21 3 5 21
2020-02-22 8 14 22
2020-02-23 19 11 23

where counter_prev and counter_current are the counts of 2 data ranges against each day.
Ranges: 2020-02-19 t0 2020-03-18 and 2020-03-19 to 2020-04-19

Counters are right but i need to select order by small date to large date so 2020-02-19 should come first which is the day of today.

[r937]

okay, first thing i notice is that these results do not match your sql

i’m lost already

next, you’re displaying “edate” which is the MIN(entered_date), but i do not understand why you have GROUP BY DAY(entered_date)

next, your calculations for counter_prev and counter_current do not seem to make any sense from one edate to the next

i’m going to have to bail here, sorry

perhaps someone else can help you