USING SQL Create a view that list all vendors names and the average_months_betwe

Question

USING SQL

Create a view that list all vendors names and the average_months_between the invoice_due_date and the invoice_date. Round the “average_months_between” to one decimal point e.g. (99.9). Filter your results to only show those vendors that the “average_months_between” is greater than or equal 1.5 months. Then, sort the result set in descending order by the average_months_between. Hint: the round of 12.567 to one decimal point can be obtained using ROUND(12.567,1) = 12.6

The tables are VENDORS and INVOICES

with VENDORS.vendor_name, INVOICES.invoice_due_date, INVOICES.invoice_date as the fields

How would you subtract the two dates and round the difference in the select statement of a view?

Explanation / Answer

To take difference of two dates use DATEDIFF method

DATEDIFF(DAY, INVOICES.invoice_date,INVOICES.invoice_due_date)

This will give difference in number of days, now if you consider 30 to get the difference is months then divide it by 30, if you need difference directly in terms of months, use "MONTH" as interval in DATEDIFF

Convert number of days obtained from difference into decimal

CONVERT(DECIMAL(5,4),DATEDIFF(DAY,DATEADD(MONTH, -3,GETDATE()),GETDATE())/30)

Now this will give result till 4 decimal places (no round off)

Round off the difference using ROUND function

SELECT ROUND(CONVERT(DECIMAL(5,4),DATEDIFF(DAY,DATEADD(MONTH, -3,GETDATE()),GETDATE())/30),1)

View definition:

CREATE VIEW
vw_AvgMonthBetween
AS
SELECT V.vendor_name, AVG(ROUND(CONVERT(DECIMAL(5,4),DATEDIFF(DAY,I.invoice_date,I.invoice_due_date)/30),1)) average_months_between
FROM VENDORS V
INNER JOIN INVOICES I
ON I.vendor_id = V.vendor_id
GROUP BY V.vendor_name
HAVING AVG(ROUND(CONVERT(DECIMAL(5,4),DATEDIFF(DAY,I.invoice_date,I.invoice_due_date)/30),1)) >=1.5

Sorting using ORDER BY is not allowed inside view definition, use ORDER while accessing data from view

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.