I have posted this once but it is a little more complicated than it looks at fir
ID: 3882631 • Letter: I
Question
I have posted this once but it is a little more complicated than it looks at first. It is asking for the delay in weeks and also for just the longest delay per Customer ID not every delay out there. I can figure out how to get the delay in weeks using the CAST function but I can't figure out how to get the MAX per customer when CAST is used to get the delay in weeks
Sales.Orders Columns Sales.Customers orderid (PK, int, not nul) custid (FK, int, null) Columns custid (PK, int, not null) f empid (FK, int, not nul) companyname (nvarchar(40), not null) orderdate (datetime, not null) requireddate (datetime, not null) shippeddate (datetime, null) shipperid (FK, int, not null) freight (money, not null) contactname (nvarchar(30), not null) - contacttitle (nvarchar(30), not null) Sales.OrderDetails address (nvarchar(60), not null) city (nvarchar(15), not null) Columns ? orderid (PK, FK, int, not null) productid (PK, FK, int, not null) region (nvarchar(15), null) unitprice (money, not null) qty (smallint, not null) discount (numeric(4,3), not null) shipname (nvarchar(40), not null) postalcode (nvarchar(10), null) country (nvarchar(15), not null) shipaddress (nvarchar(60), not null) shipcity (nvarchar(15), not null) shipregion (nvarchar(15), null) shippostalcode (nvarchar(10), null) phone (nvarchar(24), not null) fax (nvarchar(24), null) shipcountry (nvarchar(15), not null) The customer ID and company name and the longest delay in shipping on any order to that customer Express the delay in weeks and place the output in ascending order by the delayExplanation / Answer
As we need to get longest delay in shipping and query should return the delay in ascending order.
Select sc.custid, sc.companyname
(so.shippeddate - so.requireddate)/7 delay
From sales.Order so, sales.Customers sc
Where so.custid =sc.custid
Order by delay
It will give data as order by of delay and delay comes in form of weeks
Please let mw know for any queries in comment.