records repeat based on condition in sql server

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  May 5, 2017 at 8:27 pm

  #337715

  I have a question in SQL Server.

  Table : emp

  CREATE TABLE [dbo].[emp]
  (
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [sal] [int] NULL,
    [dos] [date] NULL
  )
  GO

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (1, N'balu', 10, CAST(N'2016-02-01' AS Date))

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (2, N'ravi', 12, CAST(N'2016-02-01' AS Date))

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (3, N'hari', 20, CAST(N'2016-03-07' AS Date))
  Based on data need to increase records 2.5 times of existing orders (any orders of that date) based on date wise and id should be unique when increase orders. We can prefix any number starting on id values.

  Example: date is 2016-02-01 have 2 records need to increase records 2.5 times  that is 2 * 2.5 = 5

  Final this 2016-02-01 date need to get 5 records for that date similar to other dates

  If we take another date is 2016-03-07 this date is have only one record here 1 * 2.5 =2.5. here we can consider 3 records instead of 2.5 count we can round of the value.

  Final this date 2016-03-07 need to show 3 records

  Final I need to output like below (I have given id, name, sal, dos values sample,
  we can given values based on date wise and increase 2.5 times of existing order)
  id |name |sal  |dos
  1 |balu |10  |2016-02-01
  2 |ravi |12  |2016-02-01
  11 |balu |10  |2016-02-01
  22 |ravi |12  |2016-02-01
  31 |balu |10  |2016-02-01
  3 |hari |20  |2016-03-07
  23 |hari |20  |2016-03-07
  33 |op  |10  |2016-03-07
  I have tried like emp table records inserted into another tempemp table and tempemp related ids values updates if 1 is there then updated 11

  Similar to 2 and need one more records and again I have used another tempemp1 table (temptable)
  and insert emp record one on that data and updated that records again that record loaded in to emp table.
  Final I got 5 records on 2016-02-01 and similar to another date.

  Here I can do for two dates. but this logic is not working when I have more than 1
  lakhs records with different dates and very difficult to do manually date by date

  Please tell me how to write a query to achieve this task in SQL Server

• Eirikur Eiriksson

  SSC Guru

  Points: 182919

  More actions

  May 7, 2017 at 7:16 am

  #1941466

  asrinu13 - Friday, May 5, 2017 8:27 PM

  I have a question in SQL Server.

  Table : emp

  CREATE TABLE [dbo].[emp]
  (
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [sal] [int] NULL,
    [dos] [date] NULL
  )
  GO

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (1, N'balu', 10, CAST(N'2016-02-01' AS Date))

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (2, N'ravi', 12, CAST(N'2016-02-01' AS Date))

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (3, N'hari', 20, CAST(N'2016-03-07' AS Date))
  Based on data need to increase records 2.5 times of existing orders (any orders of that date) based on date wise and id should be unique when increase orders. We can prefix any number starting on id values.

  Example: date is 2016-02-01 have 2 records need to increase records 2.5 times  that is 2 * 2.5 = 5

  Final this 2016-02-01 date need to get 5 records for that date similar to other dates

  If we take another date is 2016-03-07 this date is have only one record here 1 * 2.5 =2.5. here we can consider 3 records instead of 2.5 count we can round of the value.

  Final this date 2016-03-07 need to show 3 records

  Final I need to output like below (I have given id, name, sal, dos values sample,
  we can given values based on date wise and increase 2.5 times of existing order)
  id |name |sal  |dos
  1 |balu |10  |2016-02-01
  2 |ravi |12  |2016-02-01
  11 |balu |10  |2016-02-01
  22 |ravi |12  |2016-02-01
  31 |balu |10  |2016-02-01
  3 |hari |20  |2016-03-07
  23 |hari |20  |2016-03-07
  33 |op  |10  |2016-03-07
  I have tried like emp table records inserted into another tempemp table and tempemp related ids values updates if 1 is there then updated 11

  Similar to 2 and need one more records and again I have used another tempemp1 table (temptable)
  and insert emp record one on that data and updated that records again that record loaded in to emp table.
  Final I got 5 records on 2016-02-01 and similar to another date.

  Here I can do for two dates. but this logic is not working when I have more than 1
  lakhs records with different dates and very difficult to do manually date by date

  Please tell me how to write a query to achieve this task in SQL Server

  Quick suggestion, should be enough to get you passed this hurdle
  😎
  USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.emp') IS NOT NULL DROP TABLE dbo.emp;
CREATE TABLE [dbo].[emp]
(
 [id] [int] NULL
,[name] [varchar](50) NULL
,[sal] [int] NULL
,[dos] [date] NULL
) ;

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
VALUES (1, N'balu', 10, CAST(N'2016-02-01' AS Date))
  ,(2, N'ravi', 12, CAST(N'2016-02-01' AS Date))
  ,(3, N'hari', 20, CAST(N'2016-03-07' AS Date));

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
,COUNTS(id,name,sal,dos,DCNT,CNT) AS
(
  SELECT
   E.id
   ,E.name
   ,E.sal
   ,E.dos
   ,COUNT(E.id) OVER
    (
      PARTITION BY E.dos
    ) AS DCNT
   ,CEILING(COUNT(E.id) OVER
    (
      PARTITION BY E.dos
    ) * 2.5 ) AS CNT
  FROM dbo.emp  E
)
,LIMITER(id,name,sal,dos,DCNT,CNT,N,DOS_RID) AS
(
  SELECT
   C.id    
   ,C.name    
   ,C.sal    
   ,C.dos    
   ,C.DCNT    
   ,C.CNT    
   ,NM.N
   ,ROW_NUMBER() OVER
    (
      PARTITION BY C.dos
      ORDER BY  C.ID
    ) AS DOS_RID
  FROM  COUNTS C
  CROSS APPLY
  (
   SELECT TOP(CONVERT(INT,CEILING(C.CNT/C.DCNT),0))
    ROW_NUMBER() OVER
      (
       ORDER BY @@VERSION
      ) AS N
   FROM T T1,T T2,T T3,T T4
  ) NM
)
SELECT
  L.id
 ,L.name
 ,L.sal
 ,L.dos
FROM  LIMITER  L
WHERE L.CNT >= L.DOS_RID;

  Output
  

  id  name  sal  dos
--- ----- ---- ----------
1   balu  10   2016-02-01
1   balu  10   2016-02-01
1   balu  10   2016-02-01
2   ravi  12   2016-02-01
2   ravi  12   2016-02-01
3   hari  20   2016-03-07
3   hari  20   2016-03-07
3   hari  20   2016-03-07

  

• Brandie Tarvin

  SSC Guru

  Points: 173081

  More actions

  May 8, 2017 at 4:18 am

  #1941540

  Eirikur Eiriksson - Sunday, May 7, 2017 7:16 AM

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (1, N'balu', 10, CAST(N'2016-02-01' AS Date))
    ,(2, N'ravi', 12, CAST(N'2016-02-01' AS Date))
    ,(3, N'hari', 20, CAST(N'2016-03-07' AS Date));

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  

  Eirikur, Why do you have two semi-colons in a row? Only the first one is needed. One doesn't need to be before the WITH. WITH never needs a semi-colon right in front of it so long as it is the first word in a query or the statement above it ends in a semi-colon.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Eirikur Eiriksson

  SSC Guru

  Points: 182919

  More actions

  May 8, 2017 at 4:25 am

  #1941541

  Brandie Tarvin - Monday, May 8, 2017 4:18 AM

  Eirikur Eiriksson - Sunday, May 7, 2017 7:16 AM

  INSERT [dbo].[emp] ([id], [name], [sal], [dos])
  VALUES (1, N'balu', 10, CAST(N'2016-02-01' AS Date))
    ,(2, N'ravi', 12, CAST(N'2016-02-01' AS Date))
    ,(3, N'hari', 20, CAST(N'2016-03-07' AS Date));

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  

  Eirikur, Why do you have two semi-colons in a row? Only the first one is needed. One doesn't need to be before the WITH. WITH never needs a semi-colon right in front of it so long as it is the first word in a query or the statement above it ends in a semi-colon.

  Old habit, makes it easier to copy/paste the CTEs from the templates.
  😎