Need help converting different week ending dates

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help converting different week ending dates

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-04-12 : 23:08:19

Hi all -
Not really sure if my subject line provides some insight into what I am trying to accomplish.

Here is the issue:
- I have a parent event that has a start date.
- Belonging to that parent event, there can be multiple child events
- Each of these multiple child events will have a beginning date >= to the paren't start date.
- Each of these multiple child events can have a different duration and thus have different ending dates.
- The detail data for the child events is being recorded at a weekly level and summarized at that level.

What I am trying to figure out is a way to convert all the different start/end dates to fit within a 8 week scheme. So that the starting date, regardless of when it is, is always seen as Week1 with the next week as Week2 and so on through Week8.

Not sure if I am making sense.

If anyone has any thoughts or needs further clarification, please let me know.

Thanks
- will

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-12 : 23:24:06

you will make more sense (at least to me ) if you can provide the table DDL, some sample data DML and the expected result

KH
[spoiler]Time is always against us[/spoiler]

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-04-13 : 01:51:45

Thanks. All right. Here is the table create ddl


create schema [test] 
create table test.ParentEvent 
	(parentEventId int identity(1,1), parentEventName nvarchar(50), EventStartDate datetime, EventTypeId int)
create table test.ChildEvent 
	(childEventId int identity(1,1), parentEventId int, childEventName nvarchar(50), EventStartDate datetime)
create table test.EventActivity 
	(EventActivityId int identity(1,1), childEventId int, EventIncome decimal(10,2), EventWeekEnding datetime)
create table test.EventType 
	(EventTypeId int identity(1,1), EventType nvarchar(20))

Some data:


-- EventType table
SET IDENTITY_INSERT [test].[EventType] ON
INSERT [test].[EventType] ([EventTypeId], [EventType]) VALUES (1, N'Conference')
INSERT [test].[EventType] ([EventTypeId], [EventType]) VALUES (2, N'Seminar')
SET IDENTITY_INSERT [test].[EventType] OFF

-- ParentEvent table
SET IDENTITY_INSERT [test].[ParentEvent] ON
INSERT [test].[ParentEvent] ([parentEventId], [parentEventName], [EventStartDate], [EventTypeId]) VALUES (1, N'Dec2009_Conference', '2009-12-08', 1)
INSERT [test].[ParentEvent] ([parentEventId], [parentEventName], [EventStartDate], [EventTypeId]) VALUES (2, N'Dec2009_Seminar','2009-12-20' , 2)
INSERT [test].[ParentEvent] ([parentEventId], [parentEventName], [EventStartDate], [EventTypeId]) VALUES (3, N'Feb2010_Conference', '2010-02-10',1)
SET IDENTITY_INSERT [test].[ParentEvent] OFF

-- ChildEvent table
SET IDENTITY_INSERT [test].[ChildEvent] ON
INSERT [test].[ChildEvent] ([childEventId], [parentEventId], [childEventName], [EventStartDate]) VALUES (1, 1, N'Conference Track1', '2009-12-08')
INSERT [test].[ChildEvent] ([childEventId], [parentEventId], [childEventName], [EventStartDate]) VALUES (2, 1, N'Conference Track2', '2009-12-10')
INSERT [test].[ChildEvent] ([childEventId], [parentEventId], [childEventName], [EventStartDate]) VALUES (3, 1, N'Conference Track3', '2009-12-15')
INSERT [test].[ChildEvent] ([childEventId], [parentEventId], [childEventName], [EventStartDate]) VALUES (4, 1, N'Conference Track4', '2009-12-18')
SET IDENTITY_INSERT [test].[ChildEvent] OFF

-- Event Activity table
SET IDENTITY_INSERT [test].[EventActivity] ON
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (1, 1, CAST(230.45 AS Decimal(10, 2)), CAST(0x00009CDC00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (2, 1, CAST(576.08 AS Decimal(10, 2)), CAST(0x00009CE300000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (3, 2, CAST(17.22 AS Decimal(10, 2)), CAST(0x00009CDC00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (4, 3, CAST(99.87 AS Decimal(10, 2)), CAST(0x00009CE300000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (5, 1, CAST(1038.12 AS Decimal(10, 2)), CAST(0x00009CEA00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (6, 2, CAST(2008.10 AS Decimal(10, 2)), CAST(0x00009CEA00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (7, 4, CAST(1553.22 AS Decimal(10, 2)), CAST(0x00009CEA00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (8, 1, CAST(1955.12 AS Decimal(10, 2)), CAST(0x00009CF100000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (9, 1, CAST(1777.47 AS Decimal(10, 2)), CAST(0x00009CF800000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (10, 1, CAST(1233.00 AS Decimal(10, 2)), CAST(0x00009CFF00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (11, 1, CAST(1066.32 AS Decimal(10, 2)), CAST(0x00009D0600000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (12, 1, CAST(987.12 AS Decimal(10, 2)), CAST(0x00009D0D00000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (13, 1, CAST(1612.12 AS Decimal(10, 2)), CAST(0x00009D1400000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (14, 4, CAST(1788.90 AS Decimal(10, 2)), CAST(0x00009CF100000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (15, 4, CAST(1955.58 AS Decimal(10, 2)), CAST(0x00009CF800000000 AS DateTime))
INSERT [test].[EventActivity] ([EventActivityId], [childEventId], [EventIncome], [EventWeekEnding]) VALUES (16, 3, CAST(431.76 AS Decimal(10, 2)), CAST(0x00009CEA00000000 AS DateTime))
SET IDENTITY_INSERT [test].[EventActivity] OFF

Then, the results I'm thinking might look like this:


EventName		Week1_Amount	Week2_Amount	Week3_Amount  Week4_Amount    Week5_Amount
ConferenceTrack1	230.45			576.08			1038.12		1955.12		1777.47
ConferenceTrack2	17.22			0 (or Null)		2008.10		
ConferenceTrack3	99.87			431.76
ConferenceTrack4	1553.22			1788.90			1955.58

Thus, I am trying to get the different week ending dates to be Week1, Week2, etc. regardless of the actual date. It is just to be the indicator of the first week, second week, etc. of the event.

Asken
Starting Member

38 Posts

Posted - 2010-04-13 : 01:55:36

Something like this?

CREATE TABLE #Event ( EventId INT, StartDate DATETIME )
CREATE TABLE #ChildEvent ( EventId INT, ChildEventId INT, BeginningDate DATETIME )

INSERT #Event VALUES ( 1, '2010-04-13' )
INSERT #ChildEvent VALUES ( 1, 1, '2010-04-15' )
INSERT #ChildEvent VALUES ( 1, 2, '2010-04-16' )
INSERT #ChildEvent VALUES ( 1, 3, '2010-04-20' )
INSERT #ChildEvent VALUES ( 1, 4, '2010-04-23' )
INSERT #ChildEvent VALUES ( 1, 5, '2010-04-29' )
INSERT #ChildEvent VALUES ( 1, 6, '2010-04-30' )
INSERT #ChildEvent VALUES ( 1, 7, '2010-06-10' )
INSERT #ChildEvent VALUES ( 1, 7, '2010-06-23' ) -- Will not be shown as it's week 10 from StartDate

SELECT
	C.EventId,
	C.ChildEventId,
	E.StartDate,
	C.BeginningDate,
	'Week ' + CAST(DATEDIFF(wk, E.StartDate, C.BeginningDate) AS VARCHAR)
FROM
	#ChildEvent C

	INNER JOIN #Event E ON
	C.EventId = E.EventId
WHERE
	DATEDIFF(wk, E.StartDate, C.BeginningDate) <= 8

DROP TABLE #Event
DROP TABLE #ChildEvent

Reporting & Analysis Specialist

Asken
Starting Member

38 Posts

Posted - 2010-04-13 : 01:57:12

You can pivot the thing I wrote after summarizing by week.

Reporting & Analysis Specialist

Asken
Starting Member

38 Posts

Posted - 2010-04-13 : 03:05:31

Try this... should work.


CREATE TABLE #Event ( EventId INT, StartDate DATETIME )
CREATE TABLE #ChildEvent ( EventId INT, ChildEventId INT, BeginningDate DATETIME, Cost NUMERIC(18, 2) )

INSERT #Event VALUES ( 1, '2010-04-13' )
INSERT #ChildEvent VALUES ( 1, 1, '2010-04-15', '200.0' )
INSERT #ChildEvent VALUES ( 1, 2, '2010-04-16', '300.0' )
INSERT #ChildEvent VALUES ( 1, 3, '2010-04-20', '600.0' )
INSERT #ChildEvent VALUES ( 1, 4, '2010-04-23', '700.0' )
INSERT #ChildEvent VALUES ( 1, 5, '2010-04-29', '800.0' )
INSERT #ChildEvent VALUES ( 1, 6, '2010-04-30', '500.0' )
INSERT #ChildEvent VALUES ( 1, 7, '2010-06-10', '300.0' )
INSERT #ChildEvent VALUES ( 1, 7, '2010-06-23', '200.0' ) -- Will not be shown as it's week 10 from StartDate

INSERT #Event VALUES ( 2, '2010-04-13' )
INSERT #ChildEvent VALUES ( 2, 1, '2010-04-15', '100.0' )
INSERT #ChildEvent VALUES ( 2, 2, '2010-04-16', '400.0' )
INSERT #ChildEvent VALUES ( 2, 3, '2010-04-20', '300.0' )
INSERT #ChildEvent VALUES ( 2, 4, '2010-04-23', '100.0' )
INSERT #ChildEvent VALUES ( 2, 5, '2010-04-29', '200.0' )
INSERT #ChildEvent VALUES ( 2, 6, '2010-04-30', '700.0' )
INSERT #ChildEvent VALUES ( 2, 7, '2010-06-10', '500.0' )
INSERT #ChildEvent VALUES ( 2, 7, '2010-06-23', '500.0' ) -- Will not be shown as it's week 10 from StartDate

SELECT
	EventId, [Week 1], [Week 2], [Week 3], [Week 4], [Week 5], [Week 6], [Week 7], [Week 8]
FROM (
	SELECT
		C.EventId,
		'Week ' + CAST(DATEDIFF(wk, E.StartDate, C.BeginningDate) AS VARCHAR) AS Week,
		SUM(Cost) AS Cost
	FROM
		#ChildEvent C

		INNER JOIN #Event E ON
		C.EventId = E.EventId
	WHERE
		DATEDIFF(wk, E.StartDate, C.BeginningDate) <= 8
	GROUP BY
		C.EventId,
		'Week ' + CAST(DATEDIFF(wk, E.StartDate, C.BeginningDate) AS VARCHAR)
) AS Data
PIVOT (
	SUM(Cost)
	FOR Week IN ([Week 1],[Week 2],[Week 3],[Week 4],[Week 5],[Week 6],[Week 7],[Week 8])
) AS PivotData

DROP TABLE #Event
DROP TABLE #ChildEvent

Reporting & Analysis Specialist

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-04-13 : 11:09:05

quote:
Originally posted by Asken

Hi. Thanks so much for the help. I think that this will work perfectly. I was starting to try and use the ROW_NUMBER function to set a Week number value, but your solution seems much simpler.

Thanks!
- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources