List or Count bal of mth & mth todate

I need to count and to list events for the current month todate and to count and list event for the rest or balance of the month.

Could someone assist me in preparing these separate queries? Thank you in advance for your assistance.

Thisis my query for list all events for the current month:

SELECT EventTimeBegin AS [Closing Date - Time], EventName AS [File No], AttyLName AS [Atty], Borrower1LName AS [Borrower], Borrower1FName AS [First], ProcessorLName AS [Processor], LOLName AS [LO], ClosingLocation AS [Location], MtgeBroker AS [Broker], EventID AS [ID], ModuleID AS [MID], CreatedDate AS [Order Date]

FROM RECalendar

WHERE

(
RECalendar.FileType IN ('CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','Witness')
AND
RECalendar.ModuleID IN ('581','582','583','584','585','586','587','588','589','590','591','592','593','594','595','596','59 7','603','733','747','750','751','752','753','754','755','756','757','758','759','761','762','763',' 764','765','766','767','768','769','771','772','773','774','775','776','777','778','779','780','781' ,'782','783','784','785','786','814','815','816','817','818','819','820','821','822','823','824','82 5','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893',' 894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910' ,'911','912','914','915')
)

AND

(
month(EventTimeBegin)=month(getdate())
AND
year(EventTimeBegin)=year(getdate())
)

ORDER BY EventTimeBegin ASC

you cannot count and list at the same time

I understand that. My question was not clear.

I need to count the events from the 1st of the month to the current day in 1 query, then, in another query, list the events from the 1st day of the month until the current day.

In the other 2 queries, I need to count the events for the rest of the month in one query and list the events for the rest of the month in another query.

The following was provided in another forum. I am about to review the suggestion and test them.

if i get your question correctly:

events untill today:
...
AND
(month(EventTimeBegin)=month(getdate())
AND
year(EventTimeBegin)=year(getdate())
and
datediff(d, EventTimeBegin, getdate()) <= 0
)

and for the rest of the month:
...
AND
(month(EventTimeBegin)=month(getdate())
AND
year(EventTimeBegin)=year(getdate())
and
datediff(d, EventTimeBegin, getdate()) > 0
)

you can substitute
(month(EventTimeBegin)=month(getdate())
AND
year(EventTimeBegin)=year(getdate())
with
datediff(m, EventTimeBegin, getdate()) = 0

Just tested the queries, they work great.

they work but will not scale well (any index on EventTimeBegin will be ignored because of the functions performed on it)

better for events until today --

where EventTimeBegin between F and getdate()

better for events after today --

where EventTimeBegin between getdate() and L

now, all you gotta do is come up with expressions for F (the first day of the current month) and L (the last day of the current month)

then both WHERE clauses will be efficient, and the index will be used

F would be dateadd(dd,-day(getdate())+1,getdate())

L would be dateadd(mm,1,dateadd(dd,-day(getdate()),getdate()))