Jump to content


  • Posts

  • Joined

  • Last visited

NewEra13's Achievements


Newbie (1/7)



  1. NewEra13

    Combined 2 select

    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 !
  2. NewEra13

    Update tabble

    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 ?
  3. 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)
  4. How to update a table based on a select statement. Please take 5 minutes to see attached file. sql.txt
  5. Actually i found it: SELECT TOP 1 ColumnID FROM DB.FactTableName ORDER BY ColumnID DESC; Thanks for the fast replys.
  6. 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.
  7. 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 !
  • Create New...