masri999
3/23/2007 3:10:00 AM
On Mar 23, 1:49 am, jimj...@gmail.com wrote:
> Or hopefully not that complicated.
>
> Today Tomorrow Friday
> Who Where Act Goal Act Goal Act Goal
> Abbot Dunwitty 24 35 24 35 24 35
> Bobb Rockdale 12 30 12 30
> Clement Norcross 22 40 22 40
> Frank Compton 23 25 23 25 23 25
> Geoff Rodeo 36 0 36 0 36 0
> Sandy Cartersville 23 35 23 35
> Shelly Douglasville 32 70 32 70 32 70
> Zek SQLsville 59 70
>
> I'm trying to recreate the table above. Thus far I've managed to
> create the first 4 columns which display:
> Who, Where, Activity & Goal. Where activity is a Count Function that
> uses today's date. Now I would like to run this same query using
> tomorrow's date and tomorrow's date + 2. I've attached some psudo sql
> that should be correct. Essentially, I'd like to recreate the above
> table. So my question would be where to next? Is this a union or a
> subquery or a function? Any advice or links I can read up on? I'm
> running SQL Server 2000.
>
> SELECT
> PR.Who,
> P.Where,
> COUNT(A.Appointments) AS Act,
> D.Target
>
> FROM Appointments AS A
>
> /* JOINS */
> INNER JOIN Practice P
> ON A.LocationNo = P.LocationNo
> INNER JOIN Providers PR
> ON A.EmployeeID = PR.EmployeeID
> INNER JOIN Disco D
> ON PR.EmployeeID = D.DiscoID AND P.LocationNo = D.LocationNo
> INNER JOIN Employees E
> ON PR.EmployeeID = E.EmployeeID
>
> /* Where Conditions */
> WHERE
> /* Date = Today */
> A.DateofAppointment = CONVERT(VarChar,GetDate(),101) AND
>
> /* The Appointment Has Been Confirmed */
> A.Confirmed = 1 AND
>
> /* The weekday only */
> D.PracticeDay = DATENAME(dw, getdate()) AND
>
> /* Only Doctors */
> E.EmployeeType = 1 AND
> E.LastName <> 'SOMETHING'
>
> GROUP BY
> PR.Who,
> P.Where,
> D.Target
>
> ORDER BY
> PR.Who, P.Where
>
> Pre-emptive Thanks. Jim.
something on these lines
declare @today datetime ,@tomorrow datetime ,@dayafter datetime
set @today = CONVERT(VarChar(10),GetDate(),101)
set @tomorrow = @today + 1
set @dayafter = @today + 2
SELECT
PR.Who,
P.Where,
COUNT(CASE CONVERT(VarChar(10),A.DateofAppointment 101) = @today
then 1 end ) AS today ,
COUNT(CASE CONVERT(VarChar(10),A.DateofAppointment 101) =
@tomorrow then 1 end ) AS tomorrow ,
COUNT(CASE CONVERT(VarChar(10),A.DateofAppointment 101) =
@dayafter then 1 end ) AS dayafter ,
D.Target
FROM Appointments AS A
....
....
WHERE A.DateofAppointment >= @today and A.DateofAppointment <
dayafter + 1
GROUP BY
PR.Who,
P.Where,
D.Target