Advanced search Query library

Louisa Robinson

Is there a library of helpful advance searches - or advanced query commands available?

I have a query that was built by someone else, that pulls out all potential payments for this current month - however, I actually want to adjust this to pull through next months too - so I can run a report on payments due next month that are still contigent

It would be really helpful if we had somewhere in the resources sections of common query commands and what they actually do - to help us build these queries ourselves (As the ‘insert expression’ when building doesnt cover everything)

My query is:

(month(CAST(Payments.Schedule_Date AS DATE)) = month(getdate()) AND year(CAST(Payments.Schedule_Date AS DATE)) = year(getdate()) AND Payments.Payment_Date IS NULL) Payments.Schedule_Date IS NULL

Ray Borkman

@Louisa Robinson I’ve pinged a few people internally here that might be able to help. Will follow up.

Justin Finighan

@Louisa Robinson Hi Louisa. This is a great idea, and we would be happy to contribute to building an advanced query library and demonstrating some techniques.

It appears there is an error in the query text you have posted - that is not a valid query as it stands.

Here is one approach to solving your problem.

((MONTH(CAST(Payments.Schedule_Date AS DATE)) = MONTH(GETDATE()) AND YEAR(CAST(Payments.Schedule_Date AS DATE)) = YEAR(GETDATE())) OR (MONTH(CAST(Payments.Schedule_Date AS DATE)) = (MONTH(GETDATE()) + 1) AND (MONTH(GETDATE()) < 12) AND YEAR(CAST(Payments.Schedule_Date AS DATE)) = YEAR(GETDATE())) OR ((MONTH(CAST(Payments.Schedule_Date AS DATE)) = 1 AND (MONTH(GETDATE()) = 12) AND YEAR(CAST(Payments.Schedule_Date AS DATE)) = (YEAR(GETDATE()) + 1)))) AND Payments.Payment_Date IS NULL AND NOT Payments.Schedule_Date IS NULL

To get the next month, we can simply add one to MONTH(GETDATE()), but we run into problems when the current date is in December, and so the next month is 1 (not 13), and the year is going to be next year, ie, YEAR(GETDATE()) + 1. The above query should work throughout the year.

To break it down:

Scheduled Date is the current month:

(MONTH(CAST(Payments.Schedule_Date AS DATE)) = MONTH(GETDATE()) AND YEAR(CAST(Payments.Schedule_Date AS DATE)) = YEAR(GETDATE()))

Scheduled Date is the next month and MONTH(GETDATE()) < 12:

(MONTH(CAST(Payments.Schedule_Date AS DATE)) = (MONTH(GETDATE()) + 1) AND (MONTH(GETDATE()) < 12) AND YEAR(CAST(Payments.Schedule_Date AS DATE)) = YEAR(GETDATE()))

Scheduled Date is the next month and MONTH(GETDATE()) = 12:

(MONTH(CAST(Payments.Schedule_Date AS DATE)) = 1 AND (MONTH(GETDATE()) = 12) AND YEAR(CAST(Payments.Schedule_Date AS DATE)) = (YEAR(GETDATE()) + 1))

Cheers

Justin

Louisa Robinson

@Justin Finighan This is super helpful! I realised I stuck in an extra (wrong) part of the code on the end, i was trying to remove a part that had a staff name in.

it should have been (month(CAST(Payments.Schedule_Date AS DATE)) = month(getdate()) AND year(CAST(Payments.Schedule_Date AS DATE)) = year(getdate()) AND Payments.Payment_Date IS NULL)

Louisa Robinson

@Ray Borkman thank you - i realised i typed the coding wrong! (Copy and paste issue by user!)

it should have read (month(CAST(Payments.Schedule_Date AS DATE)) = month(getdate()) AND year(CAST(Payments.Schedule_Date AS DATE)) = year(getdate()) AND Payments.Payment_Date IS NULL)