Procedure That make insertion into table according weekdays which weekday data I have in my one table.
ALTER PROC [dbo].[CRS_VehicleAdultChildPrice_Flat]
@pk_VehiclePriceID int,
@WEEKDAYID NVARCHAR (255),
@SeasonEndDate NVARCHAR(25),
@SeasonStartDate NVARCHAR(25)
AS
DECLARE @fkVehiclePriceID int
DECLARE @SessionDate NVARCHAR(25)
DECLARE @ChildNo int
DECLARE @ChildMinAge int
DECLARE @ChildMaxAge int
DECLARE @AdultPrice decimal(18,2)
DECLARE @ChildPrice decimal(18,2)
DECLARE @TourDate datetime
DECLARE @Culture nvarchar(10)
DECLARE @CreatedBy nvarchar(255)
DECLARE @ModifiedBy nvarchar(255)
DECLARE @timings nvarchar(1000)
DECLARE @CUR CURSOR
DECLARE @CURDATE CURSOR
SET @CUR = CURSOR FOR SELECT
pk_VehiclePriceID
,AdultPrice
,VMS_TBL_VehiclePrice.Culture
,VMS_TBL_VehiclePrice.CreatedBy
,VMS_TBL_VehiclePrice.ModifiedBy
,VMS_TBL_VehiclePrice.timing
FROM VMS_TBL_VehiclePrice
INNER JOIN VMS_VehicleAdultPrice_Detail ON VMS_VehicleAdultPrice_Detail.fkVehiclePriceId=VMS_TBL_VehiclePrice.pk_VehiclePriceID
WHERE pk_VehiclePriceID=@pk_VehiclePriceID ORDER BY pk_VehiclePriceID
OPEN @CUR
FETCH NEXT
FROM @CUR INTO
@fkVehiclePriceID,
@AdultPrice,
@Culture,
@CreatedBy,
@ModifiedBy,
@timings
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CURDATE = CURSOR FOR SELECT
DATE_NAME FROM TBL_DATE_WEEKDAY WHERE WEEKDAYID IN (SELECT * FROM DBO.SPLIT(@WEEKDAYID,',')) AND ( CONVERT(DATETIME,DATE_NAME)>=Convert(DATETIME,@SeasonStartDate) AND CONVERT(DATETIME,DATE_NAME) <=Convert(DATETIME,@SeasonEndDate))
OPEN @CURDATE
FETCH NEXT
FROM @CURDATE INTO @SessionDate
print @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
print @SessionDate
INSERT INTO VMS_VehicleAdultPrice_Flat(fkVehiclePriceID, AdultPrice,TourDate, Culture, CreatedBy, CreatedOnDate, ModifiedBy, ModifiedOnDate, Row_Guid, Row_Status,timings)
VALUES(@fkVehiclePriceID,@AdultPrice,@SessionDate ,@Culture,@CreatedBy,GETDATE(),@ModifiedBy,GETDATE(),NEWID(),1,@timings)
FETCH NEXT
FROM @CURDATE INTO @SessionDate
END
CLOSE @CURDATE
DEALLOCATE @CURDATE
FETCH NEXT
FROM @CUR INTO @fkVehiclePriceID,
@AdultPrice,
@Culture,
@CreatedBy,
@ModifiedBy,
@timings
END
CLOSE @CUR
DEALLOCATE @CUR
SET @CUR = CURSOR FOR SELECT
pk_VehiclePriceID
,ChildNo
,ChildMinAge
,ChildMaxAge
,ChildPrice
,VMS_TBL_VehiclePrice.Culture
,VMS_TBL_VehiclePrice.CreatedBy
,VMS_TBL_VehiclePrice.ModifiedBy
FROM VMS_TBL_VehiclePrice
INNER JOIN VMS_TBL_ChildPrice_Detail ON VMS_TBL_ChildPrice_Detail.fkVehiclePriceId=VMS_TBL_VehiclePrice.pk_VehiclePriceID
WHERE pk_VehiclePriceID=@pk_VehiclePriceID ORDER BY pk_VehiclePriceID
OPEN @CUR
FETCH NEXT
FROM @CUR INTO
@fkVehiclePriceID,
@ChildNo,
@ChildMinAge,
@ChildMaxAge,
@ChildPrice,
@Culture,
@CreatedBy,
@ModifiedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CURDATE = CURSOR FOR SELECT
DATE_NAME FROM TBL_DATE_WEEKDAY WHERE WEEKDAYID IN (SELECT * FROM DBO.SPLIT(@WEEKDAYID,',')) AND ( CONVERT(DATETIME,DATE_NAME)>=Convert(DATETIME,@SeasonStartDate) AND CONVERT(DATETIME,DATE_NAME) <=Convert(DATETIME,@SeasonEndDate))
OPEN @CURDATE
FETCH NEXT
FROM @CURDATE INTO @SessionDate
print @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
print @SessionDate
INSERT INTO VMS_VehicleChildPrice_Flat(fkVehiclePriceID, ChildNo, ChildMinAge, ChildMaxAge, ChildPrice, TourDate, Culture, CreatedBy, CreatedOnDate, ModifiedBy, ModifiedOnDate, Row_Guid, Row_Status)
VALUES(@fkVehiclePriceID, @ChildNo, @ChildMinAge, @ChildMaxAge,@ChildPrice,@SessionDate ,@Culture,@CreatedBy,GETDATE(),@ModifiedBy,GETDATE(),NEWID(),1)
FETCH NEXT
FROM @CURDATE INTO @SessionDate
END
CLOSE @CURDATE
DEALLOCATE @CURDATE
FETCH NEXT
FROM @CUR INTO @fkVehiclePriceID,
@ChildNo,
@ChildMinAge,
@ChildMaxAge,
@ChildPrice,
@Culture,
@CreatedBy,
@ModifiedBy
END
CLOSE @CUR
DEALLOCATE @CUR
