Can query Assign on-or off-quarter based on date value?

dls0406

New Member
Joined
Oct 7, 2019
Messages
14
I have a table which includes
Column 1: Client Name
Column 2: Project date (Must be last day of month)

The unique ID is a combination of column A and Column B. So, there may be multiple instances of the same client with different project dates.

I want to create a query that looks at the project dates and can tell me, by looking at the month, if the client has project dates that are on-quarter end (3/31, 6/30, 9/30 or 12/31) or not.

In an ideal world, I would have a query which lists all clients (1 row per client) and identifies them as either on-quarter or off-quarter.

Any suggestions for how to go about this?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,586
Office Version
2013
Platform
Windows
I guess you will just put in your where criteria just as stated:

Code:
select * from Table1 where 
	(
	(datepart("m", [Project date]) = 3 and datepart("d", [Project date]) = 31)
	or 	
	(datepart("m", [Project date]) = 6 and datepart("d", [Project date]) = 30)
	or 	
	(datepart("m", [Project date]) = 9 and datepart("d", [Project date]) = 30)
	or 	
	(datepart("m", [Project date]) = 12 and datepart("d", [Project date]) = 31)
	)
equivalent with only two lines instead of four:
Code:
select * from Table1 where 
	(
	(datepart("m", [Project date]) in (3, 12) and datepart("d", [Project date]) = 31)
	or 	
	(datepart("m", [Project date]) in (6, 9) and datepart("d", [Project date]) = 30)
	)
 

Forum statistics

Threads
1,085,886
Messages
5,386,561
Members
402,006
Latest member
stringer47

Some videos you may like

This Week's Hot Topics

Top