SELECT orderid custid empid orderdate requireddate shippeddate shipperid

Select orderid custid empid orderdate requireddate

This preview shows page 7 - 8 out of 8 pages.

SELECT orderid, custid, empid, orderdate, requireddate,shippeddate, shipperid, freight, shipname, shipaddress, shipcity,shipregion, shippostalcode, shipcountryFROM Sales.OrdersWHERE custid = @cid;GO-- Test FunctionSELECT orderid, custidFROM dbo.GetCustOrders(1) AS O;SELECT O.orderid, O.custid, OD.productid, OD.qtyFROM dbo.GetCustOrders(1) AS OJOIN Sales.OrderDetails AS ODON O.orderid = OD.orderid;GO-- CleanupIF OBJECT_ID('dbo.GetCustOrders') IS NOT NULLDROP FUNCTION dbo.GetCustOrders;GO----------------------------------------------------------------------- APPLY---------------------------------------------------------------------SELECT S.shipperid, E.empidFROM Sales.Shippers AS SCROSS JOIN HR.Employees AS E;SELECT S.shipperid, E.empidFROM Sales.Shippers AS SCROSS APPLY HR.Employees AS E;-- 3 most recent orders for each customerSELECT C.custid, A.orderid, A.orderdateFROM Sales.Customers AS CCROSS APPLY(SELECT TOP (3) orderid, empid, orderdate, requireddate FROM Sales.Orders AS OWHERE O.custid = C.custid
Background image
ORDER BY orderdate DESC, orderid DESC) AS A;-- in SQL Server 2012SELECT C.custid, A.orderid, A.orderdateFROM Sales.Customers AS CCROSS APPLY(SELECT orderid, empid, orderdate, requireddate FROM Sales.Orders AS OWHERE O.custid = C.custidORDER BY orderdate DESC, orderid DESCOFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY) AS A;-- 3 most recent orders for each customer, preserve customersSELECT C.custid, A.orderid, A.orderdateFROM Sales.Customers AS COUTER APPLY(SELECT TOP (3) orderid, empid, orderdate, requireddate FROM Sales.Orders AS OWHERE O.custid = C.custidORDER BY orderdate DESC, orderid DESC) AS A;-- in SQL Server 2012SELECT C.custid, A.orderid, A.orderdateFROM Sales.Customers AS COUTER APPLY(SELECT orderid, empid, orderdate, requireddate FROM Sales.Orders AS OWHERE O.custid = C.custidORDER BY orderdate DESC, orderid DESCOFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY) AS A;-- Creation Script for the Function TopOrdersIF OBJECT_ID('dbo.TopOrders') IS NOT NULLDROP FUNCTION dbo.TopOrders;GOCREATE FUNCTION dbo.TopOrders(@custid AS INT, @n AS INT)RETURNS TABLEASRETURNSELECT TOP (@n) orderid, empid, orderdate, requireddate FROM Sales.OrdersWHERE custid = @custidORDER BY orderdate DESC, orderid DESC;/*-- in SQL Server 2012SELECT orderid, empid, orderdate, requireddate FROM Sales.OrdersWHERE custid = @custidORDER BY orderdate DESC, orderid DESCOFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;*/GOSELECTC.custid, C.companyname,A.orderid, A.empid, A.orderdate, A.requireddate FROM Sales.Customers AS CCROSS APPLY dbo.TopOrders(C.custid, 3) AS A;
Background image

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture