{"id":6460,"date":"2018-02-23T12:02:05","date_gmt":"2018-02-23T11:02:05","guid":{"rendered":"http:\/\/help.sdna.lu\/?p=6460&#038;lang=en"},"modified":"2022-12-18T11:14:04","modified_gmt":"2022-12-18T10:14:04","slug":"query-to-create-jobs-based-upon-job","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=6460","title":{"rendered":"Query to create Jobs based upon Job"},"content":{"rendered":"<p>Use this query to create Jobs based upon a single Job:<\/p>\n<p><code>Insert Into [Job] ([DBId], [Id], [Number], [ClientId], [JobTypeId], [DivisionId], [Description], [Actif], [Archive],<br \/>\n[JobServiceTRRestriction], [StartDate], [EndDate], [Chargeable], [Status], [TRLockType], [FileHolderEmployeeId],<br \/>\n[ReferenceYear], [SupplierBudgetAmount], [JobCategoryId], [StrategyId])<br \/>\nSelect [Client].[DBId] as [ResDBId],<br \/>\nROW_NUMBER() OVER(ORDER BY left(case when [Company].[Name] Is Null then [Person].[Name] else [Company].[Name] end,63)) + nnnn AS [ResId],<br \/>\nleft(case when [Company].[Name] Is Null then [Person].[Name] else [Company].[Name] end,63) + ' - Advisory' as [ResNumber],<br \/>\n[Client].[Id] as [ResClientId],<br \/>\n[Job].[JobTypeId] as [ResJobTypeId],<br \/>\n[Job].[DivisionId] as [ResDivisionId],<br \/>\n[Job].[Description] as [ResDescription],<br \/>\n[Job].[Actif] as [ResActif],<br \/>\n[Job].[Archive] as [ResArchive],<br \/>\n[Job].[JobServiceTRRestriction] as [JobServiceTRRestriction],<br \/>\n[Job].[StartDate] as [StartDate],<br \/>\n[Job].[EndDate] as [EndDate],<br \/>\n[Job].[Chargeable] as [Chargeable],<br \/>\n[Job].[Status] as [Status],<br \/>\n[Job].[TRLockType] as [TRLockType],<br \/>\n[Job].[FileHolderEmployeeId] as [FileHolderEmployeeId],<br \/>\n[Job].[ReferenceYear] as [ReferenceYear],<br \/>\n[Job].[SupplierBudgetAmount] as [SupplierBudgetAmount],<br \/>\n[Job].[JobCategoryId] as [JobCategoryId],<br \/>\n[Job].[StrategyId] as [StrategyId]<br \/>\nfrom (([Client] LEFT JOIN [Company]<br \/>\nON ([Client].[CompanyId] = [Company].[Id] and [Client].[DBId] = [Company].[DBId]))<br \/>\nLEFT JOIN [Person]<br \/>\nON ([Client].[PersonId] = [Person].[Id] and [Client].[DBId] = [Person].[DBId])), [Job]<br \/>\nwhere [Client].[DBId] = 1 and not [Client].[Id] = xxxxx<br \/>\nand [Job].[DBId] = yyyyy and [Job].[Id] = zzzzz and [Client].[EndDate] = '30\/12\/1899'<br \/>\norder by left(case when [Company].[Name] Is Null then [Person].[Name] else [Company].[Name] end,63)<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Use this query to create Jobs based upon a single Job: Insert Into [Job] ([DBId], [Id], [Number], [ClientId], [JobTypeId], [DivisionId], [Description], [Actif], [Archive], [JobServiceTRRestriction], [StartDate], [EndDate], [Chargeable], [Status], [TRLockType], [FileHolderEmployeeId], [ReferenceYear], [SupplierBudgetAmount], [JobCategoryId], [StrategyId]) Select [Client].[DBId] as [ResDBId], ROW_NUMBER() OVER(ORDER BY left(case when [Company].[Name] Is Null then [Person].[Name] else [Company].[Name] end,63)) + nnnn AS [ResId], [&hellip;]<\/p>\n","protected":false},"author":19,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[487],"tags":[],"class_list":["post-6460","post","type-post","status-publish","format-standard","hentry","category-query"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/6460","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6460"}],"version-history":[{"count":1,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/6460\/revisions"}],"predecessor-version":[{"id":6461,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/6460\/revisions\/6461"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6460"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6460"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6460"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}