SQLTeam.com | Weblogs | Forums

Query issue

oracle

#1

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


#2

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


#3

Vinnie
the datediff function is not supported on sql server, that's my problem


#4

I'm pretty sure DATEDIFF is a SQL Server function. What version of SQL Server are you running?


#5

#6

Fix the syntax on the query add inner join. I am typing From my phone.


#7

It's Oracle SQL Developer 2005-2009


#8

Ah. So this is a Microsoft SQL Server site so that's the syntax we followed.


#9

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,

(MAX(TO_CHAR(INVDATE,'DD')) - MIN(TO_CHAR(INVDATE,'DD')))

AS NUMBER_OF_DAYS

FROM INVOICE LEFT JOIN EMPLOYEE USING (EMPNUMBER)

GROUP BY (EMPFIRSTNAME ll'.'ll EMPLOYEELASTNAME),EMPNUMBER,

TO_CHAR (INVDATE,'Mon-YYYY')

ORDER BY EMPNUMBER, INVOICE_MONTH_YEAR;