Tranact SQL : Get Total Of Detail Table

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


ADVERTISEMENT

How Can I Sum By Group Total Instead Of By Each Detail Line

May 16, 2008



I hope someone can help with this problem. I'm new to SSRS 2005, having used Crystal Reports for several years

Our General Ledger Accounts consist of Dept Project AccountCode Each section is 4 digits.
22000000XXXX

I have the following sections in the report table:

Detail -- amount per transaction per account

Detail group -- sum of the transactions per account

Dept Project group €“sum of all the transactions for the Dept Project


DATE AMOUNT Budget
Detail 222100001234 4/5/2008 $100 $20,000
222100001234 4/10/2008 $200 $20,000

Detail group 222100001234 $300 $20,000

Detail 222100005678 4/12/2008 $400 $40,000
222100005678 4/11/2008 $500 $40,000

Detail group 222100005678 $900 $40,000

DeptProjectGroup 22210000 $1200 $60,000


As shown above, I sum the amounts per account in the detail group, and the amounts per DeptProject in the DeptProject Group. This works fine for the amounts.

I also placed the budget amount per account in the detail group section. This works also. But when I try to total the budget amount per DeptProjectGroup, I get the total for each transaction instead. In Crystal I would have it total by group, but I don't know how to do that in Reporting Services.

Any help would be much appreciated.

Thanks,

Sue

View 6 Replies View Related

Selecting Detail Based On A Sum Of The Detail Lines

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

Count From Detail Table

Feb 20, 2008

HiI've two tablesTableAidnameTableBidtableA_iddescoperAs you can see tableA is a master and tableB is a detail table wherewe can have many records for each related tableA record.I need to get all records for tableA with a count on some oper oftableB.I suppose I can got it with a join or a subselect but I don't use SQLoften so I'm getting crazy with this stupid query...Could somebody help ?Thanks in advanceC

View 2 Replies View Related

Get One Row From Detail/child Table

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

Charting Table Detail And Sum

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

Master/Detail Table Insertion.

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

Main And Detail Table Amount Calculations

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

Retrieving Only 50 Chars From Detail Table Of Comments

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

In Master And Detail Table Fetch Record.

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

Conditional Suppression Of Table Detail Row And If/then Statement

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

Insert Master/Detail Table Using Store Procedure!!!

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

Transform Header And Detail Flat File Into One Table

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

SQL Server 2012 :: Combining Master And Detail Records In New Table

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

SQL Server 2008 :: Insert Trigger On Master Detail Table

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

Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column

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

SQL Master/Detail (Master Query Based On Detail Values)

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

Integration Services :: Insert Data Into Header And Detail Table From XML Through SSIS Package

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

SQL Call To Count The Total Rows In Table B For Each User In Table A

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

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Aggregated Subquery - Sum Total Trips And Total Values As Separate Columns By Day

Feb 26, 2014

Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....

My code is as follows;

Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,

[Code] .....

View 3 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View 4 Replies View Related

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For some reason my Add Total is grey out, when i tried to add grand total using some expression.

I have two row & two column groups?

Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image

I'm using iff condition in my expression.. 

View 15 Replies View Related

SQL Server 2008 :: Pulling Daily Total From Cumulative Total

Jun 28, 2015

I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.

CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')

View 9 Replies View Related

Any Way To Show A Group Detail Header Row Once For Each Group In A Table?

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

Total Row Size In A Table

Jun 18, 2001

Hi,
I am getting this error when I try to run a script to create a table. The error reads as below

The total row size (12488) for table exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

Can somebody tell me what I have done wrong?
thanks
zoey

View 1 Replies View Related

Running Total In Table

Apr 28, 2004

Hi, i have an assets table with a purchase cost column and an updates table with an update cost column.

is there any way for me to add a total cost column to the assets table that will automagically add the purchase cost to the sum of the updates costs for each asset so that when queried, the total cost column can be returned with no calculation in the client?

am i looking at creating a view with the calculation in it?

If so, how do i define the total cost column please?

TIA
Fatherjack

View 3 Replies View Related

Total Of A Group In Table

Sep 20, 2007



I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round.
Any suggestions for "All products" ?
Thanks
Richard

Revenue
Product 1
2007
2008
2009
Product 2
2007
2008
2009
etc

I want to add
All products
2007
2008
2009

View 2 Replies View Related

How To Find Total In A Table?

Dec 7, 2007

I have created a report as shown below using OLTP tables.












No
Description
CA
PA
Variance

10101
Bank of Tampa
388353.69
208048.92
87%

10104
Wachovia Operating
275933.37
-
0%

10105
Wachovia Payroll
217.95
-
0%

10201
Accounts Receivable
16735.60
34829.14
-52%

20002
Account Payable
-175919.72
-106018.68
66%

Now I want to insert total in the middle of the table, like I want to display:
Total of CA when No in (10101,10104,10105) as 'Total Cash'
then
I want to display total of 'Total Cash' + 'Accounts Receivable'(10201) as 'Cash & Receivables'











No
Description
CA
PA
Variance

10101
Bank of Tampa
388353.69
208048.92
87%

10104
Wachovia Operating
275933.37
-
0%

10105
Wachovia Payroll
217.95
-
0%


Total Cash
664505.01



10201
Accounts Receivable
16735.60
34829.14
-52%


Cash & Receivables
681240.61



20002
Account Payable
-175919.72
-106018.68
66%

How do I do this?

View 7 Replies View Related

Insert Into Table Up To Certain Total

May 20, 2015

I have a view that contains loan numbers and their current balances. I have been assigned to a task of inserting the first 30 million dollars worth of loans from that view into a table. Is there a way to take a running sum of the loans I am inserting into the table and stop inserting when that sum hits 30 million?

View 3 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Adding Subreport Total To Main Report Total

Apr 28, 2006

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box €™SubRepM1€™
The name of the subreport is €˜subreport1'.

On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.

I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.

Can anyone help, thanks

View 7 Replies View Related

Decrement Total In One Table Based On Value In Another

Oct 17, 2014

I have an procedure that uses a cursor to decrement a total in one table based on value in another. I am trying to rewrite this to be set based but having some issues getting the correct values.

one table will have a total amount available and the other table will have line items for the amount to decrement (think amount left on gift card). If the total amount remaining is less than the line item amount then line item amount should be set to total amount available with total amount set to zero.

this last part is where I am having trouble.

Here is example table

CREATE TABLE qamt(id INT, amt INT);
CREATE TABLE qtot(id INT, tot INT);

INSERT INTO qamt
VALUES(1,10),
(1,20),
(1,30),
(2,90),

[Code] ....

In the end I would want to update the qamt and qtot tables. I basically want to accomplish a quirky update across multiple tables.

View 4 Replies View Related







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