I want to rank the below table by Sum [Pts} Desc AS Position?
,Sum(Games) as Pl
,SUM(Win) as W
,SUM(Draw) as D
,SUM(Loss) as L
,SUM(F) as F
,SUM(A) as A
,SUM(Total) as Tot
,ROUND(AVG(GPG),2) AS GPG
[code].....
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
Hi, I wanna know is there any advantage of perf gain when using Derived Tables over Temp Tables, advice me which one is better to use. Can I create Indexes and Insert/Update records into Derived Tables.
-Senthil
I have 3 tables TableA , TableB and TableC
I have to update tableA with the value of TableB by checking A.Field=B.Field
and TABLEB as condition that B.Field should exists in tableC
Update A Set A.Field=B.Field From tableA A
(select B.Field From TableB B where B.Field=A.Field and B.Field in (select C.Field
From tableC C))
How to do it correctly.
Thanks in advance
Hi,
I have built a database for a university project, but am having trouble with the SQL syntax for inserting derived data from a calculation into a table. At present my SQL is;
INSERT INTO MemberPayment (TotalCharge) VALUES ( [Total] )
SELECT ((MileageHistory.MileageUrban * Vehicle.EmissionsPerGramUrban) * 0.05) + ((MileageHistory.MileageCountry * Vehicle.EmissionsPerGramUrban) * 0.05) AS 'Total'
FROM [NeuCar].[dbo].[MileageHistory] JOIN [NeuCar].[dbo].[Vehicle]
ON MileageHistory.Registration = Vehicle.Registration
JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName
WHERE Member.UserName = 'wenger1' AND (MileageHistory.[Date] >= CURRENT_TIMESTAMP - 30);
Would anybody be able to point out where I am going wrong? I would very much appreciate any advice,
Kind regards,
Chima
I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.
Thanks in advance!
Below is my sql, i am getting error msg "Incorrect sytax near 'Votes'."
Can anyone tell me what im doin wrong?
UPDATE #TblExposure
SET E.Unity =
(
SELECT
-- DealId ,DocketId,
CASE Participant2
WHEN nullTHEN 'NA'
ELSE
CASE WHEN Participant2-Vote2 =0 THEN 'Yes'
ELSE 'No'
END
END AS Unanimous1
FROM #tblVoting
) Votes
FROM #tblExposure E INNER JOIN Votes
ONVotes.Dealid = E.Dealid
AND Votes.DocketId =E.DocketId
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress
FROm
Table1 where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table
Cananyone help me with this query please
I've created this:SELECTc.ProjectID,Count(c.ID) as 'Registrants',Count(dt.Hits) as 'Submissions'FROMCME_TBL cJOIN(SELECT ProjectID, Count(*) as Hits FROM CME_TBLWHERE evalDate Is Not NULL OR testDate Is Not NULLGROUP BY ProjectID) dtON c.ProjectID = dt.ProjectIDGROUP BYc.ProjectIDORDER BYc.ProjectIDand I get this:ProjectID Registrants Submissions--------- ----------- -----------adv_104699 99adv_1047185 185adv_110566 66boh_107134 34Instead, I want this:ProjectID Registrants Submissions--------- ----------- -----------adv_104699 14adv_1047185 82adv_110566 17boh_107134 12The "ProjectID" and "Submissions" columns are produced when I run thederived table (dt, above) as a standalone query. By the same token,the "Project ID" and "Registrants" columns are produced when I run the"outer" query, above.Am I on the right track here?TIA,-- Bill
View 6 Replies View RelatedHi all,I have a table in this formatcolname1 colname2 colname3col1data1 col2data1 col3data1col1data2 col2data2 col3data2col1data3 col2data3 col3data3col1data4 col2data4 col3data4I want to display it in this formatcolname1 col1data1 col1data2 col1data3 col1data4colname2 col2data1 col2data2 col2data3 col2data4colname3 col3data1 col3data2 col3data3 col3data4Basically rotate it through 90 degrees clockwise and flip it over :)I'm pretty sure this is done by using a crosstab query and or aderived table or temp table. The problem is I use a crosstab query toget the original data into the first format. I've been strugglingtrying to get the ouptput into the second format for over a day nowand just can't seem to get it to work. Can anyone give me any pointerson the general solution to this?I hope this makes sense. Thanks for the help.
View 4 Replies View RelatedI have an application that has two different database backends, one is SQL Server Compact Edition and the other is SQL Server. The reason is because the application may run at home on one of our sales agent's computers or here in the office.
I have a query that uses a derived table and works just fine in SQL Server, however when I run it in the compact edtion (having the exact same table structures) it will not run. My question is...does the Compact Edtion or the Mobile Edition allow derived tables. If not is there a way to work around this? I will happily give an example if it will help.
Thank you,
Adam
I have some questions on derived table. Below is my situation.
Table order, there are 40 columns and it has indexes for productID and addressID
Table product, there are 15 columns and it has index for productID
Table address, there are 20 columns and it has index for addressID
Query A
-------
select C.address, C.area, B.productname, B.category, A.qty, A.price
from order A
join product B
on A.productID = B.productID
join address C
on A.addressID = C.addressID
Query B
-------
select C.address, C.area, B.productname, B.category, A.qty, A.price
from (select qty, price, productID, addressID from order) A
join (select productID, productname, category from product) B
on A.productID = B.productID
join (select addressID, address, area from address) C
on A.addressID = C.addressID
Will I loss the indexing when using derived table?
Why query B performance is much better than query A?
Thanks.
Hi All,
Can any one give me code sample which uses the recordset returned by the execution of a stored procedure as a derived table.
Thanks,
venkat.
Hi,I got another question here:I want to use query like this:SELECT * FROM (sp_lock AS T) WHERE objectID = ...The purpose is that I want to query the result set returned by sp_lockusing derived table, but it doesn't work. Why?Thanks,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 2 Replies View Related
Hi,
I am working for my client on SQL 2005 Enterprise & observed a strange behaviour. Consider a table(EMPTABLE) as below:
PK, INT
VARCHAR
EMPID
EMPNAME
1
A
2
B
3
C
4
D
5
E
6
F
7
G
8
H
9
I
10
J
Now, I wanted the 5th highest EMPID from 2 from the table. Now, we know that the SQL
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 should (from all other possibilities) return me the desired value as the last row of the output table. I tried to get this in a derived table & get the MAX value i.e.
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2
) der
But this returns me 10, when I would've thought 7 was the expected answer
I remodified my query as mentioned below to strangely get 7 as the result...
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 ORDER BY EMPID ASC
) der
How did the ORDER BY hep it???
Also, if I use
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >2
) der
I get the same result without having to specify the ORDER BY in my derived TABLE...
I tried looking for similar threads. But nothing could explain this to me...
If anyone knows what I am not able to understand, please let me know.
Relative SQL newbie here......this is probably easy, but....Lets say I have a table (MainTable) that stores a list of input table names,a primary key (PKey), and a field called "Configured" for each one. Each ofthese input tables also contain a field called "Configured", which is set totrue or false in another process based on an OrderNumber. (So an order'sinputs are stored in several input tables, and the MainTable is a summarytable that shows which input tables have been configured for any givenOrderNumber).What I need to do is open each input table, and look for a record containinga specific OrderNumber and where Configured=true. If a record is found, Ineed to update the Configured field for that table in the MainTable, andthen move on to the next sub-table.The way I'm doing it now is with simple SQL and loops. Here is the basiccode (ASP):*****************************************OrderNumber = "562613" ' the current order that is being processed' reset all configured flagssql = "UPDATE MainTable SET Configured = 0"conn.execute sql, , &H00000080' get list of all tablenamessql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>'---'"set rsTableNames = conn.execute(sql)while not rsTableNames.eof' test each input table for configured flagsql = "SELECT Configured FROM " & rsTableNames("InputTableName")& _" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &"'"set rs = conn.execute(sql)If Not rs.EOF Then' update the main tablesql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &rsTableNames("PrimaryKey") & "'"conn.execute sql, , &H00000080end ifset rs = nothingrsTableNames.movenextwend*****************************************There has to be a faster way.. I think.... maybe something that could bewritten as a stored procedure? I use a similar technique in a couple ofother places and it's a bit of a performance hit, especially as the numberof input tables grows.TIA!Calan
View 6 Replies View RelatedDear friends, can anyone please guide me on how to update table with results from derived column tool?
thanks,
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
When my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable. We have tried this with various existing and new, simple queries, all to no avail. We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005. I can edit the query in 2003 on my laptop with no ill effects.
Help!
Karen
I have discovered what looks like a bug in the optimiser. I've posted it at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288243 but I wonder if any of you with SQL 2005 RTM, 2005 SP1 or 2008 CTP could confirm when this was introduced and whether it is still an issue?
Code Snippet
-- Bug report
-- 2007/07/19
-- Alasdair Cunningham-Smith
-- alasdair at acs-solutions dot co dot uk
set nocount on
go
-- example date in in British date format
set dateformat dmy
go
use tempdb
go
create table foo( bar varchar( 30 ) not null )
go
insert into foo( bar ) values ( 'fishy' )
insert into foo( bar ) values ( '19/07/2007' )
go
-- this works fine in all versions - only valid dates are passed to the convert function
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
go
-- this works on SQL 2000, but fails on SQL 2005 SP2 (I've not tried other SPs of SQL 2005):
-- Msg 295, Level 16, State 3, Line 2
-- Conversion failed when converting character string to smalldatetime data type.
--
-- I believe the query is rewritten as if the derived table query contained
-- "and convert( smalldatetime, bar, 103 ) < getdate()"
-- which would expose the convert to the invalid data
select
*
from
(
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
-- Workaround:
-- Use a case statement to protect the convert operator from the invalid data
select
*
from
(
select
case when bar like '__/__/____' then
convert( smalldatetime, bar, 103 )
else
null
end as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
drop table foo
go
The workaround I discovered is simple but ugly. I invite your comments...
alasdair.
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
View 4 Replies View RelatedI am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.
Ideas?
I was wondering what kind of SQL statement I would need to do the following:For example I have a table that has an ID (vID) column, product id (vPID) and a timestamp (vTime). What I essentially want to do is go through this table and find the top 10 instances of any vPID. I will illsutrate it below.- vID vPID vTime 1 25 101012323 2 25 101012323 3 09 101012323 4 25 101012323 5 25 101012323 6 11 101012323 7 25 101012323 8 10 101012323 9 10 101012323 10 25 101012323 11 25 101012323 12 25 101012323 13 25 101012323 14 11 101012323 15 25 101012323Now what I want to do is return the top vPID instances from highest to lowest so my output should return 25, 11, 10 etc.I hope what I'm asking made sense to you guys, basically im just looking for the top 10 instances of a any product id (vPID) Thanks.
View 3 Replies View RelatedHello All,
I am working on a ranking scenario for a client and I am stumped. Here's the situation...I have an aggregated table that has number of items sold for a salesperson for a given pay period. My client wants to the top 10 salespeople in a report. If two sales people tie, then they should have the same rank, and then the next salesperson in the ranking should have the appropriate number in the sort order. I am trying to figure out how to accomplish with the rank column below.
SalespersonID AmountSold Rank
1 5 1
2 5 1
3 4 3
4 4 3
5 2 5
I need the data in my table to look like the example and I am having a difficult time figuring out how. Any help would be appreciated.
Thanks,
Terry
My small number of brain cells prohibits me from figuring this out. I've got a table
ProviderName EOPCodeTotalDenied
Memorial Hosp66$4,598
Memorial HospA3$2,133
Memorial Hosp22$1,111
Memorial Hosp20$912
Memorial Hosp39$4,321
Memorial Hospb2$62
Parkdate Hospb2$6,251
Parkdate Hosp66$2,346
Parkdate Hosp22$1,252
Parkdate Hosp20$4,056
Stone HospV33$8,059
I need to output this table as below (with a denial rank for each hospital -- DESC sort on TotalDenied).
I'm stuck on how to get the DenialRank column to work.
ProviderName EOPCodeTotalDeniedDenialRank
Memorial Hosp66$4,5981
Memorial Hosp39$4,3212
Memorial HospA3$2,1333
Memorial Hosp22$1,1114
Memorial Hosp20$9125
Memorial Hospb2$626
Parkdate Hospb2$6,2511
Parkdate Hosp20$4,0562
Parkdate Hosp66$2,3463
Parkdate Hosp22$1,2524
Stone HospV33$8,0591
Appreciate the help.
Ray
Hi guys
I'm trying to figure out how to rank records. From the highest value to the lowest. Can you please help?
Hello all
Im working on a table in SQL server 2005 (sp2). A piece of that table is as below. I need a SQL code that adds a new column called rank that ranks starting from 0 by Tire_ID by date asc. For example, for Tire_ID = 41317 and 41350 the result will look like in the second table:
original:
Date
Tire_ID
Truck
Position
Area
10/5/1999 0:00
41317
T110
1
BAG
2/5/2000 0:00
41317
T109
1
BAG
5/9/2000 0:00
41317
T103
6
BAG
11/14/1999 0:00
41350
T109
2
BAG
4/19/2000 0:00
41350
T105
4
BAG
7/5/2000 0:00
41350
T104
4
BAG
9/3/2000 1:00
41350
T104
3
BAG
9/17/2000 1:00
41350
T115
5
BAG
Result( new)
Date
Tire_ID
Truck
Position
Area
rank
10/5/1999 0:00
41317
T110
1
BAG
0
2/5/2000 0:00
41317
T109
1
BAG
1
5/9/2000 0:00
41317
T103
6
BAG
2
11/14/1999 0:00
41350
T109
2
BAG
0
4/19/2000 0:00
41350
T105
4
BAG
1
7/5/2000 0:00
41350
T104
4
BAG
2
9/3/2000 1:00
41350
T104
3
BAG
3
9/17/2000 1:00
41350
T115
5
BAG
4
Thanks in advance
I have a sales report showing Customer, tons shipped, and sales amount with two parameters: year and month. I want to show the ranking by month by both tons and sales. It is easy enough to sort the results by one of the measures and compute a line number but I can't seem to get the rank function to work to compute the ranking by two different measures.
Any ideas?
Thanks.
I have a table that has several dozen account numbers. In the same table, each account number has several thousand control numbers (apparently the person who designed the DB never heard of Dr. Codd).
Anyway, I want to get the last five created control numbers for each account number. I have a "CreatedDateTime" field that I can use to order the control numbers by creation. I can use the Top and Rank functions to get the last five for an individual account number but I cannot seem to produce a solution with the last five for each account number.
Anyone have any ideas?
Thanks.
I'm building a standard search function for this project im working on and I wanted to know how could I do the following; Basically want I am using the standard "LIKE" function to search keywords but I also want to rank the returns based on how many matches. For example if someone uses the keywords "dog run park fast" and I have a couple of records that have certain amount combinations with these words, how would I return the one that has the most combinations to the least. If one record had "dog, run,park", another has "dog run" and the last would have "fast". How would I being those results in that order.Thanks for any help.
View 3 Replies View RelatedThe problem is :
I have table :
--------------------------------
...User ... Speed(minute)...
Maria 0.8
John 0.5
Alan 0.8
Anne 2.0
Smith 1.0
Kate 1.5
Evan 1.5
--------------------------------
---> I wanna set ranking for them such as:
Rank.....User.....Speed(minutes)
1 John 0.5
2 Maria 0.8
2 Alan 0.8
4 Smith 1.0
5 Kate 1.5
5 Evan 1.5
7 Anne 2.0
---------------------------------
Anyone can tell me how?
Thanks much