multiselect result changes while add additional dimension to filter?

Every Query I introduce with multiselect to restrict the my rows works only as long as I do not add a second dimension to the filter fields ?

My Query (derived from Management Studio) - if you run it once like this and once with the comment line enabled the count of columns returned change - what I really did not understand, because only the values should change (different slice)?

Could anyone explain this to me? or point me to a location with detailed explanation? (I have checked http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm this but it does not hit specially my question)

WITH
MEMBER [Sales Territory].[Sales Territory Group].[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__] AS 'AGGREGATE({ [Sales Territory].[Sales Territory Group].&[North America], [Sales Territory].[Sales Territory Group].&[Pacific] })'
SET [{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] AS '{ { [Sales Territory].[Sales Territory].[All Sales Territories] }, AddCalculatedMembers([Sales Territory].[Sales Territory].[Group].MEMBERS)}'
SELECT
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME , [Sales Territory].[Sales Territory].[Country].[Sales Territory Group] ON COLUMNS
FROM [Adventure Works]
WHERE
(
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__]
// , [Sales Channel].[Sales Channel].&[Internet]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Noone with an explanation or hint to further documentation ?

Who is it possible that an aditional slice of an independ dimension changes the rows / columns if not filter or empty is used ?

HANNES

multiselect result changes while add additional dimension to filter?

Every Query I introduce with multiselect to restrict the my rows works only as long as I do not add a second dimension to the filter fields ?

My Query (derived from Management Studio) - if you run it once like this and once with the comment line enabled the count of columns returned change - what I really did not understand, because only the values should change (different slice)?

Could anyone explain this to me? or point me to a location with detailed explanation? (I have checked http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm this but it does not hit specially my question)

WITH
MEMBER [Sales Territory].[Sales Territory Group].[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__] AS 'AGGREGATE({ [Sales Territory].[Sales Territory Group].&[North America], [Sales Territory].[Sales Territory Group].&[Pacific] })'
SET [{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] AS '{ { [Sales Territory].[Sales Territory].[All Sales Territories] }, AddCalculatedMembers([Sales Territory].[Sales Territory].[Group].MEMBERS)}'
SELECT
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME , [Sales Territory].[Sales Territory].[Country].[Sales Territory Group] ON COLUMNS
FROM [Adventure Works]
WHERE
(
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__]
// , [Sales Channel].[Sales Channel].&[Internet]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Noone with an explanation or hint to further documentation ?

Who is it possible that an aditional slice of an independ dimension changes the rows / columns if not filter or empty is used ?

HANNES

Multi-Row update trigger

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).Subqueries, perhaps...
UPDATE Purchasing.Product
SET LastReceivedQty = subquery.ReceivedQty,
LastReceivedDate = subquery.ReceivedDate
from Purchasing.DeliveryNote dn
inner join --Subquery
(SELECT dnd.DeliveryNoteID,
sum(i.ReceivedQty) ReceivedQty,
max(dn.ReceivedDate) RecievedDate
FROM Purchasing.DeliveryNoteDetail dnd
INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
group by dnd.DeliveryNoteID) Subquery
on dn.DeliveryNoteID = Subquery.DeliveryNoteIDsql

Multiply record fields with same mainID.

Hi everybody,
What's the most efficient way to get the following result:
I have a number of records with a MainID and a Value fields.
eg:
SubID MainID Value
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
7 3 2
8 3 2
I need the product of the Value field for each MainID.
The result has to be like this:
MainID PrValue
1 6 (1*1*2*3)
2 1 (1*1)
3 4 (2*2)
TIA,
Martin.select MainID, sum(value) as PrValue
from table
group by MainID
"martin" <kashaan007@.hotmail.com> wrote in message
news:Of%23nDPnRFHA.3144@.tk2msftngp13.phx.gbl...
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Try,
use northwind
go
create table t (
SubID int,
MainID int,
Value int
)
go
insert into t values(1, 1, 1)
insert into t values(2, 1, 1)
insert into t values(3, 1, 2)
insert into t values(4, 1, 3)
insert into t values(5, 2, 1)
insert into t values(6, 2, 1)
insert into t values(7, 3, 2)
insert into t values(8, 3, 2)
go
select
MainID,
POWER(10, SUM(LOG10(Value)))
from
t
group by
MainID
drop table t
go
I took the idea from:
The T-SQL Banker
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
AMB
"martin" wrote:
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Hi,AMB
It gives me a wrong output for the col1=2
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,4)
INSERT INTO #Test VALUES (2,2)
SELECT col1,POWER(10, SUM(LOG10(col2)))
FROM #Test GROUP BY col1
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
>
> AMB
> "martin" wrote:
> > Hi everybody,
> >
> > What's the most efficient way to get the following result:
> >
> > I have a number of records with a MainID and a Value fields.
> >
> > eg:
> >
> > SubID MainID Value
> > 1 1 1
> > 2 1 1
> > 3 1 2
> > 4 1 3
> > 5 2 1
> > 6 2 1
> > 7 3 2
> > 8 3 2
> >
> > I need the product of the Value field for each MainID.
> >
> > The result has to be like this:
> >
> > MainID PrValue
> > 1 6 (1*1*2*3)
> > 2 1 (1*1)
> > 3 4 (2*2)
> >
> > TIA,
> >
> > Martin.
> >
> >
> >
> >|||Alejandro's solution is good for values greater than zero. Here's a
more general solution for all integers (this one adapted from Celko and
others):
SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid
--
David Portas
SQL Server MVP
--|||Thanks a lot.
Works great.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
html/TheT-SQLBanker.asp
>
> AMB
> "martin" wrote:
> > Hi everybody,
> >
> > What's the most efficient way to get the following result:
> >
> > I have a number of records with a MainID and a Value fields.
> >
> > eg:
> >
> > SubID MainID Value
> > 1 1 1
> > 2 1 1
> > 3 1 2
> > 4 1 3
> > 5 2 1
> > 6 2 1
> > 7 3 2
> > 8 3 2
> >
> > I need the product of the Value field for each MainID.
> >
> > The result has to be like this:
> >
> > MainID PrValue
> > 1 6 (1*1*2*3)
> > 2 1 (1*1)
> > 3 4 (2*2)
> >
> > TIA,
> >
> > Martin.
> >
> >
> >
> >|||If we add +0.001 it works fine
SELECT col1,POWER(10, SUM(LOG10(col2))+0.001)
FROM #Test GROUP BY col1
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYVS5cnRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>|||Cast 10 to float.
SELECT
col1,
cast(POWER(cast(10 as float), SUM(LOG10(col2))) as decimal(5))
FROM #Test GROUP BY col1
go
AMB
"Uri Dimant" wrote:
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||Good catch!!!
AMB
"Uri Dimant" wrote:
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||David,
This is really a good one.
AMB
"David Portas" wrote:
> Alejandro's solution is good for values greater than zero. Here's a
> more general solution for all integers (this one adapted from Celko and
> others):
> SELECT mainid,
> CAST(ROUND(
> COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
> * SIGN(MIN(ABS(value)))
> * (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
> ,0) AS INTEGER) AS product
> FROM T
> GROUP BY mainid
> --
> David Portas
> SQL Server MVP
> --
>|||Use better the one posted by David Portas, and if you decide to use this one,
read the correction in my answer to Uri.
AMB
"martin" wrote:
> Thanks a lot.
> Works great.
> Martin.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
> html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||Ok thanks.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:210DC41A-BF9B-4211-9E16-995A554C0374@.microsoft.com...
> Use better the one posted by David Portas, and if you decide to use this
one,
> read the correction in my answer to Uri.
>
> AMB
> "martin" wrote:
> > Thanks a lot.
> >
> > Works great.
> >
> > Martin.
> >
> >
> > "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> > news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > > Try,
> > >
> > > use northwind
> > > go
> > >
> > > create table t (
> > > SubID int,
> > > MainID int,
> > > Value int
> > > )
> > > go
> > >
> > > insert into t values(1, 1, 1)
> > > insert into t values(2, 1, 1)
> > > insert into t values(3, 1, 2)
> > > insert into t values(4, 1, 3)
> > > insert into t values(5, 2, 1)
> > > insert into t values(6, 2, 1)
> > > insert into t values(7, 3, 2)
> > > insert into t values(8, 3, 2)
> > > go
> > >
> > > select
> > > MainID,
> > > POWER(10, SUM(LOG10(Value)))
> > > from
> > > t
> > > group by
> > > MainID
> > >
> > > drop table t
> > > go
> > >
> > > I took the idea from:
> > >
> > > The T-SQL Banker
> > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
> > html/TheT-SQLBanker.asp
> > >
> > >
> > > AMB
> > >
> > > "martin" wrote:
> > >
> > > > Hi everybody,
> > > >
> > > > What's the most efficient way to get the following result:
> > > >
> > > > I have a number of records with a MainID and a Value fields.
> > > >
> > > > eg:
> > > >
> > > > SubID MainID Value
> > > > 1 1 1
> > > > 2 1 1
> > > > 3 1 2
> > > > 4 1 3
> > > > 5 2 1
> > > > 6 2 1
> > > > 7 3 2
> > > > 8 3 2
> > > >
> > > > I need the product of the Value field for each MainID.
> > > >
> > > > The result has to be like this:
> > > >
> > > > MainID PrValue
> > > > 1 6 (1*1*2*3)
> > > > 2 1 (1*1)
> > > > 3 4 (2*2)
> > > >
> > > > TIA,
> > > >
> > > > Martin.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Multiply record fields with same mainID.

Hi everybody,
What's the most efficient way to get the following result:
I have a number of records with a MainID and a Value fields.
eg:
SubID MainID Value
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
7 3 2
8 3 2
I need the product of the Value field for each MainID.
The result has to be like this:
MainID PrValue
1 6 (1*1*2*3)
2 1 (1*1)
3 4 (2*2)
TIA,
Martin.
select MainID, sum(value) as PrValue
from table
group by MainID
"martin" <kashaan007@.hotmail.com> wrote in message
news:Of%23nDPnRFHA.3144@.tk2msftngp13.phx.gbl...
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>
|||Try,
use northwind
go
create table t (
SubID int,
MainID int,
Value int
)
go
insert into t values(1, 1, 1)
insert into t values(2, 1, 1)
insert into t values(3, 1, 2)
insert into t values(4, 1, 3)
insert into t values(5, 2, 1)
insert into t values(6, 2, 1)
insert into t values(7, 3, 2)
insert into t values(8, 3, 2)
go
select
MainID,
POWER(10, SUM(LOG10(Value)))
from
t
group by
MainID
drop table t
go
I took the idea from:
The T-SQL Banker
http://msdn.microsoft.com/library/de...-SQLBanker.asp
AMB
"martin" wrote:

> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>
|||Hi,AMB
It gives me a wrong output for the col1=2
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,4)
INSERT INTO #Test VALUES (2,2)
SELECT col1,POWER(10, SUM(LOG10(col2)))
FROM #Test GROUP BY col1
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/de...-SQLBanker.asp[vbcol=seagreen]
>
> AMB
> "martin" wrote:
|||Alejandro's solution is good for values greater than zero. Here's a
more general solution for all integers (this one adapted from Celko and
others):
SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid
David Portas
SQL Server MVP
|||Thanks a lot.
Works great.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/de...us/dnsqlmag02/
html/TheT-SQLBanker.asp[vbcol=seagreen]
>
> AMB
> "martin" wrote:
|||If we add +0.001 it works fine
SELECT col1,POWER(10, SUM(LOG10(col2))+0.001)
FROM #Test GROUP BY col1
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYVS5cnRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
>
http://msdn.microsoft.com/library/de...-SQLBanker.asp
>
|||Cast 10 to float.
SELECT
col1,
cast(POWER(cast(10 as float), SUM(LOG10(col2))) as decimal(5))
FROM #Test GROUP BY col1
go
AMB
"Uri Dimant" wrote:

> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> http://msdn.microsoft.com/library/de...-SQLBanker.asp
>
>
|||Good catch!!!
AMB
"Uri Dimant" wrote:

> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> http://msdn.microsoft.com/library/de...-SQLBanker.asp
>
>
|||David,
This is really a good one.
AMB
"David Portas" wrote:

> Alejandro's solution is good for values greater than zero. Here's a
> more general solution for all integers (this one adapted from Celko and
> others):
> SELECT mainid,
> CAST(ROUND(
> COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
> * SIGN(MIN(ABS(value)))
> * (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
> ,0) AS INTEGER) AS product
> FROM T
> GROUP BY mainid
> --
> David Portas
> SQL Server MVP
> --
>

Multiply record fields with same mainID.

Hi everybody,
What's the most efficient way to get the following result:
I have a number of records with a MainID and a Value fields.
eg:
SubID MainID Value
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
7 3 2
8 3 2
I need the product of the Value field for each MainID.
The result has to be like this:
MainID PrValue
1 6 (1*1*2*3)
2 1 (1*1)
3 4 (2*2)
TIA,
Martin.select MainID, sum(value) as PrValue
from table
group by MainID
"martin" <kashaan007@.hotmail.com> wrote in message
news:Of%23nDPnRFHA.3144@.tk2msftngp13.phx.gbl...
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Try,
use northwind
go
create table t (
SubID int,
MainID int,
Value int
)
go
insert into t values(1, 1, 1)
insert into t values(2, 1, 1)
insert into t values(3, 1, 2)
insert into t values(4, 1, 3)
insert into t values(5, 2, 1)
insert into t values(6, 2, 1)
insert into t values(7, 3, 2)
insert into t values(8, 3, 2)
go
select
MainID,
POWER(10, SUM(LOG10(Value)))
from
t
group by
MainID
drop table t
go
I took the idea from:
The T-SQL Banker
heT-SQLBanker.asp" target="_blank">http://msdn.microsoft.com/library/d...T-SQLBanker.asp
AMB
"martin" wrote:

> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Hi,AMB
It gives me a wrong output for the col1=2
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,4)
INSERT INTO #Test VALUES (2,2)
SELECT col1,POWER(10, SUM(LOG10(col2)))
FROM #Test GROUP BY col1
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
p" target="_blank">http://msdn.microsoft.com/library/d...ker.as
p[vbcol=seagreen]
>
> AMB
> "martin" wrote:
>|||Alejandro's solution is good for values greater than zero. Here's a
more general solution for all integers (this one adapted from Celko and
others):
SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0)
)))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid
David Portas
SQL Server MVP
--|||Thanks a lot.
Works great.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/d...-us/dnsqlmag02/
html/TheT-SQLBanker.asp[vbcol=seagreen]
>
> AMB
> "martin" wrote:
>|||If we add +0.001 it works fine
SELECT col1,POWER(10, SUM(LOG10(col2))+0.001)
FROM #Test GROUP BY col1
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYVS5cnRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
>
p" target="_blank">http://msdn.microsoft.com/library/d...ker.as
p
>|||Cast 10 to float.
SELECT
col1,
cast(POWER(cast(10 as float), SUM(LOG10(col2))) as decimal(5))
FROM #Test GROUP BY col1
go
AMB
"Uri Dimant" wrote:

> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> /TheT-SQLBanker.asp" target="_blank">http://msdn.microsoft.com/library/d...T-SQLBanker.asp
>
>|||Good catch!!!
AMB
"Uri Dimant" wrote:

> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> /TheT-SQLBanker.asp" target="_blank">http://msdn.microsoft.com/library/d...T-SQLBanker.asp
>
>|||David,
This is really a good one.
AMB
"David Portas" wrote:

> Alejandro's solution is good for values greater than zero. Here's a
> more general solution for all integers (this one adapted from Celko and
> others):
> SELECT mainid,
> CAST(ROUND(
> COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0)
)))),0)
> * SIGN(MIN(ABS(value)))
> * (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
> ,0) AS INTEGER) AS product
> FROM T
> GROUP BY mainid
> --
> David Portas
> SQL Server MVP
> --
>sql

Multiple Views & Web Form

I have ViewA that sums up 4 fields from one table. I then have ViewB that
uses ViewA to calculate the results. Now I do this with 5 different tables
and then link them all to get my final results. Each View has a date range
(begin / end) that I need to pass from a Web form.
How should I go about doing this? Should I create a temporary table to hold
the begin / end dates to use in the sub Views? Or something else? I am at a
lost on how to go about this and need some direction and syntax?
Thanks in advance for your help!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1Chamark via webservertalk.com wrote:
> I have ViewA that sums up 4 fields from one table. I then have ViewB that
> uses ViewA to calculate the results. Now I do this with 5 different tables
> and then link them all to get my final results. Each View has a date range
> (begin / end) that I need to pass from a Web form.
> How should I go about doing this? Should I create a temporary table to hol
d
> the begin / end dates to use in the sub Views? Or something else? I am at
a
> lost on how to go about this and need some direction and syntax?
> Thanks in advance for your help!!
>
Post your DDL, and let's start by eliminating these unnecessary views.
Simplify this whole mess into a single query, making it easier to pass
date parameters to.|||>> I have ViewA that sums up 4 fields [sic] from one table. I then have ViewB tha
t uses ViewA to calculate the results. <<
Fileds and columns are totally different concepts.
VIEWs do not take parameters; stored procedures take parameters. Since
this is an SQL newsgroup, we really don't care about the Web form part.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it and all your terms are wrong.|||Hi Chamark,
Use a stored procedure and call that from your application, without seeing
your DDL (create table and create view stuff) its difficult but this might
give you an idea...
create proc get_yourstuff
@.range1_start datetime,
@.range1_end datetime,
@.range2_start datetime,
@.range2_end datetime
as
begin
select ...
from view1 as v1
inner join view2 as v2 on v2.yoursurrogatekey = v1.yoursurrogatekey
where v1.yourdate between @.range1_start and @.range1_end
and v2.yourdate between @.range2_start and @.range2_end
order by ...
end
PS... ignore celko's comment about not posting about the webform part - the
guy is an idiot, this is a SQL Server programming group and your post is
fine.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6247bb4bcf5d5@.uwe...
>I have ViewA that sums up 4 fields from one table. I then have ViewB that
> uses ViewA to calculate the results. Now I do this with 5 different tables
> and then link them all to get my final results. Each View has a date range
> (begin / end) that I need to pass from a Web form.
> How should I go about doing this? Should I create a temporary table to
> hold
> the begin / end dates to use in the sub Views? Or something else? I am at
> a
> lost on how to go about this and need some direction and syntax?
> Thanks in advance for your help!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> Since
> this is an SQL newsgroup, we really don't care about the Web form part.
You are the LAST person to give any advice on posting content here, you
don't seem to realise this is a forum for MICROSOFT SQL SERVER PROGRAMMING
and NOT a SQL only forum.
Next time you post, check what forum you are posting to, this is
MICROSOFT.PUBLIC.SQLSERVER.PROGRAMMING.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151206577.847462.67840@.b68g2000cwa.googlegroups.com...
> Fileds and columns are totally different concepts.
>
> VIEWs do not take parameters; stored procedures take parameters. Since
> this is an SQL newsgroup, we really don't care about the Web form part.
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it and all your terms are wrong.
>|||Thanks Tracy,
I haven't posted the DDL before. I am hoping the following is what you are
referencing. If not please advise.
Associate is my common field.
CREATE DATABASE [CSSMetrics] ON (NAME = N'MetricsSQL_dat', FILENAME = N'C:\
Program Files\Microsoft SQL Server\MSSQL\data\MetricsSQL.mdf' , SIZE = 1405,
FILEGROWTH = 10%) LOG ON (NAME = N'MetricsSQL_log', FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL\data\MetricsSQL.ldf' , SIZE = 4112,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'CSSMetrics', N'autoclose', N'false'
GO
exec sp_dboption N'CSSMetrics', N'bulkcopy', N'false'
GO
exec sp_dboption N'CSSMetrics', N'trunc. log', N'false'
GO
exec sp_dboption N'CSSMetrics', N'torn page detection', N'true'
GO
exec sp_dboption N'CSSMetrics', N'read only', N'false'
GO
exec sp_dboption N'CSSMetrics', N'dbo use', N'false'
GO
exec sp_dboption N'CSSMetrics', N'single', N'false'
GO
exec sp_dboption N'CSSMetrics', N'autoshrink', N'false'
GO
exec sp_dboption N'CSSMetrics', N'ANSI null default', N'false'
GO
exec sp_dboption N'CSSMetrics', N'recursive triggers', N'false'
GO
exec sp_dboption N'CSSMetrics', N'ANSI nulls', N'false'
GO
exec sp_dboption N'CSSMetrics', N'concat null yields null', N'false'
GO
exec sp_dboption N'CSSMetrics', N'cursor close on commit', N'false'
GO
exec sp_dboption N'CSSMetrics', N'default to local cursor', N'false'
GO
exec sp_dboption N'CSSMetrics', N'quoted identifier', N'false'
GO
exec sp_dboption N'CSSMetrics', N'ANSI warnings', N'false'
GO
exec sp_dboption N'CSSMetrics', N'auto create statistics', N'true'
GO
exec sp_dboption N'CSSMetrics', N'auto update statistics', N'true'
GO
use [CSSMetrics]
GO
CREATE TABLE [dbo].[Bonus] (
[BonusDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CSI] (
[Part Surv Id] [float] NULL ,
[Seq C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod Id C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL ,
[#] [float] NULL ,
[Resp Val Id] [float] NULL ,
[Adjusted Weight] [float] NULL ,
[ExtSatWt] [float] NULL ,
[VerSatWt] [float] NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Surv Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CSI-Disconnect] (
[Date] [smalldatetime] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Call Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Call Scores] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Goals] (
[Segment] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Adherence] [float] NULL ,
[Efficiency] [float] NULL ,
[AHT] [float] NULL ,
[Shift Account] [float] NULL ,
[Top 2 Box] [float] NULL ,
[Disconnect] [float] NULL ,
[Call Score] [float] NULL ,
[Retained] [float] NULL ,
[RWI] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[National AR] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN Count] [float] NULL ,
[RTF] [money] NULL ,
[RTC] [money] NULL ,
[CashedOut] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[National Call Stats] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep Ssn] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NchQty] [float] NULL ,
[SchdOpenSecsQty] [float] NULL ,
[LogOnSecsQty] [float] NULL ,
[InAdherenceSecsQty] [float] NULL ,
[OutOfAdherenceSecsQty] [float] NULL ,
[HoldSecsQty] [float] NULL ,
[TotalHandleTime] [float] NULL ,
[TalkHoldAvailable] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OPA] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPA Code] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Opa Seconds Qty] [float] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Tracy McKibben wrote:
>[quoted text clipped - 6 lines]
>Post your DDL, and let's start by eliminating these unnecessary views.
>Simplify this whole mess into a single query, making it easier to pass
>date parameters to.
Message posted via http://www.webservertalk.com|||Chamark via webservertalk.com wrote:
> Thanks Tracy,
> I haven't posted the DDL before. I am hoping the following is what you are
> referencing. If not please advise.
>
Need to see your CREATE VIEW statements too, it's not clear what you
meant by "sums up 4 fields" and "calculate the results".

> Tracy McKibben wrote:
>|||I use all these views joined together to get my final results. Each sub view
has the date range. I need one common place to supply the data range coming
from a Web form to supply these views.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_AR_STATS_VIEW
AS
SELECT TOP 10000 dbo.ASSOC_AR_SUMS_VIEW.Team, dbo.ASSOC_AR_SUMS_VIEW.
Segment, dbo.ASSOC_AR_SUMS_VIEW.Associate,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0) AS RTF,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTC, 0) AS RTC,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfCashedOut, 0) AS
[Cashed Out], NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0)
/ (NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0) + NULLIF
(dbo.ASSOC_AR_SUMS_VIEW.SumOfRTC, 0)) AS Retention,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfCashedOut, 0) /
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfCashedOut, 0)
+ NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0) AS COR,
dbo.Goals.Retained
FROM dbo.ASSOC_AR_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_AR_SUMS_VIEW.Segment = dbo.Goals.
Segment AND dbo.ASSOC_AR_SUMS_VIEW.[Date] >= '5/1/2006' AND
dbo.ASSOC_AR_SUMS_VIEW.[Date] <= '5/20/2006' AND dbo.
ASSOC_AR_SUMS_VIEW.Team = 'Abbott'
ORDER BY dbo.ASSOC_AR_SUMS_VIEW.Team, dbo.ASSOC_AR_SUMS_VIEW.Segment
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_AR_SUMS_VIEW
AS
SELECT TOP 10000 Team, Segment, Associate, SUM([SSN Count]) AS
Opportunities, SUM(RTF) AS SumOfRTF, SUM(RTC) AS SumOfRTC, SUM(CashedOut)
AS SumOfCashedOut, [Date], Site
FROM dbo.[National AR]
GROUP BY Team, Segment, Associate, [Date], Site
ORDER BY Team, Segment, Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CALL_STATS_VIEW
AS
SELECT dbo.ASSOC_CALL_SUMS_VIEW.Team, dbo.ASSOC_CALL_SUMS_VIEW.Associate
,
dbo.ASSOC_CALL_SUMS_VIEW.SumOfInAdherenceSecsQty / (dbo.
ASSOC_CALL_SUMS_VIEW.SumOfInAdherenceSecsQty + dbo.ASSOC_CALL_SUMS_VIEW.
SumOfOutOfAdherenceSecsQty)
AS Adherence, dbo.ASSOC_CALL_SUMS_VIEW.
SumOfTalkHoldAvailable / dbo.ASSOC_CALL_SUMS_VIEW.SumOfLogOnSecsQty AS
Efficiency,
[sumofTotalHandleTime ] / [sumofNchQty ] AS AHT,
dbo.ASSOC_CALL_SUMS_VIEW.SumOfHoldSecsQty / dbo.
ASSOC_CALL_SUMS_VIEW.SumOfNchQty AS HT,
dbo.ASSOC_CALL_SUMS_VIEW.SumOfLogOnSecsQty / dbo.
ASSOC_CALL_SUMS_VIEW.SumOfSchdOpenSecsQty AS [Shift Account],
dbo.ASSOC_CALL_SUMS_VIEW.SumOfNchQty AS [Calls Taken],
dbo.Goals.Adherence AS [Adherence Goal], dbo.Goals.Efficiency AS [Efficiency
Goal],
dbo.Goals.AHT AS [AHT Goal], dbo.Goals.[Shift Account]
AS [SA Goal]
FROM dbo.ASSOC_CALL_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_CALL_SUMS_VIEW.Segment = dbo.
Goals.Segment
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CALL_SUMS_VIEW
AS
SELECT TOP 10000 Team, Segment, Associate, SUM(NchQty) AS SumOfNchQty,
SUM(SchdOpenSecsQty) AS SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
GROUP BY Team, Associate, Segment, [Date]
ORDER BY Team, Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_DISC_STATS_VIEW
AS
SELECT TOP 1000 dbo.ASSOC_CSI_DISC_SUMS_VIEW.Team, dbo.
ASSOC_CSI_DISC_SUMS_VIEW.Associate,
NULLIF (dbo.ASSOC_CSI_DISC_SUMS_VIEW.SumOfH1, 0) /
NULLIF (dbo.ASSOC_CSI_DISC_SUMS_VIEW.[SumOfLast Touch], 0) AS Disconnect,
dbo.Goals.[Disconnect] AS [Disconnect Goal]
FROM dbo.ASSOC_CSI_DISC_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_CSI_DISC_SUMS_VIEW.Segment = dbo.
Goals.Segment
ORDER BY dbo.ASSOC_CSI_DISC_SUMS_VIEW.Team, dbo.ASSOC_CSI_DISC_SUMS_VIEW.
Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_DISC_SUMS_VIEW
AS
SELECT TOP 5000 dbo.[CSI-Disconnect].Team, dbo.[National Call Stats].
Segment, dbo.[CSI-Disconnect].Associate, SUM(dbo.[CSI-Disconnect].H1) AS
SumOfH1,
SUM(dbo.[CSI-Disconnect].[Last Touch]) AS [SumOfLast
Touch]
FROM dbo.[CSI-Disconnect] RIGHT OUTER JOIN
dbo.[National Call Stats] ON dbo.[CSI-Disconnect].[Date]
= dbo.[National Call Stats].[Date] AND
dbo.[CSI-Disconnect].Site = dbo.[National Call Stats].
Site AND dbo.[CSI-Disconnect].Associate = dbo.[National Call Stats].Associat
e
WHERE (dbo.[CSI-Disconnect].[Date] >= '5/1/2006') AND (dbo.[CSI-
Disconnect].[Date] <= '5/31/2006') AND (dbo.[CSI-Disconnect].Site = 'Dallas')
AND
(dbo.[CSI-Disconnect].Team = 'Abbott')
GROUP BY dbo.[CSI-Disconnect].Team, dbo.[CSI-Disconnect].Associate, dbo.
[National Call Stats].Segment
HAVING (dbo.[CSI-Disconnect].Team <> N' ')
ORDER BY dbo.[CSI-Disconnect].Team, dbo.[CSI-Disconnect].Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_STATS_VIEW
AS
SELECT TOP 10000 dbo.ASSOC_CSI_SUMS_VIEW.Team, dbo.ASSOC_CSI_SUMS_VIEW.
Associate,
([Sumofextsatwt ] + dbo.ASSOC_CSI_SUMS_VIEW.
SumOfVerSatWt) / dbo.ASSOC_CSI_SUMS_VIEW.[SumOfAdjusted Weight] AS [CSI
Overall],
dbo.ASSOC_CSI_SUMS_VIEW.[CountOfSurv Strt Tm] AS
Surveys, dbo.Goals.[Top 2 Box] AS [CSI Goal]
FROM dbo.ASSOC_CSI_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_CSI_SUMS_VIEW.Segment = dbo.
Goals.Segment
ORDER BY dbo.ASSOC_CSI_SUMS_VIEW.Team, dbo.ASSOC_CSI_SUMS_VIEW.Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_SUMS_VIEW
AS
SELECT TOP 10000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
[SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt, SUM(VerSatWt)
AS SumOfVerSatWt, COUNT([Surv Strt Tm]) AS [CountOfSurv
Strt Tm], [Date]
FROM dbo.CSI
WHERE ([Date] >= ' 5/1/2006') AND ([Date] <= '5/31/2006') AND (Site =
'Dallas') AND (Team = 'Abbott')
GROUP BY Team, Associate, Segment, [Date]
ORDER BY Team, Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Tracy McKibben wrote:
>Need to see your CREATE VIEW statements too, it's not clear what you
>meant by "sums up 4 fields" and "calculate the results".
>
>[quoted text clipped - 3 lines]
Message posted via http://www.webservertalk.com|||Yes, this is DDL. And **everything** in it is wrong. If I put this in
one of my books, people would think I made it up
1. Why is everything NULL-able? That means you cannot ever have a key.
Tables by definition must have keys.
2. What careful research lead to you to discover the amazing fact
almost everything in your universe is not only NULL-able but also
NVARCHAR (255)? In your world, I use the Diamond Sutra in Chinese for
a ZIP code. And someone will. These tables will accumulate crap the
minute they go into production.
3. Why do you have data element names with spaces or special characters
in them? My personal favorite is "# FLOAT NULL". Octothrope!?
That is the sort of thing you in an old beginning programming book
where they give you a list of illegal names. We allowed quoted
identifiers in SQL so that future reserved words could be used in
current code. It was never, never meant for doing display work in the
database. What you have found is a great way to prevent readability,
portability and maintainability of code!
4. So many of the names are vague and violate ISO-11179 rules. For
team given "team NVARCHAR (255) NULL" is this the insanely lonh
name of the team? An identifier of some kind, like a URL? Their
location? Their mascot? Their total body weight? What?
5. I see you like SMALLDATETIME and MONEY to prevent portability. Good
SQL do not use proprietary data types. Do you know about MONEY math
errors and deprecation in SQL Server? Are you following GAAP rules? EU
rules?
6. You use FLOAT for a lot of things. First of all, floating point
math is only used in scientific systems for a good reason - rounding
errors and lack of precision would destroy the integrity of a
commercial system. For example, why is ssn_count a FLOAT and not an
integer? Give me an example.
7. You have an identifier called "part_surv_id FLOAT NULL" which is
a real nightmare. Identifiers have to be unique and therefore they
have to be an exact data type.
8. You have a "surv_strt_tm DATETIME", which looks like "survey
start time" but no ending time. The nature of time is that it is a
continuum, so you must have a half-open interval with an explicit or
implicit ending time.
9. Tables repeat columns over and over, but there is no RI among any of
them.
I strongly recommend that you start over and get more help than you are
going to find in a newsgroup. You clearly have no idea what a data
mdoel is, much less how to program in SQL (asking what DDL is kinda
like walking into a Mosque and asking "Hey, you on your knees, which
way is Mecca?" ).
Someone here with a lot of time on their hands might be able to get you
a kludge to make you think that you have been helped, but it will
simply destroy you in the long run.|||Celko,
First I appreciate you taking your valuable time (no tone here) to respond i
n
the detail that you did. I obviously am not as advanced as you. I took this
over as an ACCESS upgrade. I can not start over. I can't answer why about th
e
NULLs & Float or your other comments. I'm learning on the fly. I will not
waste anymore of your time, and look elsewhere for an answer.
--CELKO-- wrote:
>Yes, this is DDL. And **everything** in it is wrong. If I put this in
>one of my books, people would think I made it up
>1. Why is everything NULL-able? That means you cannot ever have a key.
> Tables by definition must have keys.
>2. What careful research lead to you to discover the amazing fact
>almost everything in your universe is not only NULL-able but also
>NVARCHAR (255)? In your world, I use the Diamond Sutra in Chinese for
>a ZIP code. And someone will. These tables will accumulate crap the
>minute they go into production.
>3. Why do you have data element names with spaces or special characters
>in them? My personal favorite is "# FLOAT NULL". Octothrope!?
>That is the sort of thing you in an old beginning programming book
>where they give you a list of illegal names. We allowed quoted
>identifiers in SQL so that future reserved words could be used in
>current code. It was never, never meant for doing display work in the
>database. What you have found is a great way to prevent readability,
>portability and maintainability of code!
>4. So many of the names are vague and violate ISO-11179 rules. For
>team given "team NVARCHAR (255) NULL" is this the insanely lonh
>name of the team? An identifier of some kind, like a URL? Their
>location? Their mascot? Their total body weight? What?
>5. I see you like SMALLDATETIME and MONEY to prevent portability. Good
>SQL do not use proprietary data types. Do you know about MONEY math
>errors and deprecation in SQL Server? Are you following GAAP rules? EU
>rules?
>6. You use FLOAT for a lot of things. First of all, floating point
>math is only used in scientific systems for a good reason - rounding
>errors and lack of precision would destroy the integrity of a
>commercial system. For example, why is ssn_count a FLOAT and not an
>integer? Give me an example.
>7. You have an identifier called "part_surv_id FLOAT NULL" which is
>a real nightmare. Identifiers have to be unique and therefore they
>have to be an exact data type.
>8. You have a "surv_strt_tm DATETIME", which looks like "survey
>start time" but no ending time. The nature of time is that it is a
>continuum, so you must have a half-open interval with an explicit or
>implicit ending time.
>9. Tables repeat columns over and over, but there is no RI among any of
>them.
>I strongly recommend that you start over and get more help than you are
>going to find in a newsgroup. You clearly have no idea what a data
>mdoel is, much less how to program in SQL (asking what DDL is kinda
>like walking into a Mosque and asking "Hey, you on your knees, which
>way is Mecca?" ).
>Someone here with a lot of time on their hands might be able to get you
>a kludge to make you think that you have been helped, but it will
>simply destroy you in the long run.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Multiple updates and Identity fields

I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What is\are the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.

TIA,

Barkingdog

Are you using SQL2k5 ? THen you can use the new OUTPUT clause. Otherwise you should have a look on SCOPE_IDENTITY() which should fit your needs. But the new OUTPUT function should be more straight forward in your case.

HTH, JEns K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

I looked up OUTPUT in sql 2k5 BOL: Here's an example that I found:

>>>
Copy Code
USE AdventureWorks;
GO
DECLARE @.MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @.MyTableVar
VALUES (N'Operator error', GETDATE());

--1. Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @.MyTableVar;

--2. Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
>>>>

So with OUTPUT the ScrapReasonID from @.MyTableVar will hold the identity of the row just inserted. Is this correct?

What is the difference in output between queries 1 and 2? Shouldn't they be the same?

TIA,

Barkingdog

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple Table Queries

I am having propblems creating a multiple table query for a stored procedure.

Here is what I have..

db1 = transaction database with multipke fields keyed on transid, and membid

db2 = transaction legal details keyed on transid & legalid

db3 = member details keyed on membid & officeid

db4 = office details keyed on officeid & membid

What I am after is a query which will result in ALL details being returned for a particular transaction

ie. transaction detail, legal detail, member detail & office detail...

please helpsomething like...

select db1.*, db2.*, db3.*, db4.*
from db1
join db2 on db1.transid = db2.transid
join db3 on db1.membid = db3.membid
join db4 on db3.officeid = db4.officeid|||Thanks, worked like a charm.

Much Obliged.. have more queries to do for this project, I am sure I will have more posts for you.

multiple stored procedure...or 1 dynamic procedure?

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
JustinHi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH|||Hi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH

Hi,

Yeah I did have a quick read of the dynamic sql like Jesse provided...and was able to solve the previous problem with it...as i was a bit under the pump when i read it, i did not read the entire thing, and missed anything relating different amounts of dynamic columns...

You have pretty much answered what i was asking anyway, so it looks like im in for a long session of writing stored procs...

Thanks Again,
Justin