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 that
has the smallest value. I have tried this, but for the result i also
want 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 INTEGER
NOT NULL
IDENTITY(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
ADVERTISEMENT
May 14, 2008
I have a table that is used for employee evaluations. There are six questions that are scored either 1, 2, 3, 4, or 5. I want to tally the responses on a page, but I wonder if I can do it without 35 separate calls to the database (I also want to get the average response for each question). I know I can do "SELECT COUNT(intWorkQuality) AS Qual1 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '1' " and then "SELECT COUNT(intWorkQuality) AS Qual2 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '2' " and so on. But can I somehow do the aggregating at the page level, and just refer back to a datasource that uses a generic statement like "SELECT intWorkQuality, intDepend, intAnalyze, intWrite, intOral, intCompatibility FROM dbo.Summer_Project_Req"? If I can, I am not sure what type of control would be best to use or what syntax to use to write the code-behind. I would like the results to be displayed in a grid format. Thanks in advance for your help.
View 3 Replies
View Related
Nov 29, 2007
Can i combine two aggregate functions in one select statement?Like sum(count (field 1) * field 2) as tcost.Can i do something like that in my query?
thanks!
Funnyfrog
View 10 Replies
View Related
Jan 26, 2008
Hi..
I'm doing a jsp with database using sql.. I'm trying to get the max of score group by name from this table:
Table record
id_no. . . .name. . . . .score
. . 1 . . . . .Philip . . . . .10
. . 2 . . . . . Jane . . . . .12
. . 3 . . . . . John . . . . .15
. . 4 . . . . . Anna . . . . .11
. . 5 . . . . . Jane . . . . .16
I use the code:
query = "select name,max(score) from record group by name";
resultset = stmt.executeQuery (query);
However, there is an error. It says, SQL Exception: Column not found
I've checked several tutorial sites and the codes there are the same command that I use. I've also tried using other aggregate functions like the sum, still the same error comes out..
Why is that so? What should I do?
Thanks a lot..
View 3 Replies
View Related
Apr 18, 2006
I'm trying to migrate an app. from Access to SQL Server, and find that Transact-SQL does not support LAST/FIRST functions. Is there any alternative to these?
Below is the Access SQL statement:
SELECT Last(tblZoneNameString.Val) AS strZoneName, tblZoneNameString.TagIndex
FROM tblZoneNameString
GROUP BY tblZoneNameString.TagIndex
HAVING (((tblZoneNameString.TagIndex)>0));
View 7 Replies
View Related
Jul 12, 2000
I'm using the data environment of VB 6.0 to create data reports.
In Oracle you can use a nested aggregate function in a select query like MAX(SUM(Field)). Can I do this in SQL? If so, how?
thanx
View 2 Replies
View Related
Feb 10, 2007
Hi everyone,
I have a database with the following tables:
- programmer
- software
- studies(name, splace, cost, course)
I would like to find out which school has the max. nr. of students, but my query - SELECT splace FROM studies HAVING count(name) = (SELECT max(count(name)) FROM studies) - doesn't works, becouse MSSQL doesn't supports nested aggregat functions.
What should I do? Please help me, thanks in advance.
View 1 Replies
View Related
Jan 30, 2006
Something a little different: C# on SQLTeam!
It's basically just cobbled together using the aggregate function and TVF in the StringUtilities sample as a starting point.
Er, sorry MS, I forgot to change the namespace from Microsoft.Samples.SqlServer.
/*
Aggregate functions: UnionBinary, IntersectBinary
Table-valued function: SplitBinary
*/
using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;
[assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1020:AvoidNamespacesWithFewTypes", Scope = "namespace",
Target = "Microsoft.Samples.SqlServer")]
namespace Microsoft.Samples.SqlServer
{
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
IsInvariantToOrder = true,
MaxByteSize = 4100)
]
public class UnionBinary : Microsoft.SqlServer.Server.IBinarySerialize
{
private byte[] bitset;
public void Init() { bitset = new byte[4]; }
public void Accumulate(SqlInt16 value)
{
if (!value.IsNull && value.Value >= 0) {
if (bitset.Length <= value.Value / 8) {
Array.Resize(ref bitset, (value.Value/32 + 1) * 4);
}
bitset[value.Value / 8] |=
System.Convert.ToByte(1 << (value.Value % 8));
}
}
public void Merge(UnionBinary other)
{
if (other.bitset.Length > bitset.Length) {
Array.Resize(ref bitset, other.bitset.Length);
}
for (int i = 0; i < bitset.Length; i++) {
bitset[i] |= other.bitset[i];
}
}
public SqlBinary Terminate() { return new SqlBinary(bitset); }
public void Read(BinaryReader r)
{
int i = r.ReadInt32();
bitset = r.ReadBytes(i);
}
public void Write(BinaryWriter w) {
w.Write(bitset.Length);
w.Write(bitset);
}
}
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
IsInvariantToOrder = true,
MaxByteSize = 4100)
]
public class IntersectBinary : Microsoft.SqlServer.Server.IBinarySerialize
{
private byte[] bitset;
public void Init() { bitset = new byte[4]; }
public void Accumulate(SqlInt16 value)
{
if (!value.IsNull && value.Value >= 0) {
if (bitset.Length <= value.Value / 8) {
Array.Resize(ref bitset, (value.Value/32 + 1) * 4);
}
bitset[value.Value / 8] &=
System.Convert.ToByte(1 << (value.Value % 8));
}
}
public void Merge(IntersectBinary other)
{
if (other.bitset.Length > bitset.Length) {
Array.Resize(ref bitset, other.bitset.Length);
}
for (int i = 0; i < bitset.Length; i++) {
bitset[i] &= other.bitset[i];
}
}
public SqlBinary Terminate() { return new SqlBinary(bitset); }
public void Read(BinaryReader r)
{
int i = r.ReadInt32();
bitset = r.ReadBytes(i);
}
public void Write(BinaryWriter w) {
w.Write(bitset.Length);
w.Write(bitset);
}
}
public sealed class SplitBinaryCls
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable SplitBinary(SqlBinary argument)
{
List<Int16> r = new List<Int16>();
if (!argument.IsNull) {
for (int i = 0; i < argument.Length; i++) {
for (int j = 0; j < 8; j++) {
if ((argument.Value[i] & Convert.ToByte(1 << j)) != 0) {
r.Add(Convert.ToInt16(i*8+j));
}
}
}
}
return r;
}
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1021:AvoidOutParameters")]
public static void FillRow(Object obj, out Int16 stringElement)
{
stringElement = (Int16)obj;
}
private SplitBinaryCls() { }
}
}
To install the compiled dll something like:
CREATE ASSEMBLY BinaryUtilities
FROM 'C:...BinaryUtilitiesindebugBinaryUtilities.dll'
WITH permission_set=Safe;
GO
CREATE AGGREGATE UnionBinary(@input smallint)
RETURNS varbinary(4096)
EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.UnionBinary];
GO
CREATE AGGREGATE IntersectBinary(@input smallint)
RETURNS varbinary(4096)
EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.IntersectBinary];
GO
CREATE FUNCTION SplitBinary(@input varbinary(4096))
RETURNS TABLE(ix smallint)
AS EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.SplitBinaryCls].[SplitBinary];
GO
View 2 Replies
View Related
Mar 26, 2006
I have a select statement like this:
"SELECT AVG(Price) FROM prices GROUP BY Category"
Which of course returns a list of average prices.
I want to get a sum of that list, something that would work like this:
"SELECT SUM(SELECT AVG(Price) FROM prices GROUP BY Category)"
That code doesn't work, but that's what I want it to do and I'm not sure how to do it.
Thanks!
View 2 Replies
View Related
Jul 23, 2005
Hi.Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I writemy own variation of a such a function. If I can appreciate how to dothis,then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or amore general function like a 10% percentile function with syntax suchasPERCENTILE(COLUMN_NAME,25).Regards JC......
View 2 Replies
View Related
Feb 14, 2006
Hi allFirstly this my first time posting to technical groups - so anymistakes I apologise for in advance.I am trying to count records in several secondary tables for the samerun in a primary table. However, there might be no records in thesesecondary tables for the specific run. Hence the sql below returnsnulls.Select run, (select count(errors) from table2 where run = t1.run groupby run) as errors, (select count(user) as users from table3 where run =t1.run and user = active group by run, dd)from table1 t1(Please note the different group bys. )I do not want nulls to be returned but to be replaced with 0. I havetried the isnull function but this does not work. egSelect run, (select isNull(count(errors),0) from table2 where run =t1.run group by run) as errors, (select isNull(count(user),0) as usersfrom table3 where run = t1.run and user = active group by run, user)from table1 t1Nor will isnull work if I put it around the select clause.Any suggestions?Thanks for the help!
View 4 Replies
View Related
Nov 29, 2006
Do aggregate functions (sum, count, min, max) inherently cause tablelocks?More concretely, would the following query typically result in a tablelock?select sum(quantity) as totalfrom productsThanks,Scott
View 3 Replies
View Related
Jul 20, 2005
I have a complex statement that is used in a SELECT statement. Afterall my calculations I need to do an additional filter on the results.Can I use an alias anywhere or is the AS clause just for columndisplay purposes?Instead of:SELECT column1,column2,some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statementsAS TotalFROM table_nameHAVINGsome_complex_statement_using_SUM_AVG_and_a_few_CAS E_statements[color=blue]> 1[/color]Can I have:SELECT column1,column2,some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statementsAS TotalFROM table_nameHAVINGTotal > 1As I understand it, the HAVING clause is used for filtering AFTERaggregate functions are calculated so the alias SHOULD be available.
View 1 Replies
View Related
May 22, 2008
I have Following SQL query
SELECT SUM(TOTAL) FROM MYTABLE WHERE DATEORIGINATED='11/11/07'
the above query will give me total for dateoriginated , but I am reading that dateoriginated value from another table and that table has multiple value of dateoriginated.
what I would like to do is to run above SQL aggregate functions query for two different dates at the same time and I would like to get total
help me how to do this?
thank u
maxmax
View 3 Replies
View Related
Jun 13, 2007
Hello Experts,
Currently my report is being shown like this:
Child Cards
K04-EP-MS48S (4/12)X
1
3
5
2
K12-EP-MS128(0/0)-XX
1
0
I would prefer the report as
Child Cards
K04-EP-MS48S (4/12)X
6
5
K12-EP-MS128(0/0)-XX
1
0
Is there any way that this is possible?
Thanks.
Lakshman.
View 1 Replies
View Related
Jan 12, 2007
Hi, need help in this statement here. I have three tables here, i.e. Sales, SalesItem, & SalesPmt. I want to display a grid that shows the Total Bill and Total Payment amounts.
My try is like this: SELECT SalesNo, SUM(Price*Qty) AS TotalBill, SUM(Payment) AS TotalPayment FROM ... GROUP BY....
No syntax error or whatever found, but the result of the total amounts is incorrect.
Say the data of the respective table below:
SalesItem
No
Qty
Price
1
1
5.00
2
2
12.00
3
4
3.50
SalesPayment
No
Amount
1
10.00
2
5.00
But the result I get from the above query is:
TotalBill
TotalPayment
86.00
45.00
Total Bill should be 43.00 and Total Payment should be 15.00.
Apparently the problem is due to the fact that I and querying on multiple tables. The correct total payment amount was multiplied by the number of rows of sales items (15.00 x 3), while the correct total bill amount was multiplied by the number of rows of sale payments (43.00 x 2).
So, what is the better way of writing this query?
View 5 Replies
View Related
Dec 1, 2004
Hi there,
I'm probably just being thick but is there a way to use conditional statements within aggregate functions? I'm trying to do something along the lines of the following -
Code:
SELECT SUM(
CASE
WHEN Currency='GBP' THEN TotalAmountCharged
ELSE TotalAmountCharged/1.45
) as total
from bookshop_orders
where year(OrderDate) = 2004 and month(OrderDate) = 9
TIA
Nick
View 4 Replies
View Related
Jul 20, 2005
Howdy,I need to write an update query with multiple aggregate functions.Here is an example:UPDATE tSETt.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )FROM dbo.test tBasically I need to get some aggregate statistics about the rows offoo and store them in rows of t. The above statement works fine...butnote how the two subSelect's have the exact same WHERE clause. Thisscreams at me to combine them...but how? I would like to havesomething like this in my query:SELECT avg(f.q), sum(f.q) FROM dbo.foo f WHERE f.p = 2...and somehow store the results in t.a and t.b. Is there any way todo this?Thanks before hand!
View 6 Replies
View Related
Feb 17, 2008
Hi Guys,
I am having trouble with a particular query that is beyond my scope of understanding.
Basically I need to pull sales records based on the following criteria:
I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..
Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.
For example, CustomerNumber 123 has recontracted 3 times..
once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..
So they have a 12 month contract each time.. then come in to recontract it.
So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.
Currently to pull ALL sales records for all customers, my query is this:
Code:
SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL
We want to get a list of customers that we can call to recontract, based on the ContractEnd field.
However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.
So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:
Code:
SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB
Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)
BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..
Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.
Can anyone please help me!
Thanks guys,
Mick
View 1 Replies
View Related
Apr 25, 2008
How to write Aggregate functions for tables and lists as If I can write them many problems in my reports will be solved. I tried writng it in the filters but I got an error saying Aggregate functions are not allowed for tables and lists. Can any one help me in this regard?????
View 4 Replies
View Related
Jun 13, 2014
I have a simple query like:
select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I get round to this?
View 6 Replies
View Related
Nov 22, 2006
Hi,
Assume there is no sale with over 2000 units, the first query returns no row while the latter returns 1 row with a null value. I am not questioning the result, I would like to understand conceptually what semantic difference between the Sql99 analytic function and regular aggregate function makes the result different in this case.
select sum(Quantity) over() from orderdetails where Quantity > 2000
select sum(Quantity) from orderdetails where Quantity > 2000
thanks in advance,
Jeopardy
View 2 Replies
View Related
Apr 21, 2015
I have report showing sales by Vendor. I need to list all the vendors with Monthly Total>5000 and combine the rest as "OTHER VENDORS"
Vendor is a Group in my report, so I tried to put an expression as a Group on:
=IIF(Sum(Fields!Mth_1_Sales.Value)>5000,Fields!Vendor_No.Value,"OTHER VENDORS")
I've got an error: "aggregate functions cannot be used in group expressions"
How do I get Vendors with Sales < 5000 into "OTHER VENDORS" ?
View 4 Replies
View Related
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
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
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
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
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
View Related
Oct 14, 2006
pls what functions in sql format values like: 122334.98765 to become only with 2 digits after coma like this: 122334.98
what part of help in books online
and also what functions do methods on dates like getting date value from string, adding two different dates, getting the day, getting th month.....
View 2 Replies
View Related
Mar 22, 2006
Hi
I am new to SQL. My scenario is I get a string from the user I need to match this string with value present in 2 cloumns. If the string is present in either of the column then i should select that particular record from the table.
Now my question is if i Have to match with one cloumn i can used charindex function is there any function with which i can match the string with data present in more than one cloumn.
Thanks Much
View 2 Replies
View Related
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
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
Dec 19, 2007
Hi ,
I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ?
For eg I have written the following function which I called from a stored procedure.
BEGIN
--Declare the return variable here
DECLARE @Rows NUMERIC(10)
DECLARE @RETURN_ENABLED VARCHAR(1)
-- Add the T-SQL statements to compute the return value here
SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name
Group By Profile_INdex
IF @Rows > 0
SELECT @RETURN_ENABLED = 'N'
ELSE
SELECT @RETURN_ENABLED = 'Y';
-- Return the result of the function
RETURN @RETURN_ENABLED;
END
Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?
From the stored procedure I'm calling the function inside a CTE.
;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled)
AS
(--Anchor.
select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item ,
dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
From sys_menu_item as tb1
where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help')
And tb1.item_parent= @menu_name
--Members
UNION ALL
select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item ,
dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
from sys_menu_item as tb2 , GetHierarchy
where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help')
And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name
)
select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled
From GetHierarchy
Am I doing it correctly ?
I would appreciated any help you could give me.
Thank you
View 5 Replies
View Related
