July 19, 2013 at 10:48 am
Hi all,
I have a table that stores "register" information, e.g. UserID, RegisterDate, Available/Unavailable, etc. but would like to display this on a month-by-month view for all members. Essentially, it would be showing the previous/current/next month name at the top, then for each member an entire list of dates within that month with a corresponding tick or cross to denote whether they were present or not.
Here's my code so far:
CREATE TABLE #Register (
EntryIDINTEGER IDENTITY(1,1) PRIMARY KEY
,EntryDateDATETIME
,EntryStatusVARCHAR(10)
,AssociatedUserIDINTEGER
)
CREATE TABLE #Users (
UserIDINTEGER
,UsernameVARCHAR(30)
)
INSERT INTO #Users
(UserID, Username)
SELECT1, 'Agent Smith'
UNION
SELECT2, 'The Oracle'
UNION
SELECT3, 'Neo'
INSERT INTO #Register
(EntryDate,EntryStatus,AssociatedUserID)
SELECT'2013-07-12','X',1
UNION
SELECT'2013-07-12','X',2
UNION
SELECT'2013-07-12','0',3
UNION
SELECT'2013-07-13','0',1
UNION
SELECT'2013-07-13','0',2
UNION
SELECT'2013-07-13','X',3
UNION
SELECT'2013-07-14','X',1
UNION
SELECT'2013-07-14','0',2
UNION
SELECT'2013-07-14','0',3
UNION
SELECT'2013-07-15','0',1
UNION
SELECT'2013-07-15','0',2
UNION
SELECT'2013-07-15','0',3
UNION
SELECT'2013-07-16','X',1
UNION
SELECT'2013-07-16','X',2
UNION
SELECT'2013-07-16','0',3
UNION
SELECT'2013-07-17','0',1
UNION
SELECT'2013-07-17','X',2
UNION
SELECT'2013-07-17','0',3
UNION
SELECT'2013-07-18','0',1
UNION
SELECT'2013-07-18','0',2
UNION
SELECT'2013-07-18','0',3
DECLARE@p_ActiveDateDATETIME
DECLARE @ActiveMonthINTEGER
,@ActiveYearINTEGER
SELECT@ActiveMonth= MONTH(ISNULL(@p_ActiveDate,GETDATE()))
,@ActiveYear= YEAR(ISNULL(@p_ActiveDate,GETDATE()))
CREATE TABLE #MonthDays (
MonthIDINTEGER
,DayCountINTEGER
,[MonthName]VARCHAR(10)
,AbbrMonthNameVARCHAR(3)
)
INSERT INTO #MonthDays
(MonthID, DayCount,[MonthName],AbbrMonthName)
SELECT1,31, 'January','Jan'
UNION
SELECT2, CASE WHEN @ActiveYear % 4 = 0 THEN 28 ELSE 29 END,'February','Feb'
UNION
SELECT3,31,'March','Mar'
UNION
SELECT4,30,'April','Apr'
UNION
SELECT5,31,'May','May'
UNION
SELECT6,30,'June','Jun'
UNION
SELECT7,31,'July','Jul'
UNION
SELECT8,31,'August','Aug'
UNION
SELECT9,30,'September','Sep'
UNION
SELECT10,31,'October','Oct'
UNION
SELECT11,30,'November','Nov'
UNION
SELECT12,31,'December','Dec'
;WITH cteDates (CalendarDate, DayCount, MonthID)
AS (
SELECTCONVERT(DATE,CONVERT(VARCHAR(4),@ActiveYear) + '-'
+ CASE WHEN @ActiveMonth < 10
THEN '0'
ELSE ''
END + CONVERT(VARCHAR(2),@ActiveMonth) + '-'
+ '01')
,md.DayCount
,md.MonthID
FROM#MonthDays AS md
WHEREmd.MonthID= @ActiveMonth
UNION ALL
SELECTDATEADD(DAY,1,c.CalendarDate), c.DayCount,c.MonthID
FROMcteDates c INNER JOIN
#MonthDays AS md2
ON(md2.MonthID=c.MonthID)
WHEREDATEADD(DAY,1,c.CalendarDate) <= DATEADD(DAY,md2.DayCount-1,CONVERT(DATE,CONVERT(VARCHAR(4),@ActiveYear) + '-'
+ CASE WHEN @ActiveMonth < 10
THEN '0'
ELSE ''
END + CONVERT(VARCHAR(2),@ActiveMonth) + '-'
+ '01'))
)
-- get the list of CalendarDates into a table variable
SELECT*
INTO#CalendarDates
FROMcteDates
DROP TABLE #Register
DROP TABLE #Users
DROP TABLE #MonthDays
DROP TABLE #CalendarDates
Now I have all the calendar dates, and subsequent registry events, I'd like to PIVOT it so that I have something like this:
1 2 3 4 5 6 7 8 9 10 ....
-------------------------------------------------------------------------------------
Name X 0 0 X 0 X 0 X 0 0
...but unfortunately don't know how! As always, any help is gratefully received!
Thanks,
Kevin.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
July 19, 2013 at 11:55 am
Maybe you want something like this or maybe I'm misunderstanding you. Feel free to correct me 🙂
SELECTu.Username,
MAX( CASE WHEN DAY( r.EntryDate) = 1 THEN EntryStatus ELSE '0' END) AS Day01,
MAX( CASE WHEN DAY( r.EntryDate) = 2 THEN EntryStatus ELSE '0' END) AS Day02,
MAX( CASE WHEN DAY( r.EntryDate) = 3 THEN EntryStatus ELSE '0' END) AS Day03,
MAX( CASE WHEN DAY( r.EntryDate) = 4 THEN EntryStatus ELSE '0' END) AS Day04,
MAX( CASE WHEN DAY( r.EntryDate) = 5 THEN EntryStatus ELSE '0' END) AS Day05,
MAX( CASE WHEN DAY( r.EntryDate) = 6 THEN EntryStatus ELSE '0' END) AS Day06,
MAX( CASE WHEN DAY( r.EntryDate) = 7 THEN EntryStatus ELSE '0' END) AS Day07,
MAX( CASE WHEN DAY( r.EntryDate) = 8 THEN EntryStatus ELSE '0' END) AS Day08,
MAX( CASE WHEN DAY( r.EntryDate) = 9 THEN EntryStatus ELSE '0' END) AS Day09,
MAX( CASE WHEN DAY( r.EntryDate) = 10 THEN EntryStatus ELSE '0' END) AS Day10,
MAX( CASE WHEN DAY( r.EntryDate) = 11 THEN EntryStatus ELSE '0' END) AS Day11,
MAX( CASE WHEN DAY( r.EntryDate) = 12 THEN EntryStatus ELSE '0' END) AS Day12,
MAX( CASE WHEN DAY( r.EntryDate) = 13 THEN EntryStatus ELSE '0' END) AS Day13,
MAX( CASE WHEN DAY( r.EntryDate) = 14 THEN EntryStatus ELSE '0' END) AS Day14,
MAX( CASE WHEN DAY( r.EntryDate) = 15 THEN EntryStatus ELSE '0' END) AS Day15,
MAX( CASE WHEN DAY( r.EntryDate) = 16 THEN EntryStatus ELSE '0' END) AS Day16,
MAX( CASE WHEN DAY( r.EntryDate) = 17 THEN EntryStatus ELSE '0' END) AS Day17,
MAX( CASE WHEN DAY( r.EntryDate) = 18 THEN EntryStatus ELSE '0' END) AS Day18,
MAX( CASE WHEN DAY( r.EntryDate) = 19 THEN EntryStatus ELSE '0' END) AS Day19,
MAX( CASE WHEN DAY( r.EntryDate) = 20 THEN EntryStatus ELSE '0' END) AS Day20,
MAX( CASE WHEN DAY( r.EntryDate) = 21 THEN EntryStatus ELSE '0' END) AS Day21,
MAX( CASE WHEN DAY( r.EntryDate) = 22 THEN EntryStatus ELSE '0' END) AS Day22,
MAX( CASE WHEN DAY( r.EntryDate) = 23 THEN EntryStatus ELSE '0' END) AS Day23,
MAX( CASE WHEN DAY( r.EntryDate) = 24 THEN EntryStatus ELSE '0' END) AS Day24,
MAX( CASE WHEN DAY( r.EntryDate) = 25 THEN EntryStatus ELSE '0' END) AS Day25,
MAX( CASE WHEN DAY( r.EntryDate) = 26 THEN EntryStatus ELSE '0' END) AS Day26,
MAX( CASE WHEN DAY( r.EntryDate) = 27 THEN EntryStatus ELSE '0' END) AS Day27,
MAX( CASE WHEN DAY( r.EntryDate) = 28 THEN EntryStatus ELSE '0' END) AS Day28,
MAX( CASE WHEN DAY( r.EntryDate) = 29 THEN EntryStatus ELSE '0' END) AS Day29,
MAX( CASE WHEN DAY( r.EntryDate) = 30 THEN EntryStatus ELSE '0' END) AS Day30,
MAX( CASE WHEN DAY( r.EntryDate) = 31 THEN EntryStatus ELSE '0' END) AS Day31
FROM#Register r
JOIN #Users u ON r.AssociatedUserID = u.UserID
GROUP BY u.Username
If you want to make it dynamic, please read the following articles.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply