Group By problem - 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)

Group By problem

Author

Topic

gwjones12
Starting Member

9 Posts

Posted - 2011-03-15 : 16:55:59

I have the following SQL that joins 2 tables and then uses a GROUP BY to SUM values over a 4 month period (the @FromMonth to @Month) into a single record per the GROUP BY...(and repeat for all "programId" but it really isn't SUMming anything as each month only has 1 record. Therefore I'm really using the SUM to just get the values from 4 records into a single display record for each programId. (ProgramIds belong to TitleIds and TitleIds belong to reportingLevels) There has to be a more "proper" way of doing this!?

The problem I've run into is that I need to retrieve the ID from the programId's first month value record (Data_ManualEntriesId) in order to go back and do an update to that "value" (Data_ManualEntries.value) once the user gives me a new value to save.

Clear as mud?

SELECT indicators.indicatorId,
Data_ManualEntries.TitleId,
Data_ManualEntries.programID,
Data_ManualEntries.reportingLevel,
SUM(CASE [month] WHEN @month THEN value ELSE NULL END) AS value,
SUM(CASE [month] WHEN @PrevMonth1 THEN value ELSE NULL END) AS PrevMonth1,
SUM(CASE [month] WHEN @PrevMonth2 THEN value ELSE NULL END) AS PrevMonth2,
SUM(CASE [month] WHEN @PrevMonth3 THEN value ELSE NULL END) AS PrevMonth3
FROM Data_ManualEntries INNER JOIN
indicators
ON Data_ManualEntries.indicatorId = indicators.indicatorId
WHERE (Data_ManualEntries.indicatorId = @IndicatorId)
AND (Data_ManualEntries.year = @FromYear)
AND (Data_ManualEntries.month >= @FromMonth)
AND (Data_ManualEntries.month <= @month)
GROUP BY indicators.indicatorId, Data_ManualEntries.reportingLevel,
Data_ManualEntries.TitleId,
Data_ManualEntries.programID

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 17:05:22

ok

you need to do an update...no code please...tell me what you have to update and why

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

gwjones12
Starting Member

9 Posts

Posted - 2011-03-16 : 07:16:01

I can do the update.

Update Data_ManualEntries
Set vale = New value user entered on screen
WHERE Data_ManualEntriesId = Data_ManualEntriesID value of row user was on in the grid they were entering data on.

The problem is that when I'm retrieving the data to display, I need the Data_ManualEntriesID that matches the "value" field of the query I'm using.

gwjones12
Starting Member

9 Posts

Posted - 2011-03-16 : 13:18:00

FYI to anyone searching this topic,
I've changed my SQL to this so I now have the ID field (sub-queries for the aditional months):

SELECT Data_ManualEntries.Data_ManualEntriesId, [year], [month], indicators.indicatorId, indicators.indicatorDesc,
Data_ManualEntries.programId, Data_ManualEntries.seniorTeamServiceTitleId,
Data_ManualEntries.reportingLevel, Managers.surName, Managers.firstName,
CASE WHEN Data_ManualEntries.reportingLevel = 'C' THEN 'Corporate Scorecard'
WHEN Data_ManualEntries.reportingLevel = 'B' THEN 'Board Scorecard'
WHEN Data_ManualEntries.reportingLevel = 'V' THEN seniorTeamServiceTitle.titleDesc
ELSE SharedData.dbo.Programs.programDesc END AS programDesc,
value, tblPrevMonth1.PrevMonth1, tblPrevMonth2.PrevMonth2, tblPrevMonth3.PrevMonth3

FROM Data_ManualEntries INNER JOIN
( -- SUB SELECT to retrieve values for Previous Month 1 (1 month ago)
SELECT Data_ManualEntries_1.Data_ManualEntriesId, indicators_1.indicatorId,
Data_ManualEntries_1.reportingLevel, Data_ManualEntries_1.seniorTeamServiceTitleId,
Data_ManualEntries_1.programId, Data_ManualEntries_1.value as PrevMonth1
FROM Data_ManualEntries Data_ManualEntries_1 INNER JOIN
indicators indicators_1 ON Data_ManualEntries_1.indicatorId = indicators_1.indicatorId
WHERE (Data_ManualEntries_1.indicatorId = @IndicatorId)
AND (Data_ManualEntries_1.year = @PrevYear1)
AND (Data_ManualEntries_1.month = @PrevMonth1)
) as tblPrevMonth1 ON
tblPrevMonth1.indicatorId = Data_ManualEntries.indicatorId
AND tblPrevMonth1.reportingLevel = Data_ManualEntries.reportingLevel
AND ISNULL(tblPrevMonth1.seniorTeamServiceTitleId,0) = ISNULL(Data_ManualEntries.seniorTeamServiceTitleId,0)
AND ISNULL(tblPrevMonth1.programId,0) = ISNULL(Data_ManualEntries.programId,0) INNER JOIN

(-- SUB SELECT to retrieve values for Previous Month 2 (2 month ago)
SELECT Data_ManualEntries_2.Data_ManualEntriesId, indicators_2.indicatorId,
Data_ManualEntries_2.reportingLevel, Data_ManualEntries_2.seniorTeamServiceTitleId,
Data_ManualEntries_2.programId, Data_ManualEntries_2.value as PrevMonth2
FROM Data_ManualEntries Data_ManualEntries_2 INNER JOIN
indicators indicators_2 ON Data_ManualEntries_2.indicatorId = indicators_2.indicatorId
WHERE (Data_ManualEntries_2.indicatorId = @IndicatorId)
AND (Data_ManualEntries_2.year = @PrevYear2)
AND (Data_ManualEntries_2.month = @PrevMonth2)
) as tblPrevMonth2 ON
tblPrevMonth2.indicatorId = Data_ManualEntries.indicatorId
AND tblPrevMonth2.reportingLevel = Data_ManualEntries.reportingLevel
AND ISNULL(tblPrevMonth2.seniorTeamServiceTitleId,0) = ISNULL(Data_ManualEntries.seniorTeamServiceTitleId,0)
AND ISNULL(tblPrevMonth2.programId,0) = ISNULL(Data_ManualEntries.programId,0) INNER JOIN

(-- SUB SELECT to retrieve values for Previous Month 3 (3 month ago)
SELECT Data_ManualEntries_3.Data_ManualEntriesId, indicators_3.indicatorId,
Data_ManualEntries_3.reportingLevel, Data_ManualEntries_3.seniorTeamServiceTitleId,
Data_ManualEntries_3.programId, Data_ManualEntries_3.value as PrevMonth3
FROM Data_ManualEntries Data_ManualEntries_3 INNER JOIN
indicators indicators_3 ON Data_ManualEntries_3.indicatorId = indicators_3.indicatorId
WHERE (Data_ManualEntries_3.indicatorId = @IndicatorId)
AND (Data_ManualEntries_3.year = @PrevYear3)
AND (Data_ManualEntries_3.month = @PrevMonth3)
) as tblPrevMonth3 ON
tblPrevMonth3.indicatorId = Data_ManualEntries.indicatorId
AND tblPrevMonth3.reportingLevel = Data_ManualEntries.reportingLevel
AND ISNULL(tblPrevMonth3.seniorTeamServiceTitleId,0) = ISNULL(Data_ManualEntries.seniorTeamServiceTitleId,0)
AND ISNULL(tblPrevMonth3.programId,0) = ISNULL(Data_ManualEntries.programId,0)

-- now continue on joining the child tables we need to connect to the main Data_ManualEntries table
INNER JOIN
indicators ON Data_ManualEntries.indicatorId = indicators.indicatorId LEFT OUTER JOIN
shareddata.dbo.Managers ON Data_ManualEntries.personId = Managers.id LEFT OUTER JOIN
shareddata.dbo.Programs ON Managers.ProgramId = Programs.programId LEFT OUTER JOIN
SharedData.dbo.seniorTeamServiceTitle ON Managers.SeniorTeamServiceTitleId = seniorTeamServiceTitle.titleID

-- where clause for the main SQL statement of the table Data_ManualEntries
WHERE (Data_ManualEntries.indicatorId = @IndicatorId)
AND (Data_ManualEntries.year = @Year)
AND (Data_ManualEntries.month = @month)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources