Reading a month/year from a column to initiate a Query

Transact-SQL
1

I hope I explain this thoroughly:

I have a DB that has a date/time column.

I am passing a SQL statement from a VB program where I input only a Month and Year (for searching) to give results.

So As an example my SQL is as follows:

SELECT ISNULL(SUM(INCOME_DISABILITY
                               + INCOME_MISC
                               + INCOME_SNAP
                               + INCOME_SSD
                               + INCOME_SSI
                               + INCOME_WORKERS_COMP
                               + PAYROLL_DAWN
                               + PAYROLL_JOHN),0) AS TOTAL_INCOME
   FROM Weekly_Expense_Table
 WHERE Month in " + xMonths + "
       AND Year = " + xYear + "

Assume I am searching for all records input from 01-01-2023 through to 01-31-2023 inclusive as the user uses the search parameter in the program they would input Month = 1, Year = 2023 or Month = 1st quarter (months 1, 2, 3), Year = 2023 (This is already handled in VB as I did this years ago in another program... however I put in month/year columns in that DB as the application has no true date column as it was not needed.)

I need a way pass user inputted xMonths and xYear variables into the SQL (where clause) and be able to read that from a date that is already in the DB as (example) 2023-01-01 00:00;00 via the PAYROLL_DATE column.

I was thinking of using: ```
EXTRACT(year FROM PAYROLL_DATE)

and

(EXTRACT(MONTH FROM PAYROLL_DATE)

but that only extracts the date (month and/or year) already in the DB and don't think that is gonna work since the variable has to be passed into the SQL with the variable already known.

Additionally, the xMonths variable is important as the user needs the ability to search for data entered in by month, quarter or year (thus the 'in' statement for 'Where Month...'' statement). Those month/quarter variables are already set in the VB.

I could run

UPDATE Weekly_Expense_Table
SET MONTH = MONTH(PAYROLL_DATE)
WHERE MONTH IS NULL

and

UPDATE Weekly_Expense_Table
SET YEAR = YEAR(PAYROLL_DATE)
WHERE YEAR IS NULL

 but is that really the most efficient way to do this since a date column already exists?
2

Please specify which dbms you are using, as this does not look like SQL Server. Please be aware that this is a SQL Server forum, therefore, someone here may or may not be able to help you with a different dbms (such as MySQL, etc.).

3

It is indeed MS SQL Server

The snippet of code I sent is how I pass it to the SQL Server via VB.

4

The proper comparison would be of the form:

WHERE PAYROLL_DATE >= DATEFROMPARTS(xYear, <starting_month>, 1) AND 
    PAYROLL_DATE < DATEADD(DATEFROMPARTS(xYear, <starting_month>, 1), MONTH,
        <total_number_of_months>)

You should be able to compute starting_month and total_number_of_months from the inputs in VB.

5

hi

hope this helps

create sample data script

declare @Weekly_Expense_Table table ( Month_x int , Year_x int , INCOME_DISABILITY int , INCOME_MISC int ,PAYROLL_DATE date)

insert into @Weekly_Expense_Table Values ( null, null , 20 , 15 ,'2023-09-01')

insert into @Weekly_Expense_Table Values ( null, null , 40 , 34 ,'2035-10-02')

insert into @Weekly_Expense_Table Values ( null,null, 200 , 150 ,'1969-10-15')
insert into @Weekly_Expense_Table Values ( null,null, 200 , 150 ,'1969-11-12')
insert into @Weekly_Expense_Table Values ( null,null, 200 , 150 ,'1969-12-12')

insert into @Weekly_Expense_Table Values ( null,null, 200 , 150 ,'2023-09-01')

declare @month int
declare @year int = 1969 
declare @quarter int = 4

select *  
  from @Weekly_Expense_Table 
 where year(PAYROLL_DATE) = @year 
   and datepart(Quarter,PAYROLL_DATE) = @quarter

image
image1059×421 15.9 KB