Question About Summing Up Different Fields In Different Records

Jul 28, 2006

Hi everyone, I have some trouble writing a SQL stored procedure that
can do the following:

We have data in one table in numeric form, but we want to sum the data
in this table based on the values of two different alpha fields. To
illustrate, let me write the following example:

Table with these records:
A B 1.1 2.2 Blah1 Blah1
A B 2.3 5.6 Blah2 Blah2
B C 7.8 9.1 Blah3 Blah3
B C 4.5 1.0 Blah4 Blah4
R F 1.1 4.3 Blah5 Blah5
B A 3.1 2.7 Blah6 Blah6

I need to write a query that will return the following result set from
the above table:
A B 3.4 7.8 Blah1 Blah1
B C 12.3 10.1 Blah3 Blah3
R F 1.1 4.3 Blah5 Blah5
B A 3.1 2.7 Blah6 Blah6

If the alphanumberic keys are the same, the sum the numeric columns up
whilst displaying one one of the records, the blah fields don't matter
if one only one is displayed. Can anyone recommend the best way to do
this? I'm running MS SQL 2005.

Thanks,
Herman

View 1 Replies


ADVERTISEMENT

Summing And Grouping Numeric Fields

Nov 6, 2006

Hello again

How do I sum up and group numeric values into one output column in SSIS?

I have data that looks like this:

Refno date credit amount

11675 1987-04-23 7.03 13501.27


and I want to add the credit and amount columns together and output them as one column, In T-SQL, i'd do this:

SELECT Refno, SUM(Credit + Amount)

FROM Transfer

GROUP BY Refno, Date

I could add this SQL into my SSIS package easily enough but I wan't to learn how to do it the SSIS way







View 4 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Records Fields With More Than One Line

Jan 2, 2008

i have a table with some data imported from another database. In one table shows some records 'manchester customer [][] phoneline [][] january' when I select these data it display in multilines. that mean 2 squares represent enter key.
I need to remove these from the records and replace space. so that i can select field value as a single line
 how can i do this  ??????
pat

View 6 Replies View Related

Duplicate Records - Two Fields

Feb 13, 2014

I have a table that contains some duplicate records. The entire record is not duplicated, but there are two fields which if both are duplicated, result in what I call a duplicate record. I can see that I have these duplicates by doing the following:

SELECT DISTINCT ReferenceNumber, ShipToCompanyID
FROM tabMedia
WHERE (MediaTypeID = '1') AND (FinishDate > '2011-01-01')
GROUP BY ReferenceNumber, ShipToCompanyID
HAVING (COUNT(ReferenceNumber) > 1) AND (COUNT(ShipToCompanyID) > 1)
ORDER BY ReferenceNumber

but of course, this only returns two fields. I need the entire record returned where the duplicate exists...

View 2 Replies View Related

Combine Records On Two Fields

Nov 5, 2007

Hello everyone -
This is my first post to the forum and I'm very new to SQL. I apologize if this is addressed elsewhere.

Here is an example of the results I am getting from my query

AdmissionID, sNurseInit, sSWInit
100, {NULL}, SAE
100, REG , {Null}


Is there a way to combine (merge, join? I don't know the right word) these records so that a single record for the admission is returned?

AdmissionID, sNurseInit, sSWInit
100, REG, SAE


Thanks in advance!
Amy

View 20 Replies View Related

Removing Records With Spaces In Fields

Mar 26, 2008

How do i eliminate records which has spaces

i tried with

select * from table
where col1 !='' or
col1 is not null

which doesn't work.. any help.

View 1 Replies View Related

Return Non-matching Records On Two Fields

Jan 18, 2008



Here is a very basic question that I have.

I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.

How can I pull back the records from table A that do not have a corresponding combination in B?

I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?


THANKS!

View 1 Replies View Related

Trying To Return Whole Records With Distinct Fields

Oct 17, 2006

Hi

I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).

For example, for the following:

Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow

I would want to return (assuming Colour was the distinct field):

Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow

How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.

Thanks for any help you can offer.

View 11 Replies View Related

Converting Rows Records Into Table Fields

Feb 21, 2002

Hi I have a table that contains duplicates. Custid, order #s
custid,order#
1, 2525
1, 2323
1, 2222
2, 6969
2, 7474
3, 8888

Here what I am trying to do. I want to create a table that contains rows from the above table in this format.

custid, order#,order#,order#
1 ,2525 ,2323, 2222

basically, I want to convert the duplicate rows into fields in a new table.

View 1 Replies View Related

Find Duplicate Records Based On Certain Fields

Jul 28, 2014

How can I pull out duplicate records based on certain fields?

Table called Bank

I want to pull out records that have duplicate inv_no, cus_no, amount,ordernum

Not all the fields are the same in each record. But I want the records that have these fields that are the same.

View 1 Replies View Related

Best Practice For Add, Edit Records Into Database With Lots Of Fields ?

Feb 7, 2006

What's the best practice for adding / editing a record into a database with lots of fields ?I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.
Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.
But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand,  with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...
What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).
With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.
Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.
So come on guys (and gals)... let's hear your thoughts on how you would handle the insert  / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)
 

View 1 Replies View Related

Selecting Unmatched Records Based On Multiple Fields

Jul 21, 2004

I need to list all the records in Table2 which don't have matching field values in Table1.

This the the exact opposite of what I need:
SELECT DISTINCT
Field1,
Field2,
Field3,
Field4,
Field5
FROM
[Table1]
WHERE EXISTS(
SELECT DISTINCT
FieldA,
FieldB,
FieldC,
FieldD,
FieldE
FROM
[Table2]
)

The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.

What I need, however, is the exact opposite.

I tried the above using NOT EXISTS but I get no records at all.

How do do this?

View 6 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records Each Containing Null Value For One Of Fields

Apr 11, 2014

I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:

Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,

[Code] ....

View 5 Replies View Related

Easiest Way Of Combining Multiple Fields From Different Records Into One Record?

Jul 20, 2005

I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!

View 5 Replies View Related

Processing CSV Flat Files With Records Of Number Of Fields

Mar 10, 2008

Hello I have some flat files that contain CSV records with different number of fields but the first 4 fields of each record type are the same of each re. eg there would be an entry of one record that has eight fields and another that has 6 fields. Which of the items in the toolbox can i use to filter the records based on the entry in the first 4 fields so i can process the filtered records.

Thank you
Kenalex

View 5 Replies View Related

SQL Server 2012 :: Excluding Records Whose Values From 2 Different Fields Match

Aug 31, 2015

Using MSSQL 2012

I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.

Below is my where clause.

WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'

---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

View 4 Replies View Related

DB Engine :: Updating Table Fields With Multiple Target Records

Apr 30, 2015

I am trying to multiple update records in Table B from a single record in Table A.  To identify the records that need to be updated I used this:

Select
Table1.cfirstnameas'Table
1 First',Table1.clastnameas'Table
1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,

Table2.cfirstnameas'Table
2 First',Table2.cLastNameas'Table
2 Last',Table2.ilocationidas'Table
2 Location',Table1.iLocationIDas'Table
1 Location'

fromTable1

JoinTable3
CRonTable1.istudentid=CR.istudentid

JoinTable2
onCR.icontactid=Table2.iContactID

whereCR.lLivesWithStudent=1
andTable1.ifamilyid>0
andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1

I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2.  I needed Table 3 to make the connection from Table 1 to 2.

View 2 Replies View Related

T-SQL (SS2K8) :: Sequential Data Selection - Identify Different Fields Within A Group Of Records?

Jun 18, 2014

How to identify different fields with in a group of records?

Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'

[Code] ...

I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.

View 1 Replies View Related

SSIS - How To Deal With A Csv File That Contains Multiple Records With A Differing Number Of Fields

Apr 20, 2007

what is the best way to uses ssis to deal with a CSV file that contains a number of records that contain a different number of fields ?

I could just load as a single column delimited by <CRLF> but then I would have to write the code to parse the line, effectively detokenising the columns myself but if that is the case then why uses ssis at all ?

View 13 Replies View Related

Summing

Jul 20, 2005

I am trying to check a list (MyList) against another List(SupplierList).I want sum the Qty's of UniqueID on MyList and extract the sum of thesame UniqueId's on SupplierList.BTW There are more than one instances of Unique Id on each list.The Script below is providing me with the correct answer for someproducts (UniqueId), but incorrect amounts for others.The incorrect answer is always a multiple of the correct answer.What am i doing wrong???Regards,CiaránSELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]= SupplierList.[Unique ID]GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]

View 1 Replies View Related

Summing With A Sub-query?

Apr 30, 2008

Hi,

I'm trying to get a sum but not doing too well. I think I need a subquery but am unsure how to phrase it.

Problem:
I need to sum timesheet hours logged at work-code level to project-level (for named projects), where a project consists of 0-to-many work-codes. The 'Project' table is used for both projects and work-codes; the 'pr_code' contains the unique code (i.e. the work-code or the project-code), 'pr_master' field contains the parent. The Timesheet table will contain pr_code's for work-codes, but won't contain an entry for a work-code if no-one has logged any time to a work-code.

Sample input:

Timesheet table
===============
pr_code|ts_hours
QWER.01|6
QWER.01|7
QWER.02|3
QWET.01|2


Project table
=============
pr_code|pr_master
QWER.01|QWER
QWER.01|QWER
QWER.02|QWER
QWET.01|QWET
QWER|QQQQ
QWET|QQQQ
QWEY|QQQQ



Intended output:
For named projects QWER, QWET & QWEY:

QWER|16
QWET|2
QWEY|0



I've got the following so far which almost gets there, but appears to be summing up as it goes i.e. QWER=16, QWET=18, QWEY=18:


SELECT p1.PR_Master AS Expr1, SUM(Timesht.TS_Hours) AS Expr2
FROM Timesheet LEFT OUTER JOIN
Projects ON Timesheet.PR_Code = Projects.PR_Code LEFT OUTER JOIN
Projects p1 ON Timesht.PR_Code = p1.PR_Code
WHERE (p1.PR_Master IN ('QWER', 'QWET', 'QWEY'))
GROUP BY p1.PR_Master


Any help most appreciated.

View 5 Replies View Related

Summing An Equation

Jun 6, 2008

This is a working 12 month intrest equation. I used this for the layout section but I am trying to take this and it gives me the correct values. But what I need to do next is have it sum those values.

I tried =SUM( whole expression but that didnt work) you can laugh at me I know but any help would be great!

=Switch(Fields!eqprecdt.Value< CDate("1 Jan 2007"),Fields!bookvalue.Value*datediff("d",Now(),#1/1/2007#)* .07/365,Fields!eqprecdt.Value> CDate("1 Jan 2007"), Fields!bookvalue.Value * datediff("d",Now(),Fields!eqprecdt.Value)* .07/365)*-1

View 5 Replies View Related

Summing Prices Day By Day

Oct 23, 2013

Let suppose that we have a table which look like this

BillDate Price
01.01.2013 2.00
01.01.2013 1.00
02.01.2013 3.00
02.01.2013 2.00
03.01.2013 1.00

I would like to sum a prices day by day and output to be like this

BillDate SumDaylyPrice
01.01.2013 3.00
02.01.2013 5.00
03.01.2013 1.00

To point I’ve reached myself is a query:

SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice
FROM Table1
WHERE BillDate BETWEEN
(SELECT Min(BillDate) FROM Table1)
AND
(SELECT Max(BillDate) FROM Table1)
GROUP BY BillDate

but this doesn’t work- summing everityng

I don’t know how to indicate in first row of query
SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice
a WHERE clause for every day separately.

View 2 Replies View Related

Summing The Value Of A Text Box.

Feb 5, 2008



As my name shows I am about read to pull my hair out on this and will take any help that I can get.
I have a table with the following values
field1,field2,field3
a | p | 1
a | n | 1
a | p | 2
b | p | 2

b | p | 2
b | n | 3

I am grouping by first column
a
p 1
n 1
p 2
-------------------
a 3 1

b
p 2
p 2
n 3
------------------
b 4 3
What I want to do if it have a value of p I want the value in one column if it has a value of n I want it in another column.
The columns are not a problem, I use a iif statement iif( field2 = p, value, 0) iif ( field2 = n, value, 0)

the problem comes when I try to total the columns.
I was trying to use the =sum(field3) in my group total.
the above example is what I want to see the below example is what I get.
a
p 1
n 1
p 2
-------------------
a 4 4

b
p 2
p 2
n 3
------------------
b 7 7

I hope this makes some since to someone out there that can help me out.
I am getting kind of thin in the hair department so I cannot afford to loose any more.



View 3 Replies View Related

Grouping And Summing

Jul 20, 2006

I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):

date prod class qty
060101 a101 1a 100
060101 a101 1a 100

I would like to have the following:

date prod class qty

060101 a101 1a 200

Any other suggestions would be greatful!!
Thanks in advance

View 4 Replies View Related

Problem Summing Each Column

Jun 4, 2007

 Hi,I need some help in summing each column in a gridview.id    name    sun    mon    tue    wed    total1      Tim       5        6        5      10      263      Sam      6        6        6      5        23The above is how the gridview looks like. In the database, I have all the fields except for total. So, I know I have to use the SUM function in SQl to get the Total. So, I am wondering how do I sum each column to get the total. I have something like this but it doesn't work:"SELECT ID, name, Sun, Mon, Tue, Wed, SUM(Sun + Mon + Tue + Wed) AS Total FROM testTable"Please helpahTan 

View 7 Replies View Related

Summing Different Combinations Of Selections

May 7, 2008

I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"

**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)

Thanks in advance
I.Shaame

View 9 Replies View Related

Summing Rows Into A Column

Mar 16, 2006

Hello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.

I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.

So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.

This is my Access query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

And this is what I have been able to salvage into MSSQL format:

SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)

How can I total the three columns and add the tax?

View 3 Replies View Related

Difficult Summing Query

Nov 23, 2005

Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.

View 5 Replies View Related

Summing From Multiple Tables

Jul 20, 2005

We recently added a new database at the company. It has only onepurpose - to hold massive amounts a daily data generated by telephonecalls on a network.The amount of data was so large (several gigabytes a day) that the guywho set up the database creates a new table for it each day.His thinking was that if we only need to query one day's worth of datathen it would be a lot faster to query a table with one day's datathan having to query many days of data in one table.I see his reasoning. Any comments or alternatives to this schemewould be appreciated.Here's the question though...I'm writing a front end for this and waswondering if the most efficient way to query and sum data acrossmultiple tables (days) is in the form of the following statement.Suppose three days of worth of data are wanted:select sum(ET) from (select sum(vc_elapsed_time) AS ET fromswitch2030608 where init_cell_info_cell = 196 union all selectsum(vc_elapsed_time) as ET from switch2030609 whereinit_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ETfrom switch2030610 where init_cell_info_cell = 196 ) tIn my front end, based on user input, I plan to keep extending thisstatement with more union alls. Is this the best way to implement thegoal of this query?-David

View 1 Replies View Related

Summing Up Based On Value Of Another Column

May 12, 2008

I have a report with several columns which include Status(0,1,2) and Time in hours. I want to be able to total up the time for each status. I Sum up the all of the fields fine. I'm new to reporting services so any help would be appreciated. Thanks

View 3 Replies View Related

Summing Up Subgroup Totals

Nov 9, 2007

In below table, the light blue rows, represents the subgroup I am trying to sum up.

The value in the "Inventory Value" contains the latest inventory value for said inventory.
Light green row is grouped by Store name, Light blue is grouped by inventory name. the expression in Inventory row for the inventory value column is =First(Fields!InventoryValue.Value).

Doing a store summation, sums up all detailed values. For example in Store A, instead of 70K, the sum would be 130K... double summing the toy soldiers and cute dolls inventory. Is there a way to do the "correct" summation or a different approach to this? Thanks!












STORE
TOTAL STORE INVENTORY VALUE
INVENTORY
% OF STORE INVENTORY
INVENTORY VALUE
WARNING DATE

STORE A
1,000,000.00







TOY SOLDIERS
2.00%
20,000.00






20,000.00
11/5/2007





20,000.00
11/2/2007



PLUSH TOYS
1.00%
10,000.00






10,000.00
11/5/2007



CUTE DOLLS
4.00%
40,000.00






40,000.00
11/2/2007





40,000.00
10/31/2007


1,000,000.00

7.00%
70,000.00









STORE B
10,000,000.00







TOY SOLDIERS
0.20%
20,000.00






20,000.00
11/5/2007





20,000.00
11/2/2007



PLUSH TOYS
0.10%
10,000.00






10,000.00
11/5/2007



CUTE DOLLS
0.40%
40,000.00






40,000.00
11/2/2007





40,000.00
10/31/2007


10,000,000.00

0.70%
70,000.00

View 4 Replies View Related







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