Simple SQL Script Using DateDiff (Average date difference for a give month)

Job ID: 32012145

Budget: $10 – $30 USD

THIS SCRIPT (SQL SCRIPT #1) CURRENTLY EXISTS. YOU WILL BE ADDING TO IT:
Microsoft SQL database has a table, “FactTrackingManufacture”, that has two columns: “RAEntryDate” and “DateShipped”. The script (SQL Script 1) calculates the date difference (in days) between RAEntryDate and DateShipped for all rows in the FactTrackingManufacture table, and then calculate the percentage of rows in which the date difference is more than 10 days for a given month. Does not include any row with ShipDate = NULL. For example, if there are 100 rows with a ShipDate in January. Of those rows, 5 rows had date differences of more than 10. On running the script, the percentage displayed is 95% for that month.

NEW REQUIREMENTS
1) For a given month, calculate the average date difference (in days) between “RAEntryDate” and “DateShipped” in the “FactTrackingManufacturer” table and exclude weekends. Display results in a new column next to "PercentageOfIncludes)
2) Exclude weekends from SQL Script #1


SQL SCRIPT #1
SELECT
MAX(DATENAME(Month, DateShipped)) 'Month'
,CASE
WHEN SUM(IncludePercentageCalc) = 0
THEN 0
ELSE CAST(SUM(IncludePercentageCalc) AS FLOAT) / CAST(COUNT(*) AS FLOAT) * 100
END AS 'PercentageOfInlcudes'
FROM (
SELECT BatchNumber
,FirstFaxed
,DateShipped
,CASE
WHEN DATEDIFF(day, RAEntryDate, DateShipped) > 10
THEN 1
ELSE 0
END 'IncludePercentageCalc'
FROM [PharmaWarehouse].[dbo].[FactTrackingManufacturer]
WHERE DateShipped IS NOT NULL
AND ExcludeFromMetrics IS NULL
AND AuthorizeNeeded = 1
) Groups
GROUP BY DATEPART(Month, Groups.DateShipped)
Related categories: SQL MySQL Microsoft SQL Server