May 21, 2012 at 9:35 am
I want to write SQL query that, when given a month of the year, will return a table with eventID as first column and next columns - all days of the given month. In the next rows it will show the events and 'Y' if the event occurred that day or otherwise 'N'. (February should have 1+28 columns whether March - 1+31) Check the example at the end for more clarity.
IF OBJECT_ID(N'tempdb..#mytable') IS NOT NULL
DROP TABLE #mytable ;
CREATE TABLE #mytable
(event_ID INT,
event_date DATE)
INSERT #mytable
VALUES (101, '2012-04-01') ,
(101, '2012-04-02') ,
(101, '2012-04-05') ,
(102, '2012-04-03') ,
(102, '2012-04-07') ,
(102, '2012-04-10') etc etc
Example result for April:
Event ID 1 2 3 4 5 6 7 8 9 10 ... 29 30
101 Y Y N N Y N N N N N ... N N
102 N N Y N N N Y N N Y ... N N
103...
May 21, 2012 at 10:05 am
You will need to use dynamic cross tabs for this. Please look at the article links below in my signature block, you will find two related to pivots and cross tabs. Please read those articles and if you have additional questions, come back here and ask.
May 21, 2012 at 10:31 am
Just had fun (with some more data-samples):
IF OBJECT_ID(N'tempdb..#mytable') IS NOT NULL
DROP TABLE #mytable ;
CREATE TABLE #mytable
(event_ID INT,
event_date DATE)
INSERT #mytable
VALUES (101, '2012-04-01') ,
(101, '2012-04-02') ,
(101, '2012-04-05') ,
(102, '2012-04-03') ,
(102, '2012-04-07') ,
(102, '2012-04-10') ,
(102, '2012-05-10') ,
(103, '2012-05-01') ,
(104, '2012-04-30')
-- that can be input param in your sp
DECLARE @reqMonth DATE
SET @reqMonth = '10 Apr 2012' -- any day in month will do
-- here the query you can put into sp:
SET NOCOUNT ON
DECLARE @pvtList NVARCHAR(500)
,@SQL NVARCHAR(1000)
SELECT TOP (DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@reqMonth)+1,0))))
ROW_NUMBER() OVER (ORDER BY a1.r) RN
INTO #mdaysno
FROM (VALUES (1),(2),(3),(4),(5),(6)) a1(r), (VALUES (1),(2),(3),(4),(5),(6)) a2(r)
SELECT RN, DATEADD(d,RN-1,DATEADD(dd,-(DAY(@reqMonth)-1),@reqMonth)) DT
INTO #mdays
FROM #mdaysno
SELECT @pvtList = ISNULL(@pvtList,'') + ',[' + CAST(RN AS VARCHAR(10)) + ']'
FROM #mdaysno
SET @pvtList = SUBSTRING(@pvtList,2,1000)
SET @SQL =
'
;WITH grp
AS
(
SELECT t.event_ID, m.RN
,CASE WHEN MAX(CASE WHEN (t.event_date = m.DT) THEN 1 ELSE 0 END) = 1 THEN ''Y'' ELSE ''N'' END found
FROM #mdays m
CROSS JOIN #mytable t
GROUP BY t.event_ID, m.RN
)
SELECT *
FROM grp
PIVOT ( max(found) for
RN in (' + @pvtList + ')) d'
exec(@sql)
-- you don't need the following in sp
drop table #mdaysno
drop table #mdays
May 22, 2012 at 12:43 am
Lynn and Eugene, thank you very much!
Eugene, you have quite interesting ways to have fun, thanks for that.:-)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply