Count From Detail Table
Feb 20, 2008
Hi
I've two tables
TableA
id
name
TableB
id
tableA_id
desc
oper
As you can see tableA is a master and tableB is a detail table where
we can have many records for each related tableA record.
I need to get all records for tableA with a count on some oper of
tableB.
I suppose I can got it with a join or a subselect but I don't use SQL
often so I'm getting crazy with this stupid query...
Could somebody help ?
Thanks in advance
C
View 2 Replies
ADVERTISEMENT
Sep 14, 2007
I am listing detail transaction lines in a table sorted by account and order number.
the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
View 4 Replies
View Related
Jul 20, 2005
Hi,-- ddl/*create table #tmp (col1 int);insert into #tmp values(1);insert into #tmp values(2);insert into #tmp values(3);create table #tmpChild (col1 int, fkCol int, Num int);insert into #tmpChild values(1,1,3);insert into #tmpChild values(2,1,2);insert into #tmpChild values(3,2,1);*/-- get parent and child data (outer join)select *from #tmp t Left Outer JOIN #tmpChild tCON t.col1 = tC.fkCol-- resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 31 2 1 22 3 2 13 NULL NULL NULL(4 row(s) affected)*/-- desired resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 3-- eleminate next row, want only Max(Num) row from the child tbl withsame FK (parentID)-- 1 2 1 22 3 2 13 NULL NULL NULL*/In other words, desired results is as follows:/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 32 3 2 13 NULL NULL NULL*/How to accomplish this task? ENV: MS SQL Server 2000TIA
View 1 Replies
View Related
Dec 13, 2005
I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)
View 3 Replies
View Related
Jan 28, 1999
Hi,
I have a master and a detail table as defined :
Master : tmpOrder
Column_name Type
------------------------------ ------------------------------
OrderID int
Date DateTime
Detail : tmpOrderItem
Column_name Type
------------------------------ ------------------------------
OrderID int
Seq int
Qty float
Amount float
How can I get the master fields and sum of qty and amount in one query. I need the following result set
OrderID Date TotalQty TotalAmount
-------------- ---------- ---------- --------------
Thank you in advance
Allex
View 1 Replies
View Related
Sep 20, 2007
Hi Experts,
I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain
Insertion in Master Table..................................... A
Insertion in Detail Table........................................B
Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.
So please can any one tell me a good approach for this.
View 6 Replies
View Related
Oct 31, 2006
Hi all,
I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at.
There is around 10 detail records per 1 main record. and the main table is around 7 mil. records
Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.
OR
I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
:confused:
Suggestions
(My attitude is going towards the trigger way but 100 percent sure)
View 4 Replies
View Related
Jan 25, 2012
I have a detail table of comments. I need to grab the fist 50 characters from the comment field from this table. The kick is that I need to get the comments from all of the records in the detail table, as the comments might be less than 50 characters, so i need to pull the comments from the other comment records that apply to the master table record.
right now i have the comments in a join to the master table, but this is causing a few records to be returned....
I guess what i am looking for is a way to grab the comment value from the first record of the detail table.
View 1 Replies
View Related
Jun 24, 2008
Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements
In Master table have 5 columns; In Master Table stored all records.
NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20
In second Table store those records who are selected and stored in 2nd table.
NameAppidFunctionCodeFunNameSubFunCode
Data810Summary0
Data820View0
Data830&View0
Ad630Mbl20
Our requirements we use one query,
In query fetch total 5 rows. and output show like this
NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y
Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha
View 3 Replies
View Related
Oct 26, 2007
Two questions:
1) I am unable to get a detail row of my table to suppress. I am using:
=Iif(IsNothing(Fields!Address2.Value), True, False)
2) I am not able to get an If/Then statement to work. I am able to use the followingl; however, ideally I would want to have multiple if/else statements:
=Iif(Fields!Taxschid.Value = "CUST", 0, Fields!Selling_Price.Value*.08)
Tx
Les
View 7 Replies
View Related
Oct 1, 2007
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
View 1 Replies
View Related
Sep 13, 2007
I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.
The file looks something like this:
FINNEY,RONNIE 0001005420
Mon 09/03 700a HOL 8.00
Tue 09/04 630a*E 326p 8.50 8.50
Wed 09/05 645a 330p 8.00 16.50
Thu 09/06 639a 2.40 18.90
HALL,MARK 0001005601
Mon 09/03 700a HOL 8.00
Tue 09/04 608a*E 257p 8.40 8.40
Wed 09/05 601a*E 259p 8.50 16.90
Thu 09/06 606a*E 3.30 20.20
JONES,WILLA JEAN 0001005702
Mon 09/03 700a HOL 8.00
Tue 09/04 556a*E 326p 9.10 9.10
Wed 09/05 600a*E 328p 9.00 18.10
Thu 09/06 554a*E 3.50 21.60
So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?
View 1 Replies
View Related
Sep 4, 2014
CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))
The data which will be going into this table is from two table which have a 1 to many relationship:
Here is the 1 side and data:
CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')
[Code] ....
Here is the Many side and data:
CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')
[Code] ....
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014
View 1 Replies
View Related
Mar 14, 2015
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
View 1 Replies
View Related
Jan 15, 2008
I have detail table like
date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8
Result View
=======
Item_id A B O T Total
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.
Smartsys
View 4 Replies
View Related
Mar 25, 2008
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Jun 2, 2015
I need to insert data into Header & Detail table. As shown in the below xml,
RecordID is identity-column and incremented by 1, after new record is saved into Header table. Need to assign the same recordID for the detail also.
Expecting output should be like as shown below:
How can we accomplish this requirement.
View 8 Replies
View Related
Jul 23, 2005
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies
View Related
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Jul 15, 2013
I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .
test99: tableName countRows
t1 20
t2 30
t3 25
View 2 Replies
View Related
Jun 11, 2007
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
View 16 Replies
View Related
Jan 17, 2006
I have 2 tables:
TableA:
Name
UserA
UserB
UserC
Table B:
Name Data
UserA xxx
UserB asdasd
UserB ewrsad
UserC dsafasc
UserA sdf
UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count
UserA 2
UserB 3
UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
View 5 Replies
View Related
Aug 6, 2006
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 5 Replies
View Related
May 23, 2007
Hello all,
I would like to use MDX to identify "contracts" which are in a table A and not in a table B, and count them to have a total per "company".
It should be something like:
Contract / Flag contracts not in table B (1=yes, 0=not)
---------------------------------------------------------------
Contract 1 1
Contract 2 0
Contract 3 1
Contract 4 1
Contract 5 0
Contract 6 0
TOTAL 3
So far I know how to distinguish at contract level which records belong to table A and not table B, but I do not have the total for the company.
In SQL it should be something like that:
SELECT Company,count(*)
FROM tableA
left join tableB
on tableA.Contract = tableB.Contract
WHERE tableB.Contract is null
GROUP BY Company
Thanks,
Guillaume
View 3 Replies
View Related
Oct 11, 2000
I noticed a web query was running unreasonably slow, so I investigated
further and found out that query was searching on the primary key field
of a table, but instead of using the index seek, it acutually did a index
scan. I don't know why that query didn't use PK index, since the search
was on PK itself.
There is an other SQL server which has the exact same table(same data), I ran
that query against that table, and the query did what it suppose to do,
it used PK index, and was able to retrieve data very fast.
So I ran following commands and see if the systerm would use PK when
running the query:
(recreate all index(all non clusted))
sp_updatestat (all indexes)
sp_recompile (table)
But it didn't work, PK index was still not been used when that query was run.
Although I were able to slove the problem by force the web query to
use PK index, but I really don't like to force useage on any index.
So I thought there might be some corruptions in that table.
I didn't a dbcc showcontig, it showed that Page Density is 95%
and Frag is only 3.4% I also didn't some manual calculation
and only found very very small amount of unused space.
But when I ran sp_spaceused against that table,
and found out that the ROW COUNT for that table is 0!
but if I did select count (*), it returned the correct row count.
So I ran following commands
dbcc checkdb --- no error reported
dbcc checktable --- no error reported
dbcc updateusage (table) --- still got 0 row cound for sp_spaceused
(0 rows count in sysindexes for that table)
Question 1: Why PK index was not been used when the "where" of that query
was PK itself.
Question 2: Why that table has 0 row count?
Question 3: How can I fix the row count problem without introducing a
clustered index on that table or rebuilding that table?
Thanks guys!
Xiao
ps. that table has about 400,000 rows, and size of 20 MB
View 1 Replies
View Related
Jul 19, 2005
select singer.id,singer.singer,count(albums.id) from singer inner join albums on albums.singer=singer.id where singer.country='England' order by singer.singer
doesnt't work because of the count(albums.id)
how can I do ?
View 3 Replies
View Related
Feb 23, 2007
hi all,
how do we sum up all the quantity in table A, and save the summary quantity into table B? can we do it live? i mean evtime quantities in table A change, the total sum in table B also change accordingly.. pliz help... thanks in advance
~~~Focus on problem, not solution~~~
View 4 Replies
View Related
Mar 15, 2007
Is it possible to directly get the row count of a different table in a RS2005 report?
I'd like to use this row count number in a calculation in another table...
This would be a lot cleaner than creating a new dataset to provide me with this value.
Thanks!
View 1 Replies
View Related
Mar 28, 2008
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
View 7 Replies
View Related
Mar 13, 2006
HelloI have this stored procedure:SELECT @openissue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND DateDIFF( day, TOpenIssue .DateStart, GETDATE() ) >= 0SELECT @oiclosed=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code = 'CLOSED' and Project=@project SELECT @oipastdue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND TOpenIssue .DateEnd<getdate()Is there away to optimize it in only one select statement?Thanks
View 2 Replies
View Related
Nov 3, 2006
Okay I wanted to get this to work right so I am wondering what I am doing wrong here.
"SELECT COUNT(A.Status) AS TOTAL FROM tts_tickets A,tts_reporters B WHERE A.Ref_Reporter_ID="123"AND A.Status=1"
I need to count the status options but only where from table b is the ID equal to the ID in question.
How do I do this?
View 3 Replies
View Related
Feb 3, 2005
I am inserting data from one table into another. To simplify:
Table 1 has columns:
1.A, 1.B, 1.C, 1.D
Table 2 has columns
2.A, 2.B, 2.C, 2.D, 2.Tag
where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
Not a count of how many records for each combination.
Therefore, if Table 1 is
x - x - x - x
x - x - x - x
x - x - x - x
x - x - x - y
x - x - x - z
then, table 2 is
x - x - x - x - 1
x - x - x - y - 2
x - x - x - z - 3
anyone help me w/ the sql on that ?
i was thinking of putting a variable in the select statement, but was unable to increment it.
thanks
View 14 Replies
View Related
