January 29, 2009 at 7:57 pm
Hi,
I hope you guys are doing great.
I was writing this query and got stuck - How do I use "CaseNoteID DISTINCT" with the query below? If I use it with SELECT it gives me the following error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
The query goes like this:
Update Survey_Response Set Answer_Int = (SELECT Sum (Answer_Int) FROM Survey_Response WHERE SurveyID = '1499' AND QuestionId IN ('344', '345', '346', '349', '353', '359', '361', '371', '378', '379', '380', '387', '395', '399', '403', '407', '408', '409', '410'))
WHERE QuestionId = '1773' AND SurveyID = '1499'
Really appreciate your time and help.
Thanks a lot,
Regards,
N
January 29, 2009 at 8:07 pm
The format of your current update from is a lot like what i've seen in Oracle and PLSQL;
SQL server supports a different format that, at least for me, seems more intuitive:
this is a best guess, since I don't know what your tables actually look like:
[font="Courier New"]
UPDATE Survey_Response
SET Answer_Int = MySubQuery.Answer_Int
CaseNoteID = MySubQuery.CaseNoteID ,
LastUpdated = GETDATE()
FROM(
SELECT CaseNoteID,
SUM (Answer_Int) AS Answer_Int
FROM Survey_Response
WHERE SurveyID = '1499'
AND QuestionId IN ('344', '345', '346', '349', '353',
'359', '361', '371', '378', '379',
'380', '387', '395', '399', '403',
'407', '408', '409', '410')
AND QuestionId = '1773' AND SurveyID = '1499'
GROUP BY CaseNoteID ) MySubQuery
WHERE Survey_Response.CaseNoteID = MySubQuery.CaseNoteID [/font]
Lowell
January 29, 2009 at 8:32 pm
Hi Lowell,
Thank you so much for your prompt response. I was wondering what the LastUpdate = GetDate() was for? I tried executing the command and it gave me an invalid column name for 'LastUpdated' as it is not one of the columns in the table.
Here is what my table looks like (with a brief explanation of their function in parenthesis):
ResponseID (Serial Number of sorts), CaseNoteID (A client ID), SurveyID(Number for the forms in the database), QuestionID(Number for questions inside of the forms), AnswerInt (Integer type text values) AnswerText (Text type answer values).
Honestly appreciate your time and help.
Thank you so much,
Regards,
Nitin
January 29, 2009 at 8:40 pm
I don't know your table structure...i just wanted to demonstrate that you can have an update that comes from a sub query as well as other values....use this as a model, not the gospel.
if you can, post the actual CREATE TABLE YOURTABLE () definition. that will remove all doubts as to what exists and what doesn't...it's the typical thing...loosy goosy data gets you loosy goosy answers...specific data gets specific answers.
also, if you can provide INSERT INTO YOURTABLE(col list...) VALUES (actual data...) anyone here can actually test and produce a working solution for you.
nitinsbajaj (1/29/2009)
Hi Lowell,Thank you so much for your prompt response. I was wondering what the LastUpdate = GetDate() was for? I tried executing the command and it gave me an invalid column name for 'LastUpdated' as it is not one of the columns in the table.
Here is what my table looks like (with a brief explanation of their function in parenthesis):
ResponseID (Serial Number of sorts), CaseNoteID (A client ID), SurveyID(Number for the forms in the database), QuestionID(Number for questions inside of the forms), AnswerInt (Integer type text values) AnswerText (Text type answer values).
Honestly appreciate your time and help.
Thank you so much,
Regards,
Nitin
Lowell
January 29, 2009 at 8:55 pm
Sorry bout that...
CREATE TABLE [Survey_Response] (
[ResponseID] [int] IDENTITY (1, 1) NOT NULL ,
[CaseNoteID] [int] NOT NULL CONSTRAINT [DF__Survey_Re__CaseN__4944D3CA] DEFAULT (0),
[SurveyID] [int] NOT NULL CONSTRAINT [DF__Survey_Re__Surve__4A38F803] DEFAULT (0),
[QuestionID] [int] NOT NULL CONSTRAINT [DF__Survey_Re__Quest__4B2D1C3C] DEFAULT (0),
[Answer_Int] [int] NULL CONSTRAINT [DF__Survey_Re__Answe__4C214075] DEFAULT (null),
[Answer_Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Survey_Re__Answe__4D1564AE] DEFAULT (null),
CONSTRAINT [PK_Survey_Response] PRIMARY KEY NONCLUSTERED
(
[ResponseID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Survey_Response (ResponseID, CaseNoteID, SurveyID, QuestionID, Answer_Int, Answer_Text)
VALUES( )
January 29, 2009 at 9:19 pm
ok, now as best as i can figure, you wanted to update one specific row, where QuestionID='1773', with the sum of all the Answer_Int for a group of specific questions.
i've tested the code below, and it seems to do just that...but here's my question:
that looks wierd to me, becuase it seems to me you are trying to store a "total" row in the same table the details.
don't you really just need the sum of all the answer_ints grouped by CaseNoteID,SurveyID?
like this:
select CaseNoteID,SurveyID,sum(Answer_INT) AS TOTALANSWERS
from Survey_Response
GROUP BY CaseNoteID,SurveyID
what is it you are trying to accomplish?
here's the working code as an example, but you might not want to be doing this:
[font="Courier New"]USE TEMPDB
CREATE TABLE [Survey_Response] (
[ResponseID] [int] IDENTITY (1, 1) NOT NULL ,
[CaseNoteID] [int] NOT NULL CONSTRAINT [DF__Survey_Re__CaseN__4944D3CA] DEFAULT (0),
[SurveyID] [int] NOT NULL CONSTRAINT [DF__Survey_Re__Surve__4A38F803] DEFAULT (0),
[QuestionID] [int] NOT NULL CONSTRAINT [DF__Survey_Re__Quest__4B2D1C3C] DEFAULT (0),
[Answer_Int] [int] NULL CONSTRAINT [DF__Survey_Re__Answe__4C214075] DEFAULT (NULL),
[Answer_Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Survey_Re__Answe__4D1564AE] DEFAULT (NULL),
CONSTRAINT [PK_Survey_Response] PRIMARY KEY NONCLUSTERED
(
[ResponseID]
)
)
INSERT INTO Survey_Response (CaseNoteID, SurveyID, QuestionID, Answer_Int, Answer_Text)
SELECT 1,1499,344,2,'' UNION SELECT 1,1499,371,2,'' UNION
SELECT 1,1499,345,3,'' UNION SELECT 1,1499,378,2,'' UNION
SELECT 1,1499,346,4,'' UNION SELECT 1,1499,379,2,'' UNION
SELECT 1,1499,353,5,'' UNION SELECT 1,1499,380,2,'' UNION
SELECT 1,1499,359,2,'' UNION SELECT 1,1499,387,2,'' UNION
SELECT 1,1499,361,2,'' UNION SELECT 1,1499,395,2,'' UNION
SELECT 1,1499,399,2,'' UNION SELECT 1,1499,403,2,'' UNION
SELECT 1,1499,407,2,'' UNION SELECT 1,1499,408,2,'' UNION
SELECT 1,1499,409,2,'' UNION SELECT 1,1499,410,2,'' UNION
SELECT 1,1499,1773,2,''
UPDATE Survey_Response
SET Answer_Int = MySubQuery.Answer_Int,
CaseNoteID = MySubQuery.CaseNoteID
FROM(
SELECT CaseNoteID,
SUM (Answer_Int) AS Answer_Int
FROM Survey_Response
WHERE SurveyID = '1499'
AND QuestionId IN ('344', '345', '346', '349', '353',
'359', '361', '371', '378', '379',
'380', '387', '395', '399', '403',
'407', '408', '409', '410')
GROUP BY CaseNoteID ) MySubQuery
WHERE Survey_Response.CaseNoteID = MySubQuery.CaseNoteID
AND QuestionId = '1773' AND SurveyID = '1499'
[/font]
Lowell
January 29, 2009 at 9:50 pm
Hi Lowell,
There are 3 forms (identified by SurveyID 1497, 1499 and 1500), having about 50+ questions each.
Throughout a clients' life, a client (CaseNoteID) would be filling several versions of each of these forms on different dates. So, if a client (CaseNoteID = 41) filled out SurveryID = 1497 2 times, I would see it in the database twice.
I have a question row in these forms which asks for the version number (say if client is filling out form A for the 2nd time, the version will be the number 2) and also the date.
The objective is to achieve the right totals for each version of the form.
I hope I was able to communicate exactly what I am trying to do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply