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 2000 Forums
 SQL Server Development (2000)
 A Challenge for all....(Who can figure this out???

Author  Topic 

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 10:00:37
I have a table that is set up like this
CustID,CustName,QuestionID,QuestionCode,QuestionText,entrydate.....

Here is what a couple lines of data look like...

001,Name,H1a,0,'did you wash windows','01/01/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/01/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/01/2007 15:55'



Now the Question code is like this.. 0=No and 1=Yes

here is the hard part.

I have hundreds of customers that I have to sort through and I have to get a code for each one. A customer might have set
Themselves up to wash windows multiple times a day, at possible random times,
plus they can answer multiple time a day even at a non scheduled time. So the could can say no and yes at minutes apart and both
answers count.

for a two week period counting back 14 days from the search date.
Code 0 = Customer Washed windows every day for 14 days
Code 1 = Customer washed windows every day exect one day on week one or one day on week two
or washed windows everyday of week one but missed one day on week two
or washed windows everyday of week two but missed on day of week one.
Code 2 = If customer doesn't = any other code then code as 2
Code 3 = Customer did not wash the windows once a day for several days but washed them at least once during the past 14 days.
Code 4 = Customer Never Washed windows for the last 14 days

If anyone could help me figure this out I would be surprised. Every solution I have tried seems to be too taxing on the system.
It also has to be compatible with SQL Server 2000 and 2005.

Thank you to anyone who can help!!!

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-02-15 : 10:31:45
1. You should normalize your data
2. Are you saying that you only care about question H1a for the purposes of generating customer codes, or do you need to get different codes for each question?
3. Don't be too surprised if someone solves your problem, there are a bunch of smart people around here!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 10:39:09
What did I win?
SELECT		q.CustID,
		CASE
			WHEN q.w1 + q.w2 = 14 THEN 0
			WHEN q.w1 >= 6 AND q.w2 >= 6 THEN 1
			WHEN q.w1 + q.w2 = 1 THEN 3
--			WHEN q.w1 = 0 AND q.w2 = 1 THEN 3
			WHEN q.w1 + q.w2 = 0 THEN 4
			ELSE 2
		END AS Code
FROM		(
			SELECT		c.CustID,
					SUM(CASE
						WHEN c.EntryDate BETWEEN w1.d1 AND w1.d2 THEN 1
						ELSE 0
					END) AS w1,
					SUM(CASE
						WHEN c.EntryDate BETWEEN w2.d1 AND w2.d2 THEN 1
						ELSE 0
					END) AS w2
			FROM		(
						SELECT DISTINCT	CustID,
								DATEADD(day, DATEDIFF(day, 0, EntryDate), 0) AS EntryDate
						FROM		{YourTableNameHere}
						WHERE		QuestionID = 'H1a'
								AND QuestionCode = 1		
					) AS c
			CROSS JOIN	(
						SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d2 UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 3, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 4, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 5, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 6, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
					) AS w1
			CROSS JOIN	(
						SELECT DATEADD(day, DATEDIFF(day, 7, GETDATE()), 0) AS d1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d2 UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 8, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 9, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 11, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 12, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT DATEADD(day, DATEDIFF(day, 13, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
					) AS w2
			GROUP BY	c.CustID
		) AS q
ORDER BY	q.CustID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 10:40:24
I need it for each type of question...
I need to clarify code 0 and code 4..
Code 0=Customer Washed windows every day for 14 days with out answering no.
Code 4=Customer Never Washed windows for the last 14 days with out answering yes at all.
I know there are some smart people out there, that is why I am challenging anyone that thinks they can help!
I just get the data.... I can't control the original table.
I can normalize it in a temp table or something???
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 10:43:51
This might perform better
SELECT		q.CustID,
		CASE
			WHEN q.w1 + q.w2 = 14 THEN 0
			WHEN q.w1 >= 6 AND q.w2 >= 6 THEN 1
			WHEN q.w1 + q.w2 = 1 THEN 3
--			WHEN q.w1 = 0 AND q.w2 = 1 THEN 3
			WHEN q.w1 + q.w2 = 0 THEN 4
			ELSE 2
		END AS Code
FROM		(
			SELECT		c.CustID,
					SUM(CASE
						WHEN w.Week = 'w1' AND c.EntryDate BETWEEN w.d1 AND w.d2 THEN 1
						ELSE 0
					END) AS w1,
					SUM(CASE
						WHEN w.Week = 'w2' AND c.EntryDate BETWEEN w.d1 AND w.d2 THEN 1
						ELSE 0
					END) AS w2
			FROM		(
						SELECT DISTINCT	CustID,
								DATEADD(day, DATEDIFF(day, 0, EntryDate), 0) AS EntryDate
						FROM		{YourTableNameHere}
						WHERE		QuestionID = 'H1a'
								AND QuestionCode = 1		
					) AS c
			CROSS JOIN	(
						SELECT 'w1' AS [Week], DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d2 UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 3, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 4, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 5, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 6, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 7, GETDATE()), 0) AS d1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d2 UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 8, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 9, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 11, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 12, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 13, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
					) AS w
			GROUP BY	c.CustID
		) AS q
ORDER BY	q.CustID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 10:46:14
Before we get on to the complete set of source data, can't we just make sure that the logic works for one question.
You see, I am not that smart so I need help from you.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-02-15 : 10:47:15
So again you're saying that the codes are calculated based on days that people washed windows, but you also say you want to do it for every question. But one of your questions is about squeegeing - so for that question will it be whether or not they squeegeed one the last 14 days?
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 10:49:09
yeah... Sorry about that.. We where both posting at the same time....
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 10:52:17
That would be correct...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 10:54:07
Without some more sample data and your expected output based on the same sample data, it might be hard to find a acceptable solution.
Meanwhile, try this
SELECT		q.CustID,
		CASE
			WHEN q.w1 + q.w2 = 14 AND q.AnsweredNo = 1 THEN 0
			WHEN q.w1 >= 6 AND q.w2 >= 6 THEN 1
			WHEN q.w1 + q.w2 = 1 THEN 3
			WHEN q.w1 + q.w2 = 0 AND q.AnsweredYes = 0 THEN 4
			ELSE 2
		END AS Code
FROM		(
			SELECT		c.CustID,
					c.QuestionID,
					MIN(c.AnsweredNo) AS AnsweredNo,
					MAX(c.AnsweredYes) AS AnsweredYes,
					SUM(CASE
						WHEN w.Week = 'w1' AND c.EntryDate BETWEEN w.d1 AND w.d2 THEN 1
						ELSE 0
					END) AS w1,
					SUM(CASE
						WHEN w.Week = 'w2' AND c.EntryDate BETWEEN w.d1 AND w.d2 THEN 1
						ELSE 0
					END) AS w2
			FROM		(
						SELECT		CustID,
								QuestionID,
								DATEADD(day, DATEDIFF(day, 0, EntryDate), 0) AS EntryDate,
								MIN(CASE WHEN QuestionCode = 0 THEN 0 ELSE 1 END) AS AnsweredNo,
								MAX(CASE WHEN QuestionCode = 1 THEN 1 ELSE 0 END) AS AnsweredYes
						FROM		{YourTableNameHere}
						GROUP BY	CustID,
								QuestionID,
								DATEADD(day, DATEDIFF(day, 0, EntryDate), 0)
					) AS c
			CROSS JOIN	(
						SELECT 'w1' AS [Week], DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d2 UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 3, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 4, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 5, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w1', DATEADD(day, DATEDIFF(day, 6, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 7, GETDATE()), 0) AS d1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS d2 UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 8, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 9, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 11, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 12, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) UNION ALL
						SELECT 'w2', DATEADD(day, DATEDIFF(day, 13, GETDATE()), 0), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
					) AS w
			GROUP BY	c.CustID,
					c.QuestionID
		) AS q
ORDER BY	q.CustID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 11:08:57
Man!!! You are my hero...

It looks like this will work well....

You have won my total respect......

They just added another element...
It could be ran for any amount of time but never look more than 14 days back, but they could run it for 8 days and the first 7 days back would count for week one and day number 8 would be week number two...
but I still need to return the code.
Plus there might be days where they don't answer anything and I still have to count that day... and figure it into the code.

Crazy....

---Thank you for all your help so far!
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 11:39:37
here is the other issue....

They can answer multiple times a day...
The way you have the script set up now, I am getting numbers beyond 14...IF that makes sense?

They could answer yes or no to washing windows as many times as they want in one day. when figureing out the code to return. I am more worried of how many days there was an answer of Yes or No answered during the whole week or two.


That is where this get really challenging... and makes it almost impossible to figure out.

that is why this is a true challenge. I thought we had it figured out at first, but it appears we are back to the drawing board.
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 11:45:10
Better clarification on the Code....

If on 02/01/07 I got 22 answers of yes
and 2 answers of no to washing windows then for
02/01/07 I have an answer of 1 yes and 1 no.

so on code 0 there can be no answers during the 14 days of a 'no' or code 4 no answers of a 'yes' during the 14 days.

So if anyone or you "Peso" can figure this out. I would be greatly thankful of it....

that is why I felt this might be a very hard challenge..
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 12:24:07
quote:
Originally posted by Peso

Without some more sample data and your expected output based on the same sample data, it might be hard to find a acceptable solution.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 13:25:23

001,Name,H1a,0,'did you wash windows','01/01/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/01/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/01/2007 15:55'
001,Name,H1a,0,'did you wash windows','01/02/2007 13:21'
001,Name,H1b,0,'Did you squeege','01/02/2007 15:55'
001,Name,H1a,1,'did you wash windows',01/03/2007 15:45'
001,Name,H1b,1,'Did you squeege','01/03/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/03/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/03/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/03/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/03/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/03/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/03/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/03/2007 12:21'
001,Name,H1a,1,'did you wash windows',01/03/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/03/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/03/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/03/2007 15:45'
001,Name,H1b,1,'Did you squeege','01/03/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/04/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/04/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/04/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/04/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/04/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/04/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/04/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/04/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/04/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/04/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/04/2007 15:45'
001,Name,H1b,1,'Did you squeege','01/05/2007 15:55'
001,Name,H1a,1,'did you wash windows','01/05/2007 13:21'
001,Name,H1a,1,'did you wash windows',01/05/2007 15:45'
001,Name,H1b,0,'Did you squeege','01/05/2007 15:55'


The desired outcome is like you already did.

custid and code
001,1
002,4

I hope that helps??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 14:31:21
What do you think?
declare	@searchdate datetime
select	@searchdate = '20070109'

-- prepare sample data
declare	@s table (custid varchar(3), custname varchar(4), questionid varchar(3), questioncode tinyint, questiontext varchar(200), entrydate datetime)

insert	@s
select	'001', 'Name', 'H1a', 0, 'did you wash windows', '01/01/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/01/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/01/2007 15:55' union all
select	'001', 'Name', 'H1a', 0, 'did you wash windows', '01/02/2007 13:21' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/02/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 15:45' union all
select	'001', 'Name', 'H1b', 1, 'Did you squeege', '01/03/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/03/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/03/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 12:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/03/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/03/2007 15:45' union all
select	'001', 'Name', 'H1b', 1, 'Did you squeege', '01/03/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/04/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/04/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/04/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/04/2007 15:45' union all
select	'001', 'Name', 'H1b', 1, 'Did you squeege', '01/05/2007 15:55' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/05/2007 13:21' union all
select	'001', 'Name', 'H1a', 1, 'did you wash windows', '01/05/2007 15:45' union all
select	'001', 'Name', 'H1b', 0, 'Did you squeege', '01/05/2007 15:55'

-- Stage the source data
DECLARE	@Stage TABLE (CustID VARCHAR(3), QuestionID VARCHAR(3), Wk1AnswerNo INT, Wk1AnswerYes INT, Wk2AnswerNo INT, Wk2AnswerYes INT) 

INSERT		@Stage (CustID, QuestionID, Wk1AnswerNo, Wk1AnswerYes, Wk2AnswerNo, Wk2AnswerYes)
SELECT		w.CustID,
		w.QuestionID,
		SUM(CASE WHEN w.WeekNo = 'wk1' THEN w.AnswerNo ELSE 0 END) AS Wk1AnswerNo,
		SUM(CASE WHEN w.WeekNo = 'wk1' THEN w.AnswerYes ELSE 0 END) AS Wk1AnswerYes,
		SUM(CASE WHEN w.WeekNo = 'wk2' THEN w.AnswerNo ELSE 0 END) AS Wk2AnswerNo,
		SUM(CASE WHEN w.WeekNo = 'wk2' THEN w.AnswerYes ELSE 0 END) AS Wk2AnswerYes
FROM		(
			SELECT		c.CustID,
					q.QuestionID,
					d.WeekNo,
					d.EntryDate,
					MAX(CASE
						WHEN a.QuestionCode = 0 THEN 1
						ELSE 0
					END) AS AnswerNo,
					MAX(CASE
						WHEN a.QuestionCode = 1 THEN 1
						ELSE 0
					END) AS AnswerYes
			FROM		(
						SELECT DISTINCT	CustID
						FROM		@s
					) AS c
			CROSS JOIN	(
						SELECT DISTINCT	QuestionID
						FROM		@s
					) AS q
			CROSS JOIN	(
						SELECT 'wk1' AS WeekNo, DATEADD(day, DATEDIFF(day, 0, @SearchDate), 0) AS EntryDate UNION ALL
						SELECT 'wk1', DATEADD(day, DATEDIFF(day, 1, @SearchDate), 0) UNION ALL
						SELECT 'wk1', DATEADD(day, DATEDIFF(day, 2, @SearchDate), 0) UNION ALL
						SELECT 'wk1', DATEADD(day, DATEDIFF(day, 3, @SearchDate), 0) UNION ALL
						SELECT 'wk1', DATEADD(day, DATEDIFF(day, 4, @SearchDate), 0) UNION ALL
						SELECT 'wk1', DATEADD(day, DATEDIFF(day, 5, @SearchDate), 0) UNION ALL
						SELECT 'wk1', DATEADD(day, DATEDIFF(day, 6, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 7, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 8, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 9, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 10, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 11, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 12, @SearchDate), 0) UNION ALL
						SELECT 'wk2', DATEADD(day, DATEDIFF(day, 13, @SearchDate), 0)
					) AS d
			LEFT JOIN	(
						SELECT DISTINCT	CustID,
								QuestionID,
								QuestionCode,
								DATEADD(day, DATEDIFF(day, 0, EntryDate), 0) AS EntryDate
						FROM		@s
					) AS a ON a.CustID = c.CustID AND a.QuestionID = q.QuestionID AND a.EntryDate = d.EntryDate
			GROUP BY	c.CustID,
					q.QuestionID,
					d.WeekNo,
					d.EntryDate
		) AS w
GROUP BY	w.CustID,
		w.QuestionID

-- Show the expected result
SELECT		CustID,
		QuestionID,
		CASE
			WHEN Wk2AnswerYes + Wk1AnswerYes = 14 AND Wk2AnswerNo + Wk1AnswerNo = 0 THEN 0
			WHEN Wk2AnswerYes >= 6 AND Wk1AnswerYes >= 6 THEN 1
			WHEN Wk2AnswerNo >= 0 AND Wk1AnswerNo = 0 AND Wk2AnswerYes + Wk1AnswerYes >= 1 THEN 3
			WHEN Wk2AnswerNo + Wk1AnswerNo = 15 AND Wk2AnswerYes + Wk1AnswerYes = 0 THEN 4
			ELSE 2
		END AS Code
FROM		@Stage
ORDER BY	CustID,
		QuestionID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 15:18:30
Hey...

This looks like it is going to work.

You have taken my challenge and succeeded!
Thank you!!!!!!!!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-15 : 15:19:47
I thought you were in desparate need of your paycheck


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

morphviper
Yak Posting Veteran

60 Posts
Posted - 2007-02-15 : 15:29:54
Yeah... Something like that...
I had a solution but only tested with a few cust id's but when I tested it with hundreds of customer ID's it would error out for memory issues... and this was due today... So I was in a panic... You rescued me!

Thanks man!

I hope someday I will be able to help someone on this forum,
but that might be a while.. I am still learning and after seeing your solution... I still have a ton to learn... You are a smart man!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site