Finding data for child entities
-
I've lurked here for some time now and learned much, but have never had a question I felt worthy of posting until now.
I'm looking for some assistance constructing what I believe to be calls from a stored proc to a UDF that then recursively calls itself. Certainly it may be possible to do it another way (self join?), and I am completely open to suggestions. For some reason I'm not being able to come up with an elegant solution. The results will ultimately be fed to a web application. I'm much better at that...than this.
Though this is not a multi-level marketing company... it operates in similar fashion
I need to be able to pass EntityID and EntityTypeID to different stored procs and return:
1) Total sales at all terminals in the hierarchy (only a $ will be returned). Sales_All_Children
2) Total sales grouped by TransactionDate. Sales_By_Date
3) Total sales grouped by each EntityID/EntityTypeID. Sales_By_Child
4) Total sales grouped by ProductID. Sales_By_Product.
5) Total Sales grouped by TerminalID. Sales_By_Terminal.
If you wanted to throw in optional parameters for @BeginDate and @EndDate that would be great too.
I do need $0 if no sales existed.
We cannot assume that the hierarchy will always be maintained in a
descending fashion, so the test of "where ParentETypeID < ETypeID"
is not guaranteed to be relevant.
We can assume that the only EntityType that can be a parent to a
Terminal is Store(6) and the only EntityType that can be a parent
to Store is Retailer(5)
A Store can have more than one Terminal
Any/All help most graciously appreciated.
-CD
I've built out a small sample set here:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EntityTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EntityTypes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Organizations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Organizations]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Terminals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Terminals]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Transactions]
GO
CREATE TABLE [dbo].[EntityTypes] (
[EntityTypeID] [int] NOT NULL ,
[Description] [varchar] (30) NOT NULL ,
CONSTRAINT [PK_EntityTypes] PRIMARY KEY CLUSTERED
(
[EntityTypeID] ) ON [PRIMARY] , ) ON [PRIMARY]
go
CREATE TABLE [dbo].[Organizations] (
[EntityID] [varchar] (30) NOT NULL ,
[EntityTypeID] [int] NOT NULL ,
[ParentEntityID] [varchar] (30) NOT NULL ,
[ParentEntityTypeID] [int] NOT NULL ,
CONSTRAINT [PK_Organizations] PRIMARY KEY CLUSTERED
(
[EntityID] ,
[EntityTypeID] ) ON [PRIMARY] ) ON [PRIMARY]
go
CREATE TABLE [dbo].[Terminals] (
[TerminalID] [varchar] (30) NOT NULL ,
[ParentEntityID] [varchar] (30) NOT NULL ,
CONSTRAINT [PK_Terminals] PRIMARY KEY CLUSTERED
(
[TerminalID] ) ON [PRIMARY] , ) ON [PRIMARY]
go
CREATE TABLE [dbo].[Transactions] (
[TerminalID] [varchar] (30) NOT NULL ,
[Amount] [numeric](7, 2) NOT NULL ,
[ProductID] [varchar] (10) NOT NULL ,
[TransactionDate] [smalldatetime] NOT NULL ) ON [PRIMARY]
go
CREATE PROCEDURE [dbo].[Sales_All_Children]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select this FROM that
go
CREATE PROCEDURE [dbo].[Sales_By_Child]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select this FROM that
go
CREATE PROCEDURE [dbo].[Sales_By_Product]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select this FROM that
go
CREATE PROCEDURE [dbo].[Sales_By_Terminal]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select this FROM that
go
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (0, 'Root')
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (1, 'Association')
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (2, 'Master')
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (3, 'Reseller')
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (4, 'Sub')
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (5, 'Retailer')
INSERT INTO EntityTypes (EntityTypeID, [Description]) VALUES (6, 'Store')
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EP',1,'EP',0)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EP',2,'EP',1)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EP',3,'EP',2)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPRA',5,'EP',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPRB',5,'EP',2)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPRC',5,'EP',1)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPS1',6,'EPRA',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPS2',6,'EPRB',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPS3',6,'EPRB',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPS4',6,'EPRC',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPS5',6,'EPRC',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('EPS6',6,'EPRC',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHR',2,'EP',1)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHR',3,'SHR',2)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRRA',5,'SHR',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRRB',5,'SHR',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRRC',5,'SHR',2)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRS1',6,'SHRA',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRS2',6,'SHRA',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRS3',6,'SHRB',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('SHRS4',6,'SHRC',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('RUS',3,'SHR',2)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('RUSRA',5,'RUS',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('RUSS1',6,'RUSRA',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('RUSRB',5,'RUS',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('RUSS2',6,'RUSRB',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('2CP',3,'SHR',2)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('2CPRA',5,'2CP',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('2CPS1',6,'2CPRA',5)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('TAD',4,'SHR',3)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('TADRA',5,'TAD',4)
INSERT INTO Organizations (EntityID, EntityTypeID, ParentEntityID, ParentEntityTypeID) VALUES ('TADS1',6,'TADRA',5)
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10000', 'EPS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10002', 'EPS2')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10004', 'EPS3')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10006', 'EPS4')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10008', 'EPS5')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10010', 'EPS6')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10012', 'EPS6')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10020', 'SHRS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10022', 'SHRS2')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10024', 'SHRS3')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10026', 'SHRS4')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10030', 'RUSS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10032', 'RUSS2')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10040', '2CPS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10042', '2CPS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10050', 'TADS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10052', 'TADS1')
INSERT INTO Terminals (TerminalID, ParentEntityID) VALUES ('10054', 'TADS1')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10000',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10000',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10000',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10000',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10000',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10002',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10002',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10002',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10002',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10002',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10004',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10004',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10004',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10004',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10004',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10006',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10006',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10006',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10006',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10006',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10008',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10008',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10008',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10008',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10008',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10010',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10010',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10010',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10010',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10010',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10012',3,'P101','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10012',3,'P102','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10020',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10020',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10020',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10020',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10020',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10022',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10022',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10022',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10022',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10022',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10024',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10024',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10024',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10024',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10024',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10026',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10026',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10026',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10026',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10026',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10030',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10030',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10030',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10030',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10030',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10032',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10032',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10032',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10032',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10032',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10040',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10040',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10040',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10040',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10040',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10042',3,'P101','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10042',3,'P102','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10050',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10050',1,'P102','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10050',1,'P101','3/1/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10050',1,'P101','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10050',1,'P102','3/15/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10052',3,'P101','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10052',3,'P102','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10054',4,'P101','3/10/2004')
INSERT INTO Transactions (TerminalID, Amount, ProductID, TransactionDate) VALUES ('10054',4,'P102','3/10/2004')
-
Do you need something like this :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_All_Children]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop proc [dbo].[Sales_All_Children]
GO
CREATE PROCEDURE [dbo].[Sales_All_Children]
(
@EntityID varchar(30),
@EntityTypeID int
) AS
begin
--Total sales at all terminals in the hierarchy
select Sales_All_Children = sum(TR.Amount)
from dbo.Transactions TR, dbo.Terminals TE
where TR.TerminalID = TE.TerminalID
and TE.ParentEntityID in ( select O.EntityID from Organizations O where O.EntityID = @EntityID or O.ParentEntityID = @EntityID )
end
go
-
Base on the sample.
create function fnTerminalEntityID (@EntityID varchar(30), @EntityTypeID int)
returns @TerminalEntity table (EntityID varchar(30), EntityTypeID int)
as
begin
declare @level int
declare @stack table (entityid varchar(30), entitytypeid int, level int)
insert into @stack values(@EntityID, @EntityTypeID, 1)
set @level = 1
while @level > 0
begin
if exists(select * from @stack where level = @level)
begin
select @EntityID = EntityID, @EntityTypeID = EntityTypeID from @stack where level = @level
insert @TerminalEntity select EntityID, EntityTypeID from @stack a
where not exists(select * from Organizations where ParentEntityID = a.EntityID and ParentEntityTypeID = a.EntityTypeID)
and EntityID = @EntityID and EntityTypeID = @EntityTypeID
delete from @stack where level = @level and EntityID = @EntityID and EntityTypeID = @EntityTypeID
insert @stack select EntityID, EntityTypeID, @level + 1 from Organizations where ParentEntityID = @EntityID and ParentEntityTypeID = @EntityTypeID
if @@rowcount > 0
set @level = @level + 1
end
else
set @level = @level - 1
end
return
end
go
create PROCEDURE [dbo].[Sales_All_Children]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select sum(isnull(amount,0))[Sales_All_Children] FROM transactions a
inner join terminals b on a.terminalid = b.terminalid
inner join dbo.fnTerminalEntityID(@EntityID, @EntityTypeID) c on c.EntityID = b.ParentEntityID
go
create PROCEDURE [dbo].[Sales_By_Date]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select sum(isnull(amount,0))[Sales], TransactionDate FROM transactions a
inner join terminals b on a.terminalid = b.terminalid
inner join dbo.fnTerminalEntityID(@EntityID, @EntityTypeID) c on c.EntityID = b.ParentEntityID
group by transactiondate
go
create PROCEDURE [dbo].[Sales_By_Child]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select sum(isnull(amount,0))[Sales_By_Child], c.EntityID, c.EntityTypeID FROM transactions a
inner join terminals b on a.terminalid = b.terminalid
inner join dbo.fnTerminalEntityID(@EntityID, @EntityTypeID) c on c.EntityID = b.ParentEntityID
group by c.EntityID, c.EntityTypeID
go
create PROCEDURE [dbo].[Sales_By_Product]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select sum(isnull(amount,0))[Sales_By_Product], ProductID FROM transactions a
inner join terminals b on a.terminalid = b.terminalid
inner join dbo.fnTerminalEntityID(@EntityID, @EntityTypeID) c on c.EntityID = b.ParentEntityID
group by ProductID
go
create PROCEDURE [dbo].[Sales_By_Terminal]
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
Select sum(isnull(amount,0))[Sales_By_Terminal], a.TerminalID FROM transactions a
inner join terminals b on a.terminalid = b.terminalid
inner join dbo.fnTerminalEntityID(@EntityID, @EntityTypeID) c on c.EntityID = b.ParentEntityID
group by a.TerminalID
go
Test cases
exec [Sales_All_Children] 'EP',1
exec [Sales_By_Date] 'EP',1
exec [Sales_By_Child] 'EP',1
exec [Sales_By_Product] 'EP',1
exec [Sales_By_Terminal] 'EP',1
go
-
wz700,
Fantastic, Thank you! You can't imagine how much you have helped me thus far. Where do I send the check?
[Sales_By_Child] is quite useful as it exists but I would probably call it [Sales_By_Store]. My intended request might have been better broken into 2 procs:
[Sales_By_Immediate_Child] which would return EntityID, EntityTypeID, $ for each first level child.
Passing in EP, 1 would return:
EP, 2, $15 (sales for terminals 10000, 10002, 10004)
EPRC, 5, $21 (sales for terminals 10006, 10008, 10010, 10012)
Passing in EP, 2 would return:
EP, 3, $5 (sales for terminal 10000)
EPRB, 5, $10 (sales for terminals 10002, 10004)
and...
[Sales_By_Immediate_Child_Type] which would return EntityTypeID, Description, $ for each first level child type.
Passing in EP, 1 would return:
2, Master, $15
5, Retailer, $21
Passing in EP, 2 would return:
3, Reseller, $5
5, Retailer, $10
my humble thanks
--CD
-
One way of doing Sales_By_Immediate_Child:
You may want to finish Sales_By_Immediate_Child_Type. Let me know if having difficult.
create proc Sales_By_Immediate_Child
(
@EntityID varchar(30),
@EntityTypeID int
)
AS
set nocount on
declare @ChildEntityID varchar(30), @ChildEntityTypeID int
declare @Result table ([Sales_By_Immediate_Child] money, EntityID varchar(30), EntityTypeID int)
declare Immediate_Child_cursor cursor local for
select EntityID, EntityTypeID from Organizations
where ParentEntityID = @EntityID and ParentEntityTypeID = @EntityTypeID
Open Immediate_Child_cursor
Fetch next From Immediate_Child_cursor INTO @ChildEntityID, @ChildEntityTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @result
Select sum(isnull(amount,0))[Sales_By_Immediate_Child], @ChildEntityID [EntityID], @ChildEntityTypeID EntityTypeID
FROM transactions a
inner join terminals b on a.terminalid = b.terminalid
inner join dbo.fnTerminalEntityID(@ChildEntityID, @ChildEntityTypeID) d on d.EntityID = b.ParentEntityID
Fetch next From Immediate_Child_cursor INTO @ChildEntityID, @ChildEntityTypeID
END
CLOSE Immediate_Child_cursor
DEALLOCATE Immediate_Child_cursor
select * from @result
GO
exec Sales_By_Immediate_Child 'EP',2
/* Result
5.0000 EP 3
10.0000 EPRB 5
*/
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply