Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group by when number of group by variables changes


Group by when number of group by variables changes

Author
Message
brozycki
brozycki
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 163
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,
      OrdNumber         Varchar(15) Not Null,
      RegID            Integer Null,
      OpenDate          DateTime Null,
      Status            Varchar(10) Null,
      Branch            Varchar(15) Null,
      Referral         Integer Null   Wink


   Create Table #myAgents(
      AgentNum         Integer Not Null,
      Name             Varchar(150)   Wink

   
   Create Table #myBranches(
      ID INT Identity(1,1) Primary Key NonClustered,
      Branch            Varchar(15) Not Null,
      RegID            Integer Null,
      Description         Varchar(35) NULL   Wink


--POPULATE #myOrders table

Insert INTO #myOrders
      (OrdNumber, RegID, OpenDate, Status, Branch, Referral)
      SELECT 'Ord1', 0, '2013-10-01 00:00:00.000', 'New', '1234', NULL         UNION
      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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search