Am I able to add columns to my query that are not from a data table?

Databases
#1

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?

#2

Have you tried it? :biggrin:

The best way to learn is to try. See what works and what doesn’t…

(spoiler: that works)

#3

Sorry, but this is not helpful. I spent 60 mins on Google trying to see if this is possible, but perhaps I am using incorrect terminology as all of my results referenced altering the data in the database itself, and not just on the query output.

I wouldn’t even know where to begin to try “experimenting” and I do not know what syntax I would use, and my Google searches have not helped.

#4

What DBMS are you using? I just tried and this works in SQL Server (which is what that looked like). MySQL/PostgreSQL might be a slightly different syntax

Declare @p_Start_Date          datetime = '2022-01-21'

select field1
     , field2
	 , startDate = @p_Start_Date
  from tableName
 where field3 = 'value'
#5

Thank you. Yes it is Microsoft SQL Server – sorry I should have mentioned that. All of my experience is with MySQL, so I am not fully familiar with any differences in syntax.

It looks like it is pretty simple then: In my select criteria I just add a column name I want to display on the output, and then associate it with data I want displayed in it.

I will try to make that work with the script. Unfortunately we had a very brilliant data scientist create the script, so it is very complex. I feel like a handyman with a screw driver trying to repair an engine on a space shuttle. Unfortunately due to the prevailing interdepartmental politics at our organization we are sort of stuck with what we have and can’t get assistance from out IT department to make such a simple amendment.

Now I have to pour through lines of code and try plugging that in, in several spots to see where it might work. I have already tried in a few logical areas to insert this, but it has not worked yet. There are many select statements in this code and my attempts to find the “master select” has not gone well.

Back to trial and error, but at least I know what to insert now… thank you.

#6

I have it working now, and I likely should ask this as a new question in the forum, but how can I limit the result to date only and not the time.

Based on what I am reading online, I need to use the cast(GETDATE) function, but my current experiments are unsuccessful:

Working code within the Select:
, @p_Start_Date As DateTxStart

Attempts:

, cast(GETDATE() As @p_Start_Date) As DateTxStart
, cast(GETDATE(@p_Start_Date)) As DateTxStart

#7

I found the proper syntax I should be using:

, convert(DATE, @p_Start_Date) As DateTxStart