Setting up a count off of different date rules in ETL

JonFass

Hi everyone,

I'm trying to set up a column that would show me how many workdays to pay a consultant for, depending on how many days they worked for over the month.

The variables are whether the start or end date was during that month (and so they would only be comped for that part of the month - from start to month end of form month start to work end).

I've tried to set up an ETL process to give me the month and year for different date points to compare against one another to see which rule should apply. I then tried to set up a case to give me the relevant number of workdays from different columns in the data set, depending on the situation (below), but keep getting a syntax error message.

Any ideas on how I could make this work?

MarkSnodgrass

From a syntax perspective, you are missing an END statement. You only have one, but you need two because you have two CASE statements.

MarkSnodgrass

From a syntax perspective, you are missing an END statement. You only have one, but you need two because you have two CASE statements.

mhouston

What's your dataset structure like? If you can get it to a row per day worked, you could join to the calendar dimension, and then do a group by on month and year to count the number of days worked.

JonFass

This is exactly what I needed, thank you!