Dairy Project Part 2.3 .Insert Data script

------------Registration
--Print 'Registration Data Insert'
Declare @i INT=1
While (@i<=5000) 
BEGIN
--print @i
INSERT INTO [dbo].[Registration]
(
[Code]
           ,[Saluationid]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Genderid]
           ,[MilkTimeId]
           ,[MilkTypeId]
           ,[StartDate]
           ,[openingBalance]
           ,[ContactNo]
           ,[Address]
           ,[Countryid]
           ,[Stateid]
           ,[Districtid]
           ,[Talukaid]
           ,[Cityid]
           ,[MobileNo]
           ,[PhoneNo]
           ,[Emailid]
           ,[Status]
           ,[AddedBy]
           ,[UpdatedBy]
           )
     VALUES
           (@i
           ,1
           ,'Fname'+'_'+Convert(varchar(15),@i)
   ,'Mname'+'_'+Convert(varchar(15),@i)
   ,'Lname'+'_'+Convert(varchar(15),@i)
           ,1
           ,1
           ,1
           ,GETDATE()
           ,0
           ,'1111111111'
           ,'local Address'
           ,1
           ,1
           ,1
           ,1
           ,1
           ,'1111111111'
           ,'1234567890'
           ,'Fname'+Convert(varchar(15),@i)+'@gmail.com'
           ,1
           ,1
           ,1
)
         
Set @i=@i+1
END
GO

------------DailyMilkCollection

-----below Query will insert 50 X 730 (2 years Data) X 4 = 156000 Rows 

------------------------------------Cust_id X 730 ( 2 yrs data) X 4 (Insert milk type & milk time)

Print 'DailyMilkCollection Data Insert'
GO
SET NoCOUNT ON
Declare @Cust_id INT=1
While (@Cust_id<=50) 
BEGIN
print @Cust_id
--------------Date loop Start 
Declare @Date Date 
Set @Date='2021-01-01'
While (@Date<='2022-12-31') 
BEGIN
--Print @Date
-----------------Milktime -morning
--------Cow
INSERT INTO [dbo].[DailyMilkCollection]
   ([CustomerID]
   ,[FATValue]
   ,[SNFValue]
   ,[Quantity]
   ,[Rate]
   ,[Amount]
   ,[MilkTimeID]
   ,[MilkTypeID]
   ,[Date_Time]
   ,[Issettled]
   ,[Iscancelled]
   ,[AddedBy]
   ,[UpdatedBy]
   )
VALUES
   (
@Cust_id 
   ,(Select Top 1 FAT_Value from FATMaster F 
   Inner join RateMaster R On F.Id=R.FATID
   where MilkTypeID=1 order by NEWID())       -------Cow
   ,(Select Top 1 SNF_Value from SNFMaster S
   Inner join RateMaster R On S.Id=R.SNFID
   where MilkTypeID=1 order by NEWID())   -------Cow
   ,(SELECT Cast(RAND()*(20-9)+5 As numeric(5,2))) 
   ,0 ---- Rate
   ,0 ---- Amount
   ,1 ---- MilkTimeID ------morning
   ,1 ---- MilktypeID ------Cow
   ,@Date
   ,0 ---- Issettled
   ,0 ---- Iscancelled
   ,1 ---- AddedBy
   ,1 ---- UpdatedBy        
   )
   
--------Buffalow
INSERT INTO [dbo].[DailyMilkCollection]
   ([CustomerID]
   ,[FATValue]
   ,[SNFValue]
   ,[Quantity]
   ,[Rate]
   ,[Amount]
   ,[MilkTimeID]
   ,[MilkTypeID]
   ,[Date_Time]
   ,[Issettled]
   ,[Iscancelled]
   ,[AddedBy]
   ,[UpdatedBy]
   )
VALUES
   (
@Cust_id 
   ,(Select Top 1 FAT_Value from FATMaster F 
   Inner join RateMaster R On F.Id=R.FATID
   where MilkTypeID=2 order by NEWID())       -------Buffalow
   ,(Select Top 1 SNF_Value from SNFMaster S
   Inner join RateMaster R On S.Id=R.SNFID
   where MilkTypeID=2 order by NEWID())   -------Buffalow
   ,(SELECT Cast(RAND()*(20-9)+5 As numeric(5,2))) 
   ,0 ---- Rate
   ,0 ---- Amount
   ,1 ---- MilkTimeID ------morning
   ,2 ---- MilktypeID ------Buffalow
   ,@Date
   ,0 ---- Issettled
   ,0 ---- Iscancelled
   ,1 ---- AddedBy
   ,1 ---- UpdatedBy        
   )
-----------------Milktime -Evening
--------Cow
INSERT INTO [dbo].[DailyMilkCollection]
   ([CustomerID]
   ,[FATValue]
   ,[SNFValue]
   ,[Quantity]
   ,[Rate]
   ,[Amount]
   ,[MilkTimeID]
   ,[MilkTypeID]
   ,[Date_Time]
   ,[Issettled]
   ,[Iscancelled]
   ,[AddedBy]
   ,[UpdatedBy]
   )
VALUES
   (
@Cust_id 
   ,(Select Top 1 FAT_Value from FATMaster F 
   Inner join RateMaster R On F.Id=R.FATID
   where MilkTypeID=1 order by NEWID())       -------Cow
   ,(Select Top 1 SNF_Value from SNFMaster S
   Inner join RateMaster R On S.Id=R.SNFID
   where MilkTypeID=1 order by NEWID())   -------Cow
   ,(SELECT Cast(RAND()*(20-9)+5 As numeric(5,2))) 
   ,0 ---- Rate
   ,0 ---- Amount
   ,2 ---- MilkTimeID ------Evening
   ,1 ---- MilktypeID ------Cow
   ,@Date
   ,0 ---- Issettled
   ,0 ---- Iscancelled
   ,1 ---- AddedBy
   ,1 ---- UpdatedBy        
   )
--------Buffalow
INSERT INTO [dbo].[DailyMilkCollection]
   ([CustomerID]
   ,[FATValue]
   ,[SNFValue]
   ,[Quantity]
   ,[Rate]
   ,[Amount]
   ,[MilkTimeID]
   ,[MilkTypeID]
   ,[Date_Time]
   ,[Issettled]
   ,[Iscancelled]
   ,[AddedBy]
   ,[UpdatedBy]
   )
VALUES
   (
@Cust_id 
   ,(Select Top 1 FAT_Value from FATMaster F 
   Inner join RateMaster R On F.Id=R.FATID
   where MilkTypeID=2 order by NEWID())       -------Buffalow
   ,(Select Top 1 SNF_Value from SNFMaster S
   Inner join RateMaster R On S.Id=R.SNFID
   where MilkTypeID=2 order by NEWID())   -------Buffalow
   ,(SELECT Cast(RAND()*(20-9)+5 As numeric(5,2))) 
   ,0 ---- Rate
   ,0 ---- Amount
   ,2 ---- MilkTimeID ------morning
   ,2 ---- MilktypeID ------Buffalow
   ,@Date
   ,0 ---- Issettled
   ,0 ---- Iscancelled
   ,1 ---- AddedBy
   ,1 ---- UpdatedBy        
   )
Set @Date=DATEADD(Day,1,@Date)
END
        --------------Date loop End
Set @Cust_id=@Cust_id+1
END

GO
Select * from DailyMilkCollection 

GO
Update D
Set D.Rate=T.Rate 
from DailyMilkCollection D
inner join
(
Select FAT_Value,SNF_Value,Rate,MilkTypeID 
from RateMaster R inner Join FATMaster F
On R.FATID=F.id
inner Join SNFMaster S
On R.SNFID=S.id
)T
ON D.FATValue=T.FAT_Value AND D.SNFValue=T.SNF_Value AND D.MilkTypeID=T.MilkTypeID

GO
Update DailyMilkCollection
Set Amount=Rate*Quantity, Code=TransId
GO
Select * from DailyMilkCollection