I have a very simple problem I am not able figure out on my own. Perhaps some of you can see where my thinking goes wrong.
Real life situation: My coworkers are filling out forms that describe our cases. Usually only one form for each case is filled. However, sometimes the situation changes and a new form is filled, and this information 'replaces' the old information. I need to make a list of the date of the last completed form for each case.
I have a view that puts together a list of ALL completed forms (vis_ArkivsakID_for_fullfø
kjema). From this I want to select the rows that have the last form for each distinct case. There are two different columns that determine the last form:
1) the date of completing the form (FullførtSkjema_Dato)
2) ResponseID, which is needed if there are more than one form filled out on the last date.
As you can see I have tried to do two nested select statements. If I keep these separate, they work, but I get too many cases. Therefore, I think that nesting them by first selecting the forms with max date within each case, and then within each date the max ResponseID.
CREATE VIEW [dbo].[vis_ArkivsakID_for_sist_fullført_klageskjema]
,MAX(ResponseID) AS ResponseID
,MAX([FullførtSkjema_Dato]) AS FullførtSkjema_Dato
GROUP BY Sas_ArkivSakID, ResponseID )
GROUP BY Sas_ArkivSakID, FullførtSkjema_Dato
If I run only the SELECT part of this code, I get following error message:
Incorrect syntax near the keyword 'GROUP'
I hope some of you are smarter than me!