BIT-Wise Aggregation

Oct 29, 2006

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

View 6 Replies


ADVERTISEMENT

I4 To I8 In Aggregation

May 30, 2007

I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table.



Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold.



I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing.



I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround?

View 1 Replies View Related

GROUP BY Without Aggregation -- Is It Possible ?

Jan 14, 2004

Here is what I want to do.

I have a database with HotelInfo

hotelid
hotelname
hotelstate
hotelcity
hotelphone etc etc

I want to display all hotels I have grouped by state

SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
GROUP BY hotelstate


So basically I was For eg.

TX
hotel1 Dallas xxx-xxx-xxxx
hotel4 Plano xxx-xxx-xxxx

CA
hotel2 San Fransisco xxx-xxx-xxxx




How can I group by without Aggregation ?

View 5 Replies View Related

Aggregation Query

Feb 21, 2003

Hi.
i have a problem that related to aggregation functions.
i have a table (requests)that consist with the following fields:event_id, request_type,data.
i want to present a report that for each event_id it will show the number of records where the request_type value is:"VMR".

i wrote the following query-
SELECT event_id, Count(request_type)
FROM requests
WHERE ((requestType)="VMR")
GROUP BY event_id

the problem is that:if event_id don't has a record with the value "VMR" it will not be showen in the report and the goal is to present those kind of events with the value 0.
who can i make it happen.

thanks.

View 6 Replies View Related

SQL Aggregation Poser

Jul 20, 2005

Can anyone help me with an problem I have come across in my databasedesign.I have a primary table and a related table with 3 child records (eachwith a numeric field). I require a query to return the primary keyfrom the main table and the PRODUCT (i.e. all numeric valuesmultiplied together) of the three child records, much like a SUM wouldadd them together.Any help would be gratefully received!Tony.

View 3 Replies View Related

Sum And Aggregation In A Matrix

Jun 14, 2007

Hello,



I have a matrix where I count number of employees per country, per product line.

The calcul is a simple sum :

(sum(Fields!ID__T_E_Employee_Distinct.Value)).



My report is correct at week level:

March 2007

W9 W10 W11 W12 W13

Product_Line1 17 17 17 17 17

Country Product_Line2 4 4 5 5 5

Product_Line3 25 25 26 25 25

Product_Line4 12 12 12 12 12



However my report is false when I drill-up to month level.



I obtain:

March 2007

Product_Line1 85

Country Product_Line2 23

Product_Line3 126

Product_Line4 60



but I want to obtain:

March 2007

Product_Line1 17

Country Product_Line2 5

Product_Line3 26

Product_Line4 12



For information, my datasource is an OLAP cube.



I have tried to add scope in the sum function but it doesn't work...

Moreover the sum is mandatory for me at week level, but I can have max of the sum at month level.

How to do max of sum in SSRS (I can have only one aggragation function in an expression).



Any help is welcome.



Thanks.

Guillaume.



View 2 Replies View Related

Problem With An Aggregation

Aug 6, 2007

Hi,

I am new to the reporting services and I've been working on problem in one of my reports all day long and after 8 hours of frusturation I decided ask for a profesional help.

Ok here is my problem: I have a report that calculates the amount of meetings with our clients. The dataset contains an activity_id field that we assign for each our meetings with our clients. SSRS counts these meetings and shows it in a drilldown enabled report. Everything seems fine on the report except that someof the activities involves few different clients and SSRS is not counting the activities multiple times in region drilldown as there is only one activity id associates in that region even though it contains different companies. And I want those companies to be calculated in too.

From the crude drawing below I wanted to explain my dilemma visually. As it can be seen the total number of meetings we had is actually 40. But as we had 3 activities that involves more than 1 clients it only gives 37 as a count. I would like to know is there a way to make the report count the same activity multiple times if activity_id is associated with more than one clients.


I hope I managed to explain my problem

**********************************************************************************************
Manager Region Market Company Meeting Detail

+Manager 1 (9 meetings)

+Manager 2 (37 meetings)

- West (37 meetings)







-Denver (37 meetings)



+Company 1 (5 meetings)
+Company 2 (2meetings)
+Company 3 (2meetings)
+Company 4 (3meetings)
+Company 5 (0meetings)
+Company 6 (0meetings)
+Company 7 (5meetings)
+Company 8 (1meetings)
+Comapny 9 (19meetings)
+Company 10 (3meetings)
Total (40 meetings)

View 5 Replies View Related

Aggregation Usage

Apr 10, 2008

I know we can design Aggregation usage for a dimension within a cube. For example, you can choose Full , None, Unrestricted and Default method. I assume this is the Global settings.

Is it possible that Ican do the same thing for the attributes within a dimension? I cannot find such property in attributes. Does this means we can't control which attributes we want Analysis Services to aggregate in a dimension?

View 6 Replies View Related

Aggregation Table

Jul 3, 2007

Hi all



i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS



thanks & regards

View 1 Replies View Related

Smart Aggregation

Jan 1, 2007

I'm having problems implementing the following in reporting services 2005.

My hierarchy looks like this (just to illustrate the problem...):

University->Student->Exam

My query returns the following fields:

University,Student,StudentPayment,ExamName,ExamScore

I need to create a report that will show the hierarchy and to smartly aggregate the StudentPayment to both the Student and the University levels.

The problem is that the StudentPayment field is being multiplied by the number of exams in the upper level aggregation.

If only I could set the granularity level of the StudentPayment measurement...

Note that I don't have access to the query, so I can't change anything on that front.

Thanks,

Efi

View 6 Replies View Related

Wise Job Scheduling

Feb 22, 2007

Guys,

I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.

Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:

1. Backup all dbs [in case defragmentation messes something up]
2. Defragmentation [this will grow the transaction log]
3. Backup transaction log [backs up trans log and truncates inactive portion of it]
4. Shrink log file

Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.

Is it regrowing of the log file that will affect performance?

Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?


Thanks a lot

View 6 Replies View Related

Max Value Row Wise Query

Jan 25, 2015

I have a table having 3 coulmns.

amnt1 amnt2 amnt3
100 200 230
200 170 120
290 188 299
800 170 120

i need to get max amount row wise like

amnt
230
200
299
800

View 4 Replies View Related

T-SQL (SS2K8) :: PIVOT Without Aggregation

Jun 28, 2015

I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:

DECLARE @OracleEngData TABLE
( DataSourceCHAR(8)
, [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));

If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.

TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.

I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:

OMNI_NUMBER,TIMESTAMP,DATA_TYPE,PARAMETER,PARAMETE_VALUE
506026,1413240436,test_data,cnr,211250000,54.8
506026,1413244259,test_data,cnr,211250000,53.2
506026,1413244679,test_data,cnr,211250000,53.1
506026,1413309646,test_data,cnr,211250000,53.4
506026,1413315987,test_data,cnr,211250000,53

[code]...

But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation.

View 2 Replies View Related

Measure Displayed Without Aggregation ?

Sep 27, 2007

Hello,

I have a Sales cube and I want to be able to display Products and the
Price at which they were purchased in a given period. I have a
Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can show Products grouped by their list price.

My Product Dimension consists of product_numbers (such as 100, 101,
102 etc...)
My Sales Fact Table consists of sales data (such as product_key,
price, net_sales, returns, etc...) at the transaction level.


I want to be able to view the data like this:


Price Net_sales Returns
Product
100 $5.99 $2005 $320
101 $3.51 $7110 $998


where Net_sales and Returns are "summed" and Price is simply a "Group
By". In other words, this report would show the net sales and returns
by product for a given price.


I'd rather not use a Price dimension since we have hundreds of
products at hundreds of different prices. Moreover, this data is
already in the Sales Fact table.


Thanks for any help provided

View 1 Replies View Related

Class Does Not Support Aggregation

Feb 7, 2006

I am trying to port a database from SQL Server 2005 Express to SQLServer 2005 Standard. Idetached my database from the SQL Sever 2005 Express instance andattached it to the SQL Server 2005 Standard instance. Whenever I do aselect on tables in the database I have no problem. However if Iattempt to open or modify a table by selecting open or modify from thepopup menu, I get the following error --TITLE: Microsoft SQL Server Management Studio------------------------------Class does not support aggregation (or class object is remote)(Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))(Microsoft.SqlServer.SqlTools.VSIntegration)------------------------------BUTTONS:OK------------------------------I tried repairing the .NET 2.0 framework but to no avail.Please help!!!Thanks,

View 3 Replies View Related

Pivot Table Without Aggregation?

Mar 29, 2007

Hello, I have a resultset as follows:fields: Name, RankIDvalues:Prorduct A, 4Product B, 33Product C, 221(etc)Name is always unique. RankID may not be.I want to take that result set and basically pivot it to have the Namevalues as columns, and the RankID of each one as the data. So youwould end up with only one row like:Product A | Product B | Product C | etc4 | 33 | 221 | etcIs this possible? I do not want to sum the data or anything, simplyrotate it sort of.Any advice is appreciated.

View 3 Replies View Related

Aggregation On Monthly Base

Jul 24, 2006

I need to create a report containing the headcounts of the employees for each month. The data I can retrieve from the database looks like this:









Name
StartEmp
EndEmp

tom
1/jan/05
15/mrt/05

dirk
1/jan/05
31/mrt/06

jan
1/feb/05
NULL

In order to get the right information for the report the information should be represented in the following way:











January 2005
February 2005
March 2005
"April" 2005

Tom
1
1
0,5
1

Dirk
1
1
1
1

Jan
0
1
1
1

HeadCount
2
3
2,5
3
(there should be more data in the 2nd table but it's a sliced representation of the data. Sliced by 2 report parameters: startdate (=1 January 2005) and enddate (=30 April 2005) )

I need some help about building up the query to aggregate the info from table1 to the format of table2.

View 4 Replies View Related

Failure On Null Aggregation

Dec 20, 2007

Hi all,

my package is failing every now and then on the following error:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x00040EDA Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".
yes, I do have a null aggregation, but in all my tests this didn't cause a failure...
anyone had experience with this?

Thanks.

View 13 Replies View Related

Transact SQL :: Getting Last Record In Aggregation

Jul 17, 2015

I have daily data that I wish to summarise into monthly. The field [Close] will return the last day of the month that has a value.The query below summarises the data as I want it but using the max of [Close] in the month instead rather than the Last value for the month. In Access I used to use Last function that worked perfectly. From googling, Last doesn't seem to exist in T-SQL but there must be a way to do it.

    D.YearMonth_Number,max(AT.[Close])
FROM
      tbl_Date
ASD
INNERJOINtbl_ASXStats
ASAT
OND.YearMonthDay_Number
=AT.YearMonthDay_Number

[code]...

View 5 Replies View Related

How To Design Aggregation For Partition Using XML Or AMO

May 23, 2008



Hi,

Can anyone please help me.....

how to design aggregation for new partition using XML or AMO ???

I know how to clone partition using AMO(I guess it will take care of aggregation) and set AggregationDesignID in XML.

But, want to know how to design aggregation for new partition using XML or AMO....

Thanks
Nisanth

View 1 Replies View Related

String Aggregation In SQLCompact

Nov 22, 2007

Is this possible in any way? Basically I have a standard pair of tables in a master/detail relationship. I need to somehow be able to query the child table for all children related to a parent record and return some sort of aggregation of a certain column. The problem is that I still need to know what each value was, so it can't be something like a SUM or an AVG.

As if that wasn't complicated enough, this will be done on 1000+ records at a time and is being used in a binding situation.

I've figured out how to do this with standard SQL Server with some dirty FOR XML hacks, but I'm beginning to think that it's not in any way possible with SQL Compact.

Any thoughts?

Thanks.

View 1 Replies View Related

Turning Off Aggregation On A Certain Hierarchy

May 20, 2008

I have a multi dimensional cube. Among the dimensions I have, there is one dimension that has one hierarchy defined. When I view any of my measures on this hierarchy, I don't want the measures to be aggregated. I read some threads here having to do with semi additive behaviors, but Im not sure if it applies to my case. (or maybe it does and I'm just not getting it)

For example,

DimPerson

PersonID, PersonName, AnotherPersonID
1 , Person_A , null
2 , Person_B , 1
3 , Person_C , 1

The purpose of "AnotherPersonID" is to have a self join that describes a certain relationship. So, in this case, Person B and C are related to Person A. I describe this relationship using a hierarchy where A is a parent of B and C. However, I don't want the measures for Person_A to be replaced by the sum of the measures in Person_B and Person_C.

Why do I have a hierarchy if I don't want to sum the numbers? This is motivated by the need to dynamically report on A's numbers when B or C's numbers are reported. I'm just using persons as an example, but, in my case I have a bunch of members that can be paired with another member in that dimension. And when I report on a member that joins to another member in that dimension, I need to dynamically report on that member's measures as well.

Lastly, If the use of a hierarchy is not the best approach for this, would you recommend another approach?

View 4 Replies View Related

Aggregation That Results Two Columns

Mar 12, 2007

Hi,
I have following data coming from prior transformations.

Id Unit Name NewFlag
___ __________ ____________

1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0


and so on.

I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.

Just wondering if it is possible by using Aggregate and how.

Thanks for your help in advance,

View 1 Replies View Related

Class Does Not Support Aggregation

Dec 23, 2005

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)


Program Location:

   at Microsoft.SqlServer.Management.UI.VSIntegration.ISqlToolsVSHlpFactory.CreateHierarchyWrapper(Guid& clsidInnerHier)
   at Microsoft.SqlServer.Management.UI.VSIntegration.SqlWorkbenchHierarchy.CreateHierarchyWrapper(Type typeToWrap, ILocalRegistry localReg)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.get_SqlVirtualProject()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.InvokeDesigner(IManagedConnection connection)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.Invoke()Hi there,

I'm using SQL Server 2005 Dev. with Windows XP Pro. SP2.
50GB free space with 1GB of RAM
After I installed SQL Server with no problems.
I tried to open a table in AdventureWorks database and I'm getting an error:


Could some one tell me what is going on ?

Thanks an advanced

Oded Dror
Email: odeddror@cox.net


 

View 13 Replies View Related

Data In Column Wise

Apr 18, 2008

My Table(collection) Structure consist of below:
TypeDate ItemAmount
REVENUE2007-04-01serv002***
REVENUE2007-04-01Serv004***
REVENUE2007-04-01serv005***
REVENUE2007-04-01AMC-***
ROYALTY2007-04-01serv002***
ROYALTY2007-04-01Serv004***
ROYALTY2007-04-01serv005***
ROYALTY2007-04-01AMC***

O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

View 6 Replies View Related

Need Weekly Wise Data

Apr 2, 2007

Hi,

Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.
Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.

Help!!!

Regards,
Navneet

View 11 Replies View Related

Is There Any Difference Performance Wise

Dec 4, 2007

Dear All,
is there any difference performance wise using
select * from my_table
and
select mycol1,mycol2....mycoln from my_table


actually i've read from one article the there is big difference....
please clear my doubt...
thanks in advance

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 8 Replies View Related

Is There Anyway To Generate Day Wise Scripts

Feb 12, 2008

Dear All,
is there any way to generate daywise scripts in database? i mean all the transactions wether insert update or delete....
i need all the changes on that particular date(with in 24 hours)....
is there any procedure or function for this?


i've almost 1200 tables in that database.


note : due to replication is not possible for my scenario, i'm thinking in different ways to achieve my purpose.

this is my requirement
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96959

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Sequence No Group Wise

Jul 23, 2005

Hi All,I want to give sequence no group wise, as follows:Section EmpCode-----------------------A 1001A 1003A 1009B 1023B 1030C 1044-----------------------I want to set sequence no. group wise like this for above data.Seq. Section EmpCode------------------------------1 A 10012 A 10033 A 10091 B 10232 B 10301 C 1044----------------------------Suggestion in this regard will be appreciatedThanks in Advance,T.S.Negi

View 3 Replies View Related

Having The Issue Regarding The Group Wise SUM

Apr 25, 2008

I have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group.
Can anybody give the suggestions how to resolve the problem..
I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..

location invoice no. date quantity item
delhi inv/del/1 1/1/2008 3 item-1
item-2
item-3
inv/del/2 2/1/2008 2 item-4
item-5
Location Total 5


This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.



thanx you..

View 3 Replies View Related

How To Use Order By Clause Row Wise

Sep 9, 2015

i want to know how to use order by clause in row wise

for example, suppose the rows is
10 20 30 40

so the output is should be
10
20
30
40

View 3 Replies View Related

Problems With Aggregation Of Distinctcount-measure

Mar 26, 2005

Hi Experts,

I have a problem:
I have various cube packed in a virtual cube to give me distinctCount Values of customers who purchased products (DC customer] and purchased products (DC product) along with Saleries [RW] over numerous dimensions such as productgroups customergroups etc.
In the calculated measure [RW kum] I can sum the the salaries from the beginning of the year until [time].currentmember with the following MDX-Code:
With member [Measures].[RW kum] As 'Sum(Periodstodate([time].[year]).[Measures].[RW])'
Select {[Measures].[RW],[Measures].[RW kum], [Measures].[DC customer]} on rows,
[Productgroup].[Itemgroup].Allmembers on columns
From [vcub_Sales]
Where [Time].[2005].[march]

IF I use this PeriodsToDate-Formular with the the measure DC customer I will get the sum of distinctcounts of the periods.

What I want is the distinct count of all customers who have purchased since the beginning of the Year until the [Time].currentmember in the same Level.

Given this problem, do I..
1. have make another Cube or
2. another time dimension or
3. is there a way in MDX to get the result?

I would appreciate any replies.

Zannadar

View 1 Replies View Related

Pulling Data Into One Line (Aggregation)

Aug 19, 2015

eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...

I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?

E.G.

CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT
CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT
ETC...........

View 4 Replies View Related







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