Determining Security: customer

Showing posts with label customer. Show all posts

Tuesday, March 27, 2012

Determine next order id

I'm working on a sproc that determines the next order id for a specified customer. The table has

custid int,

ordernum varchar(10)

Data is:

1000, 1000-001

1000, 1000-002

1001, 1001-001

1000, 1000-003

I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).

I honestly don't know where to begin.

Can someone please help?

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(OderNumber,3) + 1 AS VARCHAR(6)),3)
FROm Orders
Order by OrderNumer
Where CustId = @.CustId

SELECT @.LASTOrdernNum = ISNULL(@.LASTOrdernNum,'001')

SELECT CAST(CustId as VARCHAR(4)) + '-' + LASTOrdernNum

Should b something like the above (untested).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

That was very close. Thanks very much.

Is there a way to return a string value? I won't be calling this SP with ExecuteScalar, so I'd like to RETURN the value instead of just selecting it.

Here's the working version:

Code Snippet

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

I can use RETURN on the last line instead of select, but my test code errors with:

Conversion failed when converting the varchar value '10052-002' to data type int.

|||

RETURN allows for only an integer operand, which is generally used as a status or return code.

You may want to consider creating a function rather than a procedure.

|||

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

@.retval varchar(255) output

)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT @.retval = CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

sql

Sunday, March 25, 2012

Determine closedate depending on a metatable and this closedate will be used in a query

Hi, This is a diffcult issue to explain. I hope to make my problem
clear to you.

SITUATION
I'm building A SLA Query for a customer. This customer has an awkward
way to determine the SLA results ;-) Depending on a category which is
stored in a headertable (Requests) a field and logic is determined how
to get a proper Close_Date. This Close_date can be the closedate of
the request. It is also possible that the close_date is a certain
detail record (Request_lines). Also It is possible that this
close_date is the ordered_date of a certain line_item.

DONE SO FAR
I have created a metatable with rules per category. With this rule i
would like to determine a close_date. This close_date will be used as
parameter in a function from which i determine the total minutes how
long this request has endured.

GOAL
I want to create something like this:
SELECT id, getdiffSLA(@.StartDate, GetCloseDate(some parameters))
FROM...

I've created the function 'GetCloseDate()' in which i want to
determine the closedate. In this function i want to determine the
rule. Execute the proper logic and get the date.

This is what i'm trying to do in a function (this is one rule of many)
:

.....
ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
.....
.....
SET @.vcSQL = N' SELECT @.max = MAX(Close_date)
FROM samis.dbo.SAD_A_REQUEST_LINES
WHERE Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'

Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT

SET @.dCloseDate = @.max

ELSE IF...
....

THE PROBLEM
This doesn't work and yeah i know i can't use exec /sp_executesql in a
function. But if i try this in a stored procedure i can't use this in
a Query.
SELECT id, storedprocedure FROM ... Doesn't work, also.

An option is that i could create a cursor, loop every record and call
the stored procedure, get the close_date, execute my SLA calculation
function, store the result in a temptable, use this temptable in the
query <pffff>.

But the table consists of 200000 records (with a detailtable) and
performance is a issue. It's used for loading a datawarehouse and not
in a OLTP system so a bit slow performance is allowed but not to much.

SO how do i do this without using a cursor?

Greetz

Hennie"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0407062303.44ff9664@.posting.google.c om...
> Hi, This is a diffcult issue to explain. I hope to make my problem
> clear to you.
> SITUATION
> I'm building A SLA Query for a customer. This customer has an awkward
> way to determine the SLA results ;-) Depending on a category which is
> stored in a headertable (Requests) a field and logic is determined how
> to get a proper Close_Date. This Close_date can be the closedate of
> the request. It is also possible that the close_date is a certain
> detail record (Request_lines). Also It is possible that this
> close_date is the ordered_date of a certain line_item.
> DONE SO FAR
> I have created a metatable with rules per category. With this rule i
> would like to determine a close_date. This close_date will be used as
> parameter in a function from which i determine the total minutes how
> long this request has endured.
> GOAL
> I want to create something like this:
> SELECT id, getdiffSLA(@.StartDate, GetCloseDate(some parameters))
> FROM...
> I've created the function 'GetCloseDate()' in which i want to
> determine the closedate. In this function i want to determine the
> rule. Execute the proper logic and get the date.
> This is what i'm trying to do in a function (this is one rule of many)
> :
> ....
> ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
> BEGIN
> .....
> .....
> SET @.vcSQL = N' SELECT @.max = MAX(Close_date)
> FROM samis.dbo.SAD_A_REQUEST_LINES
> WHERE Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
> @.vcIN_String + ')'
> Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT
> SET @.dCloseDate = @.max
> ELSE IF...
> ...
> THE PROBLEM
> This doesn't work and yeah i know i can't use exec /sp_executesql in a
> function. But if i try this in a stored procedure i can't use this in
> a Query.
> SELECT id, storedprocedure FROM ... Doesn't work, also.
> An option is that i could create a cursor, loop every record and call
> the stored procedure, get the close_date, execute my SLA calculation
> function, store the result in a temptable, use this temptable in the
> query <pffff>.
> But the table consists of 200000 records (with a detailtable) and
> performance is a issue. It's used for loading a datawarehouse and not
> in a OLTP system so a bit slow performance is allowed but not to much.
> SO how do i do this without using a cursor?
> Greetz
> Hennie

I don't really follow your description completely, but it seems that one key
issue is working with delimited strings, so you may want to look at this
article to see how to avoid using dynamic SQL:

http://www.sommarskog.se/arrays-in-sql.html

That might help you to create your function, but if not then you should
consider posting some more detailed information - the CREATE TABLE
statements for the tables, INSERTs of some sample data, and what you expect
to be returned for each category. That's usually clearer than a description,
and someone may be able to give more specific help.

Simon|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

>> Depending on a category which is stored in a header table (Requests)
a field [sic] and logic is determined how
to get a proper Close_Date. This Close_date can be the closedate of the
request. It is also possible that the close_date is a certain detail
record [sic] (Request_lines). Also It is possible that this close_date
is the ordered_date of a certain line_item. <<

Rows are not records; fields are not columns; tables are not files. I
also hope that those "vc_" prefixes did not mean "VARCHAR(n)" in
violation of ISO-11179 rules. It woiuld look like you are still writing
BASIC and procedural code, not SQL. No wonder you are thinking about
dynamic SQL kludgers and cursors.

>> I have created a metatable with rules per category. <<

I am not sure what that means. A decision table in SQL, perhaps?

>> So how do I do this without using a cursor? <<

My first thought is to write something like this:

UPDATE Requests
SET close_date
= CASE category
WHEN 1 THEN <<close_date of certain detail>>
WHEN 2 THEN <<ordered_date of certain detail>>
ELSE close_date END; -- do nothing

Without better specs, this is about as far as I can get. Each category
would lead to a scalar query expression that finds the desired data
value, maybe something like this:

CASE category
...
WHEN 2
THEN (SELECT MAX(close_date)
FROM SadRequestLines AS S1
WHERE S1.parent_quote = Requests.numberprgn
AND S1.part_no
IN (SELECT part_no FROM PartsCategory_2))

The important point is to avoid all procedural code, dynamic SQL and
cursors. If you use the CASE expression, you can move all the logic
into a single, optimizable statement. My guess would be that it should
be about 10x faster than your procedural approach.

If the conditions are really tricky, then I would consider a decision
table tool to generate the WHEN predicates. LOok up Logic Gem as an
example of such a thing.

--CELKO--

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pfff what a lot of answers and questions again. OK i'll drop my query
stuff:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[XXX_FN_GetCloseDate]') and xtype in (N'FN', N'IF',
N'TF'))
drop function [dbo].[XXX_FN_GetCloseDate]
GO

CREATE FUNCTION XXX_FN_GetCloseDate(@.vcCategory as Varchar(50),
@.vcNumberPRGN as varchar(50)) RETURNS varchar(8000)
WITH ENCRYPTION
AS
DECLARE @.vcField Varchar(50)
DECLARE @.vcPartString Varchar(8000)
DECLARE @.iRuleNoVarchar(8000)
DECLARE @.vcsqlVarchar(8000)
DECLARE @.vcTempStringVarchar(8000)
DECLARE @.vcPartNovarchar(50)
DECLARE @.vcIN_StringVarchar(8000)
DECLARE @.dCloseDateDatetime
DECLARE @.maxDatetime

-- Intitialisation
SET @.vcIN_String = ''

DECLARE FieldsCur CURSOR
FOR
SELECT MET_Field, MET_Partno, Met_RuleNo
FROM xxxxx_control.dbo.xxxxx_PWC_SLA_Metrics
WHERE MET_Code = @.vcCategory

OPEN FieldsCur
FETCH FROM FieldsCur INTO @.vcField, @.vcPartString, @.iRuleNo

IF @.@.FETCH_STATUS = 0
BEGIN
IF @.iRuleNo = 1 --SAD_A_REQUESTS.CLOSE_DATE
BEGIN
SET @.vcSQL = 'SELECT '''+ @.vcCategory + ''',''' +
@.vcNumberPRGN + ''',' + 'Close_Date FROM xxxxxx.dbo.SAD_A_REQUESTS
WHERE NumberPRGN = ' + @.vcNumberPRGN
END
ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
-- Extract the partnos from the MET_Field.
SET @.vctempString = LTRIM(RTRIM(@.vcPartString))
WHILE Len(@.vctempString) <> 0
BEGIN
SET @.vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@.vctempString)
SET @.vcPartNo = LTRIM(RTRIM(@.vcPartString))
IF CHARINDEX(',', @.vctempString)<> 0
SET @.vcTempString = SubString(@.vctempString, CHARINDEX(',',
@.vctempString)+1, Len(@.vctempString) )
ELSE
SET @.vcTempString = ''
-- This string is created voor de IN in the Query.
IF Len(@.vctempString) <> 0 -- comma is needed
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + '''' + ','ELSE
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + ''''

END
SET @.vcSQL = 'SELECT '''+ @.vcCategory + ''',''' + @.vcNumberPRGN + ''','
+ 'MAX(Close_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'

CREATE TABLE #CloseDate (CloseDate DateTime)
INSERT INTO CloseDate EXEC ('SELECT MAX(Close_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @.vcNumberPRGN
+ ''' AND Part_No In ('+ @.vcIN_String + ')')
SET @.dCloseDate = (SELECT CloseDate FROM #CloseDate)
DROP TABLE #CloseDate
END
ELSE IF @.iRuleNo = 3
BEGIN
-- Extract the partnos from the MET_Field.
SET @.vctempString = LTRIM(RTRIM(@.vcPartString))
WHILE Len(@.vctempString) <> 0
BEGIN
SET @.vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@.vctempString)
SET @.vcPartNo = LTRIM(RTRIM(@.vcPartString))

IF CHARINDEX(',', @.vctempString)<> 0
SET @.vcTempString = SubString(@.vctempString, CHARINDEX(',',
@.vctempString)+1, Len(@.vctempString) )
ELSE
SET @.vcTempString = ''

-- This string is created voor de IN in the Query.
IF Len(@.vctempString) <> 0 -- comma is needed
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + '''' + ','
ELSE
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + ''''

--Print '@.vcIN_String : ' + @.vcIN_String

END
--CREATE TABLE #OrderedDate (OrderedDate DateTime)
--INSERT INTO #OrderedDate
SET @.vcSQL = 'SELECT '+ @.vcCategory + ',' + @.vcNumberPRGN + ',' +
'MAX(Ordered_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'
-- SET @.vcSQL = 'SELECT @.max = MAX(Ordered_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @.vcNumberPRGN
+ ''' AND Part_No In ('+ @.vcIN_String + ')'

Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT
SET @.dCloseDate = @.max
SET @.dCloseDate = (SELECT OrderedDate FROM #OrderedDate)
DROP TABLE #OrderedDate
END
--ELSE IF @.iRuleNo = 4
--BEGIN

--END

--Print CAST(@.dCloseDate as varchar)

END

CLOSE FieldsCur
DEALLOCATE FieldsCur

--RETURN @.dCloseDate
RETURN @.vcSQL
END

----------------

I've edited it quite a lot so i hope it's readable...

That i was creating it as a procedural program has been an idea of
myself too. So i tried to get the logic in a query: So tried this:

SELECT
A.NUMBERPRGN,
A.SubCategory,
C.MET_Field,
C.MET_Partno,
C.Met_RuleNo,
CASE
WHEN Met_RuleNo = 1 THEN
(SELECT cast(Close_Date as varchar) FROM xxxxx.dbo.SAD_A_REQUESTS WHERE
NumberPRGN = A.NumberPRGN)

WHEN Met_RuleNo = 2 THEN
(SELECT cast(MAX(Close_date) as varchar)
FROM xxxxx.dbo.SAD_A_REQUEST_LINES B WHERE B.Parent_Quote = A.NumberPRGN
AND B.Part_No In (SELECT PartNoFROM
xxxxxx_Control.dbo.xxxxx_FN_Convert_From_CSVTOTabl e(C.MET_Partno)))

WHEN Met_RuleNo = 3 THEN '3' ELSE '999999999999' END
FROM xxxxxx.dbo.SAD_A_Requests A
LEFT OUTER JOIN xxxxxx._Control.dbo.xxxxxx_PWC_SLA_Metrics C ON
A.SubCategory = C.MET_Code

I'm almost there where i want because When i replace C.MET_Partno in the
inline function (returns a table) with a string like
'COM_06,ATC_04,ANC_03,ALC_03,AAC_04,ASC_02,APC_05' it's working but i I
got the following error:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '.'.

Research teached me that Inline functions doesn't allow fields as
parameter only variables or constants. So now i'm right at the start and
running out of ideas. This problem is driving me nuts (Aargh)..

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> I'm building A SLA Query for a customer. This customer has an awkward
> way to determine the SLA results ;-)

I know what a TLA is, but what is an SLA?

> An option is that i could create a cursor, loop every record and call
> the stored procedure, get the close_date, execute my SLA calculation
> function, store the result in a temptable, use this temptable in the
> query <pffff>.
> But the table consists of 200000 records (with a detailtable) and
> performance is a issue. It's used for loading a datawarehouse and not
> in a OLTP system so a bit slow performance is allowed but not to much.

Then you consider this: if you say:

SELECT dbo.my_udf(col) FROM tbl

then your SELECT statement becomes a cursor behind the scenes. SQL Server
does not have any method to call you function for all rows at once. It
may be faster than a real cursor, but in comparison with UDF-less SELECT
statement the difference may be stunning.

For your actual problem there is too little information to suggest
something, and in any case, it may too complex to address completely in
a newsgroup posting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is the second time i enter a complete message and when i push
submit it is all gone. I hate this. Great. I forgot to click on the
radiobutton??!!! I will notice soon. Grrrrr. So my answer is shorter now
than i want.

So in short. SLA is SErvice line agreement and is an agreement between a
customer and a service deliverer.

I solved this issue by creating a temporary table and joining with this
table. Perhaps i didn't explain it to well or the problem was to
complicated. Thanx anyway...

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Saturday, February 25, 2012

Designing Reports for SQL Reporting Services 2000

My customer has SQL Reporting Services on a server with SQL 2000 Standard Edition. The customer wants to develop his own Reporting Services reports. We have been recommending Visual Basic.NET Standard 2003 for this purpose in that it costs less than $100 (usually). However, with the advent of Visual Studio 2005, the VB.NET 2003 is becoming difficult or impossible to obtain. One option is Visual C#.NET Standard 2003 - I assume it will work - does anyone know for sure?

More importantly, when Visual C#.NET 2003 becomes unvailable, what options are left for developing RS 2000 reports?

Thanks for any suggestions.

Mark

>More importantly, when Visual C#.NET 2003 becomes unvailable, what options are left for developing RS 2000 reports?

None from Microsoft. There are third-party solutions, e.g. Cizer.

|||

You could also write a small tool that performs a RDL structure "downgrade" conversion (see e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=988366&SiteID=1). However note that RS 2000 doesn't support a number of RS 2005 features (e.g. multi value parameters).

-- Robert

designing inheriting entities

Dear All,
our customer has 3 kinds of entites
the first one is the ApplicationCenter where students can register to
exams...
and there are types of app centers like University offices, high schools
etc.. these types goes to another table..
and finally each type has its own instances like University one ,
university2 , univ3 etc. which means there are several more tables Like
universities , highschools , examCenters etc...
the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
of the customer..
the listing of the centers and their info is straightforward however when it
is time to edit many entities needs to be updated at the same time
.........
i am trying to figure out a table design where i can build sort of
inheriting entities..
any clue?
best regards..
emre dincer
Emre DNER wrote:
> Dear All,
> our customer has 3 kinds of entites
> the first one is the ApplicationCenter where students can register to
> exams...
> and there are types of app centers like University offices, high schools
> etc.. these types goes to another table..
> and finally each type has its own instances like University one ,
> university2 , univ3 etc. which means there are several more tables Like
> universities , highschools , examCenters etc...
> the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
> of the customer..
> the listing of the centers and their info is straightforward however when it
> is time to edit many entities needs to be updated at the same time
> ........
> i am trying to figure out a table design where i can build sort of
> inheriting entities..
> any clue?
> best regards..
> emre dincer
It is possible to ensure that each common attribute appears only in
one place in the hierarchy for each type of entity. Therefore the
problem of updating the same attribute in multiple places won't arise.
Fifth Normal Form and the Principle of Orthogonal Design are two
principles that will help you achieve a good model. Google for them if
you aren't already familiar with them.
David Portas

designing inheriting entities

Dear All,
our customer has 3 kinds of entites
the first one is the ApplicationCenter where students can register to
exams...
and there are types of app centers like university offices, high schools
etc.. these types goes to another table..
and finally each type has its own instances like university one ,
university2 , univ3 etc. which means there are several more tables Like
universities , highschools , examCenters etc...
the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
of the customer..
the listing of the centers and their info is straightforward however when it
is time to edit many entities needs to be updated at the same time
........
i am trying to figure out a table design where i can build sort of
inheriting entities..
any clue?
best regards..
emre dincerEmre D=DDN=C7ER wrote:
> Dear All,
> our customer has 3 kinds of entites
> the first one is the ApplicationCenter where students can register to
> exams...
> and there are types of app centers like university offices, high schools
> etc.. these types goes to another table..
> and finally each type has its own instances like university one ,
> university2 , univ3 etc. which means there are several more tables Like
> universities , highschools , examCenters etc...
> the requirement is to have ADD/Edit/Delete screens of all ApplicationCente=[/vbcol
]
rs[vbcol=seagreen]
> of the customer..
> the listing of the centers and their info is straightforward however when =[/vbcol
]
it[vbcol=seagreen]
> is time to edit many entities needs to be updated at the same time
> ........
> i am trying to figure out a table design where i can build sort of
> inheriting entities..
> any clue?
> best regards..
> emre dincer
It is possible to ensure that each common attribute appears only in
one place in the hierarchy for each type of entity. Therefore the
problem of updating the same attribute in multiple places won't arise.
Fifth Normal Form and the Principle of Orthogonal Design are two
principles that will help you achieve a good model. Google for them if
you aren't already familiar with them.
David Portas

designing inheriting entities

Dear All,
our customer has 3 kinds of entites
the first one is the ApplicationCenter where students can register to
exams...
and there are types of app centers like University offices, high schools
etc.. these types goes to another table..
and finally each type has its own instances like University one ,
university2 , univ3 etc. which means there are several more tables Like
universities , highschools , examCenters etc...
the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
of the customer..
the listing of the centers and their info is straightforward however when it
is time to edit many entities needs to be updated at the same time
........
i am trying to figure out a table design where i can build sort of
inheriting entities..
any clue?
best regards..
emre dincerEmre D=DDN=C7ER wrote:
> Dear All,
> our customer has 3 kinds of entites
> the first one is the ApplicationCenter where students can register to
> exams...
> and there are types of app centers like University offices, high schools
> etc.. these types goes to another table..
> and finally each type has its own instances like University one ,
> university2 , univ3 etc. which means there are several more tables Like
> universities , highschools , examCenters etc...
> the requirement is to have ADD/Edit/Delete screens of all ApplicationCente=rs
> of the customer..
> the listing of the centers and their info is straightforward however when =it
> is time to edit many entities needs to be updated at the same time
> ........
> i am trying to figure out a table design where i can build sort of
> inheriting entities..
> any clue?
> best regards..
> emre dincer
It is possible to ensure that each common attribute appears only in
one place in the hierarchy for each type of entity. Therefore the
problem of updating the same attribute in multiple places won't arise.
Fifth Normal Form and the Principle of Orthogonal Design are two
principles that will help you achieve a good model. Google for them if
you aren't already familiar with them.
--
David Portas

Designing a fact table to hold customer-product ownership by day

If I'm designing a fact table to hold customer-product ownership by day, can one somehow get by with just storing customer purchase dates and return dates, rather than a record for each day the customer owns a product?

I need to make sure that if a customer bought a product in Jan and returned it 1 month later, but then bought it again in April - that they show up as having that item in
Jan and April - to date (but excluding Feb, March)

One thing that will be tough is the potential for this to happen several times with the same customer, but still showing all gaps of not having product. If a date dimension is not used, the I'd really like to take the customer's current status for a product - is it possible to do this?

Also, another question, speaking of customer counts and rollup
once it's determined that a customer has a certain product on x day (count of 1) and doesn't have a product on a different day (count of -1),
how would one determine that for the level above product, -subcategory-, that there were 5 customers that had Accessories?

I see that adventure works uses distinct count to make up customer count, but what about when returns are entered into the picture?
I'm thinking of doing them as -1, but maybe there's a better way to handle them and ultimately, the 'distinct customer count' rollup to higher levels?

I realize this post contains a lot of questions - if I can get at least one answered for starters, that would be awesome.

Hi,

if you contact me by mail and send me more detail and if it's possible some data I can try to figure out a possible solution. (If you can wait few days)

francesco.dechirico(AT)fastwebnet.it

|||

Still looking into this problem - months later

With a fact table like:

Customer Product Date Qty

1 1 1/1/2005 1

1 1 3/2/2005 -1

1 2 4/1/2005 1

I'd like to have a query show that a customer is active on dates 1/1/2005-3/1/2005, and from 4/1/2005 on... this should also be done by distinct count of customer ID, but down to product if possible.

Here is an example mdx statement I composed, but the results aren't exactly what I'm seeking. Yes, it adds up Invoice Qty, but I was hoping to 'create' the date rows in between.

with member x

as DistinctCount(sum({null:[Transaction Date].[Calendar Date].CurrentMember}, [Invoice Qty]), Customer.Customer.[Customer ID].CurrentMember)

member y as [Transaction Date].[Calendar Date].CurrentMember.UniqueName

select {[invoice sales],[Measures].[Invoice Qty], x, y} on 0,

[Transaction Date].[Calendar Month].[Calendar Month] on 1

from sales

where (customer.customer.&[1])

|||

Any ideas on this one?

Mainly, I'm looking for something to create the non existant date rows in the output. Whether it be a script SCOPE or calculated member..

|||

Ok, I got a lot further after reading the ideas in this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1205862&SiteID=1

I've defined a measure to be Cumulative Qty Shipped - summing up all shipped qty to the day in question.

Then another measure (CustomerCount) that does: iif([Cumulative Qty Shipped] > 0, 1, NULL) -- in order to make sure that a customer only gets a tally of 1, not any higher.

Now, what I'm concerned about is the bleeding that happens. ie:

If looking at 2 customers, the execution path of AS is to sum all qty shipped to the current date section, then evaluate the logic: iif([Cumulative Qty Shipped] > 0, 1, NULL)

This would also happen if looking at 1 customer and multiple products. All of the products' qty would be summed, then evaluated for > 0..

Is there a way to sort of turn this around so that the calculation is first done on strictly customer and product, then doing the iif([Cumulative Qty Shipped] > 0, 1, NULL) check?

Plus another behavior needed is 'distinct' customer count. If looking at the all products level, and a customer owns 3 products, they should still only be counted once. If looking the members of Products, then the customer could be tallied once per product.

|||

I tried another script scope, hoping that it would work to cover the needs discussed, but it did not.

This is what I'd used. Hopefully it will spark some ideas from others! Smile

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

this = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

ENDSCOPE;

and differently:

SCOPE(Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

Measures.CustomerCount = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

ENDSCOPE;

|||

I hate to be responding so many times in a row, but I'm looking for some direction on this one.

Thank you!!