sql query for finding slots for next four days avoiding sunday

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

开发者_如何学JAVA

Output is as follows

remMax remAm  remPM

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

I need to avoid sundays when calculating next four days and is my sql query is correct

---

How about that.

SELECT TOP 4
    dateName(dw,a.appoint_date) dayN,
    (s.AM - SUM(case a.is_am when 1 then 1  else 0   end)) AS Remaining AM,
    (s.PM - SUM(case a.is_am when 0 then 1  else 0   end)) as Remaining PM,
    (s.slots - COUNT(a.is_am)) AS Remaining Total Slots
FROM
    pda_appoint a, slot s
WHERE
    dateName(dw,a.appoint_date) = s.DayName
    AND dateName(dw,a.appoint_date) != 'Sunday'
    AND a.appoint_date > GETDATE()
GROUP BY a.appoint_date
ORDER BY a.appoint_date

---

How about this

                declare @t table (DayName1 varchar(25), slots int, am int, pm int)
            insert @t values('Monday',50,30,20)
            insert @t values('Tuesday',50,30,20)
            insert @t values('Wednesday',50,30,20)
            insert @t values('Thursday',50,30,20)
            insert @t values('Friday',50,30,20)
            insert @t values('Saturday',50,30,20)
            insert @t values('Sunday',50,30,20)


            declare @t1 table (appoint_date datetime, is_am int)
            insert @t1 values('8/9/2011',0)
            insert @t1 values('8/10/2011',0)
            insert @t1 values('8/10/2011',1)

      /*   You can create the below as a Table valued function that will return the values for next 4 days .you need to pass @appoint_date as a parameter*/

            declare @appoint_date datetime
            set @appoint_date='8/6/2011'
            ;with cte as
            (

            select dateName(dw,@appoint_date) dayN,
            convert(varchar(12),@appoint_date,101) appoint_date,

             1 as num


             Union all
             select 
            dateName(dw,DATEADD(day, 1, appoint_date)) dayN,
            convert(varchar(12),DATEADD(day, 1, appoint_date),101) appoint_date,

             num+1
            from cte

            where num<5

            )



            select top 4 dayN,(c.AM-temp.AM) as AM,(c.PM-temp.PM) as PM,(c.Slots-Temp.Total) as Total 
            from
            (
            select  TOP 4
             dateName(dw,a.appoint_date) dayN,   
             SUM(case b.is_am when 1 then 1  else 0   end) AS AM,   
             SUM(case b.is_am when 0 then 1  else 0   end) as PM,   
               COUNT(b.is_am) AS Total

                from cte a left outer join @t1 b
                on a.appoint_date=b.appoint_date
                where a.dayN !='Sunday'
                group by a.appoint_date
                )Temp

                inner join @t c on Temp.dayN=c.dayname1

---

dayN              AM    PM  Total
Saturday    30  20  50
Monday            30    20  50
Tuesday 30  19  49
Wednesday   29  19  48