I am quite new to SQL and I am working with an existing script that we use to pull data from a database and paste into an Excel sheet for further analysis.
The data we pull is in data ranges, and we just edit the date range and then execute the script to get our data. However, occasionally one of my team members (including myself) will accidentally not increment a date. (i.e. If we just pulled data from 2020, and typed in 2020-01-01 and 2020-12-31 as the data range, then sometimes when we pull the data after for 2021, the year of the start date might not get amended, and only the end date gets amended, so we end up pulling for a 2-year period, being 2020-01-01 to 2021-12-31.)
The yearly data is copied into annual tabs on our worksheet. The script performs numerous calculations and filters to the data, so we aren’t able to pull just the raw data from the database, and then have Excel do all the work. (While we could try to get our IT department to alter the script to just pull the raw data, however there would be so many rows of data it would greatly impact the performance of the sheet, or possibly just outright crash it).
What I would like to do, is essentially append on a column for “Start Date” and for “End Date”. The start date the user enters into the script would populate all the rows in “Start Date”, and similarly for the end date. Yes, this would be adding on many rows of redundant data, however once pasted into the spreadsheet, I will have the title row then grab the start and end date from the first row of data and display it above the top heading row of the sheet, and highlight it if the date range is outside of the expected date range for that annual tab.
Currently, here is the script declarations and part of they query we use that references the date ranges:
Declare @p_Start_Date datetime = '2022-01-21'
, @p_End_Date datetime = '2022-01-31'
, @x_Today datetime
, @x_Five_Years_Previous datetime
Select @x_Today = dbo.FN_Start_of_Day(GetDate())
, @x_Five_Years_Previous = dbo.FN_Start_of_Day(DateAdd(year, -5, GetDate()))
, @p_Start_Date = dbo.FN_Start_of_Day(@p_Start_Date)
, @p_End_Date = dbo.FN_End_of_Day(@p_End_Date)
Am I able to add a start date column and an end date column to my data query results with SQL as I have described above?