We are having a requirement to Aggregate the data and create LY, CY data across 5 Metrics.
the Volume of Data will be 200 Mil - 250 Mil
Server Configuration:
Memory: 32 GB
Processors: 16
understand the bench mark configuration needed for the server & any hints on better aggregation methods & LY / CY data
I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it.
SELECT
Receipt
,"Date"
,Item
,Reason
[Code] ....
currently need to re-create an aggregate table in a proc every night to aggregate purchases broken down by person/store in groups of 3, 6 12 etc months.finding the performance of it is very slow as it covers 500,000 million rows.The query looks like
SELECTCusID(int)
, StoreID(int)
, SUM(L3M) as Last3Months
, SUM(L6M) as Last6Months
, SUM(L9M) as Last9Months
[code]...
I need to make changes to this because it is using a BETWEEN on a datetime column. I was wondering though, if anyone else has made agg tables like this before an found a better way of doing them?
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
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...........
I manage a legacy system that dumps it's data into a number of different databases (same schema) on a nightly basis using bulk insert. I need to formulate a strategy for efficiently aggregating that data into a single database right after these nightly extractions complete. Here is my current stategy:
1. Duplicate the legacy system's database schema and add an identifier column to specify which database the data loaded from.
2. Each night, delete all records in the table.
3. Each night, for each database:
3a. Set each table's default value to a value that references the current database being loaded.
3b. Use the legacy system's flat files and format files to bulk insert into the database.
3c. Clear the default value.
What other steps would faciliate performance? Dropping and recreating the indexes? Does anyone forsee faults in this strategy?
Thanks,
Matt
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
However, in my simple cube I get (B).
Is there a setting I need to adjust to get (A)?
I have 1 table that is just a list of feeds. A, B, C, D etc (15 rows in total) and each feed has other information attached to it such as Full name, location etc etc. I am only interested in the Feed column.
Each feed then has a corresponding data table which contains a list of records. Eg Feed A data is contained in TableA, Feed B data is contains in TableB and so on.
Basically what I need is a combined table that shows the list of Feeds in the 1st Column ( So A, B, C, D…..) and then a second column which counts the records from each separate data table corresponding to that feed.
So the end result would look something like this:
Feed------No of Records
A----------4 (from TableA)
B----------7 (from TableB)
C----------8 (from TableC)
D----------1 (from TableD)
Possible?
Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
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?
I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data.
Sample Data:
CREATE TABLE #t (LoanID VARCHAR(5), BorrowerName VARCHAR(20), BorrowerOrder INT);
GO
INSERT INTO #t VALUES
('::E', 'John Smith', 0)
, ('::E', 'Jane Smith', 1)
, ('::E', 'Rob Jackson', 2)
, ('AF_CF', 'Sloan Burton', 1)
[code]...
I don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data..I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans.
Attempt with CTE:
--With CTE
;WITH cte AS
(SELECT
Borrower1 = BorrowerName
, Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder)
, RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)
[code]...
Is there a better, cleaner way to do this? Or is the CTE the best way to go?
[URL]
I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.
Use Sales_Builder
Go
SELECT DISTINCT
GBPriceList.[Mfr_Part_Num],
[Code]....
Problem 1: I have the following table which shows the location of a person at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006
The delta increments by +1 every time the location changes.
I would like to return an aggregate grouped by delta as per example below.
EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200
I am using the following query (which works fine):
select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2:I have the following table which shows the location of different people at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003
I would like to return an aggregate grouped by person and location.
For example
EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200
What modifications do I need to the above query (Problem 1)?
I have this table:
CREATE TABLE [dbo].[ACT_SECUNDARIA](
[CODACTIVIDADE] [int] IDENTITY(1,1) NOT NULL,
[CODCTB] [int] NOT NULL,
[CODCAE] [int] NULL,
[CODSECTOR] [int] NOT NULL,
[Code] ....
I want to delete every record that has more than one entry (codctb; codcae)
For example: if there are three records with the same codctb and codcae I want to delete two so that there can only be one.
How can I achieve this using t-sql?
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 ?
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.
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 RelatedHello,
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.
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)
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?
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
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
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
Using the Log file viewer in sql auditing I can see only 1000 record....How can we see more than 1000 records or earlier data...
View 6 Replies View RelatedWe configured IDERA SQL Safe for backups and restores.we setup an email for notifications. One day we performed manual backup operation for 150 databases from SQL Safe tool. Unnoticed it backed up to C:Backup folder.
We got alerts with the report of backups on C: drive. Then we moved backup files to respective folders.But, I could not clear the records from the report, its been 25 days, still we are receiving the alerts as below.How can I clear this report or to configure or setup anything to avoid this, in future as well.
Below is the sample records from the report. I need this report to be cleared.
Subject: SQL Safe Validation Report
The following files are recorded in the SQLSafe repository, but no longer exist in their locations:
C:BackupLUXOR_DB_GroupEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_InsightEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_IMEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiteEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_ICEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_vdbEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_M_SEPF_Diff_201402050045 (1 of 1).safe
I use code below to insert data into a temp table.
How many records can insert into a temp table?
Select * into #temp from ORDER
I've a table as below
custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7
How to split csv values to new fields. so that the desired output should be as below
custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
View 5 Replies View RelatedI have idea on SMK, DMK and symmetric and asymmetric keys. I have also idea on TDE. But Is there any way to encrypt all the records of all the columns of a table in a database? actually I need to encrypt the database. Someone .... thinks that when someone will write select query he will get the encrypted records. As per as I am concerned it is not possible. I can encrypt the specific column using symmetric or other keys...
Is there any software or any tool which will provide encrypted records of database?
Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
I want to generate following op
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11
2 111 1 b 11-111
3 1111 2 c 11-111-1111
4 11111 3 d 11-111-1111-11111
Following is the query
i want to retrieve around 10000 in one go.. its taking around 8 seconds.. how to make it faster?
even if i retrieve 1 record or 10000 records, its taking around 8 seconds...
--- create table
create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
---- insert records
insert into test values (1, '11', 0, 'a')
insert into test values (2, '111', 1, 'b')
insert into test values (3, '1111', 2, 'c')
insert into test values (4, '11111', 3, 'd')
---- result query
;WITH SInfo AS
(
SELECTsId
,scode
,ParentId
,sName
,CONVERT(nvarchar(800), scode) AS Hierarchy
[Code] .....
With this query i get only the records i need, but i would like to output in this way
1 - 20
21 - 30
31 - 40
of course in the real environment the ID are not consecutive, this is just one example of data.
declare @temp table (ID int)
declare @i int = 1
while(@i<1000) begin
insert into @temp values (@i)
set @i=@i+1
end
select ID from (
select ID, row_number() over (order by ID)
as rn
from @temp
) q where (rn % 20=0) OR (rn % 20=1)
I have one table having three columns.This table contains lot of repeated records. I want to delete this records .
In this below example i want to delete all the records which columns id and no columns contains same values.
id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1
First three records the columns id and no contains same value. I want to delete this three records.
But in last record for id =35 and no column =45623.it is not repeated so it should not be deleted.