Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pW2XD-0007CA-AU for pgsql-sql@arkaria.postgresql.org; Sat, 25 Feb 2023 21:56:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pW2XC-0000Z1-0b for pgsql-sql@arkaria.postgresql.org; Sat, 25 Feb 2023 21:56:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pW2XB-0000Ys-Gh for pgsql-sql@lists.postgresql.org; Sat, 25 Feb 2023 21:56:49 +0000 Received: from premium22-2.web-hosting.com ([68.65.122.104]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pW2X7-0001tk-FT for pgsql-sql@postgresql.org; Sat, 25 Feb 2023 21:56:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=vPnUImlLQrxhwZuS8Mo2AgqMCi4nX/m0uxKrW9mXjic=; b=q8Lcmhjr9LOU9rUkTjavNoPXdn 3DVIAEwyt+/J0N3uY6Plc8jC+6RvqCKFrl3k00fQM1ETqz4m6IR0/yl4eITP6XW49VMIzVKARwDs/ 0jptyNrfTB+z5tMwy7UD3B8sOld45iHjpJuGmZvO0SXOreC5ugqqDzUqedjNtQ2CFfJcMJvXxGwat hAjBezV2Pl9ZIusGD5L4dkIozxJs/OCOGs3iYefpIj+34VJMNSKE2w+1DOCzcrVtKlMcH3OlSW5+j HOmJeiryFaYm995Y4jPpwiXZxj7a90x+cZ/rhDHyEn2bkpsIcNWhZEx7WU4gRoS6OUYR9Vr9WxBq9 znTAvYuA==; Received: from mail-yw1-f176.google.com ([209.85.128.176]:45909) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.95) (envelope-from ) id 1pW2X0-00Fj2S-Oh for pgsql-sql@postgresql.org; Sat, 25 Feb 2023 16:56:43 -0500 Received: by mail-yw1-f176.google.com with SMTP id 00721157ae682-536bf92b55cso75390867b3.12 for ; Sat, 25 Feb 2023 13:56:38 -0800 (PST) X-Gm-Message-State: AO0yUKUlYoE/9jda5Acb+sQhsLK5D784kLAHisMPx9Q1BjencIqwZV+2 NKikn4D0hBgL2w2LkTrcytK9FJTd3Yg5nJYATOk= X-Google-Smtp-Source: AK7set8GHcswskol2YDGHO5KUdMnW4KwNW+W24y/iTxUFWKIrULIZ5oT/5+7pHGulxt/UJkDfh/K/liah+Zc3wdiTbk= X-Received: by 2002:a25:f90b:0:b0:a24:1001:1fd2 with SMTP id q11-20020a25f90b000000b00a2410011fd2mr5811486ybe.0.1677362197736; Sat, 25 Feb 2023 13:56:37 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Sat, 25 Feb 2023 13:56:27 -0800 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Take the data from table and rotate the member on every 7th day or starting day of the week and continue for whole week continue till the date range To: nikhil raj Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary="000000000000f4b56a05f58d51fa" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f4b56a05f58d51fa Content-Type: text/plain; charset="UTF-8" On Sat, Feb 25, 2023 at 12:37 PM nikhil raj wrote: > > Hi All, > > Need help in rotation of data in *Postgres*. > > There is a table called crew_details > > [image: enter image description here] > > > There is a requirement to rotate the crew members for in weekly or on the > every 7th day and continue for rest 6days the same order of for the given > date range un till it reach end date . and the expected output should be > like this below > > [image: >] > > Please can any one help me in in the rotation in *postgres SQL* > Thanks for posting the table and output. I'm not totally clear on the business requirements here but it seems like you have a crew rotation sequence that is, in this case modulus 5? I'm not sure I've got the SQL skills to easily make this in a single query (but surely others here can). But just to clarify the business rules for such a SQL wizard, I would think the solution involves some kind of hidden variable (new column, aggregation, built in function?) where the number 1 team member's crew position number is incremented by 1 mod 5 for each new shift date. Whenever that tracking counter becomes 1 again, convert their actual shift order number to 5, and subtract 1 from every other crew members' crew position number. Does that sound like the correct business rule for the process you're trying to create? Steve --000000000000f4b56a05f58d51fa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, Feb 25, 2023 at 12:37 PM nikh= il raj <nikhilraj474@gmail.com= > wrote:
=

Hi All,

Need help in rotation of data in=C2=A0Postgres.

There is a = table called crew_details

3D"enter<= /p>

There is a requirement to rotate the crew members for in weekly = or on the every 7th day and continue for rest 6days the same order of for t= he given date range un till it reach end date . and the expected output sho= uld be like this below

3D">"

Please can any one help me= in in the rotation in=C2=A0p= ostgres SQL


Thanks for posting the table and output. I'm not totally clear on= the business requirements here but it seems like you have a crew rotation = sequence that is, in this case modulus 5?

I'm = not sure I've got the SQL skills to easily make this in a single query = (but surely others here can). But just to clarify the business rules for su= ch a SQL wizard, I would think the solution involves some kind of hidden va= riable (new column, aggregation, built in function?) where the number 1 tea= m member's crew position number is incremented by 1 mod 5 for each new = shift date. Whenever that tracking counter becomes 1 again, convert their a= ctual shift order number to 5, and subtract 1 from every other crew members= ' crew position number. Does that sound like the correct business rule = for the process you're trying to create?

Steve=
--000000000000f4b56a05f58d51fa--