SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Dealing with aggregate in SET list in UPDATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
424 Posts

Posted - 07/29/2013 :  10:52:09  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

547 Posts

Posted - 07/29/2013 :  13:09:24  Show Profile  Reply with Quote
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
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
424 Posts

Posted - 07/29/2013 :  13:32:49  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/29/2013 :  13:44:54  Show Profile  Reply with Quote
Can you provide dbo.spr_DriverTimeEntry definition, some example input data and expected output
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
424 Posts

Posted - 07/29/2013 :  14:22:38  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/29/2013 :  15:04:20  Show Profile  Reply with Quote
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:


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
 


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.

Edited by - MuMu88 on 07/29/2013 15:12:46
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
424 Posts

Posted - 07/29/2013 :  15:48:59  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000