|
In the output clause you can specify the $action column that tells you whether it was an insert, update or a delete. And since you only have updates when matched...
|
|
|
|
|
Any help to figure out this query is highly appreciated.
I have three tables. (Scripts to load test data copied below).
First table #UserAccounts has UserAccounts, AccountType and BillingAccountKey.
Second table #BillingTransactions had Billingtransactionkey(surrogate key), BillingAccountKey and BillingCode.
UserAccount and BillingAccountKey has 1 to 1 relationship.
Third Table #BillingCodeRank has BillingCode and Rank.
I want to see the billing code associated with each UserAccount (with account type 'O') that has the max rank.
Example:UserAccount 456 has BillingAccountKey =2 and has three billing codes (222,333,444) having rank 6,5,4 recpectively.
The result should show BillingCode 222 associated with UserAccount 456 because codee 222 has the max rank among the three codes.
The final result from the test data would be as shown below:
Result:
UserAccount AccountType BillingAccountKey BillingCode Rank
456 O 2 222 6
789 O 3 111 7
102 O 5 333 5
--Scripts to load test data
SELECT * INTO #UserAccount FROM (
SELECT 123 AS UserAccounts, 'I' AS AccountType, 1 AS BillingAccountKey
UNION ALL
SELECT 456, 'O', 2
UNION ALL
SELECT 789, 'O', 3
UNION ALL
SELECT 101, 'I', 4
UNION ALL
SELECT 102, 'O', 5) A
SELECT * FROM #UserAccount
--================================================================
SELECT * INTO #BillingTransactions FROM (
SELECT 1 AS BillingTransactionKey, 1 AS BillingAccountKey, 111 AS BillingCode
UNION ALL
SELECT 2, 2, 222
UNION ALL
SELECT 3, 2, 333
UNION ALL
SELECT 4, 2, 444
UNION ALL
SELECT 5, 3, 111
UNION ALL
SELECT 6, 3, 555
UNION ALL
SELECT 7, 3, 666
UNION ALL
SELECT 8, 3, 222
UNION ALL
SELECT 9, 5, 333
UNION ALL
SELECT 10, 5, 777)A
SELECT * FROM #BillingTransactions
--===============================================
SELECT * INTO #BillingCodeRank FROM(
SELECT 111 AS BillingCode, 7 AS [Rank]
UNION ALL
SELECT 222, 6
UNION ALL
SELECT 333, 5
UNION ALL
SELECT 444, 4
UNION ALL
SELECT 555, 3
UNION ALL
SELECT 666, 2
UNION ALL
SELECT 777, 1
UNION ALL
SELECT 888, 1
UNION ALL
SELECT 999, 3
UNION ALL
SELECT 101, 5)A
SELECT * FROM #BillingCodeRank
Thank you for you help.
|
|
|
|
|
Something like this should work:
WITH cteOrderedBillingCodes As
(
SELECT
T.BillingAccountKey,
T.BillingCode,
R.Rank,
ROW_NUMBER() OVER (PARTITION BY T.BillingAccountKey ORDER BY R.Rank DESC) As RN
FROM
#BillingTransactions As T
INNER JOIN #BillingCodeRank As R
ON R.BillingCode = T.BillingCode
)
SELECT
A.UserAccount,
A.AccountType,
A.BillingAccountKey,
C.BillingCode,
C.Rank
FROM
#UserAccount As A
LEFT JOIN cteOrderedBillingCodes As C
ON C.BillingAccountKey = A.BillingAccountKey
And C.RN = 1
WHERE
A.AccountType = 'O'
;
ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
sorry its very complex to me.
|
|
|
|
|
I am stuck in the middle of a project. I am having an issue with adding the data source in "Entity Data Model Wizard"
Navigation Path:::::
Solution Explorer : Models(Folder): Add : New Item : ADO.NET Entity Data Model : EF Designer From Database : New Connection
Now here in "New Connection" I want to connect my postgresql database using Postgresql Datasource but I have only SQL Server datasource option there, Anybody know how to deal with this ERROR..??
Note: I have added Npgsql, entityframework5.Npgsql through NuGet.
|
|
|
|
|
|
Message Closed
modified 27-Feb-18 8:31am.
|
|
|
|
|
You've already had an answer posted.
And remember, the people who answer questions here are volunteers. Nobody is being paid to give you an answer in a fixed amount of time.
Try engaging with the people who are trying to help you, rather than flouting the rules of the site.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Message Closed
modified 27-Feb-18 8:31am.
|
|
|
|
|
So talk to the person who posted it!
And I say again: the people who answer questions here are volunteers. Have a little patience.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have posted it .. But I dont find any option to delete it..
|
|
|
|
|
Hello everybody,
i'm writing to ask an advice.
In my Access db, I've many tables but the most important table is named "Products".
The Primary Key for this table is the "Product Code".
For every product I've to save different fields. Some fields are descriptives fields that rarely change while other fields are prices fields that change different times per day.
Which is the best way to represent a product? I have these 3 ideas:
1) TABLE1 where I insert only descriptives fields and has the product code as primary key. TABLE2, where i save only prices, that has a relation one to one with the first table and share the primary key with it.
2) TABLE1 where I insert descriptives fields and an integer field at the end which is in relation with TABLE2 where I save prices. In this case the primary key of table2 will be an identity field. Also in this case it will be a one to one relation. I'm sceptic for this solution because I always add and remove products from table1 so, after 1 year it will be possible that I will have 10.000 product in my database but the primary key of TABLE2 will be a very big number like 50.000.
3) Only TABLE1 where I save every fields both desctiptives and prices fields.
Which is the best solution in your opinion? In this moment i've adopted the first solution...
Thank
Giacomo
|
|
|
|
|
It depends.
If a product can only ever have a single price, with no price-breaks, no customer-specific prices, etc., then store that price in the products table.
If you need a more complicated pricing structure, then store the prices in a separate table, with a one-to-many relationship between products and prices.
For example:
Prices:
ID (PK, Identity)
ProductCode (FK to products)
FromDate (optional)
ToDate (optional)
MinimumQuantity (optional)
MaximumQuantity (optional)
CustomerPriceGroup (optional)
Price
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much Richard....I think i will use your solution that is similar to my idea number 2 because, i forgot to explain, I will have to store different prices for every products....
|
|
|
|
|
If the ProduceCode is user editable then it should never be used as a foreign or primary key, use the ProductID an identity field.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When I'll insert a product I'll also insert the ProductCode but then I never won't be able to modify it. I'll only be able to remove the product
|
|
|
|
|
JackMisani wrote: Which is the best solution in your opinion?
A normalized model. Rules for normalization can be found on the wiki 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
JackMisani wrote: that rarely change while other fields are prices fields that change different times per day.
What happens if someone wants to return something that they bought yesterday?
If they order it online two weeks ago and it ships today what price are they charged?
|
|
|
|
|
My products will be only stocks.
Every stocks has always a trading code that i'll use as PK. For every stocks i need to store 2 kinds of fields:
1) Descriptive fields that changes rarely like: description, isin code, country, expiration
2) Prices fields like: last price, minimun, maximum
When I launch my app i will load my db data in my memory and i'll start to download prices from servers. I'll do calculation between the prices i've in memory and the price I receive from server and only when i'll click "Save" button i'll write data in my db....-
|
|
|
|
|
Hi All,
I have a stored procedure as below,
ALTER PROCEDURE [dbo].[usp_Update_Service_Program_Detail]
@PK_Service_Program_County_RateCap_Detail int,
@Unit_Type int,
@Rate_Cap decimal(15,2),
----@County int,
@ModifiedBy nvarchar(50),<br />
@ModifiedOn datetime=getdate
AS
BEGIN
select '220' 'Col' -- Test statement
--Logic is here
END
But when I am running the stored procedure as below:
exec usp_Update_Service_Program_Detail @PK_Service_Program_County_RateCap_Detail= 53196434
, @Unit_Type=1
, @Rate_Cap=100
, @ModifiedBy='aaleemmo'
, @ModifiedOn=getdate
I am getting the following error:
Error converting data type nvarchar to datetime at @ModifiedOn=getdate
Why is it an error, when getdate returns datetime why should I convert it into Datetime again, when I try it, its giving me error. Can anybody please help me what is the way to execute it and I am getting similar problem from my C# code also, can anybody please help me in this regards.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: @ModifiedOn datetime=getdate
Try GetDate()
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Abdul Aleem.
i find your error and please try this code.
create PROCEDURE [dbo].[usp_Update_Service_Program_Detail]
@PK_Service_Program_County_RateCap_Detail int,
@Unit_Type int,
@Rate_Cap decimal(15,2),
----@County int,
@ModifiedBy nvarchar(50),
@ModifiedOn datetime=getdate
AS
BEGIN
select '220' 'Col' -- Test statement
--Logic is here
END
exec usp_Update_Service_Program_Detail @PK_Service_Program_County_RateCap_Detail= 53196434, @Unit_Type=1
, @Rate_Cap=100
, @ModifiedBy='aaleemmo'
, @ModifiedOn=''
|
|
|
|
|
Hi,
I have been given an already existing application to modify, there is a need to modify an existing Table in the Database also, so I added a new Table which has association of the old tables to create a many to many relationship with unique combination. Like for example if Unit Cost of a Service and Program was same for all Cities earlier now I have added a Table which has ServiceProgramCostId, CityId and Unit Cost, which is ServiceProgramCityCostId as PK.
Now the problem came with communication, I am trying to explain my manager that this same City impact will have in other tables as well, its better to create a combination for LineItemsServiceProgramCityCost instead of LineItemsServiceProgramCost and BusinessRulesServiceProgramCityCost instead of BusinessRulesServiceProgramCost as well, as they are now dependent upon the City.
The other thing is the Previous Developer was deleting the orphaned records from ServiceProgramCost table so with new Combination ServiceProgramCityCost I am not able to delete the records from LineItemsServiceProgramCost, BusinessRulesServiceProgramCost tables like earlier, because the deletion now means from BusinessRulesServiceProgramCityCost table, I am becoming nervous about deleting records from LineItemsServiceProgramCost, BusinessRulesServiceProgramCost tables.
My question is, is it not better to use the new city relationship in those two tables is better or is it better to go ahead with old approach but limiting the deletes? The previous developer who worked is just saying simple what's there? But not suggesting me or maybe saying with Manager that I am not able to work or something. Our Business Analyst is on maternity leave.
Any idea would be greatly helpful - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello,
I am working on a presentation on how developers could improve their relationship with their dbas. I am trying to receive as much feedback as possible. What is the biggest pet peeve(s) DBAs have with developers?
|
|
|
|
|
And you are asking this in a developer forum!
You might be better served trying SQL Server Central and the Oracle forums, you are going to get a higher DBA content there.
Personally I think we (developers) need to understand the restrictions under which a good DBA works.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
