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.
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)
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.
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)
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.
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
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:
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...
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.
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?
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:
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.
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.
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.
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.
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.
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?
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
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
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
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
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.
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
