newbie query help 1

[patrichek]

Hi,
I'm trying to query records by month using the query builder. I'm a complete newbie, can someone help me with "date" criteria? ApptDate is the field that i'd like to insert the criteria in.

here's my statement:

SELECT PatientMaster.PatientAccountNumber AS Expr1, PatientMaster.PatientFirstName AS Expr2, PatientMaster.PatientLastName AS Expr3,
PatientMaster.PatientPhysicianCode AS Expr4, ApptMaster.ApptDoctor AS Expr6, ApptMaster.ApptAccountNumber, ApptMaster.ApptDate
FROM PatientMaster INNER JOIN
ApptMaster ON PatientMaster.PatientAccountNumber = ApptMaster.ApptAccountNumber

Thanks!

 

[SQLDenis]

here is an example that will grab everuthing for February 2006
Lookup datepart in BOL
run this to see what it returns
select datepart(m,getdate())as Month,datepart(yyyy,getdate()) as Year

SELECT     p.PatientAccountNumber AS Expr1, p.PatientFirstName AS Expr2, p.PatientLastName AS Expr3, 
                      p.PatientPhysicianCode AS Expr4, a.ApptDoctor AS Expr6, a.ApptAccountNumber, a.ApptDate
FROM         PatientMaster  p INNER JOIN
                      ApptMaster  a ON p.PatientAccountNumber = a.ApptAccountNumber
WHERE datepart(m,a.ApptDate) = 2
AND datepart(yyyy,a.ApptDate) = 2006

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jby1]

Hi

You might get a better response to your question if you include a description of what output you are expecting to get.

It is quite hard (for me!) to see what you are trying to achieve with this query!

 

[jby1]

Ooops, as I sent that post, someone answered the question!!

 

[bborissov]

SELECT PatientMaster.PatientAccountNumber AS Expr1,
       PatientMaster.PatientFirstName AS Expr2,
       PatientMaster.PatientLastName AS Expr3,
       PatientMaster.PatientPhysicianCode AS Expr4,
       ApptMaster.ApptDoctor AS Expr6,
       ApptMaster.ApptAccountNumber,
       ApptMaster.ApptDate
FROM PatientMaster
     INNER JOIN ApptMaster ON PatientMaster.PatientAccountNumber =  ApptMaster.ApptAccountNumber
WHERE DatePart(month,ApptMaster.ApptDate) = 1 AND DatePart(year,ApptMaster.ApptDate) = 2006

will get all records for Jan 2006.

Borislav Borissov

 

[patrichek]

spacibo boris! ya bydy propobyu. Sorry my Russian is awful!

 

[patrichek]

Jiby1, I was trying to pull records of patients who are scheduled for surgery by month.

Boris' statement is working!

thanks again!

 

[jby1]

Thanks. I think I was just being a little thick!

 

[bborissov]

You Russian is exacly like mine ))))) I am glad it is working.

Borislav Borissov