SQL Clone
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 (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 184
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 )


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


Create Table #myBranches(
ID INT Identity(1,1) Primary Key NonClustered,
Branch Varchar(15) Not Null,
RegID Integer Null,
Description Varchar(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', 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