USE [mybookstore]
GO

/****** Object:  StoredProcedure [dbo].[GetSales]    Script Date: 5/15/2014 9:26:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetSales]
( 
   @pBeginDate DateTime = NULL,
   @pEndDate DateTime = NULL
)
AS
BEGIN

select H.InvID, H.OrdDate, H.ExpDate, H.PoNumber, H.CustNumber, H.ShippedDate, D.InvPk, D.ProductID, 
D.Quantity * D.UnitPrice as Sales, S.SalesRepNumber, S.FullName, C.CustName
from InvHeader H inner join InvDetails D on H.InvID = D.InvID
inner join Customers C on H.CustNumber = C.CustNumber
inner join SalesRep S on C.Salesperson = S.SalesRepNumber
where H.ShippedDate IS NOT NULL
and (H.ShippedDate BETWEEN (@pBeginDate) AND (@pEndDate))

END

WHERE H.ShippedDate IS NOT NULL AND 
      (
      (@pBeginDate IS NOT NULL AND @pEndDate IS NOT NULL AND H.ShippedDate BETWEEN (@pBeginDate) AND (@pEndDate)
   OR (@pBeginDate is NULL OR @PEndDate is null)
      )

quote:

---

Originally posted by BitsAndBytes

Thanks for the help.

How do you and you others in the know, handle that situation of where only start or end date is specified? I'm a rookie. Looks like if end date isn't supplied then query till current date would be a good idea, if so how do I do that? But as far as the begin date not being specified do you guys default to the first day of the current year or something like that?

---