How to get the missing dates

Hi Everyone,
I was wondering how I could get the missing dates. I have the following table:
--Attendance--
ClientId
AttendDate
--Patients--
PatientId
LastName
FirstName
ect...
This table holds dates that our client has been in our office. But I need to
find out what dates the clinet was not there given a date range. What is the
best way to do this? I have the following query. It will work for only one
client. The problem is that I may need to do it for all patients or just one
patient.
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.ToDate smalldatetime,
@.FromDate smalldatetime
AS
create table #range1(dt smalldatetime)
while (@.ToDate <= @.FromDate)
begin
insert into #range1 values(@.ToDate)
Set @.ToDate = dateadd(dd,1,@.ToDate)
end
Select dt from #range1
where dt not in (Select AttendDate from Attendance
where Patientid = @.PatientId)
drop table #range1
GO
The also only returns the dates themself, while I still need other data to
be included in the query like the first and last name of the Patient. I
played with the query some but could not get the results I was looking for.
Thanks for any comments.
MichaelYou may benefit from a calendar table. Try this resource:
http://www.aspfaq.com/show.asp?id=2519
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:81388BE6-50B5-459A-8919-C4C949097FB1@.microsoft.com...
> Hi Everyone,
> I was wondering how I could get the missing dates. I have the following
> table:
> --Attendance--
> ClientId
> AttendDate
> --Patients--
> PatientId
> LastName
> FirstName
> ect...
> This table holds dates that our client has been in our office. But I need
> to
> find out what dates the clinet was not there given a date range. What is
> the
> best way to do this? I have the following query. It will work for only one
> client. The problem is that I may need to do it for all patients or just
> one
> patient.
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.ToDate smalldatetime,
> @.FromDate smalldatetime
> AS
> create table #range1(dt smalldatetime)
> while (@.ToDate <= @.FromDate)
> begin
> insert into #range1 values(@.ToDate)
> Set @.ToDate = dateadd(dd,1,@.ToDate)
> end
> Select dt from #range1
> where dt not in (Select AttendDate from Attendance
> where Patientid = @.PatientId)
> drop table #range1
> GO
> The also only returns the dates themself, while I still need other data to
> be included in the query like the first and last name of the Patient. I
> played with the query some but could not get the results I was looking
> for.
> Thanks for any comments.
> Michael
>

How to get last payment made

I need to get the clientid, last payment date, and last payment amount. I have tried using MAX but this does not work. Can anyone see what I'm doing wrong? I get all payments and all dates. I'm summing the payment amount incase they made two payments on the same day, not likely but...

SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]
FROM dbo.tblLoanINNERJOIN
dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_ID
GROUP BY dbo.tblLoan.Client_ID
HAVING (dbo.tblLoan.Client_ID = @.Client_ID)

Hi,

You can use "SELECT TOP 1" and "ORDER BY PaymentDate DESC"

Or you can use MAX(PaymentDate) to get the right record using that value in the WHERE clause. You may use CTE to get the code tidy.

|||

SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE (dbo.tblLoan.Client_ID = @.Client_ID)AND dbo.tblPayments.PaymentDate = (SELECT TOP 1 p.PaymentDateFROM dbo.tblPayments pINNERJOIN dbo.tblLoan lON l.Loan_ID = p.Loan_IDWHERE l.Client_ID = @.Client_IDORDER BY p.PaymentDateDESC)GROUP BY dbo.tblLoan.Client_ID

|||

Thank you very much, addie