Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group by when number of group by variables changes Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 10:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:35 AM
Points: 124, Visits: 63
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
Post #1536025
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse