September 6, 2018 at 4:41 am
Hi all!
I have this query:
SELECT Per.per_personal_id,EMP.vacation_model FROM dbo.personal PER
JOIN dbo.employment EMP ON EMP.EmployeeID = PER.per_personal_id
WHERE EMP.DATEFROM <= 79508 AND (EMP.DATETO = 0 OR emp.DATETO >= 79508)
giving a rsult set of:
per_personal_id vacation_model
2 1
4 3
6 3
9 3
11 4
13 4
........ (app. 2000 sets)
In the table PERSONAL, I have a field called "vacation_as_duty", which I want to update with "vacation_model"
The correct row in the table PERSONAL is the one with the value "per_personal_id "
How to?
Best regards
Edvard Korsbæk
The table PERSONAL is defined as:
CREATE TABLE [dbo].[PERSONAL](
[per_tlf_privat] [char](20) NULL,
[per_mobil_privat] [char](20) NULL,
[per_e_mail_privat] [char](60) NULL,
[per_noter] [varchar](4999) NULL,
[per_personal_id] [int] IDENTITY(1,1) NOT NULL,
[per_bestyrelse] [tinyint] NULL,
[per_fornavn] [char](20) NULL,
[per_efternavn] [char](20) NULL,
[per_gade] [char](35) NULL,
[per_postnr] [char](15) NULL,
[per_bynavn] [char](20) NULL,
[per_cpr] [char](14) NULL,
[per_cpr_ja_nej] [char](4) NULL,
[per_gruppe] [smallint] NULL,
[per_kategori] [char](20) NULL,
[per_underkat] [char](20) NULL,
[per_timer] [decimal](7, 2) NULL,
[per_faste] [tinyint] NULL,
[per_ansat] [int] NULL,
[per_fratraadt] [int] NULL,
[per_tlf] [char](20) NULL,
[per_mobil] [char](20) NULL,
[per_e_mail] [char](60) NULL,
[per_hyre] [decimal](7, 2) NULL,
[per_departments_id] [int] NULL,
[per_magtanvendelse] [tinyint] NULL,
[per_color] [int] NULL,
[payscale] [nvarchar](10) NULL,
[weeklyhours] [int] NULL,
[tempstart] [datetime] NULL,
[tempend] [datetime] NULL,
[trainingstart] [datetime] NULL,
[trainingend] [datetime] NULL,
[trainingcoach] [nvarchar](50) NULL,
[onleavestart] [datetime] NULL,
[onleaveend] [datetime] NULL,
[picture] [image] NULL,
[istemp] [bit] NULL,
[isonleave] [bit] NULL,
[isintraining] [bit] NULL,
[filetype] [nvarchar](50) NULL,
[vacationwithpay] [float] NULL,
[vacationwithoutpay] [float] NULL,
[specialvacation] [float] NULL,
[lastvacationyear] [float] NULL,
[lastwithoutpay] [float] NULL,
[lastspecial] [float] NULL,
[initials] [nvarchar](25) NULL,
[positiontypechoice] [tinyint] NULL,
[accountnumber] [nvarchar](50) NULL,
[cpr2] [nvarchar](20) NULL,
[temphasended] [bit] NULL,
[payscaleid] [int] NULL,
[supplementpay] [float] NULL,
[childcaredaysleft] [decimal](7, 2) NULL,
[vacation_as_duty] [tinyint] NULL,
[Person_ID] [numeric](18, 0) NULL,
[CPR_encrypted] [varbinary](256) NULL,
[Not_in_roll] [tinyint] NULL,
[add_to_outlook] [tinyint] NULL,
CONSTRAINT [per_vis_personal_id] PRIMARY KEY CLUSTERED
(
[per_personal_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
September 6, 2018 at 6:04 am
Something like this?
update p
set vacation_as_duty = e.vacation_model
from dbo.Personal p
JOIN dbo.employment e ON e.EmployeeID = p.per_personal_id
WHERE e.DATEFROM <= 79508 AND (e.DATETO = 0 OR emp.DATETO >= 79508)
September 6, 2018 at 6:14 am
Very Nice!
Only had to replace emp.dateto with e.dateto
The way of UPDATE P ... is new to me, and very,very useful.
Thanks
Best regards
Edvard Korsbæk
September 6, 2018 at 6:18 am
Edvard Korsbæk - Thursday, September 6, 2018 6:14 AMVery Nice!
Only had to replace emp.dateto with e.datetoThe way of UPDATE P ... is new to me, and very,very useful.
Thanks
Best regards
Edvard Korsbæk
Ah yes, missed that. Thanks for posting back.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply