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:
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
SELECT 456, 'O', 2
SELECT 789, 'O', 3
SELECT 101, 'I', 4
SELECT 102, 'O', 5) A
SELECT * FROM #UserAccount
SELECT * INTO #BillingTransactions FROM (
SELECT 1 AS BillingTransactionKey, 1 AS BillingAccountKey, 111 AS BillingCode
SELECT 2, 2, 222
SELECT 3, 2, 333
SELECT 4, 2, 444
SELECT 5, 3, 111
SELECT 6, 3, 555
SELECT 7, 3, 666
SELECT 8, 3, 222
SELECT 9, 5, 333
SELECT 10, 5, 777)A
SELECT * FROM #BillingTransactions
SELECT * INTO #BillingCodeRank FROM(
SELECT 111 AS BillingCode, 7 AS [Rank]
SELECT 222, 6
SELECT 333, 5
SELECT 444, 4
SELECT 555, 3
SELECT 666, 2
SELECT 777, 1
SELECT 888, 1
SELECT 999, 3
SELECT 101, 5)A
SELECT * FROM #BillingCodeRank
WITH cteOrderedBillingCodes As
ROW_NUMBER() OVER (PARTITIONBY T.BillingAccountKey ORDERBY R.Rank DESC) As RN
#BillingTransactions As T
INNERJOIN #BillingCodeRank As R
ON R.BillingCode = T.BillingCode
#UserAccount As A
LEFTJOIN cteOrderedBillingCodes As C
ON C.BillingAccountKey = A.BillingAccountKey
And C.RN = 1WHERE
A.AccountType = 'O'
I am stuck in the middle of a project. I am having an issue with adding the data source in"Entity Data Model Wizard"
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.
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...
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....-
ALTER PROCEDURE [dbo].[usp_Update_Service_Program_Detail]
@ModifiedBy nvarchar(50),<br />
select'220''Col' -- Test statement
--Logic is here
But when I am running the stored procedure as below:
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.
"There is already enough hatred in the world lets spread love, compassion and affection."
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.
"There is already enough hatred in the world lets spread love, compassion and affection."
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?
What is the biggest pet peeve(s) DBAs have with developers?
I don't know but I have never understood why there are problems. It really boils down to egos. A lot of people in this industry have too big of an ego and can't work well with other people.
I've been lucky enough to never work for such a big company that there were different roles like that. I've been lucky enough to always be a dba and a developer.
I have heard stories where developers do not have enough access to the DB so it makes it hard to do their job when they are always waiting on some dba to write code for them. But I'm not sure what complaints the dbas have about devs.
Everyone is born right handed. Only the strongest overcome it.
This is a little late, but here go some of my annoyances.
1) The sky is falling emails. Just because your application isn't working, doesn't mean the entire environment is down.
2) CCing everyone in management because you are having an issue...
3) Failure to plan, just because you failed to plan out a release doesn't constitute an emergency on my part.
Not communicating releases and complaining when something doesn't work right.
[The DW team, loves to chew up 100s of gigs of space in a single release without communicating then screaming about when the drives fill up]
4) Trying to use the Database server as a File server, send mail server, or other random stuff. No, the database server is there to serve up data, not be some Swiss utility knife.
5) Not keeping in mind my time, and the totality of the environment. Please start out with where your problem is at.
I have 1700 databases spread across Dev, Test, PreProd and Production. No I don't just know where you are having an issue at, if I was psychic I would come up with the winning lotto numbers so I no longer need to work.
6) Security rules are not just setup randomly, they are often established by controls put in place by auditors.
I am accountable to those rules, so everyone needs to follow them.
Things like demanding SA rights to a db instance, no, there is nothing at the Database Server level you need to change. Things have been setup that way for a reason.
7) Not taking advice on a designs that are just terrible.
Just because it worked with 10 rows of data doesn't mean it will scale with 10M rows. Complaining about the server isn't going to solve your issue.
Setting aside the petty grips, a lot of the issues boil down to communication and a willingness to collaborate. My time gets stretched in a lot of directions, I go from meetings with network, server, dev, auditors, management, vendors and yes even end users. It’s not always possible for me to just drop everything to help someone out.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
Last Visit: 16-Nov-18 15:10 Last Update: 16-Nov-18 15:10