Billing users

We have a mssql hotel with our clients databases of various sizes. Our problem is our to bill these clients in a fair way to cover the total cost of maintaining the database server. I will appreciate any suggestion. Thanks in advance!
There aren't charge back mechanisms built in to SQL Server as you know.
You'll need to do the work based on sampling of some kind and come up with a
formula that you think is decent for your needs.
Take a look at the columns in master..sysprocesses and see what's of
interest to bill by.
Some people I've chatted with bill primarily based on IO and IO is easy to
track on a file level by using fn_virtualfilestats.
You would think that tracking by IO is not representative. But in many ways,
disk is the most expensive part of most server configurations and it's easy
to track on a pretty reliable way with the above function...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Bamyz" <anonymous@.discussions.microsoft.com> wrote in message
news:D6BBBA2F-0274-40CB-BF9B-5EB4667C376D@.microsoft.com...
> We have a mssql hotel with our clients databases of various sizes. Our
problem is our to bill these clients in a fair way to cover the total cost
of maintaining the database server. I will appreciate any suggestion. Thanks
in advance!
|||Thanks very much Brian and have a happy easter!

Billing users

We have a mssql hotel with our clients databases of various sizes. Our problem is our to bill these clients in a fair way to cover the total cost of maintaining the database server. I will appreciate any suggestion. Thanks in advance!There aren't charge back mechanisms built in to SQL Server as you know.
You'll need to do the work based on sampling of some kind and come up with a
formula that you think is decent for your needs.
Take a look at the columns in master..sysprocesses and see what's of
interest to bill by.
Some people I've chatted with bill primarily based on IO and IO is easy to
track on a file level by using fn_virtualfilestats.
You would think that tracking by IO is not representative. But in many ways,
disk is the most expensive part of most server configurations and it's easy
to track on a pretty reliable way with the above function...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Bamyz" <anonymous@.discussions.microsoft.com> wrote in message
news:D6BBBA2F-0274-40CB-BF9B-5EB4667C376D@.microsoft.com...
> We have a mssql hotel with our clients databases of various sizes. Our
problem is our to bill these clients in a fair way to cover the total cost
of maintaining the database server. I will appreciate any suggestion. Thanks
in advance!|||Thanks very much Brian and have a happy easter!

Billing users

We have a mssql hotel with our clients databases of various sizes. Our probl
em is our to bill these clients in a fair way to cover the total cost of mai
ntaining the database server. I will appreciate any suggestion. Thanks in ad
vance!There aren't charge back mechanisms built in to SQL Server as you know.
You'll need to do the work based on sampling of some kind and come up with a
formula that you think is decent for your needs.
Take a look at the columns in master..sysprocesses and see what's of
interest to bill by.
Some people I've chatted with bill primarily based on IO and IO is easy to
track on a file level by using fn_virtualfilestats.
You would think that tracking by IO is not representative. But in many ways,
disk is the most expensive part of most server configurations and it's easy
to track on a pretty reliable way with the above function...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Bamyz" <anonymous@.discussions.microsoft.com> wrote in message
news:D6BBBA2F-0274-40CB-BF9B-5EB4667C376D@.microsoft.com...
> We have a mssql hotel with our clients databases of various sizes. Our
problem is our to bill these clients in a fair way to cover the total cost
of maintaining the database server. I will appreciate any suggestion. Thanks
in advance!|||Thanks very much Brian and have a happy easter!

Bill of material (SQL2000)

Hi,
Does anyone got a really good/fast example on how to do a BOM with unlimited
levels in SQL2000
- without using cursors :)
2 tables (one for items, one for parent/child relations)
In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
In advance, thanks...
Kr. Sorenhow do you want your data output? just a SP that returns a list of all
materials a particular item requires, right down the tree?
if so then the only way I can think of without cursors is using global
temporary tables and recursively called stored procedures to populate
it (but then you might as well use a cursor for that).
if you're wanting columns of data to represent the tree structure then
it's Dynamic SQL you'll need|||Soren, here goes:
-- DDL & Sample Data for Parts, BOM
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.BOM') IS NOT NULL
DROP TABLE dbo.BOM;
GO
IF OBJECT_ID('dbo.Parts') IS NOT NULL
DROP TABLE dbo.Parts;
GO
CREATE TABLE dbo.Parts
(
partid INT NOT NULL PRIMARY KEY,
partname VARCHAR(25) NOT NULL
);
INSERT INTO dbo.Parts(partid, partname) VALUES( 1, 'Black Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 2, 'White Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 3, 'Latte');
INSERT INTO dbo.Parts(partid, partname) VALUES( 4, 'Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 5, 'Double Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 6, 'Cup Cover');
INSERT INTO dbo.Parts(partid, partname) VALUES( 7, 'Regular Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES( 8, 'Stirrer');
INSERT INTO dbo.Parts(partid, partname) VALUES( 9, 'Espresso Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES(10, 'Tea Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(11, 'Milk');
INSERT INTO dbo.Parts(partid, partname) VALUES(12, 'Coffee Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(13, 'Tea Leaves');
INSERT INTO dbo.Parts(partid, partname) VALUES(14, 'Water');
INSERT INTO dbo.Parts(partid, partname) VALUES(15, 'Sugar Bag');
INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee');
INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans');
CREATE TABLE dbo.BOM
(
partid INT NOT NULL REFERENCES dbo.Parts,
assemblyid INT NULL REFERENCES dbo.Parts,
unit VARCHAR(3) NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
UNIQUE(partid, assemblyid),
CHECK (partid <> assemblyid)
);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 1, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 2, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 3, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 4, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 5, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 1, 'mL', 230.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 2, 'mL', 205.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 2, 'mL', 25.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 3, 'mL', 225.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 5, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 5, 'EA', 2.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(13, 10, 'g' , 5.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 10, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 12, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(16, 12, 'g' , 15.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(17, 16, 'g' , 15.00);
GO
-- Creation Script for Function fn_partsexplosion
---
-- Function: fn_partsexplosion, Parts Explosion
--
-- Input : @.root INT: Root part id
--
-- Output : @.PartsExplosion Table:
-- id and level of contained parts of input part
-- in all levels
--
-- Process : * Insert into @.PartsExplosion row of input root part
-- * In a loop, while previous insert loaded more than 0 rows
-- insert into @.PartsExplosion next level of parts
---
USE tempdb;
GO
IF OBJECT_ID('dbo.fn_partsexplosion') IS NOT NULL
DROP FUNCTION dbo.fn_partsexplosion;
GO
CREATE FUNCTION dbo.fn_partsexplosion(@.root AS INT)
RETURNS @.PartsExplosion Table
(
partid INT NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
unit VARCHAR(3) NOT NULL,
lvl INT NOT NULL,
n INT NOT NULL IDENTITY, -- surrogate key
UNIQUE CLUSTERED(lvl, n) -- Index will be used to filter lvl
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0; -- Initialize level counter with 0
-- Insert root node to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT partid, qty, unit, @.lvl
FROM dbo.BOM
WHERE partid = @.root;
WHILE @.@.rowcount > 0 -- while previous level had rows
BEGIN
SET @.lvl = @.lvl + 1; -- Increment level counter
-- Insert next level of subordinates to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT C.partid, P.qty * C.qty, C.unit, @.lvl
FROM @.PartsExplosion AS P -- P = Parent
JOIN dbo.BOM AS C -- C = Child
ON P.lvl = @.lvl - 1 -- Filter parents from previous level
AND C.assemblyid = P.partid;
END
RETURN;
END
GO
-- Parts Explosion
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM dbo.fn_partsexplosion(2) AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
Output:
partid partname qtyn unit lvl
-- -- -- -- --
2 White Tea 1.00n EA 0
6 Cup Cover 1.00n EA 1
7 Regular Cup 1.00n EA 1
10 Tea Shot 1.00n EA 1
14 Water 205.00n mL 1
11 Milk 25.00n mL 1
13 Tea Leaves 5.00n g 2
14 Water 20.00n mL 2
-- Parts Explosion, Aggregating Parts
SELECT P.partid, P.partname, PES.qty, PES.unit
FROM (SELECT partid, unit, SUM(qty) AS qty
FROM dbo.fn_partsexplosion(2) AS PE
GROUP BY partid, unit) AS PES
JOIN dbo.Parts AS P
ON P.partid = PES.partid;
Output:
partid partname qty unit
-- -- -- --
2 White Tea 1.00 EA
6 Cup Cover 1.00 EA
7 Regular Cup 1.00 EA
10 Tea Shot 1.00 EA
13 Tea Leaves 5.00 g
11 Milk 25.00 mL
14 Water 225.00 mL
In SQL Server 2005 it would look like this:
-- CTE Solution for Parts Explosion
DECLARE @.root AS INT;
SET @.root = 2;
WITH PartsExplosionCTE
AS
(
-- Anchor member returns root part
SELECT partid, qty, unit, 0 AS lvl
FROM dbo.BOM
WHERE partid = @.root
UNION ALL
-- Recursive member returns next level of parts
SELECT C.partid, CAST(P.qty * C.qty AS DECIMAL(8, 2)),
C.unit, P.lvl + 1
FROM PartsExplosionCTE AS P
JOIN dbo.BOM AS C
ON C.assemblyid = P.partid
)
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM PartsExplosionCTE AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:ObORLLiXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Does anyone got a really good/fast example on how to do a BOM with
> unlimited levels in SQL2000
> - without using cursors :)
> 2 tables (one for items, one for parent/child relations)
> In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
> In advance, thanks...
> Kr. Soren
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details then Google Nested
Sets model.

Bill of material

I have a table with data
create table a
(
a01 char(4),
a02 char(4),
a03 int
);
insert into a
values('a','b',1);
insert into a
values('a','c',1);
insert into a
values('x','c',1);
insert into a
values('c','g',1);
insert into a
values('b','h',1);
insert into a
values('h','k',1);
insert into a
values('b','k',1);
insert into a
values('g','k',1);
if my entry where value "k", i want show result
a01,a03
a 2
x 1
how to do ....
-
thanks!Get a copy of TREES & HIERARCHIES IN SQL for help.|||thanks a lot!
this my tree sql parent to find child ,but i want child to find parent,
do you have any idea?
ALTER PROCEDURE BOMIA_PPart
(
@.MODE NVARCHAR(12)
)
AS
SET NOCOUNT ON
CREATE TABLE #TEMP_A
(
LVL INT,
PARENT NVARCHAR(12) ,
PARTNO NVARCHAR(12) ,
M CHAR(2),
P CHAR(2),
PARTDESC NVARCHAR(40) ,
UNIT NVARCHAR(3) ,
USAGE REAL,
LOCATION NVARCHAR(18) ,
SXRQ NVARCHAR(10),
SSRQ NVARCHAR(10)
)
DECLARE @.ENDTREE INT
DECLARE @.NLVL INT
SELECT @.ENDTREE = 0
SELECT @.NLVL=1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT 1,IB001 ,IB003 ,IB031,IB004,IB011,IB008,IB009
FROM BOMIB
WHERE IB001=@.MODE
WHILE (@.ENDTREE =0 )
BEGIN
SELECT @.NLVL=@.NLVL+1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT @.NLVL,
A.IB001 ,
A.IB003 ,
A.IB031,
A.IB004,
A.IB011,
A.IB008,
A.IB009
FROM BOMIB A, #TEMP_A B
WHERE A.IB001 =B.PARTNO
AND B.LVL=@.NLVL -1
AND B.P!='1'
IF NOT EXISTS (SELECT PARTNO COLLATE database_default FROM #TEMP_A WHERE
LVL =@.NLVL)
SELECT @.ENDTREE=1
END
UPDATE #TEMP_A
SET M=AA070,
PARTDESC=AA020,
UNIT=AA050
FROM INVAA,#TEMP_A
WHERE AA010=PARTNO
SELECT
CAST(REPLICATE('.',LVL)+CAST(LVL AS NVARCHAR(2)) AS CHAR(12)) AS ',
PARENT AS ',
PARTNO AS ',
M,
CASE WHEN P='1' THEN '-'
WHEN P='2' THEN '+'
WHEN P='3' THEN '*'
ELSE '.'
END AS P,
PARTDESC AS ',
UNIT AS ',
ROUND(USAGE,2) AS ',
SXRQ AS ',
SSRQ AS ',
LOCATION AS '
FROM #TEMP_A
DROP TABLE #TEMP_A
SET NOCOUNT OFF
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121909782.095848.154870@.g14g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for help.
>|||Did you bother to follow up my posting? TREES & HIERARCIES IN SQL
might be good read before you post again .sql