List or Count bal of mth & mth todate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

List or Count bal of mth & mth todate

Author

Topic

reeljustice
Starting Member

2 Posts

Posted - 2004-09-19 : 15:04:53

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','597','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','825','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

Don't miss the forest for the trees.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-19 : 15:23:34

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

Go with the flow & have fun! Else fight the flow

reeljustice
Starting Member

2 Posts

Posted - 2004-09-19 : 22:43:47

spirit1:

Thank you very much for the language. I just tested the language for the 4 queries. Worked great and is just what I needed. That helps me to quickly see what we completed for the month, in a count and in a list AND to see what we have left for the month in a count and in a list.

Before, I was having to do that in a spreadsheet every time.

Thanks much.

Don't miss the forest for the trees.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources