SUM Of All Calculated Columms On A Joined Child Table
Apr 9, 2008
Hi,
This is probably straightforward but I'm not sure how to approach this problem.
I have 2 tables, one parent and a child.
The child table has a Quantity and a Price field and also a FK to the parent table.
I want the parent table to display its own row data along with the Total Cost of the child rows in the child table.
So it needs to multiply the price by the quantity but also do a sum of this calculated column.
To start off with I tried SUM( TOTALCOST ) and it didn't like it.
I've got an idea I might need to create a View but was wondering what is the standard way of doing something like this?
E.g.
Parent Table
PK TOTALCOST
Row 1 50
Row 2 5
Child Table
PK FK Quantity Price TOTALCOST
Row 1 1 2 10 20
Row 2 1 3 10 30
Row 3 2 1 5 5
Thanks.
View 13 Replies
ADVERTISEMENT
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Sep 15, 2015
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
View 4 Replies
View Related
Aug 11, 2015
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.
View 2 Replies
View Related
Sep 8, 2006
Hello
Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?
insert #MyTempTable
exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3
JOIN dbo.OtherTable...
I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.
I just can't figure it out
Many Thanks!
Worf
View 2 Replies
View Related
Jul 29, 2014
table1
id value
1 11
2 12
3 13
4 14
table2
id1 value1
1 21
2 22
1 31
2 32
in need output as follows
id value id1 value1
1 11 1 21
2 12 2 22
3 13 null null
4 14 null null
1 11 1 31
2 12 2 32
3 13 null null
4 14 null null
View 9 Replies
View Related
Dec 20, 2006
How do I delete from a joined table....
I want to delete all the records from transaction AND order table where status is > 4
sumfing like:
DELETE ORDERS.*, TRANSACTIONS.*
from ORDERS
INNER JOIN TRANSACTIONS
where ORDERS.order_no = TRANSACTIONS.order_no
and status > 4
but doesnt seem 2 like dat???
THANKS :)
View 5 Replies
View Related
Nov 9, 2005
I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.
Any thoughts, words of warning?
Thanks
Mark
View 17 Replies
View Related
Apr 20, 2007
Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part.
Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it .
Any ideas on how to attack this?
View 3 Replies
View Related
Dec 15, 2005
Guys - sorry for the long post - hope it's clear
DDL/DML below
I want to update the startdate column (for all rows) so that when period is 0 then the new value
is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the
enddate column for the row of the previous column (with the same freq)
ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq
create table #periods (period int , startdate datetime , [enddate] datetime , freq int)
insert #periods ( period , startdate , enddate , freq)
select 0 , '01-Jan-1900' , '31-Jan-2001' , 1
union all
select 1 , '01-Jan-1900' , '28-Feb-2001' , 1
union all
select 2 , '01-Jan-1900' , '31-Mar-2001' , 1
union all
select 3 , '01-Jan-1900' , '30-Apr-2001' , 1
union all
select 4 , '01-Jan-1900' , '31-May-2001' , 1
union all
select 0 , '01-Jan-1900' , '31-Jan-2002' , 3
union all
select 1 , '01-Jan-1900' , '28-Feb-2002' , 3
union all
select 2 , '01-Jan-1900' , '31-Mar-2002' , 3
union all
select 3 , '01-Jan-1900' , '30-Apr-2002' , 3
union all
select 4 , '01-Jan-1900' , '31-May-2002' , 3
select * from #periods -- gives
periodstartendfreq
01900-01-01 00:00:00.0002001-01-31 00:00:00.0001
11900-01-01 00:00:00.0002001-02-28 00:00:00.0001
21900-01-01 00:00:00.0002001-03-31 00:00:00.0001
31900-01-01 00:00:00.0002001-04-30 00:00:00.0001
41900-01-01 00:00:00.0002001-05-31 00:00:00.0001
01900-01-01 00:00:00.0002002-01-31 00:00:00.0003
11900-01-01 00:00:00.0002002-02-28 00:00:00.0003
21900-01-01 00:00:00.0002002-03-31 00:00:00.0003
31900-01-01 00:00:00.0002002-04-30 00:00:00.0003
41900-01-01 00:00:00.0002002-05-31 00:00:00.0003
Desired result
select * from #periods -- gives
periodstartendfreq
02000-12-01 00:00:00.0002001-01-31 00:00:00.0001
12001-01-31 00:00:00.0002001-02-28 00:00:00.0001
22001-02-28 00:00:00.0002001-03-31 00:00:00.0001
32001-03-31 00:00:00.0002001-04-30 00:00:00.0001
42001-04-30 00:00:00.0002001-05-31 00:00:00.0001
02000-12-01 00:00:00.0002002-01-31 00:00:00.0003
12002-01-31 00:00:00.0002002-02-28 00:00:00.0003
22002-02-28 00:00:00.0002002-03-31 00:00:00.0003
32002-03-31 00:00:00.0002002-04-30 00:00:00.0003
42002-04-30 00:00:00.0002002-05-31 00:00:00.0003
/*
I know I need a case statement to test for column 0 and to join the table on itself and have put something together
but it fails for column 0 and updates to NULL - I think it must be to do with the join ??
This is what I've got so far :
UPDATE PA1
SET
PA1.Startdate =
CASE
WHEN PA2.period = 0
THEN
2000-12-01 00:00:00.000
ELSE
PA1.Enddate
END
FROM #periods AS PA1
JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1
Any help gratefully received as always
*/
View 5 Replies
View Related
Jul 24, 2012
I'm looking to pull back some results but not include those that have a value in a column that matches a value in a temp table column.
The below code returns all the values rather than excluding those that are in the temp table.
Code:
------Folder Differences-----
--Create Temp Table--
CREATE TABLE #fdiff
(foldername VARCHAR(255))
SELECT
replace(
replace(foldername,'[Template] ','')
[Code] .....
View 2 Replies
View Related
May 2, 2015
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
USE [tt]
GO
/****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistry](
[id] [int] IDENTITY(1,1) NOT NULL,
[code]....
Desired results:
purchasedamountidregistryidtitleogimgdescriptionURLamountpricecreatedate
[URL]
View 9 Replies
View Related
Oct 10, 2005
Hello everyone, I have a query problem.I'll put it like this. There is a 'publishers' table, and there is a'titles' table. Publishers publish titles (of course). Now I want to make aquery (in MS SQL Server) that would return the last title published by everyof the publishers. Quite clear situation. But I can't make it work.If I use inner join (which I should, because I need data from both tables)then I get a result showing all publishers and all titles. What I want toget is all publishers, and only their last title, so I don't have more thanone line for the same publisher, and this line should contain publisherdetails and last title details.I tried using DISTINCT, but it works on a whole resultant row rather then acolumn, and since rows are all distnict (because they also contain columnsfrom titles) this didn't help me.What I can do is (in my application) first get a list of publishers, andthen loop through them selecting only the last title belonging to eachpublisher. I want to see if there is a way to accomplish the same thing withan SQL query (or maybe a stored procedure, view, or whatever). Anything ispossible, as long as it stays within SQL server and doesn't rely on theclient application.Of course, both 'publishers' and 'titles' tables have a primary key('publisherID', and 'titleID'), and 'titles' has a 'publisherID' columnwhich relates titles with publishers.Help :)
View 4 Replies
View Related
Jun 25, 2007
Hi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?
thanks in advance.
View 4 Replies
View Related
Feb 6, 2008
Can someone please help me with a better way to format the following query. This works, but I know it is hidious.
Code Snippet
select
convert(varchar, processed, 101) as Date,
count(o.id) as [# Orders],
sum(distinct a.runnercount) as [# Runners],
sum(o.total) as [$ Gross],
sum(o.fee) as [$ Fees],
(sum(o.total)-sum(o.fee)) as [$ Net]
from [order] o join (select convert(varchar,processed,101) as date, count(*) as runnercount from orderitem oi inner join [order] o on o.id = oi.orderid where typeofextraid = 4 group by convert(varchar,processed,101)) a on convert(varchar,processed,101) = a.date
where statemented = @statemented
group by convert(varchar, processed, 101)
2 tables: Order and OrderItem. I need the sum of a specific record type from the OrderItem table along with all the other aggregate columns group by day.
Thanks.
Nathan
View 1 Replies
View Related
Mar 11, 2008
I have two tables - products and productpropertyvalue
I need to select multiple fields from the productpropertyvalue as it corresponds to the product id. The script I am using is
select a.id, a.productname, a.siteprice,
b.propertyvalue
from product a, productpropertyvalue b
where a.id = b.productid and propertyid=590
This allows me to extract only 1 propertyid. I need to make it add 3 other columns for propertyid=589, 617, 615
Any help solving this problem would be appreciated - thanx!
Ron
View 14 Replies
View Related
May 2, 2015
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
View 0 Replies
View Related
Sep 13, 2007
I am trying to get a SQL statement to work with both Access 2000 and SQL Server 2000.
The statement that works in SQL Server is:
---
UPDATE [myTable2]
SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0
FROM [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]
WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---
(names have been changed to protect the innocent)
The statement that works in Access is:
---
UPDATE [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]
SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0
WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---
It seems that neither will accept the other format. Can anyone suggest how I can rearrange the statement so that it works in both?
View 2 Replies
View Related
Jul 7, 2015
We currently have an application whereby the user will come along sign up add some information about them, choose what interests / hobbies they're in to and then click save.After completing the sigh up they're able to search for other individuals within locations (look at this functionality as a dating site) We currently have a stored procedure whereby when the user clicks Advance Search they fill in all the fields they filled in when signing up and when they click search we return all users that match the data the user has selected. This part is working correctly.
The issue im having is extending this stored procedure to take a user defined table type which has two columns UserId and ActivityId both columns are nullable.This user defined table type is referred to in two places, when the user signs up and when they do an advance search.When the user does an advance search he / she can choose a list of hobbies / interests this will then be passed in to the database and I will join on my User_Activities table to find the users that have the Activities passed in.When I join on this table I get duplicate records purely because one user may have 5 - 10 activities depending on how active the individual is. My current stored procedure looks like this
@Ambition int = null,
@Body int = null,
@Diet int = null,
@Drinking int = null,
@Ethnicity int = null,
@Exercise int = null,
@HeightFrom int = null,
[code]...
So my question is how can I select all users that match the criteria passed in, as well as finding the users that match the activities passed in (the user can either match all of them or a minimum of 1). Also my User_Activities has two columns UserId and ActivityId identical to the user defined table type
View 9 Replies
View Related
Jul 29, 2015
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
'Mailing1, Mailing2, Mailing3'
View 9 Replies
View Related
Sep 29, 2015
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
View 5 Replies
View Related
May 2, 2015
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
View 0 Replies
View Related
Jun 11, 2006
Here's my problem:I'm developing an ASP.NET 2.0 application that has a user select one or moreauto manufacturers from a listbox ("lstMakes"). Once they do this, anotherlistbox ("lstModels") should be filled with all matching models made by theselected manufacturers. If lstMakes was not multi-select, I'd have noproblem. But in this case it has to be multi-select. The database is SQLServer 2005 which does not accept arrays as parameters. I've been told thatI have to create an XML document that will act as a filtered Manufacturerstable that I can join to my Models table in my stored procedure. Problem isI don't have the foggiest idea how to do this. I've seen some examples thatjust leave me scratching my head so I was hoping someone could look at whatI'm trying to do and show me how to do this. Thanks!
View 2 Replies
View Related
Nov 18, 2014
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
View 17 Replies
View Related
Jul 23, 2005
Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken
View 4 Replies
View Related
Nov 7, 2006
I'm using SQL server 200
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
---------------------------------------------------------------------------------------------------------------------------------
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
View 1 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Sep 21, 2007
Hello,
I'm attempting to explore the functionality of SSIS and all the available data flow transformations, and I'm trying to learn how to use the tool correctly.
How would someone update a table with data which is calculated from that same table.
This is a made up example. Lets say I have a table:
CREATE TABLE [dbo].[tst_source](
[tst_key] [int] IDENTITY(1,1) NOT NULL,
[object] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color_count] [int] NULL
)
I would like to populate the field color_count with the count of items listed with that color.
Data:
tst_key object color color_count
1, chair blue NULL
2, table black NULL
3 lamp black NULL
----
So the last column for 1st rec value would be 1, and for 2nd and 3rd, would be two.
tst_key object color color_count
1, chair blue 1
2, table black 2
3 lamp black 2
-- With T-SQL I would use the following code:
update tst_source
set color_count = res.cnt
from tst_source
Inner join (select count(tst_key) cnt,color
from tst_source
group by color) res
ON res.color = tst_source.color
But I would like to use the Data Transformations. Can someone explain in great detail which Data Transformation tasks I would need to use?
Thank you.
View 11 Replies
View Related
Aug 19, 2006
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies
View Related
Jan 28, 2012
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
View 5 Replies
View Related
Jul 20, 2005
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies
View Related
May 12, 2015
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
View 10 Replies
View Related
