Derived Table Problem

Jul 20, 2005

I've created this:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

and I get this:

ProjectID Registrants Submissions
--------- ----------- -----------
adv_104699 99
adv_1047185 185
adv_110566 66
boh_107134 34

Instead, I want this:

ProjectID Registrants Submissions
--------- ----------- -----------
adv_104699 14
adv_1047185 82
adv_110566 17
boh_107134 12

The "ProjectID" and "Submissions" columns are produced when I run the
derived 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


ADVERTISEMENT

Advantage Of Derived Table Over Temp Table

Jan 22, 2008

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

View 8 Replies View Related

Help With Derived Table

Mar 10, 2006

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

View 2 Replies View Related

Inserting A Derived Value Into A Table

Feb 28, 2008

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

View 2 Replies View Related

Derived Table Not Updatable

Oct 6, 2004

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!

View 4 Replies View Related

Updating Using Derived Table Err:

May 12, 2008

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

View 1 Replies View Related

Ranking A Derived Table?

Jul 30, 2013

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].....

View 2 Replies View Related

Do I Need A Derived Table For This Situation

Feb 1, 2007

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

View 2 Replies View Related

Cross Tab Or Derived Table

Jul 20, 2005

Hi 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 Related

Derived Table Alternative

Feb 7, 2007

I 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

View 10 Replies View Related

Questions On Derived Table

Jul 31, 2007

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.

View 5 Replies View Related

How To Use A Stored Procedure As A Derived Table

Nov 20, 2001

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.

View 1 Replies View Related

Use Result Set Returned By Sp As Derived Table

Jun 11, 2006

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

MAX Of TOP In Derived Table Behaving Strange

Feb 19, 2008



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.

View 6 Replies View Related

Searching A List Of Tables, Derived From Another Table

Sep 21, 2005

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 Related

How To Update A Table From Derived Column Results

Apr 6, 2007

Dear friends, can anyone please guide me on how to update table with results from derived column tool?

thanks,

View 9 Replies View Related

Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?

Aug 1, 2007

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.

View 1 Replies View Related

Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?

Aug 1, 2007



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.

View 5 Replies View Related

SQL Server 2012 :: Derived Table To Extract Value Of Account For Comparison

Nov 2, 2014

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.

View 7 Replies View Related

SQL Gets Corrupted With 'WITH' Command And Rubbish When Editing Query Containing Derived Table

Jan 31, 2008

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

View 17 Replies View Related

Request For Confirmation Of SQL 2005 Optimiser Bug With Derived Table And Convert()

Jul 23, 2007

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.

View 5 Replies View Related

Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task

Jul 22, 2015

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 Related

Using Foreach Loop To Parse An SQL Table - Lookup And Create Derived Column

Oct 25, 2007

I 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?

View 1 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related

Derived Tables

Feb 2, 2004

OK....I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?


Thanks,
Trey

View 1 Replies View Related

Derived Column

May 6, 2008

Hi, wondering if anyone can help me. I currently have a field that has a date and time in it in the format dd/mm/yyyy hh:mm:ss. Ideally I would like to get rid of the time part of it altogether but for it to still be recognised as a date as opposed to a string. However, I've been told that this is not possible in 2005, is this true? If this is the case, what would be the best way to set the time to 00:00:00 after the date for all records on that field?

View 1 Replies View Related

Derived Tables

Dec 12, 2006

Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.

I presently have the following code

SELECT ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname,

ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID,

ProSolution.dbo.Offering.Code

FROM ProSolution.dbo.StudentDetail INNER JOIN

ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN

ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID

WHERE (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND

(ProSolution.dbo.Enrolment.CompletionStatusID = '1')




The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.

567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS

Can anyone give us any guidance please?

Thanks
Chip

View 16 Replies View Related

Derived Fields

Jul 23, 2005

Guys, this is what I want to do. How can I do this ?Select employid,Gross=( Select sum (uprtrxam) from Fleet..upr30300 where pyrlrtyp=1),DedofWages=( Select sum (uprtrxam) from Fleet..upr30300 wherepyrltyp=2),Gross-DedofWagesfrom FLEET..UPR00100The problem is the third field (Gross-DedofWages). It says Invalidcolumn. Any ideas ?Thanks, Girish

View 3 Replies View Related

Derived Column

Jan 30, 2007

I have two columns made up of 4 digits numbers eg col1 1234, col2 9876

I want to create a derived column so i get a eight digit column, eg col3 12349876

I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression

Thanks

Robbie

View 20 Replies View Related

Derived Column

Dec 3, 2007

I am trying to transfer data from SQL Server 2005 table to another SQL Table. In the source table there is a field called Region [nvarchar(max)]. The values for these fields will be like APAC-China, NA-Racine, etc., i.e Region followed by the country name seperated by - symbol. I want the destination tablre with 2 fields Region[nvarchar(max)] and Country [nvarchar(max)]. I am using a Derived Column task to achieve the same.

Can anyone please help me out in doing this data conversion?

Thanks


View 4 Replies View Related

Group By Derived Value?

Dec 11, 2007

Is there a way to group by a derived value? For example, in the query below, is there a way to group the results by the AgeBracket field? I'd like to see adults (0) in one grouping and children (1) in the other.

SELECT AgeBracket = CASE WHEN dbo.GetAge(DOB, GETDATE()) > 18 THEN 0 ELSE 1 END, COUNT(*)
FROM tableX

Thanks!

View 1 Replies View Related

Derived Column

Nov 2, 2007

Hi,

I am doing a task which will get all data based on several base and crosswork tables. I used stored procedure to get the data, but the next step will be using derived column to massaging data, and to load to the destination database. Is there a way to do that since I must generate data on the fly?

Thanks,

Megan

View 13 Replies View Related

Derived Column Help

Jul 16, 2007

Hi Guys,



How can i put in a derived column the value of 3 columns? I've tried these:



[MyId]+[Paper1]

"[MyId]+[Paper1]"



but no luck. How can i put it right?



Thanks



Gemma

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved