How to Determine Data Source Associated with Report

Within report design mode, I can see the DataSet, but I can't tell if this
was created using a Shared Data Source or was it created before I created
and started using a Shared Data Source. I have a problem publishing reports
that used the Shared Data Source. Now I want to check and see how I
originally set the DataSet -- to use the Shared Data Source or not. Any way
to see that?
Thanks,
Boolean1On Mar 1, 1:24 pm, "Boolean1" <Boole...@.comcast.net> wrote:
> Within report design mode, I can see the DataSet, but I can't tell if this
> was created using a Shared Data Source or was it created before I created
> and started using a Shared Data Source. I have a problem publishing reports
> that used the Shared Data Source. Now I want to check and see how I
> originally set the DataSet -- to use the Shared Data Source or not. Any way
> to see that?
> Thanks,
> Boolean1
In the 'Data' tab select the datasets for the report in question and
select the Edit Selected Dataset button [...]. On the 'Query' tab,
below Datasource: is the datasource for the dataset. Select the [...]
button to the right. Near the bottom of the General tab look to see if
'Use shared datasource reference' is checked. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

how to design queries graphically?

Are there anytool, which would allow me to design queries graphically, if not design then atlease analyse them graphically.

Tool should atleast show join of 4tables graphically.

Thank You.

SQL Server Management Studio has graphical capabilities - launched in one of two ways:

1.Create a 'New Query' then right-click on the query pane and select 'Design Query in Editor...'.

2.Connect to a server in Object Explorer then navigate to the following node:

Server -> Databases -> <Your database> -> Views

Right-click on 'Views' and select 'New View...' to launch the query editor.

Chris

|||

Try this......

And it's a single query, runs at a single go. I would like to know any s/w / utility (can be third party) which could decode following query.

SELECT '' AS TranNo, 'SC' AS JournalCode, cSupplier AS SupplierCode, cOrderNo AS OrderNo, cWareHouse AS Warehouse, nGrnNo AS GRNNo,

dReceiptDate AS BookEntryDate, vDefaultGLAC AS AccString, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN nTrnsQty * nUnitPrice / [nReceiptsCurrConRate] ELSE nTrnsQty * nUnitPrice * [nReceiptsCurrConRate] END)) AS FOB, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN LandedCost / [nReceiptsCurrConRate] ELSE LandedCost * [nReceiptsCurrConRate] END))

AS LandedCost, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc1)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nUnitPrice * nLineDisc1 / 100)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount1, CONVERT(NUMERIC(18,

2), SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc2)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc2) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)

/ [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100) * [nReceiptsCurrConRate] END)

END)) AS Discount2, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc3)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc3) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice)

- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)) * nLineDisc3 / 100) / [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice)

- (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))

* nLineDisc3 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount3

FROM (SELECT PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,

PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,

PMPT.dReceiptDate, 112) AS dReceiptDate, ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,

PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,

cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3, SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL

THEN 0 ELSE PMGRNLLC.nBElementalCost END) AS LandedCost

FROM PMPT LEFT OUTER JOIN

PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode AND

PMPT.cSupplier = PMGRNLLC.cSupplierCode AND

PMPT.cOrderNo = PMGRNLLC.cOrderNo AND

PMPT.nLineNo = PMGRNLLC.nLineNo AND

PMPT.cItemCode = PMGRNLLC.cItemCode AND

PMPT.cWareHouse = PMGRNLLC.cWareHouse AND

PMPT.nGrnNo = PMGRNLLC.nGrnNo LEFT OUTER JOIN

ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode LEFT OUTER JOIN

OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',

'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs') AS B ON

PMPT.cCompanyNo = B.CompanyCode AND RTRIM(PMPT.cSupplier) = B.SupplierCode AND

PMPT.cOrderNo = B.OrderNo AND PMPT.cWareHouse = B.WareHouse AND

PMPT.nGrnNo = B.GRNNo

WHERE (PMPT.cCompanyNo = 'GT') AND (PMPT.nGrnNo > 0) AND (PMPT.nTrnsQty > 0) AND

(B.SupplierCode IS NULL)

GROUP BY PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,

PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,

PMPT.dReceiptDate, 112), ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,

PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,

cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3) AS A

WHERE (dReceiptDate >= '20060401') AND (dReceiptDate <= '20070331')

GROUP BY nGrnNo, dReceiptDate, cSupplier, cOrderNo, cWareHouse, nGrnNo, vDefaultGLAC

ORDER BY GRNNo

|||

Although the query looks scary it actually isn't as complex as it looks at first sight, however I've put the query through a 3rd-party refactoring tool for clarity, see below.

It depends what you mean by 'decode'? What are you trying to ascertain by studying a diagram that studying the query wouldn't reveal?

Chris

Code Snippet

SELECT '' AS TranNo,
'SC' AS JournalCode,
cSupplier AS SupplierCode,
cOrderNo AS OrderNo,
cWareHouse AS Warehouse,
nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate,
vDefaultGLAC AS AccString,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN nTrnsQty * nUnitPrice
/ [nReceiptsCurrConRate]
ELSE nTrnsQty * nUnitPrice
* [nReceiptsCurrConRate]
END)) AS FOB,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN LandedCost
/ [nReceiptsCurrConRate]
ELSE LandedCost
* [nReceiptsCurrConRate]
END)) AS LandedCost,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc1)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc1)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount1,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc2)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc2)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount2,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc3)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc3)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount3
FROM (
SELECT PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112) AS dReceiptDate,
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3,
SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL THEN 0
ELSE PMGRNLLC.nBElementalCost
END) AS LandedCost
FROM PMPT
LEFT OUTER JOIN PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode
AND PMPT.cSupplier = PMGRNLLC.cSupplierCode
AND PMPT.cOrderNo = PMGRNLLC.cOrderNo
AND PMPT.nLineNo = PMGRNLLC.nLineNo
AND PMPT.cItemCode = PMGRNLLC.cItemCode
AND PMPT.cWareHouse = PMGRNLLC.cWareHouse
AND PMPT.nGrnNo = PMGRNLLC.nGrnNo
LEFT OUTER JOIN ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode
LEFT OUTER JOIN OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs')
AS B ON PMPT.cCompanyNo = B.CompanyCode
AND RTRIM(PMPT.cSupplier) = B.SupplierCode
AND PMPT.cOrderNo = B.OrderNo
AND PMPT.cWareHouse = B.WareHouse
AND PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT')
AND (PMPT.nGrnNo > 0)
AND (PMPT.nTrnsQty > 0)
AND (B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112),
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3
) AS A
WHERE (dReceiptDate >= '20060401')
AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo,
dReceiptDate,
cSupplier,
cOrderNo,
cWareHouse,
nGrnNo,
vDefaultGLAC
ORDER BY GRNNo

|||

3rd-party refactoring tool is what i'm looking for, it would be much more better if it does graphical representation.

Can you give me any names?

|||

This is a great product for laying out (refactoring) SQL:

http://www.red-gate.com/products/SQL_Refactor/index.htm

However it can't create graphical representations of queries.

To be honest I'm not sure you'd gain a lot from displaying that particular query graphically - in fact I reckon that doing so, and making subsequent modifications, could lead to errors due to the query's complexity. IMO in this case it would be far better to 'bite the bullet' and try to interpret and understand the query text.

Chris

How to design this?

I know this group is about programming, and there is no other group (or
is there?) about database design. So I'll just ask it here.
A product can be sourced from multiple suppliers (worldwide). So I came
up with the tables below.
CREATE TABLE Product (
ProductCode NVARCHAR(10) NOT NULL PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Supplier (
SupplierCode NVARCHAR(10) NOT NULL PRIMARY KEY,
SupplierName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE ProductSupplier (
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
..,
PRIMARY KEY (ProductCode, SupplierCode)
)
Now, for a product that is source from a supplier, I have multiple
prices, depending on the quantity I ordered. How would I model this? Is
the following correct?
CREATE TABLE ProductSupplierPrice (
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
Price MONEY NOT NULL,
Qty BIGINT NOT NULL,
PRIMARY KEY (ProductCode, SupplierCode)
)
Or, should I just have an identity column in ProductSupplier, and then
references it from ProductSupplierPrice? Like this:
CREATE TABLE ProductSupplier (
ProductSupplierId INT NOT NULL PRIMARY,
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
..,
? UNIQUE INDEX (ProductCode, SupplierCode)
)
CREATE TABLE ProductSupplierPrice (
ProductSupplierPriceId INT NOT NULL PRIMARY,
ProductSupplierId INT NOT NULL
REFERENCES ProductSupplier(ProductSupplierId),
Price MONEY NOT NULL,
Qty BIGINT NOT NULL,
PRIMARY KEY (ProductCode, SupplierCode)
)> CREATE TABLE ProductSupplierPrice (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
is incorrect

> CREATE TABLE ProductSupplierPrice (
> ProductSupplierPriceId INT NOT NULL PRIMARY,
> ProductSupplierId INT NOT NULL
> REFERENCES ProductSupplier(ProductSupplierId),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
is incorrect too because (as you've mentioned above) there are (possible)
several prices for each product from each supplier depending on quantity.
So, Qty column should also be a part of the primary key of
ProductSupplierPrice table. But you may add surrogate key column to this
table (not null'ed Identity with Unique index on it) for use in FK
constraints, if needed.
WBR, Evergray
--
Words mean nothing...
"Michael Wong" <nospam@.email.here> wrote in message
news:%23uJb5FzQGHA.5152@.TK2MSFTNGP10.phx.gbl...
>I know this group is about programming, and there is no other group (or is
>there?) about database design. So I'll just ask it here.
> A product can be sourced from multiple suppliers (worldwide). So I came up
> with the tables below.
> CREATE TABLE Product (
> ProductCode NVARCHAR(10) NOT NULL PRIMARY KEY,
> ProductName NVARCHAR(50) NOT NULL,
> ...
> )
> CREATE TABLE Supplier (
> SupplierCode NVARCHAR(10) NOT NULL PRIMARY KEY,
> SupplierName NVARCHAR(50) NOT NULL,
> ...
> )
> CREATE TABLE ProductSupplier (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> ...,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
> Now, for a product that is source from a supplier, I have multiple prices,
> depending on the quantity I ordered. How would I model this? Is the
> following correct?
> CREATE TABLE ProductSupplierPrice (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
> Or, should I just have an identity column in ProductSupplier, and then
> references it from ProductSupplierPrice? Like this:
> CREATE TABLE ProductSupplier (
> ProductSupplierId INT NOT NULL PRIMARY,
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> ...,
> ? UNIQUE INDEX (ProductCode, SupplierCode)
> )
> CREATE TABLE ProductSupplierPrice (
> ProductSupplierPriceId INT NOT NULL PRIMARY,
> ProductSupplierId INT NOT NULL
> REFERENCES ProductSupplier(ProductSupplierId),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )|||Thanks for the hint.
Evergray wrote:
>
> is incorrect
>
>
> is incorrect too because (as you've mentioned above) there are (possible)
> several prices for each product from each supplier depending on quantity.
> So, Qty column should also be a part of the primary key of
> ProductSupplierPrice table. But you may add surrogate key column to this
> table (not null'ed Identity with Unique index on it) for use in FK
> constraints, if needed.
>|||>> I have multiple prices, depending on the quantity I ordered. How would I
model this? <<
I know it is an example, but you might want to use reasonable data
types. Do you really expect a BIGINT quantity?
CREATE TABLE ProductQuantiyBreaks
(product_code CHAR(13) NOT NULL -- upc codes?
REFERENCES Products (product_code)
ON DELETE CASCADE
ON UPDATE CASCADE,
,supplier_id CHAR(10) NOT NULL --duns number
REFERENCES Suppliers(supplier_id),
ON DELETE CASCADE
ON UPDATE CASCADE,
low_qty INTEGER NOT NULL
CHECK(low_qty > 0),
high_qty INTEGER NOT NULL,
CHECK(low_qty < high_qty),
unit_price DECIMAL (12,5) NOT NULL,
PRIMARY KEY (product_code, supplier_id, low_qty));
Now fill in the discounts by (low_qty, high_qty) ranges so you can use
a BETWEEN predicate to locate the price of one unit. Never use
IDENTITY, since it is non-relational; never use MONEY since it is
proprietary and has weird math results.|||Yeah, that's definitely easier when querying with the low_qty and high_qty.
Just one question, having
PRIMARY KEY (product_code, supplier_id, low_qty), does that mean the
table ProductQuantiyBreaks is independent of table ProductSupplier?
Sorry to ask this, but I'm still new to these composite keys.
Thanks
--CELKO-- wrote:
>
> I know it is an example, but you might want to use reasonable data
> types. Do you really expect a BIGINT quantity?
> CREATE TABLE ProductQuantiyBreaks
> (product_code CHAR(13) NOT NULL -- upc codes?
> REFERENCES Products (product_code)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ,supplier_id CHAR(10) NOT NULL --duns number
> REFERENCES Suppliers(supplier_id),
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> low_qty INTEGER NOT NULL
> CHECK(low_qty > 0),
> high_qty INTEGER NOT NULL,
> CHECK(low_qty < high_qty),
> unit_price DECIMAL (12,5) NOT NULL,
> PRIMARY KEY (product_code, supplier_id, low_qty));
> Now fill in the discounts by (low_qty, high_qty) ranges so you can use
> a BETWEEN predicate to locate the price of one unit. Never use
> IDENTITY, since it is non-relational; never use MONEY since it is
> proprietary and has weird math results.
>|||having PRIMARY KEY (product_code, supplier_id, low_qty), does that
mean the table ProductQuantiyBreaks is independent of table
ProductSupplier? <<
It means that price breaks are VERY dependent on suppliers. In fact
that important; you need to pick the cheapest guy
product_code supplier_id low_qty high_qty unit_price
========================================
================
1234567890123 1111122222 1 10 10.00
1234567890123 3333344444 1 5 10.00
1234567890123 3333344444 6 10 9.50
Both suppliers are the same up to quantity 5, so I would need to make a
decision. After that, go with 3333344444.|||Ok, I got it to work with:
REFERENCES ProductSupplier(Productcode, SupplierCode)
--CELKO-- wrote:
> having PRIMARY KEY (product_code, supplier_id, low_qty), does that
> mean the table ProductQuantiyBreaks is independent of table
> ProductSupplier? <<
> It means that price breaks are VERY dependent on suppliers. In fact
> that important; you need to pick the cheapest guy
> product_code supplier_id low_qty high_qty unit_price
> ========================================
================
> 1234567890123 1111122222 1 10 10.00
> 1234567890123 3333344444 1 5 10.00
> 1234567890123 3333344444 6 10 9.50
> Both suppliers are the same up to quantity 5, so I would need to make a
> decision. After that, go with 3333344444.
>

How to design this cube?

Hi,

I have a question on designing the fact table. There are 2 tables in my database: OrderHeader,OrderDetail. And the OrderDetail table has different lines for different product.

If the OrderDetail table is the fact table, how can I get the measure on how many orders we have? I cannot simply use count(orderno) as the measure because the field,orderno, is duplicate in the OrderDetail table, and analysis services don't support "count distinct" for the measure. Or any ideas for redesigning this cube?

Thank you so much!

Why do you say that analysis services doesn't support "count distinct" for the measure - DistinctCount measure aggregation function exists in both AS 2000 and 2005:

http://msdn2.microsoft.com/en-us/library/ms175623(SQL.90).aspx#AggFunction

>>

Aggregation function Additivity Returned value

Sum

Additive

Calculates the sum of values for all child members. This is the default aggregation function.

Count

Semiadditive

Retrieves the count of all child members.

Min

Semiadditive

Retrieves the lowest value for all child members.

Max

Semiadditive

Retrieves the highest value for all child members.

DistinctCount

Nonadditive

Retrieves the count of all unique child members.

>>

|||

Really? But in my AS 2000(SQL Server 2000 standard version), the Aggregate Function in Measure's properties only has 4 options: sum, count, min, max.

How can I add DistinctCount in it?

Thanks.

|||

Well, I've seldom used AS 2000 Standard Edition, so I can't say from first-hand knowledge; but BOL doesn't mention Distinct Count as an Enterprise Edition only feature. In any case, your best bet is to upgrade to AS 2005, since there are some limitations with the DistinctCount aggregation in AS 2000.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1cdv.asp

>>

Features Supported by the Editions of SQL Server 2000

This topic summarizes the features that the different editions of Microsoft? SQL Server? 2000 support.

>>

|||

Thank you, Deepak, I appreciate your kindly reply.

In "Calculated Members", I designed a measure using DistinctCount({[OrderNo]}). But the result is not I want. Any ideas?

Deepak Puri wrote:

Well, I've seldom used AS 2000 Standard Edition, so I can't say from first-hand knowledge; but BOL doesn't mention Distinct Count as an Enterprise Edition only feature. In any case, your best bet is to upgrade to AS 2005, since there are some limitations with the DistinctCount aggregation in AS 2000.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1cdv.asp

>>

Features Supported by the Editions of SQL Server 2000

This topic summarizes the features that the different editions of Microsoft? SQL Server? 2000 support.

>>

|||Since you're using AS 2000, try: DistinctCount([OrderNo].Members)|||Thank you Deepak. You are the man!

How to design the history table to be more efficient?

I am running a website of crossword puzzle and Sudoku games. The website is designed to be:

There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.

The current solution is:
3 tables are designed for the functions mentioned above.

Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied

PS: Fields listed above are only related ones, not the complete structure.

Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.

When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.

When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.

Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.

Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?what the? "loading a table" won't you just be searching the table?

What size do you expect this table to grow to?|||sorry, what i said "loading the table" means to do the query via a sql statement.

what i am worry about is the table will become bigger and bigger with more and more games online. Although i have added index to fields GameID and UserId in Table C, but i still think the efficiency will decrease with more lines inserted into the table.

Currently i have 2000 around games, each game is played 40 times for average. this is a big number compared with the total number of games.

I am wondering if there is another way to design the structure to avoid this.

my friend suggest me to add an extra field in Table B, holding all the finished userID, which likes: "User001|User005|User007"

And when a game is loaded by a user, the program could match the user's id with this field to find whether the user has played this game before.

But I am afraid this will need a big field to hold all the possible userIDs. say I have 10000 users, and the length of a unique user ID is 6 chars, so this field should be designed to be able to hold (6+1)*10000=60000, which is quite huge, right?|||Huj

you seem to have chosen the correct approach - a classic instancing table - I would strongly recommend you don't use the flat earth approach suggested by your friend.

It does'nt look like you've reached any performance problems as yet but if you do I would primarily be looking at :-

Finish History table with Clustered composite primary Key.
Potential archiving of old data in this table
Using Ints for ID's (if your not already)
ensure instancing table (FinishHistory) only holds primary keys (ie smallest overall record length)

Should run like a rocket

GW|||my friend suggest me to add an extra field in Table B, holding all the finished userID, which likes: "User001|User005|User007"

Some freind...what a nigghtmare that would be...if you want, add a child table that stores that data...in rows|||hi all, thanks all for your reply.

Brett Kaiser, if i add a child table store that data in rows, it is actual an alternative way of TableC, which is my headache: the increase speed is much higher than TableB's....

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?
Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>
|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel[vbcol=seagreen]
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema
> ?
>
|||Why not make "Education Certificate" a dimension all of its own and then have a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema[vbcol=seagreen]
> ?
>|||Why not make "Education Certificate" a dimension all of its own and then hav
e a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

How to design SQL Server 2005 Reporting Services Reports in VS.NET 2005 web applications

Hi,

Can we design SQL Server 2005 Reporting Services Reports in VS.NET 2005 web applications. If so how they can be designed. Plz help me if any one know the solution.

Thanx in advance,

Vidya

Are you asking if you can use VS.Net 2005 to design reports, yes, both client and server reports are built using VS.Net.

If you are asking if you can use a web application to build a report, well yes you could, but you would have you design it. All sql server reports are is an xml file, so if you can build a front end that will generate the xml in the proper format then you are good to go! I even believe there is a .Net Class that would assist you in this...

Josh

|||

u can design RS2005 in 2 ways:

1.u can open Business Intelligence Projects->Report Server Project,but than u will have to use Report Server.to design open a report file(rdl)

2.u can use ReportViewer within the Web application.to design open a report file(rdlc)

How to design SP with variable number of params?

I have a form that allows a user to update contact info. For example:
First name
Last name
PO Box
City
State
There are actually more fields (up to 50) but I have only used five for
simplicity. Sometimes a user will submit an update for all fields.
However, for a web service, some one may only send the First Name or any
other one field. In that case, is it better to design an SP for each case?
I see that having scaling issues.
Another approach is to design one SP with many conditionals (50). Both
approaches are inefficient. What is a better way?
Thanks,
BrettSpecify default values for your parameters. I.e.,
CREATE PROCEDURE sample
@.paramLast VARCHAR(30) = NULL, -- NULL default value
@.paramFirst VARCHAR(30) = NULL, -- NULL default value
@.paramPoBox VARCHAR(30) = NULL,
@.paramCity VARCHAR(30) = '', -- Empty string default value
@.paramState CHAR(2) = 'NY' -- 'NY' default value
It will be a little tedious for 50 fields, but will allow you to not specify
parameters on calling.
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||Michael C# wrote:
> Specify default values for your parameters. I.e.,
> CREATE PROCEDURE sample
> @.paramLast VARCHAR(30) = NULL, -- NULL default value
> @.paramFirst VARCHAR(30) = NULL, -- NULL default value
> @.paramPoBox VARCHAR(30) = NULL,
> @.paramCity VARCHAR(30) = '', -- Empty string default
> value @.paramState CHAR(2) = 'NY' -- 'NY' default
> value
> It will be a little tedious for 50 fields, but will allow you to not
> specify parameters on calling.
>
I'm not sure that will work for the OP for updating.
Specify all updatable values in the parameter list. 50 is a lot, and I
might question the number of attributes on the underlying table. Unless
you're dealing with more than one table and could break up the updates
in a meaningful way.
David Gugick
Imceda Software
www.imceda.com|||Commonly we would just update all data on an update in the stored procedure
unless there is a great reason not to. You could do something like:
CREATE PROCEDURE TABLE_UPDATE
@.LastName VARCHAR(30) = NULL,
@.FirstName VARCHAR(30) = NULL,
as
update table
set lastName = coalesce(@.lastName, lastName),
firstName = coalesce(@.firstName, firstName)
go
Then if you call it with table_update @.firstName ='Bob'
The current value of lastName will be used, and the new value for
@.firstName.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||there is one thing that bothers me with this approach (regardles of number
of fields). the thing is that on update, event if the value for the column
is unchanged, the constraints are being checked all the same. eg, if there
is a foreign key constraint, updating a fk column (with the same value, thus
in fact not updating at all) will cause a lookup in the referenced table,
which is absolutely unnecessary, imho. but the alternatives - dynamically
constructing the update statement, or creating a separate statement for
every combination of params - make even less sense.
any thoughts?
dean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
procedure
> unless there is a great reason not to. You could do something like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||This seems to be the best approach of the posts here. I see there probably
isn't a way to get around conditionals for NULL checks. coalesce is a type
of conditional but probably better than using multiple IF statements
correct?
Thanks,
Brett
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
> procedure unless there is a great reason not to. You could do something
> like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%2359VcIWGFHA.524@.TK2MSFTNGP14.phx.gbl...
> Michael C# wrote:
> I'm not sure that will work for the OP for updating.
>
Why not? Here's an example of a stored procedure, with a variable number of
params, that updates a table.
--Create Table and Primary Key
CREATE TABLE [dbo].[Table1] (
[IDNum] [int] NOT NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[IDNum]
) ON [PRIMARY]
GO
--Populate table
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (0, 'Jetson',
'George')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (1, 'Flintstone',
'Fred')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (2, 'Rubble',
'Barney')
GO
--Create stored procedure with variable number of parameters
CREATE PROCEDURE usp_UpdateRecord
@.paramID INT,
@.paramLast VARCHAR(50) = NULL,
@.paramFirst VARCHAR(50) = NULL
AS
UPDATE Table1 SET LastName = @.paramLast
WHERE IDNum = @.paramID
AND @.paramLast IS NOT NULL
UPDATE Table1 SET FirstName = @.paramFirst
WHERE IDNum = @.paramID
AND @.paramFirst IS NOT NULL
GO
--Now call the stored procedure with a variable
--number of parameters each time
EXEC usp_UpdateRecord @.paramID = 0, @.paramLast = 'Johnson'
EXEC usp_UpdateRecord @.paramID = 1, @.paramFirst = 'Wilma'
EXEC usp_UpdateRecord @.paramID = 2, @.paramLast = 'Public', @.paramFirst =
'John'
GO

> Specify all updatable values in the parameter list. 50 is a lot, and I
> might question the number of attributes on the underlying table. Unless
> you're dealing with more than one table and could break up the updates in
> a meaningful way.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I personally would not recommend you design a procedure to perform up to
50 distinct updates to update a single row in the table. Seems more work
and overhead than a single update to me.
David G.|||Are you talking about the overhead incurred when typing in the code once, or
the overhead incurred each time you UPDATE 50 fields in order to change one?
Michael C.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eunWozbGFHA.3112@.tk2msftngp13.phx.gbl...
>I personally would not recommend you design a procedure to perform up to 50
>distinct updates to update a single row in the table. Seems more work and
>overhead than a single update to me.
> --
> David G.
>|||Michael C# wrote:
> Are you talking about the overhead incurred when typing in the code
> once, or the overhead incurred each time you UPDATE 50 fields in
> order to change one?
> Michael C.
I just mean the possibly running up to 50 individual updates to satisfy
what a single update can do. Plus, the implementation does not allow you
to return a column value to NULL, if needed.
My only real point here is issuing a single update and supplying all
parameters is generally the easiest, most maintainable, and safest
implementation. If the OP has a component in ASP.net or his/her
fat-client app that automates the execution of the update, then he only
has to write it once.
David G.

how to design social network database

i want to show me any templet of a social network database model any one can help me

Quote:

Originally Posted by mulualem94

i want to show me any templet of a social network database model any one can help me

what meean social network database? YOUTUBE= social network?

how to design queries graphically?

Are there anytool, which would allow me to design queries graphically, if not design then atlease analyse them graphically.

Tool should atleast show join of 4tables graphically.

Thank You.

SQL Server Management Studio has graphical capabilities - launched in one of two ways:

1.Create a 'New Query' then right-click on the query pane and select 'Design Query in Editor...'.

2.Connect to a server in Object Explorer then navigate to the following node:

Server -> Databases -> <Your database> -> Views

Right-click on 'Views' and select 'New View...' to launch the query editor.

Chris

|||

Try this......

And it's a single query, runs at a single go. I would like to know any s/w / utility (can be third party) which could decode following query.

SELECT '' AS TranNo, 'SC' AS JournalCode, cSupplier AS SupplierCode, cOrderNo AS OrderNo, cWareHouse AS Warehouse, nGrnNo AS GRNNo,

dReceiptDate AS BookEntryDate, vDefaultGLAC AS AccString, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN nTrnsQty * nUnitPrice / [nReceiptsCurrConRate] ELSE nTrnsQty * nUnitPrice * [nReceiptsCurrConRate] END)) AS FOB, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN LandedCost / [nReceiptsCurrConRate] ELSE LandedCost * [nReceiptsCurrConRate] END))

AS LandedCost, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc1)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nUnitPrice * nLineDisc1 / 100)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount1, CONVERT(NUMERIC(18,

2), SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc2)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc2) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)

/ [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100) * [nReceiptsCurrConRate] END)

END)) AS Discount2, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc3)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc3) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice)

- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)) * nLineDisc3 / 100) / [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice)

- (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))

* nLineDisc3 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount3

FROM (SELECT PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,

PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,

PMPT.dReceiptDate, 112) AS dReceiptDate, ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,

PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,

cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3, SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL

THEN 0 ELSE PMGRNLLC.nBElementalCost END) AS LandedCost

FROM PMPT LEFT OUTER JOIN

PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode AND

PMPT.cSupplier = PMGRNLLC.cSupplierCode AND

PMPT.cOrderNo = PMGRNLLC.cOrderNo AND

PMPT.nLineNo = PMGRNLLC.nLineNo AND

PMPT.cItemCode = PMGRNLLC.cItemCode AND

PMPT.cWareHouse = PMGRNLLC.cWareHouse AND

PMPT.nGrnNo = PMGRNLLC.nGrnNo LEFT OUTER JOIN

ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode LEFT OUTER JOIN

OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',

'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs') AS B ON

PMPT.cCompanyNo = B.CompanyCode AND RTRIM(PMPT.cSupplier) = B.SupplierCode AND

PMPT.cOrderNo = B.OrderNo AND PMPT.cWareHouse = B.WareHouse AND

PMPT.nGrnNo = B.GRNNo

WHERE (PMPT.cCompanyNo = 'GT') AND (PMPT.nGrnNo > 0) AND (PMPT.nTrnsQty > 0) AND

(B.SupplierCode IS NULL)

GROUP BY PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,

PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,

PMPT.dReceiptDate, 112), ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,

PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,

cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3) AS A

WHERE (dReceiptDate >= '20060401') AND (dReceiptDate <= '20070331')

GROUP BY nGrnNo, dReceiptDate, cSupplier, cOrderNo, cWareHouse, nGrnNo, vDefaultGLAC

ORDER BY GRNNo

|||

Although the query looks scary it actually isn't as complex as it looks at first sight, however I've put the query through a 3rd-party refactoring tool for clarity, see below.

It depends what you mean by 'decode'? What are you trying to ascertain by studying a diagram that studying the query wouldn't reveal?

Chris

Code Snippet

SELECT '' AS TranNo,
'SC' AS JournalCode,
cSupplier AS SupplierCode,
cOrderNo AS OrderNo,
cWareHouse AS Warehouse,
nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate,
vDefaultGLAC AS AccString,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN nTrnsQty * nUnitPrice
/ [nReceiptsCurrConRate]
ELSE nTrnsQty * nUnitPrice
* [nReceiptsCurrConRate]
END)) AS FOB,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN LandedCost
/ [nReceiptsCurrConRate]
ELSE LandedCost
* [nReceiptsCurrConRate]
END)) AS LandedCost,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc1)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc1)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount1,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc2)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc2)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount2,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc3)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc3)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount3
FROM (
SELECT PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112) AS dReceiptDate,
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3,
SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL THEN 0
ELSE PMGRNLLC.nBElementalCost
END) AS LandedCost
FROM PMPT
LEFT OUTER JOIN PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode
AND PMPT.cSupplier = PMGRNLLC.cSupplierCode
AND PMPT.cOrderNo = PMGRNLLC.cOrderNo
AND PMPT.nLineNo = PMGRNLLC.nLineNo
AND PMPT.cItemCode = PMGRNLLC.cItemCode
AND PMPT.cWareHouse = PMGRNLLC.cWareHouse
AND PMPT.nGrnNo = PMGRNLLC.nGrnNo
LEFT OUTER JOIN ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode
LEFT OUTER JOIN OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs')
AS B ON PMPT.cCompanyNo = B.CompanyCode
AND RTRIM(PMPT.cSupplier) = B.SupplierCode
AND PMPT.cOrderNo = B.OrderNo
AND PMPT.cWareHouse = B.WareHouse
AND PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT')
AND (PMPT.nGrnNo > 0)
AND (PMPT.nTrnsQty > 0)
AND (B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112),
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3
) AS A
WHERE (dReceiptDate >= '20060401')
AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo,
dReceiptDate,
cSupplier,
cOrderNo,
cWareHouse,
nGrnNo,
vDefaultGLAC
ORDER BY GRNNo

|||

3rd-party refactoring tool is what i'm looking for, it would be much more better if it does graphical representation.

Can you give me any names?

|||

This is a great product for laying out (refactoring) SQL:

http://www.red-gate.com/products/SQL_Refactor/index.htm

However it can't create graphical representations of queries.

To be honest I'm not sure you'd gain a lot from displaying that particular query graphically - in fact I reckon that doing so, and making subsequent modifications, could lead to errors due to the query's complexity. IMO in this case it would be far better to 'bite the bullet' and try to interpret and understand the query text.

Chris

How to design link tables?

I have been having a similar discussion on the MS Access newsgroup for the
last w and I wanted to discuss the issues in the context of MS SQL in
addition to MS Access. I hope this form of cross posting is not offensive to
anyone.
Lets assume I have to relations: student and course and I want to create a
junction table to record which students are taking which courses.
(1) If I create a two column table containing fkStudent and fkCourse where
the primary consists of these two columns. Now I want to perform a join to
find out all the courses a particular student is taking. Since the primary
key index structure requires both foreign keys and I'm only providing the
value for fkStudent and not fkCourse, will I be preforming a linear search
on the junction table when I preform a join to determine what courses I am
taking?
My guess is yes. If your answer is yes, would you anticipate a linear search
to be a problem? I would. If you agree that we should avoid linear searches,
then what would you recommend for a primary key on this link table? Do we
need a pimary key at all? Most folks say yes -- but I'm not clear on why.
(2) Lets say I have many thousands of job titles -- too many for a combo
box. I want to have a M:M relationship between Job Titles and Job Postings.
Let's say I find a job posting on the web and I need to find the foriegn key
for a job title (if it exists) or create a job title (if it does not exist).
I can assume it does not exist and try an SQL INSERT and, if that fails, use
a SELECT. Or, I can assume it does exist and use SELECT and if I don't find
any, use INSERT. Either way, the worst case scenerio requires two redundant
lookups. Is there a better approach?
Thanks,
SiegfriedHi
1) Look at [Order Details] table in Northwind database.
It is a "junction table" between Orders and Product tables.
2)
IF NOT EXISTS (SELECT * FROM Table WHERE....)
INSERT INTO ......
ELSE
SELECT <columns> FROM ......
"Siegfried Heintze" <siegfried@.heintze.com> wrote in message
news:%23ofWEqlsFHA.460@.TK2MSFTNGP15.phx.gbl...
>I have been having a similar discussion on the MS Access newsgroup for the
> last w and I wanted to discuss the issues in the context of MS SQL in
> addition to MS Access. I hope this form of cross posting is not offensive
> to
> anyone.
> Lets assume I have to relations: student and course and I want to create a
> junction table to record which students are taking which courses.
> (1) If I create a two column table containing fkStudent and fkCourse where
> the primary consists of these two columns. Now I want to perform a join to
> find out all the courses a particular student is taking. Since the primary
> key index structure requires both foreign keys and I'm only providing the
> value for fkStudent and not fkCourse, will I be preforming a linear search
> on the junction table when I preform a join to determine what courses I am
> taking?
> My guess is yes. If your answer is yes, would you anticipate a linear
> search
> to be a problem? I would. If you agree that we should avoid linear
> searches,
> then what would you recommend for a primary key on this link table? Do we
> need a pimary key at all? Most folks say yes -- but I'm not clear on why.
> (2) Lets say I have many thousands of job titles -- too many for a combo
> box. I want to have a M:M relationship between Job Titles and Job
> Postings.
> Let's say I find a job posting on the web and I need to find the foriegn
> key
> for a job title (if it exists) or create a job title (if it does not
> exist).
> I can assume it does not exist and try an SQL INSERT and, if that fails,
> use
> a SELECT. Or, I can assume it does exist and use SELECT and if I don't
> find
> any, use INSERT. Either way, the worst case scenerio requires two
> redundant
> lookups. Is there a better approach?
> Thanks,
> Siegfried
>

How to design flexible relationships between documents?

Hi,
I have an inventory application which also handles accounts payable, and
accounts receivables.
One of the problems I currently have is that I need to tie, in a flexible
way, different transaction types.
For example, I need to tie invoices to their "shipping-notifications", to
the original orders, etc...
A friend of mine was telling me about SAP and how there you can in a
flexible way define documents (or transactions) so that they can be tied up
to one or more documents of a given type.
I've been thinking about this, but I can't figure out how a design that
would allow this in a flexible way....
How does this work?
Thanks,
EdgardEdgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied up
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?|||Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribió en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
>> Hi,
>> I have an inventory application which also handles accounts payable, and
>> accounts receivables.
>> One of the problems I currently have is that I need to tie, in a flexible
>> way, different transaction types.
>> For example, I need to tie invoices to their "shipping-notifications", to
>> the original orders, etc...
>> A friend of mine was telling me about SAP and how there you can in a
>> flexible way define documents (or transactions) so that they can be tied
>> up to one or more documents of a given type.
>> I've been thinking about this, but I can't figure out how a design that
>> would allow this in a flexible way....
>> How does this work?
>> Thanks,
>> Edgard
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?

How to design flexible relationships between documents?

Edgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied u
p
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribi en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?|||Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribi en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?|||Hi,
I have an inventory application which also handles accounts payable, and
accounts receivables.
One of the problems I currently have is that I need to tie, in a flexible
way, different transaction types.
For example, I need to tie invoices to their "shipping-notifications", to
the original orders, etc...
A friend of mine was telling me about SAP and how there you can in a
flexible way define documents (or transactions) so that they can be tied up
to one or more documents of a given type.
I've been thinking about this, but I can't figure out how a design that
would allow this in a flexible way....
How does this work?
Thanks,
Edgard|||Edgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied u
p
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?

how to design dynamic reports based on user's choice

Hi all,

I'm a beginner to Report Services, and have tons of questions.

Here's the first one:

if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?

For example,

the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.

Could someone tell me how to do it (create and deploy the reports)?

Thanks a million!

Jonee

I think this it's possible to certain extend, but not sure if 100% percent. It would take some research and see how far can you get on this one.

How to design Database to search faster from 1 million customer''s

I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.

Application serves two functionalities

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

Scenario: The above requirement can be complemented by following example

Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.

Now if some new customer want to open a new account in bank, then bank officials want to know whether the

new customer is one of the existing customer or not(without asking to customer itself).

System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface

then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).

Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).

Scenario: The above requirement can be complemented by following example

If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.

I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.

The bank example is not a good one, as the banks are always requesting a unique identifying attribute from the customers (like an id from their id card or their SSN). If you want to search through all the fields, you would have to implement something like fulltext searching / soundex functionality (as I assume that you did not wrote Jhon instead of John accidentially)

Requirement 2 is not a database issue, as the other bank database is normally not on the same server and is normally reached via a Web service through a service bus.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jeans for replying to my post.

Perhaps you got me otherwise.Let me now tell you exact sitiuation.

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

We consider exact sitiuation here,

We have a huge (about 1 million records) database of people involved in loan, credit card or any kind of fraud against banks.It is combined database for all banks in a country and each bank is contributing a list of defaulters from it's side to this combined database.

Now if some new customer comes to avail the services of a bank, then bank wants to ensure that new customer never appeared in this defaulter list before.

But one important searching criteria for matching against this defaulter's dabase is that we should able to carryout sounlike search i.e (let me explain with one example mentioned below)

If a person names "Mohammed Ali", then our application should be able to find out variation of names which sounds similar to orignal name of person, i.e

"Mohammad Ali"

"Mohamad Ali"

This requirement is expected from banks if new customer comes to bank with fake ID Proof or with forged documents to avail the services. In this case there will be no pre defined unique id for customer or any identifier and application will have to solely on name matching logic.

My concern is mostly associated with the performance of application, especially to the database design (for optimal performance), rather then logic of search.

|||

There are a lot of factors to take into account - hardware architecture, I/O, memory, database structure, etc. database architecture also has to be considered - creating filegroups which will contain the database files, storing the database files in multiple drive spindles, creating the tables to be stored in filegroups so that searches can be performed by multiple drive splindles, etc.

|||

Thanks bass,for your reply

But our main focus is on database design rather then Hardware configuration...Hardware is not a issue as we have plenty for our use.

I shall appriciate your efforts if you can suggest something for DB design or tuning of database.

|||

Although the soundex functionality is implemented in SQL Server there might be more sophisticated algorithms out there that might fit your need better than the SOUNDEX in SQL Server does. but to your point about the performance of the system: there should be no problem in searching even "large" (though 1M is not that large) database for the names passed by the application. My design suggestion would be to store each name normalalized in tables, generate the soundex words either in your frontend application or using a CLR function and query your table for the soundex terms. By the score of matching and found items you can choose to display the TOP N customers who match the names passed or who have a certain score (e.g. passing John Smith might bring back a long list of entries :-) ) If the tables gets even bigger you could decide to use table partitiioning to scale out your design.

Jens K. Suessmeyer

http://www.sqlserver2005.de

how to design database for chinese and japanese characters

I need a small confirmation regarding storing the Chinese and Japanese characters in sql server. Can we store Chinese and Japanese characters on a same database with Chinese Collation? Or else we need to store it separately with respective collations.

I tried to store both characters on db with Chinese collation it works but I am not so sure if it is right way to do so. Please confirm on this as we are doing research stage to build website in Chinese and japanese.

Thanks in advance.

Moving to SQL Server Setup and Upgrade|||

vrkanaka wrote:

I need a small confirmation regarding storing the Chinese and Japanese characters in sql server. Can we store Chinese and Japanese characters on a same database with Chinese Collation? Or else we need to store it separately with respective collations.

I tried to store both characters on db with Chinese collation it works but I am not so sure if it is right way to do so. Please confirm on this as we are doing research stage to build website in Chinese and japanese.

Thanks in advance.

The Chinese and Japanese alphabets are more than 2000 characters so you need to find the version of Chinese and Japanese and use the correct code page and collations for each language with NVarchar or NVarchar(max). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms144250.aspx

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

How to design Data Warehouse for Traditional OLTP System

I am successfuly runing my relational database system designed in SQL 2000, and it has data for last 5 years, runing smoothly.

Now, i want to create a data warehouse for my database... How do i create?

From where to get start?

Any Sample Project that demonstrate How to design DW for OLTP system. Any Example that converts Northwind DB to DW?

There are several ways for you to start. You can read a little bit about Start and Snowflake schemas.

You can take a look at the design of AdventureWorksDW sample database and AdventureWorks sample AS project.

You can also try and take a run a cube wizard creating a new cube without using datasource. And once you've defined a cube structure, BI Dev Studio will generate a relational DB schema for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to Design and Handle a Table with 30 Million rows

Hi

my requirement is like this - I need to maintain a subscription DB with more then 30 million rows in sql server thet keeps on increasing. I need to Update ,Insert and select from the Table. Problem is this has to be integrated with existing application where performance should not get effected. In this table application id and user number are unique.

Can anyone suggest what should be the approach.with minimum effect on performance of existig application.

ThanksIf you have proper indexes it should work with no problem.

Writing new queries could be challenging and will require more indexes. But with proper indexing you can manage it too.

Don't create clustered index if possible it will slow down update\insert and will require plenty free space on a Server.

Control updated number of records update, never update all table at once, it could bust transaction log and stop a Server.|||Can i select distinct values of a column from such a large table without using select distinct. Because this query runs very slowly on such a large table.|||Try to put index over this column.
In this case only index will be searched and not table itself.

Good Luck.

how to design a thorough test plan?

I am going to handle a test to a list of querys to find their efficency(spending of time)

here is my test plan:

there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average spending of time;
fetch next query from cursor;
end
...

Is there any better test plan?(just test spending of time)
or test tools?One of the things I think you would want to include is the changing of parameter data (if applicable).

by that I mean that...

select * from tblMyTest where MyID = 12345

might return a lot faster then

select * from tblMyTest where MyID = 54321

depending on how the tables have been constructed.

You probably want to test with different levels of data as well eg, 10000 record, 1000000 records etc.

What exactly are your trying to prove by your testing? Performance obviously, but are you also stress testing, load testing and durability testing, all of which are performance related.

HTH.|||The main purpose of the test plan is to compare perfomance of the same querys to different databases which have same data but different Logical/phsical structure, or to compare performance of different versions of the same query to same database.
---may call it "test different structure's performance"?
we do that because we want to get a general contractive performance report of all querys or versions when we want make some change to databases or querys, that'll help us to decide whether to apply the change.|||Okie, well in that case one of the things you probably want to include in your testing is how the query performs when other activities are taking place on the database tables that the query is referencing.

You may find that despite the fact that 70% or the time the 3 seconds query is faster, 30% of the time the query take 10 seconds longer because of the locking that is involved in the query.|||thanks! Actually All querys is executed in sequence in a batch,and there is only one batch running,we will stop other clients also,so I think In that case wonnt occur a lock.
one thing I am not sure is that whether a query will run faster or later if the query was run in different order in sequence?|||I can't think of any reason why it would,... but you might want to try it just to make sure...|||thanks for advises!