Sql query to find slots for next four 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

General SQL Server Forums

New to SQL Server Programming

Sql query to find slots for next four days

Author

Topic

jacobmk
Starting Member

6 Posts

Posted - 2011-08-02 : 01:45:15

Hi,

I have doubts in sql query.

I have slots table. It basically contain maximum slots ,maximum slots for am and Pm
DayName slots AM PM

1 Monday 50 30 20
2 Tuesday 50 30 20
3 Wednesday 50 30 20
4 Thursday 50 30 20
5 Friday 25 25 0
6 Saturday 15 15 0
7 Sunday 0 0 0

I have appointment table. This table is used for adding appointment

table structure

Appointdate iS_AM

8/7/2011 12:00:00 AM 1
8/5/2011 12:00:00 AM 1
8/6/2011 12:00:00 AM 1
8/2/2011 12:00:00 AM 1
8/2/2011 12:00:00 AM 1
8/2/2011 12:00:00 AM 0
8/3/2011 12:00:00 AM 0
8/4/2011 12:00:00 AM 1
8/4/2011 12:00:00 AM 0

If it is 1 it is Am else PM.

I need to display remaining available slots for the next four days.
I need to avoid sundays.
How can we avoid sundays.

my query so far is this

with cte as
(

select dateName(dw,appoint_date) dayN,convert(varchar(12),appoint_date,101) appoint_date, sum(case is_am when 1 then 1 else 0 end) as AM,
sum(case is_am when 0 then 1 else 0 end) as PM ,sum (case is_am when 0 then 1 when 1 then 1 end) as Total
from pda_appoint where

convert(varchar(12),appoint_date,111) between
Convert(varchar(10), getdate() ,111) and Convert(varchar(10), dateadd(dd,3,getdate()) ,111)
group by appoint_date

)
select p.AM-cte.AM as [Rem AM],p.PM-cte.PM as [Rem PM],p.slots-cte.Total as [Rem Total] from cte inner join pda_slots p on cte.dayN=day_name

Output is as follows

remMax remAm remPM

28 19 47
30 19 49
29 19 48
23 0 23

I need to avoid sundays and is my sql query is correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 02:26:34

for avoiding sundays just use where condition like

where dateName(dw,appoint_date) <> 'Sunday'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources