Aggregating Values That Match Aggregate Result

Sep 23, 2015

The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.

-- greatly simplified schema that makes as much sense as the real schema
CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10));
CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue));
CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));

[Code] ....

Desired results:

SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment
UNION ALL
SELECT 2, 'second row', '2015-10-11', 150
UNION ALL
SELECT 3, 'third row', NULL, NULL

I know I'm doing something wrong in the last query and I believe another sub-query is needed?

Let me answer a few questions in advance:

Q: This schema looks horrible!
A: You don't know the half of it. I cleaned it up considerably for this question.

Q: Why is this schema designed like this?
A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.

Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical.
A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).

View 2 Replies


ADVERTISEMENT

Using Different Scopes For Aggregating Values

Mar 5, 2007

Hello everybody,



I'm facing a big problem in my actual report development task... however, what i need to do is (in my opinion) so basic that there has to be a way to do this...

What does it all about:

My customer needs a report in which different measures are shown in rows in a matrix table. columns are reserved for the months, simulating a sort of calendar aspect.

However, the matrix has one additional group in it's rows and the whole matrix is contained within a list.

A possible situation could be following:

Coca Cola

Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
TOT %

PM BRU 619 53% 122 23 34 24 0 67 72 78 56 90 78 65
NET 572 58% 87 12 30 13 0 54 65 50 43 85 73 60
IN BRU 554 47% 45 54 8 23 32 45 6 76 34 87 87 65
NET 421 42% 30 43 5 20 12 32 3 60 32 80 65 39





TOT Jan Feb Mar ...

BRU 1173
NET 993

Pepsi Cola

...

The problem are those percentages in red. They are calculated on total amount of BRU/NET without distinction of the first group in the table (PM or IN). If I set the value for the textbox to the following expression:

=(sum(Fields!value.Value)/sum(Fields!value.Value,"Level2"))*100 Where Level2 is the Details specified in the list (in our case Coca Cola or Pepsi Cola) Reporting services makes the sum of the value field without making the distinction of which type of value it is dealing with...

My question is now rather obvious: Is this calculation possible and if yes how?? I've thought to write a custom method in Reporting Services (no assembly) but I've no Idea how i can 1) access the dataset. 2) I need to be able to specifiy the name of the measure for which the calculation needs to be done and 3rd I also need to pass the current detail of the list item to be able to apply the necessary filters on the dataset to perform the aggregation....

Any idea's how to perform this??

Thanks in advance

Alain



View 1 Replies View Related

Aggregating Member Properties Or Not Aggregating Distinct Data

Feb 23, 2005

I have an employee, who received an initial starting bonus of $50k.
This value will be static from day 1 to day n and will never change. I
want to see the intial starting bonus, but any sets higher than the
employee dimension will need to aggregate the starting bonus. Is there
an easy way to do this?

If I just look at the data from an employee's perspective, I can do
this by making the measure a Min, Max, or Avg Aggregate function. But,
if for instance I want to view the data from the perspective of
departments, it would need to sum() the data instead (which min/max/avg
don't do).

If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.

View 4 Replies View Related

How Come The Result Sets Do Not Match? Why No Error?

Feb 21, 2008

Scenario: Launch SQL Server 2005 Management Studio & Connect to Server, ie: ServerNameInstanceName. Click "New Query" Button

Running the following SQL Statement returns 842 rows but should return 1343:

SELECT A.id, C.PName, B.School
FROM users AS A
LEFT OUTER JOIN Bio AS B ON A.id = B.id
LEFT OUTER JOIN Groups AS C ON A.PName = C.PName ORDER BY A.Id

I get 1343 rows when I change Line 2 of SQL Statement to: "FROM dbName.dbo.users AS A".

Clicking "New Query" w/out first opening the database must run the query in the context of the Master Database??
I noticed in Sql Server 2000 if I connect Query Analyzer to the Server and run the query I get 842 & I can see that the default selected database is master. When I make the change mentioned above I then get 1343.

Why doesn't this produce an error though? I'd rather get an error than the wrong result set.

View 4 Replies View Related

T-SQL (SS2K8) :: Aggregate Monthly Values From Interval Values?

May 14, 2014

I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.

Currently it is displaying.....

MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real

-===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL

Output should display as

MeterId Period Reading

4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15

View 4 Replies View Related

Aggregate (SUM) A Column And Then Use The Result?

May 7, 2007

Hi,



I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.



Thanks

View 9 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Aggregate Function For Select Statement Result?

Oct 19, 2004

Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.

currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.

Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.

*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales


This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.

thanks,
Tom Anderson
Software Engineer
Custom Business Solutions

View 3 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Converting Result Of Aggregate Function Calculation To A Double (C#)

Jan 5, 2008

I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good).
 If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable?  Possibly, is the expression after an AS something
that I can manipulate into a double variable?  My end goal is to insert the result of the calculation into a database.
What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps:
  <asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource">
<Columns>
<asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" />
<asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" />
<asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" />
<asp:BoundField DataField="density" HeaderText="density" SortExpression="density" />

</Columns>
</asp:GridView>

<asp:SqlDataSource ID="lbsgalDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT
a.Formulation,
SUM (a.lbs) AS lbs,
SUM ((a.lbs)/(b.density)) AS gal,
( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density
FROM Formulations a INNER JOIN Materials b
ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation">

<selectparameters>
<asp:controlparameter controlid="DropDownList1" name="Formulation" propertyname="SelectedValue" type="String" />
</selectparameters>

</asp:SqlDataSource> 
 

View 2 Replies View Related

Query To Get Rows Which Match With All Given Values

Sep 10, 2007

Hi all,

I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:

SELECT *
FROM TblUser u
WHERE EXISTS

(

SELECT *

FROM TblScore s

WHERE s.FKIDUser = PKIDUser

)

With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15

I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.

So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.

Someboy would have an idea at my problem ?

Thanks in advance
Jerome

View 7 Replies View Related

Transact SQL :: Get Row If All Column Values Match

Nov 10, 2015

Please don’t look for table design satisfies NF, this is just for example

Student table
Id            StudentId
1                    10
2                    20
3                    30

Student_Class
Id            ClassId
1              100
1              101
1              102
1              103
2              100
2              101

When I give studentId 10 and class ids = 100, 101, 102, 103 then result should be get row from student table only if all given class ids matched.

Result:
Id            Student ID          ClassId
1              10                          100, 101, 102, 103

Case 2: Student Id: 10    class Ids = 100, 101 the no results since all the class ids for student 10 in Student_Class are not matching with the given class Ids parameter.

View 11 Replies View Related

Use Aggregate Values

Jun 6, 2003

Hi all,

I have a table with the following fields:

carrier,calc_date,ind_id,rcf

I need to run a query to get the following result(by carrier and for each calc_date, calculate the percentage of all individuals who have rcf greater than 0.73):

carrier,calc_date,count of ind with rcf > 0.73, count of all individual, percentage of individuals with rcf's greater than 0.73.

does anyone have an idea of how to achieve that result?

Thanx

View 4 Replies View Related

SQL Server 2012 :: How To Match Values From A PIVOT

Jan 31, 2014

I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.

Here's some test data and code which shows the principle:

CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE

[Code] ....

The result set is:

ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL

Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?

View 4 Replies View Related

Updating With Aggregate Values

Mar 9, 2004

I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account
I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below

update A
set df_date1
= max(df_date1) from b
where a.account_id = b.account_id

I get the error message
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.

Is there another way to do this with a subselect and update?

View 2 Replies View Related

Displaying The Values That Dont Match And Replacing By NULL

Feb 6, 2003

Hi,

Im having a couple of problems with SQL.

I have this query...

SELECT Band.Name, Member.Name
FROM Member
JOIN MemberOf ON Member.Mid = MemberOf.Mid
JOIN Band On MemberOf.Bid = Band.Bid
WHERE MemberOf.Instrument = 'keyboards';

which basically shows me the name of the bands who had keyboard players. I would like it to also display the names of the band who didnt have keyboard players, replacing the keyboard players name with "NULL".

so... my questions are :)

how do u get it to display the records that do not match the condition, and how do u get it to replace the keyboard players name with "NULL" when they do not match the condition.

PS. The three tables are

Band. Which has Bid as a primary key.
Member. Which has Mid as a primary key.
MemberOf. Which links these through its two foreign keys Mid and Bid.

Thanks for your help! (assuming someone does)

View 2 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

Is There An Aggregate Function To Sum A Datetime Field Values?

Jan 15, 2004

Hi all...

how I can obtain the sum of a datetime field as aggregate function?

Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?

Thanks..

Massimo

View 7 Replies View Related

Problem Finding Values With Aggregate Functions

Jul 23, 2005

Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name

View 2 Replies View Related

Error - Column Name Or Number Of Supplied Values Does Not Match Table Definition

Oct 17, 2013

I have a table names Alert_Event and a new column named BSP_Phone has been added to the table. I am trying to set NULL values to the column and I get the below error. I am setting null values in the bolded text in the query.

Error Message:

Msg 213, Level 16, State 1, Procedure SaveBSPOutageInfo, Line 22
Column name or number of supplied values does not match table definition.USE [gg]
GO

/****** Object: StoredProcedure [dbo].[SaveBSPOutageInfo] Script Date: 10/17/2013 19:01:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME, @eventOrigin varchar(10),

[code]....

View 3 Replies View Related

Transact SQL :: Column Name Or Number Of Supplied Values Does Not Match Table Definition

Sep 15, 2015

I have two tables (i.e. Table1 and Table2).

SELECT
* FROM [dbo].[Table1]

 Date
id
9/15/2015

[code]...

Table2 has three columns (i.e. Date, Count and Rule Type). Column “Rule Type “has a default value which is “XYZ”..Now I want to insert Data from Table1 to Table2. I am using following query:-

declare @Startdate
datetime
DEclare @enddate
datetime

[code]...

Column name or number of supplied values does not match table definition.I am using SQL 2012. I understand Table1 has 2 Columns but Table2 has 3 Columns. Is there anyway, I can move data from source table to Destination table where Destination Table has more number of Columns? 

View 2 Replies View Related

T-SQL (SS2K8) :: How To Aggregate Interval Data To Hourly Values

May 12, 2014

I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?

SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId

Current Output

MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000

View 7 Replies View Related

SQL 2012 :: How To Target Transactional Values With Aggregate Amount

Apr 27, 2015

I am trying to exclude records that have an assessed value that has been waived in an aggregation. For Example:

Here is my table:

CREATE TABLE #temptable (ReportingMonth Varchar(6), Fee_Code Varchar(20), Fee_Transaction_Amount Decimal(12,2), Fee_Transaction_Date Datetime, Fee_Transaction_Type Char)
INSERT INTO #temptable (ReportingMonth, Fee_Code, Fee_Transaction_Amount, Fee_Transaction_Date, Fee_Transaction_Type)
SELECT 'Jan-13', 'ONE TIME DRAFT FEE', '20', '01/24/2013', 'A'
UNION ALL SELECT 'Feb-13', 'LATE CHARGE', '33.6', '02/19/2013', 'A'
UNION ALL SELECT 'Mar-13', 'LATE CHARGE', '37.01', '03/18/2013', 'A'

[code]....

Here are Data Mapping Description

Reporting Month = Month - Year
Fee Code = Fee Description Name
Fee Transaction Amount = Fee Amount
Fee Transaction Date = When Fee Amount was Applied
Fee Transaction Type = "A" = Assessed Fee; "W" = Waived Fee; "P" = Paid Fee

I've also included an image with beginning data set and what I want to identify in red and what my final data set should look like after the exclusion of those 4 records are removed.

Here are the logic requirements:In the attachment what I need the logic to do is essentially identify the $20 One Time Draft Fee from the first instance using the MIN Transaction date. Since there were $80 waived for this fee code (One Time Draft Fee), I would expect to see the first 4 (highlighted in red) to be identified as the target and as you can see in the attachment the second data set had the 4 highlighted items removed. That should be my final output.

trying to loop and remove the waive amounts from the assess amounts and tied it back to remove from my base data.

View 1 Replies View Related

Data Access :: Column Name Or Number Of Supplied Values Does Not Match Table Definition

Jun 22, 2015

I'm executing a stored procedure but got error :

Msg 213, Level 16, State 1, Procedure ExtSales, Line 182
Column name or number of supplied values does not match table definition.

View 5 Replies View Related

Transact SQL :: Insert Using Select Intersect / Except - Number Of Supplied Values Not Match Table Definition

May 12, 2015

I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created

CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)

The following stmt won't work...

INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C

... as the number of supplied values does not match table definition.

View 2 Replies View Related

Reporting Services :: SSRS Matrix Aggregate Values And Rounding Error

Jun 11, 2015

I am using SSRS 2014. I'm using a matrix instead of a tablix because it allows me to have dynamic columns. In the example I'm showing, two of the columns use the sum function to get the total counts per practice. The third column contains percentages so I averaged for the total but the value is inaccurate compared to the value I would get if the divided the the two totals that are sums of the counts. Is there a way for me to specify that I want to divide the total counts numerator divided by the total counts denominator?

Here's an example report output with the percentage column averaged (inaccurate):

PCP            numerator denominator percentage
John Smith  66             104  63.46
Tom Jones    4                36    11.11
.
.
.
Jane Doe     1    1       100
Total 708          1005       72.3

So the 72.3 value is from Avg(metricvalue)

I would like to do this instead: % total = 708/1005, which equals 70.5 - a significant difference.

The metricvalue column is what is the value for every number above (Because it's a matrix).

View 2 Replies View Related

Compare Values In Result Set.

Sep 11, 2007

This is my first post and I am probably not using the correct terminology but I will try. I want to compare the value of one of my fields in a result set to the value of the same column/field, but from the previous row in the result set. I am trying to identify the row in the result set when the value in a specified column changes value. I want to identify this row by placing a 1 in the last column. If that value did not change, then place a 0 in the last column.
I am not sure if this will make sense to anyone out there. Please let me know if more clarification is needed and I will try to provide it. Thanks for all your help in advance.

Don

View 2 Replies View Related

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View 2 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   
 
 

View 4 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 4 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 1 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 4 Replies View Related

Removing Null Values From A Result Set

Jan 9, 2007

Hi,

I have following query which is returing null values along with other values. Could you please tell me how I can restrict to not to return null values

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Thanks,

Prudhvi

View 6 Replies View Related







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