Dealing with aggregate in SET list in UPDATE

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 2012 Forums

Transact-SQL (2012)

Dealing with aggregate in SET list in UPDATE

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-29 : 10:52:09

I am trying to update the following statement so that is pulls the min value of StrTm and StartBreak since table DriverTimeEntry can not return more then one result.

UPDATE #tmpDriverTime
SET @StartPlus8 =	CASE 
						WHEN  ((CONVERT(int, StrtTm) + 800) > 2400) AND (CONVERT(INT, StartBreak) < 1600) THEN ((CONVERT(INT, StrtTm) + 800) - 2400)
						ELSE (CONVERT(INT, StrtTm) + 800)
					END,
	WorkDay1Break =	CASE
						WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
						WHEN ((WorkDay1 > 8 AND StartBreak IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
						WHEN (@StartPlus8 < CONVERT(INT, StartBreak) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
						ELSE 0
					END
FROM dbo.spr_DriverTimeEntry
WHERE spr_DriverTimeEntry.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND PyrllID IN (
	1,
	222,
	1013,
	1014
);

I tried just adding the MIN() to the column names I needed but I get the error "An aggregate may not appear in the set list of an UPDATE statement." The code I was trying when this happened is:

UPDATE #tmpDriverTime
SET @StartPlus8 =	CASE 
						WHEN  ((CONVERT(int, min(StrtTm)) + 800) > 2400) AND (CONVERT(INT, min(StartBreak)) < 1600) THEN ((CONVERT(INT, min(StrtTm)) + 800) - 2400)
						ELSE (CONVERT(INT, min(StrtTm)) + 800)
					END,
	WorkDay1Break =	CASE
						WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
						WHEN ((WorkDay1 > 8 AND StartBreak IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
						WHEN (@StartPlus8 < CONVERT(INT, min(StartBreak)) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
						ELSE 0
					END
FROM dbo.spr_DriverTimeEntry
WHERE spr_DriverTimeEntry.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND PyrllID IN (
	1,
	222,
	1013,
	1014
);

Is there a way to make it pull the MIN value from the columns I need?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-29 : 13:09:24

you can use Top 1 *
as described here:
http://msdn.microsoft.com/en-us/library/ms189463.aspx

You will get quicker response if you post your question following these guidelines:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-29 : 13:32:49

I can't use top one since I do not know which of the rows that would be returned would have the min values, it is also possible that the row that has the min SrtTm does not have the min StartBreak. Not 100% why you game me the link to the guidelines, if there is data that you need to see please ask for it. I think it is a faily simple question and stated clearly, how do I mode the first code set so I can use MIN() on the stated columns.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-29 : 13:44:54

Can you provide dbo.spr_DriverTimeEntry definition, some example input data and expected output

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-29 : 14:22:38

quote:
Originally posted by MuMu88

Can you provide dbo.spr_DriverTimeEntry definition, some example input data and expected output

Not sure why you need it but here is the table definition:

CREATE TABLE [dbo].[spr_DriverTimeEntry] (
    [DtWrkd]        DATETIME      NOT NULL,
    [TrckID]        INT           NOT NULL,
    [DrvrID]        INT           NOT NULL,
    [StrtTm]        VARCHAR (5)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EndTm]         VARCHAR (5)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [StrtMlg]       INT           NULL,
    [EndMlg]        INT           NULL,
    [CTMls]         INT           NULL,
    [NJMls]         INT           NULL,
    [NYMls]         INT           NULL,
    [OthrMls]       INT           NULL,
    [GllsDsptched]  INT           NULL,
    [GllsDlvrd]     INT           NULL,
    [NmbrStps]      INT           NULL,
    [Loads]         INT           NULL,
    [PyblHrs]       FLOAT (53)    NULL,
    [PyrllID]       INT           NOT NULL,
    [TimeEntryNote] VARCHAR (200) NULL,
    [DtFinished]    DATETIME      NULL,
    [BatchNo]       VARCHAR (15)  NULL,
    CONSTRAINT [pk_DtWrkd_TrckID_DrvrID] PRIMARY KEY CLUSTERED ([DtWrkd] ASC, [TrckID] ASC, [DrvrID] ASC, [PyrllID] ASC) WITH (FILLFACTOR = 90)
);

a simplified input would be:

Dtwked: 7-1-13, Drvier:1 :StrTM :0430 StartBreak: null
Dtwkred: 7-1-13, Driver:1 strTm: 1200 startbreak: 1430

The expected out put would be: 7-1-13, driver:1, StrTM: 0430, StartBreak:1430

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-29 : 15:04:20

Stellvia,

You can only use MIN() in a query if you include a GROUP BY clause in your select statement.
Based on what you have told me, I suspect that for your application it will suffice to group by spr_DriverTimeEntry.DrvrID, dtwrkd for example:
[CODE]

SELECT spr_DriverTimeEntry.DrvrID, dtwrkd, MIN([StrtTm]), MIN([EndTm]) FROM spr_DriverTimeEntry
WHERE WHERE spr_DriverTimeEntry.DrvrID = 1
AND CONVERT(VARCHAR(12), dtwrkd, 112) = '7-1-13'
AND PyrllID IN (
1,
222,
1013,
1014
) GROUP BY spr_DriverTimeEntry.DrvrID, dtwrkd

[/CODE]

EDIT: If each driver takes multiple breaks per day, the above query will tell you the time at which each driver took their first break on any given day.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-29 : 15:48:59

I am not doing a normal select, if you reference my original post you will see it is an UPDATE with two SET commands.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources