How to use case statement in fragments?

Questions / Help
1

I have a SQL query that I need to write in ecto. something like this

    SELECT salesperson, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) NOT IN (1, 7) THEN  [pencil_sales] END)) as pencil_salesAV_weekday, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) NOT IN (1, 7) THEN [pen_sales] END) as pen_salesAV_weekday, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) IN (1, 7) THEN  [pencil_sales] END)) as pencil_salesAV_weekend, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) IN (1, 7) THEN [pen_sales] END) as pen_salesAV_weekend, 
       Month(the_date) as Month,
       Year(the_date) as Year
FROM regionalsales 
GROUP BY GROUPING SETS((Month(the_date), Year(the_date), salesperson), (salesperson));

I’m having a hard time converting this in ecto. I need to use case statement in the fragment but I don’t know the exact syntax and I’m missing something in that case. How can I write this?

2

You could probably use filter/2 (Ecto.Query.API — Ecto v3.7.1) for the avg / case part.

The group by part would probably be something like group_by: fragment("GROUPING SETS((Month(?), Year(?), ?), (?))", the_date, the_date, salesperson, salesperson)

1 Like
3

Actually I’ve figured out most of the part. Just need some help here

Avg(CASE WHEN DATEPART(weekday, [the_date]) NOT IN (1, 7))

which I’ve written something like this


fragment("case when date_part(?,?) not in (1, 7)", "weekday", t.date)

date is a timestamp here.

But this giving me this

undefined function fragment/2

4

Here I posted a helper which allows writing case statements without fragments.

1 Like
5

It’s interesting. But in my case, I have to use fragments right? since I need to extract weekday from the date_part

6

For that part - yes.