quote:

---

Originally posted by Peso

These are not challenges!
You want us to do your work for you, for free? While you can sit back and relax with your Pinã Colada in your cubicle at the callcenter?

---

quote:

---

Originally posted by morphviper

The exact results would be
CustID,a,b,c,d

158,1,0,0,1

---

quote:

---

A = Customer answered one question with no more than two day between answers. (from the answer of the first question to the answer of the last question)

---

-- Initialize the starting date to check from
DECLARE	@QuestionDate DATETIME

SELECT	@QuestionDate = '20070213'

-- Stage the data
DECLARE	@Stage TABLE
	(
		CustID INT,
		QuestionRef VARCHAR(10),
		Wk2_0 INT,
		Wk2_14 INT,
		Wk2_5 INT,
		Wk1_0 INT,
		Wk1_14 INT,
		Wk1_5 INT,
		DaysApart INT,
		PartAnswers INT
	)

INSERT		@Stage
		(
			CustID,
			QuestionRef,
			Wk2_0,
			Wk2_14,
			Wk2_5,
			Wk1_0,
			Wk1_14,
			Wk1_5
		)
SELECT		q.CustID,
		q.QuestionRef,
		SUM(CASE
			WHEN d.wk = 'wk2' AND d.theDate = q.theDate THEN q.Answer0
			ELSE 0
		END),
		SUM(CASE
			WHEN d.wk = 'wk2' AND d.theDate = q.theDate THEN q.Answer14
			ELSE 0
		END),
		SUM(CASE
			WHEN d.wk = 'wk2' AND d.theDate = q.theDate THEN q.Answer5
			ELSE 0
		END),
		SUM(CASE
			WHEN d.wk = 'wk1' AND d.theDate = q.theDate THEN q.Answer0
			ELSE 0
		END),
		SUM(CASE
			WHEN d.wk = 'wk1' AND d.theDate = q.theDate THEN q.Answer14
			ELSE 0
		END),
		SUM(CASE
			WHEN d.wk = 'wk1' AND d.theDate = q.theDate THEN q.Answer5
			ELSE 0
		END)
FROM		(
			SELECT		CustID,
					DATEADD(day, DATEDIFF(day, 0, Ob_date), 0) AS theDate,
					QuestionRef,
					MAX(CASE
						WHEN AnswerCode = 0 THEN 1
						ELSE 0
					END) AS Answer0,
					MAX(CASE
						WHEN AnswerCode BETWEEN 1 AND 4 THEN 1
						ELSE 0
					END) AS Answer14,
					MAX(CASE
						WHEN AnswerCode = 5 THEN 1
						ELSE 0
					END) AS Answer5
			FROM		#MyTable
			GROUP BY	CustID,
					DATEADD(day, DATEDIFF(day, 0, Ob_date), 0),
					QuestionRef
		) AS q
CROSS JOIN	(
			SELECT	'wk1' AS wk, DATEADD(day, DATEDIFF(day, 0, @QuestionDate), 0) AS theDate UNION ALL
			SELECT	'wk1', DATEADD(day, DATEDIFF(day, 1, @QuestionDate), 0) UNION ALL
			SELECT	'wk1', DATEADD(day, DATEDIFF(day, 2, @QuestionDate), 0) UNION ALL
			SELECT	'wk1', DATEADD(day, DATEDIFF(day, 3, @QuestionDate), 0) UNION ALL
			SELECT	'wk1', DATEADD(day, DATEDIFF(day, 4, @QuestionDate), 0) UNION ALL
			SELECT	'wk1', DATEADD(day, DATEDIFF(day, 5, @QuestionDate), 0) UNION ALL
			SELECT	'wk1', DATEADD(day, DATEDIFF(day, 6, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 7, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 8, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 9, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 10, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 11, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 12, @QuestionDate), 0) UNION ALL
			SELECT	'wk2', DATEADD(day, DATEDIFF(day, 13, @QuestionDate), 0)
		) AS d
GROUP BY	q.CustID,
		q.QuestionRef

-- Update some vital intermediate statistics
UPDATE		s
SET		s.DaysApart = q.DaysApart
FROM		@Stage AS s
INNER JOIN	(
			SELECT		CustID,
					QuestionRef,
					DATEDIFF(day, MIN(Ob_Date), MAX(Ob_Date)) AS DaysApart
			FROM		#MyTable
			GROUP BY	CustID,
					QuestionRef
		) AS q ON q.CustID = s.CustID AND q.QuestionRef = s.QuestionRef

UPDATE		s
SET		s.PartAnswers = q.PartAnswers
FROM		@Stage AS s
INNER JOIN	(
			SELECT		CustID,
					SUM(Wk2_0 + Wk2_14 + Wk2_5 + Wk1_0 + Wk2_14 + Wk2_5) AS PartAnswers
			FROM		@Stage
			WHERE		QuestionRef <> 'bb-bmf1'
			GROUP BY	CustID
		) AS q ON q.CustID = s.CustID
WHERE		s.QuestionRef = 'bb-bmf1'

-- Show the result this way
SELECT		CustID,
		QuestionRef,
		CASE
			WHEN DaysApart <= 2 THEN 1
			ELSE 0
		END AS A,
		CASE
			WHEN QuestionRef = 'bb-bmf1' AND Wk2_14 + Wk2_5 >= 2 AND Wk1_14 + Wk2_5 >= 2 THEN 1
			ELSE 0
		END AS B,
		CASE
			WHEN QuestionRef = 'bb-bmf1' AND Wk2_5 + Wk1_5 <= 0.25 * PartAnswers THEN 1
			ELSE 0
		END AS C,
		CASE
			WHEN QuestionRef = 'bb-bba0' AND Wk2_14 + Wk2_5 + Wk1_14 + Wk1_5 >= 1 THEN 1
			ELSE 0
		END AS D
FROM		@Stage
ORDER BY	CustID,
		QuestionRef

-- Show the result that way
SELECT		CustID,
		MAX(CASE
			WHEN DaysApart <= 2 THEN 1
			ELSE 0
		END) AS A,
		MAX(CASE
			WHEN QuestionRef = 'bb-bmf1' AND Wk2_14 + Wk2_5 >= 2 AND Wk1_14 + Wk2_5 >= 2 THEN 1
			ELSE 0
		END) AS B,
		MAX(CASE
			WHEN QuestionRef = 'bb-bmf1' AND Wk2_5 + Wk1_5 <= 0.25 * PartAnswers THEN 1
			ELSE 0
		END) AS C,
		MAX(CASE
			WHEN QuestionRef = 'bb-bba0' AND Wk2_14 + Wk2_5 + Wk1_14 + Wk1_5 >= 1 THEN 1
			ELSE 0
		END) AS D
FROM		@Stage
GROUP BY	CustID
ORDER BY	CustID

quote:

---

Originally posted by morphviper

I won't lie... I was in trouble, because I suck at this stuff

---