Group by when number of group by variables changes

• brozycki

  SSC Veteran

  Points: 255

  More actions

  January 29, 2014 at 10:25 am

  #146646

  I am stuck on a better way to go about this sql. I need to return order counts by Region, Branch, Referral and month. The number of months could vary depending on how many months are passed in. A start date will also be passed in. Each months cycle is the 26th through the 25th so if the start date is 01/16/14 and 3 months is passed in I would need counts for 12/26/13 – 01/25/14 which would be counts for Jan-14, 11/26/13 – 12/25/13 and 10/26/13 – 11/25/13. @DateOne26 calculates the first months 26th, @DateOne25 calculates the first months 25th, @DateRange26 calculates the 26th of the farthest date away based on how many months are passed in. I know that the MonthYear variable is incorrect – it returns the current month and year of the transaction, not the 26th-25th month it falls into. I am not sure how to get the month I want.

  The database is SQL 2005 in 2000 compatibility Mode. I was thinking about using dynamic SQL to build the query but I wonder if there is a better way to do the whole thing. Any suggestions or can someone point me in another direction?

  --@StartDate and @NumberOfMonths are passed in

  --Create temp tables

  Create Table #myOrders(

  ID Int Identity(1,1) Primary Key NonClustered,

  OrdNumberVarchar(15) Not Null,

  RegIDInteger Null,

  OpenDate DateTime Null,

  StatusVarchar(10) Null,

  BranchVarchar(15) Null,

  ReferralInteger Null)

  Create Table #myAgents(

  AgentNumInteger Not Null,

  Name Varchar(150))

  Create Table #myBranches(

  ID INT Identity(1,1) Primary Key NonClustered,

  BranchVarchar(15) Not Null,

  RegIDInteger Null,

  DescriptionVarchar(35) NULL)

  --POPULATE #myOrders table

  Insert INTO #myOrders

  (OrdNumber, RegID, OpenDate, Status, Branch, Referral)

  SELECT 'Ord1', 0, '2013-10-01 00:00:00.000', 'New', '1234', NULLUNION

  SELECT 'Ord2', 1, '2013-10-26 00:00:00.000', 'New', '33333', '22558'UNION

  SELECT 'Ord3', 1, '2013-10-27 00:00:00.000', 'New', '33333', '22558'UNION

  SELECT 'Ord4', 1, '2013-11-14 00:00:00.000', 'Cancelled', '33333', '22558'UNION

  SELECT 'Ord5', 0, '2013-11-22 00:00:00.000', 'New', '1234', '778'UNION

  SELECT 'Ord6', 0, '2013-11-23 00:00:00.000', 'New', '1234', '325'UNION

  SELECT 'Ord7', 0, '2013-11-23 00:00:00.000', 'New', '1234', '325'UNION

  SELECT 'Ord8', 0, '2013-11-28 00:00:00.000', 'New', '22222', '22558'UNION

  SELECT 'Ord9', 0, '2013-12-06 00:00:00.000', 'New', '22222', '22558'UNION

  SELECT 'Ord10', 0, '2013-12-07 00:00:00.000', 'New', '22222', '778'UNION

  SELECT 'Ord11', 0, '2013-12-11 00:00:00.000', 'New', '22222', '22558'UNION

  SELECT 'Ord12', 1, '2013-12-29 00:00:00.000', 'New', '33333', '22558'UNION

  SELECT 'Ord13', 1, '2014-01-06 00:00:00.000', 'New', '33333', '22558'UNION

  SELECT 'Ord14', 1, '2014-01-06 00:00:00.000', 'New', '33333', '778'UNION

  SELECT 'Ord15', 0, '2014-01-08 00:00:00.000', 'New', '1234', '778'UNION

  SELECT 'Ord16', 1, '2014-01-10 00:00:00.000', 'New', '33333', '22558'UNION

  SELECT 'Ord17', 1, '2014-01-11 00:00:00.000', 'New', '33333', '22558'UNION

  SELECT 'Ord18', 0, '2014-01-14 00:00:00.000', 'New', '22222', '33117'

  --POPULATE #myAgents table

  Insert INTO #myAgents

  (AgentNum, Name)

  SELECT 325, 'Company of the Year'UNION

  SELECT 778, 'Moon Landing Mortgage Corporation'UNION

  SELECT 22558, 'Third Insurance Agency'UNION

  SELECT 33117, 'BBCTT Corporation'

  --POPULATE #myBranches table

  Insert INTO #myBranches

  (Branch, RegID, Description)

  SELECT '1234', 0, 'First Branch'UNION

  SELECT '22222', 0, 'Second Branch'UNION

  SELECT '33333', 1, 'Third Branch'

  --DECLARE Variables

  Declare@StartDate as DateTime,

  @NumberofMonths as Integer,

  @DateOne25 as Varchar (12),

  @DateOne26 as Varchar(12),

  @DateRange26 as Varchar (12)

  Set @StartDate = '01/16/2014'

  Set @NumberOfMonths = 3

  Set @DateOne26 = (select right('00' + convert(varchar, DatePart(month, DATEADD(month, -1, @StartDate))),2) + '/26/' + convert(varchar, DatePart(Year, DateAdd(Month, -1,@StartDate))))

  Set @DateOne25 = (select right('00' + convert(varchar, DatePart(month, getdate())),2) + '/25/' + convert(varchar, DatePart(Year, getdate())))

  Set @DateRange26 = (select right('00' + convert(varchar, DatePart(month, DATEADD(month, ((@NumberOfMonths-1) * -1), @DateOne26))),2) + '/26/' + convert(varchar, DatePart(Year, DateAdd(Month, ((@NumberOfMonths-1) * -1), @DateOne26))))

  select O.Regid, O.Branch, B.Description, O.Referral, A.Name as ReferralName,

  (Left(DateName(mm,OpenDate),3) + '-'+ Right(DateName(yy,OpenDate),2)) as MonthYear,

  Count(MonthOneCount.OrdNumber) as OrtString1,

  Count(MonthTwoCount.OrdNumber) as OrtString2,

  Count(MonthThreeCount.OrdNumber) as OrtString3

  from #myOrders O

  Left Outer Join #myAgents A on O.Referral= A.Agentnum

  Left Outer Join #myBranches B on O.Branch = B.Branch and O.RegID = B.RegID

  --First Month Count

  Left Outer Join (select #myOrders.OrdNumber

  From #myOrders

  Where #myOrders.OpenDate between @DateOne26 and @DateOne25

  group by #myOrders.OrdNumber) MonthOneCount

  On MonthOneCount.OrdNumber = O.OrdNumber

  --Second Month Count

  Left Outer Join (select #myOrders.OrdNumber

  From #myOrders

  Where #myOrders.OpenDate between

  (select right('00' + convert(varchar, DatePart(month, DATEADD(month, -1, @DateOne26))),2) + '/26/' + convert(varchar, DatePart(Year, DateAdd(Month, -1,@DateOne26))))

  and

  (select right('00' + convert(varchar, DatePart(month, DATEADD(month, -1, @DateOne25))),2) + '/25/' + convert(varchar, DatePart(Year, DateAdd(Month, -1,@DateOne25))))

  group by #myOrders.OrdNumber) MonthTwoCount

  On MonthTwoCount.OrdNumber = O.OrdNumber

  --Third Month Count

  Left Outer Join (select #myOrders.OrdNumber

  From #myOrders

  Where #myOrders.OpenDate between

  (select right('00' + convert(varchar, DatePart(month, DATEADD(month, -2, @DateOne26))),2) + '/26/' + convert(varchar, DatePart(Year, DateAdd(Month, -2,@DateOne26))))

  and

  (select right('00' + convert(varchar, DatePart(month, DATEADD(month, -2, @DateOne25))),2) + '/25/' + convert(varchar, DatePart(Year, DateAdd(Month, -2,@DateOne25))))

  group by #myOrders.OrdNumber) MonthThreeCount

  On MonthThreeCount.OrdNumber = O.OrdNumber

  where O.OpenDate between @DateRange26 and @DateOne25 and Status Not In ('Cancelled', 'Template', 'Old')

  Group by O.Regid, O.Branch, B.Description, O.Referral, A.Name,

  (Left(DateName(mm,OpenDate),3) + '-'+ Right(DateName(yy,OpenDate),2))

  Order by O.Regid, O.Branch, O.Referral

  DROP TABLE #myOrders

  DROP TABLE #myAgents

  DROP TABLE #myBranches