Construct a query that will show the number of days that exist between the first invoice and last invoice, for each month, for each employee, using the min max function. Be sure to provide the SQL script that will carry out this function.
I know I have to join my invoice table with employee table, group by employee number
and show an output o f
emp name, emp id, mon, total days
The query needs to show how many days between start date and end date
I have to use max and min the subtract max minus min = num of days I'm missing something in my syntax I need help finishing the below query and writing the min man syntax
employee.emp_numb,
employee.emp_last_name,
invoice.inv_date
from employee
inner join invoice
on employee.emp_numb = invoice.emp_number
If this is for a test question the probably want to see a recursive cte, but for real world I'd just create a
Select * from
Employee a
(
Select emp_num,month(inv_date) as month,year(inv_date) as year,min(inv_date) as minmonth,max(inv_date) as maxmonth ,datediff('d',min(inv_date),max(inv_date) as diffdays from invoice aa
Group by month(inv_date),year(inv_date),emp_numb) b
On a.emp_numb = b.Emp_numb
this is part of a working query that a classmate showed me. I'm not sure how to write my tables into this query. This is supposed to support the min max commands
TO CHAR(INVDATE. 'Mon-YYYY') AS INVOICE_MONTH_YEAR,