Running report for certain days

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 2005 Forums

Transact-SQL (2005)

Running report for certain days

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-05-28 : 12:53:30

I need to run a report where if it's Thursday then it will look for appointment dates for the following Monday, Friday will look for Tuesday, and then Monday will look for Wednesday. I found the Datepart syntax, however, I'm not sure if it should go in the Select statement area or where. The below query works but it literally looks for appointments 2 days out whether it be a weekday or weekend.

Select distinct c.ConfigECode as OfficeName,
p.PatsFirstName,
p.PatsLastName,
CONVERT(varchar(10),p.PatsBirthDate,101) as PatsBirthDate,
ins.CarrierName,
ins.CarrierPhone,
pol.PolEmployeeFirstName as EmpFirstName,
pol.PolEmployeeLastName as EmpLastName,
pol.PolEmployeeSSN,
CONVERT(varchar(10),pol.PolEmployeeBirthDate,101) as EmployeeBirthDate,
cov.GroupName,
appt.dt_appt as FutureAppt
From tPats p
Inner Join tConfig c
on c.ConfigOfficeNumber = p.OfficeNumber
Inner Join tInsurancePol pol
on pol.PolPatientID = p.PatsNumber
and pol.OfficeNumber = p.OfficeNumber
Inner Join tInsuranceCarrier ins
on ins.CarrierID = pol.PolCarrierID
Inner Join tInsCoverage cov
on cov.CoverageID = pol.PolCoverageID
Inner Join tbl_appt appt
on appt.PatsNumber = p.PatsNumber
and appt.OfficeNumber = p.OfficeNumber
Where c.ConfigOfficeNumber in ('241150','240728','240808')
and pol.PolStatus in (0,3)
and c.ConfigCenterType = 2
and CONVERT(varchar(10),appt.dt_appt,101) =
DATEADD(dd,+2,CONVERT(varchar(10),GETDATE(),101))
and appt.stat_cd = 0
Order by c.ConfigECode, p.PatsLastName, p.PatsFirstName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:04:10

may be this

SET DATEFIRST 1

DECLARE @Counter int

SET @Counter=CASE WHEN DATEPART(dw,GETDATE()) <6 THEN 2
                  ELSE 4
             END

Select distinct c.ConfigECode as OfficeName,
p.PatsFirstName,
p.PatsLastName,
CONVERT(varchar(10),p.PatsBirthDate,101) as PatsBirthDate,
ins.CarrierName,
ins.CarrierPhone,
pol.PolEmployeeFirstName as EmpFirstName,
pol.PolEmployeeLastName as EmpLastName,
pol.PolEmployeeSSN,
CONVERT(varchar(10),pol.PolEmployeeBirthDate,101) as EmployeeBirthDate,
cov.GroupName,
appt.dt_appt as FutureAppt
From tPats p
Inner Join tConfig c
on c.ConfigOfficeNumber = p.OfficeNumber
Inner Join tInsurancePol pol
on pol.PolPatientID = p.PatsNumber
and pol.OfficeNumber = p.OfficeNumber
Inner Join tInsuranceCarrier ins
on ins.CarrierID = pol.PolCarrierID
Inner Join tInsCoverage cov
on cov.CoverageID = pol.PolCoverageID
Inner Join tbl_appt appt
on appt.PatsNumber = p.PatsNumber
and appt.OfficeNumber = p.OfficeNumber
Where c.ConfigOfficeNumber in ('241150','240728','240808')
and pol.PolStatus in (0,3)
and c.ConfigCenterType = 2
and CONVERT(varchar(10),appt.dt_appt,101) = 
DATEADD(dd,@Counter,CONVERT(varchar(10),GETDATE(),101))
and appt.stat_cd = 0
Order by c.ConfigECode, p.PatsLastName, p.PatsFirstName

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-05-29 : 10:07:07

I had to change this CASE WHEN DATEPART(dw,GETDATE()) <6 from <6 to <4 which made it work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources