This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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.
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).