Filter Date field on Month/Year only

[dsemitekol]

Hello Community. I'm using In-DB tools today, connecting to a server.

I have a standard SQL date field.  I want to filter on the month and year only.  I know that I can use the Filter tool and enter a BETWEEN date range, however, I need to enter each month range for an entire year.

"DEMAND_DATE" BETWEEN '2016-02-01'
AND
'2016-02-28'

I do not want to have to enter 12 filters for each month of each year and make sure that I have the correct number of days in each month.  I get an error when trying to look for '2016-02-01' AND '2016-02-31' because the computer is smart enough to know that there are not 31 days in February :)

Also, I need to do this for 3 years in one workflow, that's 36 filters!

Am I missing a simple step to either do a fuzzy filter or something?

THANKS!

[LordNeilLord]

Hey @dsemitekol

Why do you need to bring one month at a time?

Can you not filter on the entire 3 year period and then use subsequent tools to process each month/year?

Neil

[LordNeilLord]

If you want to just filter on month/year check out the datepart function in SQL... https://www.w3schools.com/sql/func_sqlserver_datepart.asp

You can do something like...datepart('month', demand_date) between 1 and 12 And datepart('year', demand_date) = 2018

I'm not a sql expert so this is just a guide

Neil

[dsemitekol]

Hi @LordNeilLord Thanks for the reply. I have 3 years of sales data at the customer level. The customer can repeat during a month. I want to look at only their first visit during each month for each year. For example: Bob shops in March on the 5th and the 21st. I only want Bob's March 5th visit. Bob shops again in April. I want to see Bob's visit in April. After thinking about it, I am not sure if parsing out the month/year is going to help my workflow. I think I am going to go the original route: filter the dataset by each month. Perform my "first instance" using the summarize tool and then rejoin all the data at the end of the workflow with one giant Union tool. It is going to cause 36 filters, summarize, joins, and select tools but I can not think of a better solution.

[LordNeilLord · Accepted solution]

Hey @dsemitekol

Ok you're on the right lines.....to make this super simple rather than building loads of filters....Pull the Month and Year back in your query and then in the summarize tool...group by Month, Year, Customer ID/Customer Name and return the Min_Visit Date. This will give you the first instance of each month and year for each customer.

I've mocked up and example (not InDB but the principle is exactly the same).

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[dsemitekol]

Thanks @LordNeilLord this is exactly what I was looking for.  I can reduce the number of filters and then add a simple join at the end to pull the attributes for each customer back into the work flow.

Thanks again.

[dsemitekol]

Here is the updated syntax for In-DB Formula Tool using SQL script and connecting to a Netezza server:

EXTRACT (month FROM "DATE")

Let month be the month value that you are looking to pull from the field

Let "DATE" be the title of the field that contains the dates