Prev: Time data type
Next: Setting of @@FETCH_STATUS
From: robertm600635 on 29 Nov 2009 22:46
I have an insert query that runs every day at midnight that records our
client's current environmental status (it's for a mental health facility).
Here's the code:

-----------------------------------------------------------------------------------------------
INSERT INTO dbo.tblCensusHistoryWithDischarge (FullName, LastName,
FirstName, FileNumber, ClientNumber, StaffID, Environment, Location,
Residence,
FundSource, Act10, StartDate, ExpEndDate, EndDate, CensusTimestamp)

SELECT TOP (100) PERCENT dbo.tblClients.LastName + N', ' +
dbo.tblClients.FirstName AS FullName, dbo.tblClients.LastName,
dbo.tblClients.FirstName,
dbo.tblClients.FileNumber,dbo.tblClients.ClientNumber,
dbo.tblEnvironments.StaffID,
dbo.tblEnvironments.Environment, dbo.tblEnvironments.Location,
dbo.tblEnvironments.Residence,
dbo.tblEnvironments.FundSource, dbo.tblEnvironments.Act10,
dbo.tblEnvironments.StartDate,
dbo.tblEnvironments.ExpectedEndDate, dbo.tblEnvironments.EndDate,
CONVERT(VARCHAR(10), GETDATE(), 101)

FROM dbo.tblClients INNER JOIN
dbo.tblEnvironments ON dbo.tblClients.ClientID =
dbo.tblEnvironments.ClientID

WHERE (dbo.tblEnvironments.EndDate IS NULL)

ORDER BY FullName
-----------------------------------------------------------------------------------------------

The WHERE condition is there so the query only captures clients who are
currently in some phase of treatment, therefore the EndDate field is null. I
need to add another condition so I can also record clients who were
discharged on the current date. All dates are entered on the front end in
mm/dd/yyyy format so in the tables they look like mm/dd/yyyy 00:00:00.000.
I've played around with it for a while but can't get it right. Thanks in
advance for any help you can give me.
From: Plamen Ratchev on 29 Nov 2009 23:32
If the dates stored are with time set at midnight, then this should work:

WHERE dbo.tblEnvironments.EndDate IS NULL
OR dbo.tblEnvironments.EndDate = DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010101')

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Time data type
Next: Setting of @@FETCH_STATUS