WHERE Clause Help Needed - SQL Server Forums

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)

WHERE Clause Help Needed

Author

Topic

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-21 : 23:07:37

HI,

I have a two column table in a SQL 2008 database named Question and Response. The Question column generates a row for one of three questions... First Name, Last Name and Start Date. So the table looks like this...

Question / Response
1 / Joe
2 / Smith
3 / 3/30/2010 4:00:00 AM
1 / Jane
2 / Doe
3 / 3/28/2010 4:00:00 AM
etc.

Ok, my query runs perfect based on my needs with the exception of the WHERE clause. I want to filter my results based on the current date.

Can anybody assist me please? My query is below...

SELECT qr.UniqueResponseID,
'<b>Name </b>' +
MAX(CASE WHEN q.DynamicQuestionID = '1c0bb648-e1f7-4fcf-b440-5d95e815a7a2' THEN qr.Response ELSE NULL END) +
'<b>Date </b>' +
MAX(CASE WHEN q.DynamicQuestionID = '40a739bb-4542-4e35-9656-475819810507' THEN qr.Response ELSE NULL END) AS 'Todays Date'

FROM DynamicForms_QuestionResponse qr
JOIN DynamicForms_Question q ON q.DynamicQuestionID = qr.DynamicQuestionID
WHERE ??????????
GROUP BY qr.UniqueResponseID

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 02:34:52

Maybe I did get you wrong but that looks like a very poor design, sorry.
Why do you not have a table with columns for each needed value?
Question / First_Name / Last_Name ... and so on

No, you're never too old to Yak'n'Roll if you're too young to die.

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-22 : 08:31:10

Yes,I agree... it is a poor design but let me explain. I'm using DotNetNuke and the forms module that I'm using has it built this way. Basically, I'm trying to work with what I have and honestly I'm just about there with the exception of the WHERE cause.

Can you assist me?

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 09:10:41

I don't like it but try this:


WHERE
'<b>Name </b>' +
MAX(CASE WHEN q.DynamicQuestionID = '1c0bb648-e1f7-4fcf-b440-5d95e815a7a2' THEN qr.Response ELSE NULL END) +
'<b>Date </b>' +
MAX(CASE WHEN q.DynamicQuestionID = '40a739bb-4542-4e35-9656-475819810507' THEN qr.Response ELSE NULL END)
LIKE 
'%'+convert(varchar(10),getdate(),101)+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-22 : 09:25:14

How do you associate a set of questions to a person / test?

With just two columns (question and answer) how do you know who answered it.

For example there's nothing in the data posted to indicate whether the person is Jane Doe or Jane Smith

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-22 : 09:33:00

There are additional columns... in particular UniqueResponseID which is how I us my Group the responses.

Here's the error I received webFred...

Error on Primary Select: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 09:38:14

I also don't like this but give it a try:


select * from
(SELECT qr.UniqueResponseID,
 '<b>Name </b>' +
 MAX(CASE WHEN q.DynamicQuestionID = '1c0bb648-e1f7-4fcf-b440-5d95e815a7a2' THEN qr.Response ELSE NULL END) +
 '<b>Date </b>' +
 MAX(CASE WHEN q.DynamicQuestionID = '40a739bb-4542-4e35-9656-475819810507' THEN qr.Response ELSE NULL END) AS 'Todays Date'
 FROM DynamicForms_QuestionResponse qr
 JOIN DynamicForms_Question q ON q.DynamicQuestionID = qr.DynamicQuestionID )dt
where dt.[Todays Date]
LIKE 
'%'+convert(varchar(10),getdate(),101)+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-22 : 14:21:18

Hi... I received another error. I really appreciate your help. Any other suggestions?

Error on Primary Select: Column 'DynamicForms_QuestionResponse.UniqueResponseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 14:35:31

quote:

Originally posted by webfred

I also don't like this but give it a try:


select * from
(SELECT qr.UniqueResponseID,
 '<b>Name </b>' +
 MAX(CASE WHEN q.DynamicQuestionID = '1c0bb648-e1f7-4fcf-b440-5d95e815a7a2' THEN qr.Response ELSE NULL END) +
 '<b>Date </b>' +
 MAX(CASE WHEN q.DynamicQuestionID = '40a739bb-4542-4e35-9656-475819810507' THEN qr.Response ELSE NULL END) AS 'Todays Date'
 FROM DynamicForms_QuestionResponse qr
 JOIN DynamicForms_Question q ON q.DynamicQuestionID = qr.DynamicQuestionID 
GROUP BY qr.UniqueResponseID)dt
where dt.[Todays Date]
LIKE 
'%'+convert(varchar(10),getdate(),101)+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-22 : 14:53:55

Well, I think we're almost there. At the moment, it displays zero results. I see 'Todays Date' as the column header but no data under it.

I was expecting to see 1 entry for today... 3/22/2010.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 14:58:27

Then make a select WITHOUT the WHERE clause so you can see the way the DATE in your result is formatted.
Maybe it is different formatted so a compare with '3/22/2010' cannot match exactly.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 15:09:45

Maybe it is because the dateparts are without leading zeroes?
Try this:
where dt.[Todays Date]
like
'%'+
convert(varchar(2),datepart(mm,getdate()))+
'/'+
convert(varchar(2),datepart(dd,getdate()))+
'/'+
convert(varchar(4),datepart(yyyy,getdate()))+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-22 : 15:35:01

That did the trick... you're amazing! Thanks so much for your help on this. I greatly appreciate it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 16:34:47

I'm not proud about it but it works and that is fine

No, you're never too old to Yak'n'Roll if you're too young to die.

MartyBevo
Starting Member

10 Posts

Posted - 2010-03-23 : 00:08:51

I actually have one more question if it's not too much trouble. At the moment, the date displays on the screen as 3/23/2010 4:00:00 AM.

Is there anyway to make it display March 23, 2010?

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-23 : 04:22:56

I think it is not easy because it look like you're handling with string data.
Normally you should have a datetime column in your database and do the formatting in your front end.
I really don't know exactly how your data looks like but try this:


select * from
(SELECT qr.UniqueResponseID,
 '<b>Name </b>' +
 MAX(CASE WHEN q.DynamicQuestionID = '1c0bb648-e1f7-4fcf-b440-5d95e815a7a2' 
          THEN qr.Response ELSE NULL END) +
 '<b>Date </b>' +
 MAX(CASE WHEN q.DynamicQuestionID = '40a739bb-4542-4e35-9656-475819810507' 
          THEN convert(varchar(30),convert(datetime,qr.Response),107) ELSE NULL END) AS [Todays Date]
 FROM DynamicForms_QuestionResponse qr
 JOIN DynamicForms_Question q ON q.DynamicQuestionID = qr.DynamicQuestionID 
GROUP BY qr.UniqueResponseID)dt
where dt.[Todays Date] 
like '%' + select convert(varchar(30),getdate(),107) + '%'

If this isn't working then I don't know...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources