NewEra13

Hello i have these 2 metrics that works good so far:

-- --------------------------------------------
-- Nb Unique Accounts 
-- -------------------------------------------
select count(distinct(O.user_id))
from DB.order O LEFT JOIN DB.orderCompleted OrC
    ON O.id  = OrC.order_id
where reason in ('2') 

-- --------------------------------------------
-- Nb Accounts that are eighter deactivated or invalid
-- -------------------------------------------
select count(distinct(O.user_id))
from DB.order O JOIN DB.orderCompleted OrC
    ON O.id  = OrC.order_id
where reason (0,1) 

QUESTION#1: We now need a 3 rd metric that will caclculate the % of nb of accounts that are eighter deactivated or invalid. How could we acheive that ?

QUESTION#2: We would also like to only have 1 big SQL performing all 3 small queries above in order to then use this unioned or joined big sql into a Business Intelligence Reporting Tool that allows to use sql coding in order to display it in a crossTab 

Thanks to all !

Thanks

Hello,

1- We have some recors that exists in our "datamart" table but doesn't exists anymore in our source system tables.

2- Therefore, we want to hardcore "Unknown" or 0 values into some of those record's columns (column1 and column2 for example)

3- This is our 2 tables beeing involved:

Datamart.TableA

SourceSystem.TableB

4- We want to UPDATE our Datamart.TableA table as mentionned above in order to insert "Unknown" or 0 values on the columns of the recors that NO longer exists in our SourceSystem.TableB

5- This is how our 2 tables are linked:

Datamart.TableA.Enumber = SourceSystem.TableB.Eid

and

Datamart.TableA.AccountNo = SourceSystem.TableB.AccountID

QUESTION:

I sort of have an idea that i will probably need to use a NOT EXISTS somewhere in my code as well as

Datamart.TableA.Eunmber = SourceSystem.TableB.Eid

and

Datamart.TableA.AccountNo = SourceSystem.TableB.AccountID

But, i'm not quite sure how to formulate all this. Any advice please ?

Hello again,

By the way could you help me make this update work as for i'm getting the error message:

Incorrect syntax near ')'

UPDATE dbo.Part

SET SupplierShortName = NationalSupplier.ShortName,

SupplierLongName = NationalSupplier.LongName

from(SELECT *

FROM dbo.Part

JOIN dbo.NationalSupplier

ON Part.SupplierNumber = NationalSupplier.Number

AND (ISNULL(Part.SupplierShortName,'') <> ISNULL(NationalSupplier.ShortName,'')

OR ISNULL(Part.SupplierLongName,'') <> ISNULL(NationalSupplier.LongName,''))

LEFT OUTER JOIN dbo.NationalPart

ON Part.NationalPartID = NationalPart.NationalPartID

WHERE Part.DWCreationEntityID = 1

AND NationalPart.NationalPartID is NULL)

Thanks !

How to update a table based on a select statement. Please take 5 minutes to see attached file.

sql.txt

Actually i found it:

SELECT

TOP 1 ColumnID FROM DB.FactTableName

ORDER

BY ColumnID DESC;

Thanks for the fast replys.

Thanks. By the way, there a ways to know what's the next IDColumn value available in a table ?

I mean i want to know what IDColumn valye i can enter when creating my new row that is not already present in my big table.

Hello,

I need to fake data in our development table by creating new row with a new EntityID = 100 but that could have all other data as the same as for EntityID = 99 for example.

Msg 2627, Level 14, State 1, Line 2

Violation of PRIMARY KEY constraint 'XPK_FactTable'. Cannot insert duplicate key in object 'DataBase.FactTable'.

The statement has been terminated.

Thanks !