I have a question in SQL Server.
Table : emp
CREATE TABLE [dbo].[emp]
(
[id] [int] NULL,
[name] varchar 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 % 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 % 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 % 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