Is there a better way to add a table name suffix that will adjust to add the correct month and day when the month does not correspond to the reporting period? The below code works when the last day of the month is also the run day and month of this report. I need to be able to add the correct report month and day for those times when the report run month and day is after the reporting month. For example The below code runs on August 2nd but I need the Table suffix to reflect Saturday July 31 using the month digit of 7 and the day of 31.
Sorry this is so wordy. I hope I am clear.
---Create intermin Contract Table to be used later in the process to create The final table
---Because the run time for this last process starts on Monday morning, The Suffix for the
-----Contract Table Weekly needs to reflect the Saturday when the snapshots were created
------When the run time month is a new month but the snapshots run month is prior then the Month suffix
--------need to be adjusted to reflect the month when the snapshots were run. For example: in July
---------snapshots were run on Saturday July 31 however this process is run on Monday August 2nd
----------in this case the Month Suffix will need to be adjusted back to July (digit 7) for this
-------------Contract Table Weekly.
--------------this logic also applies for year-end contract tables process during the first days
----------------of the new year month of January
DECLARE @ContractTableDateSuffix as VarChar(5000),
@ContractTableName as VarChar(5000),
@ContractTableStrSQL as VarChar(5000)
---------This will work when the last day of the week is the last day of the month when source data feeds are run
SET @ContractTableDateSuffix = '_'+ CAST(Datepart(month,GetDate())-1 as VarChar(2)) + '_W'
+ CAST(Datepart(Month,GetDate())-1 as VarChar(2)) + CAST(Datepart(Day,GetDate()-2)as VarChar(2))
+ '_' + CAST(Right(Datepart(Year,GetDate()),2) as VarChar(2))
SET @ContractTableName = 'Contract_Table'+ @ContractTableDateSuffix
SET @ContractTableStrSQL = N'select *
Into '+@ContractTableName+'
from #Riders_2'+
' create clustered index ix_1 on '+@ContractTableName+' (company_id, contract, account)'
Exec (@ContractTableStrSQL)
Sorry this is so wordy. I hope I am clear.
---Create intermin Contract Table to be used later in the process to create The final table
---Because the run time for this last process starts on Monday morning, The Suffix for the
-----Contract Table Weekly needs to reflect the Saturday when the snapshots were created
------When the run time month is a new month but the snapshots run month is prior then the Month suffix
--------need to be adjusted to reflect the month when the snapshots were run. For example: in July
---------snapshots were run on Saturday July 31 however this process is run on Monday August 2nd
----------in this case the Month Suffix will need to be adjusted back to July (digit 7) for this
-------------Contract Table Weekly.
--------------this logic also applies for year-end contract tables process during the first days
----------------of the new year month of January
DECLARE @ContractTableDateSuffix as VarChar(5000),
@ContractTableName as VarChar(5000),
@ContractTableStrSQL as VarChar(5000)
---------This will work when the last day of the week is the last day of the month when source data feeds are run
SET @ContractTableDateSuffix = '_'+ CAST(Datepart(month,GetDate())-1 as VarChar(2)) + '_W'
+ CAST(Datepart(Month,GetDate())-1 as VarChar(2)) + CAST(Datepart(Day,GetDate()-2)as VarChar(2))
+ '_' + CAST(Right(Datepart(Year,GetDate()),2) as VarChar(2))
SET @ContractTableName = 'Contract_Table'+ @ContractTableDateSuffix
SET @ContractTableStrSQL = N'select *
Into '+@ContractTableName+'
from #Riders_2'+
' create clustered index ix_1 on '+@ContractTableName+' (company_id, contract, account)'
Exec (@ContractTableStrSQL)