Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

sql query to show month calendar view

Hi guys been a while and I am at a lost..

I need a month view of days ie:

Calendar
but what i need is for 6th to show someone name that is on leave...

code for finding who is on leave and for how long is

SELECT (b.title+' '+ b.fname+' '+  b.sname) as staff, datename(dw,sb.datetime_from)as dayofweek,sb.datetime_from, 
DATEDIFF(DAY,sb.datetime_from, sb.datetime_to)as daycount, bt.break_name

FROM staff_break AS sb INNER JOIN
break_type AS bt ON sb.break_type_id = bt.break_type_id RIGHT OUTER JOIN
vstaff AS b ON sb.staff_id = b.staff_id
WHERE (b.staff_id > 0)
and datetime_from >=GETDATE()-31
order by datetime_from

Open in new window



i had code to create a calendar, but to be honest this may not help

with monthDates
as
(
    select  DATEADD(month, datediff(month, 0, getdate()),0) as d
            ,DATEPART(week, DATEADD(month, datediff(month, 0, getdate()),0)) as w
    union all
    select  DATEADD(day, 1, d)
            ,DATEPART(week, DATEADD(day, 1, d))
    from monthDates
    where d < DATEADD(month, datediff(month, 0, getdate())+1,-1)
)

select  max(case when datepart(dw, d) = 1 then datepart(d,d) else null end) as [Sun]
        ,max(case when datepart(dw, d) = 2 then datepart(d,d) else null end) as [Mon]
        ,max(case when datepart(dw, d) = 3 then datepart(d,d) else null end) as [Tue]
        ,max(case when datepart(dw, d) = 4 then datepart(d,d) else null end) as [Wed]
        ,max(case when datepart(dw, d) = 5 then datepart(d,d) else null end) as [Thu]
        ,max(case when datepart(dw, d) = 6 then datepart(d,d) else null end) as [Fri]
        ,max(case when datepart(dw, d) = 7 then datepart(d,d) else null end) as [Sat]
from monthDates
group by w

Open in new window



ideall what i want as end result in sql is

Ideal Result
realyy hope you can help
0
James Murrell
Asked:
James Murrell
  • 4
  • 3
1 Solution
 
PortletPaulCommented:
What happens if there are multiple people on leave on a day?
0
 
James MurrellProduct SpecialistAuthor Commented:
Whoops i forgotten about that ideally need them to added as well
0
 
PortletPaulCommented:
added in what manner?
extra rows? a concatenated string of names?

sorry, but you need to consider the nature of the expected result
(and then how it will be presented for consumption)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
James MurrellProduct SpecialistAuthor Commented:
no worries: sorry i thought i posted the comment last week...

in image above whereit says James - holiday  I would love it to say

for example

6th James - Holiday
      David - Holiday
      Richard - Holiday

if all three where off that day
0
 
PortletPaulCommented:
>>"sorry i thought i posted the comment last week..." I did :)
now going to bed though

will try to get back to this tomorrow
0
 
PortletPaulCommented:
Here is something for discussion. It shows multiple staff away in several places. Not I have not tried to implement the table [break_type] and the data used is rudimentary.
|    SUN |        MON |        TUE |        WED |        THU |               FRI |   SAT |
|--------|------------|------------|------------|------------|-------------------|-------|
| (null) |     (null) |     (null) |     (null) |     (null) |             Wilma | Wilma |
|  Wilma | Fred,Wilma | Fred,Wilma | Fred,Wilma | Fred,Wilma | Fred,Barney,Wilma | Wilma |
|  Wilma |      Wilma |      Wilma |      Wilma |      Wilma |             Wilma | Wilma |
|  Wilma |      Wilma |      Wilma |      Wilma |      Wilma |             Wilma |    23 |
|     24 |         25 |         26 |         27 |         28 |                29 |    30 |


WITH
      mDates
      AS (
                  SELECT
                        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)                 AS d
                      , DATEPART(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS w
                  UNION ALL
                        SELECT
                              DATEADD(DAY, 1, d)
                            , DATEPART(WEEK, DATEADD(DAY, 1, d))
                        FROM mDates
                        WHERE d < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)
            ),
      monthDates
      AS (
                  SELECT
                        mDates.d
                      , mdates.w
                      , COALESCE(
                                STUFF(
                                       (SELECT ',' +  b.fname
                                       FROM staff_break AS sb
                                       JOIN vstaff AS b ON sb.staff_id = b.staff_id
                                       WHERE mDates.d BETWEEN sb.datetime_from AND sb.datetime_to
                                       FOR XML PATH('')),1,1,'')
                                  , CONVERT(varchar(20), datepart(d,d))
                         ) AS DisplayMe
                  FROM mDates
          )

SELECT
      MAX(CASE WHEN DATEPART(dw, d) = 1 THEN DisplayMe ELSE NULL END) AS [Sun]
    , MAX(CASE WHEN DATEPART(dw, d) = 2 THEN DisplayMe ELSE NULL END) AS [Mon]
    , MAX(CASE WHEN DATEPART(dw, d) = 3 THEN DisplayMe ELSE NULL END) AS [Tue]
    , MAX(CASE WHEN DATEPART(dw, d) = 4 THEN DisplayMe ELSE NULL END) AS [Wed]
    , MAX(CASE WHEN DATEPART(dw, d) = 5 THEN DisplayMe ELSE NULL END) AS [Thu]
    , MAX(CASE WHEN DATEPART(dw, d) = 6 THEN DisplayMe ELSE NULL END) AS [Fri]
    , MAX(CASE WHEN DATEPART(dw, d) = 7 THEN DisplayMe ELSE NULL END) AS [Sat]
FROM monthDates
GROUP BY
      w


CREATE TABLE vstaff
	([staff_id] int, [title] varchar(3), [fname] varchar(6), [sname] varchar(10))
;
	
INSERT INTO vstaff
	([staff_id], [title], [fname], [sname])
VALUES
	(1, 'Mr', 'Fred', 'Flintstone'),
	(2, 'Mr', 'Barney', 'Rubble'),
	(3, 'Mrs', 'Wilma', 'Flinstone')
;

CREATE TABLE staff_break
	([staff_id] int, [datetime_from] datetime, [datetime_to] datetime)
;
	
INSERT INTO staff_break
	([staff_id], [datetime_from], [datetime_to])
VALUES
	(1, '2013-11-04 00:00:00', '2013-11-08 00:00:00'),
	(2, '2013-11-08 00:00:00', '2013-11-08 00:00:00'),
	(3, '2013-10-28 00:00:00', '2013-11-22 00:00:00')
;

Open in new window

http://sqlfiddle.com/#!3/49f12/21
0
 
James MurrellProduct SpecialistAuthor Commented:
Wow you are great, big thank you. something so simple - but very hard... great wish i could give more points


thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now