Summarize Data Over Partitions

Oct 17, 2007

Hi champs!
The data i have is like this:

nr date value

------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11

.....

I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value

------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53

any help is much appreciated.
thanks

View 3 Replies

Summarize Data Using Single Query

May 31, 2007

I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.

Here is example data for one customer.

1 �� Only Qty is negative

Product Qty Price

Shirt 2 6.00

Shirt -1 6.00

-------------------------------------------------------------------------------------------------

Shirt 1 6.00 Result

2 - Only price is negative

Product Qty Price

Pant 2 6.00

Pant 1 -6.00

-------------------------------------------------------------------------------------------------

Pant 1 6.00 Result

Thanks for help!!

Pintoo

View 1 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Data Moving Between Partitions

Mar 9, 2008

I have a requirement that I need to reload the last seven days worth of data each night to ensure that we pick up late arriving and updated records. To avoid having to do updates we delete the last seven days data and reload.

I was wondering if it is possible to set up the table as a partition, paritioned on a value (OLD, NEW) or similar.

The job would set the last day in the NEW partition to be old, the theory being that this would cause the rows to move to the OLD parition, and then truncate the new partion rather than deleting. The last seven days data could then be inserted into the empty new partition.

My questions is 1. Is my theory about the data moving from one partition to another correct. 2. Can I actually truncate and individual parition, 3. Do you think it will perform any quicker. We would expect data in the range of 100K to 500K rows in the seven days and will store up to 4 years of historical data.

Thanks for your thoughts

Stapsey

View 1 Replies View Related

Can Data Partitions Be Used With Associative Tables?

Aug 1, 2006

First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database
schema which contains associative tables for many-to-many relationships.

We have two tables, a User table
and an Audit table. A user can be
assigned more than one Audit. An Audit
can be assigned to more than one User.
So an AuditUser associative table exists. If data partitions are used based on User,
then any Audits that are assigned to one or more users should be copied to the
proper partition for each User (the msmerge_current_partition_mappings table
with the proper partition_id values).

In order to insert records with such a schema, the following
steps occur in order:

Insert
new row into Audit table with new rowguidInsert
entry into AuditUser table associating the auditguid with every userguid that
is assigned this audit.

Merge replication triggers are fired on insert of the Audit
row and another one for the insert of the AuditUser row.

When the Audit row is inserted, the replication trigger follows
the following logic:

Inserts
a copy of that row into the msmerge_contents table. Evaluates
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table). To do this, it checks to see if the
AuditGuid is referenced in one or more AuditUser rows. Since we haven��t inserted the AuditUser
row at this point, the trigger��s logic doesn��t find a partition to copy
this row to.

When the AuditUser row is inserted, the replication trigger performs
the same logic as with the Audit row, it:

Inserts
a copy of that row into the msmerge_contents table.Evaluates
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table). Since the row meets the criteria for one
or more partitions, it is copied to the msmerge_current_partition_mappings
table for each partition that exists.

When replication occurs, we see only the AuditUser rows
copied down to our device, and not the corresponding Audit rows. Now that we understand the triggers, it is
plain to see why. If the AuditUser row
could be inserted first, then the trigger on the Audit row would copy that row
into the proper partitions and all would work well. However, the Audit row must be inserted
first, so that foreign key relationship constraints are preserved.

It seems that the Update trigger on the AuditUser row
actually walks the relationships and copies any related child rows to the
msmerge_current_partition_mappings table.

View 3 Replies View Related

Why Does Moving Data Between Partitions Take So Long ?

Oct 17, 2007

I have a table with millions of rows and about 70 columns that move through a number of states (11 possible states in all) from "New" via various states to "Processed" and eventually to "Archive" (there's a complicated state diagram that I won't bore you with)

Movement between states is based on a heap of business logic including the move to Archive (not just dates).

Different sorts of processing (querying and update both by users and overnight processing) are carried out on the data according to its state.

Maintaining the indexes for optimum performance across the board is a headache.

We have two problems in that we want better query performance and want to be able to easily switch out objects that are in the Archive state.

I had in mind partitioning the table (and its indexes) on state so that :
(a) Queries would be directed only at the appropriate partition (that is always use "where state=" as part of the query)
(b) The Archive partition could be swapped out of the table periodically

In my test setup 10 of 11 partitions are in [PRIMARY] but Archive is in a different filegroup.

Query performance is OK - execution plans look good.

However my update performance is now appalling when moving between any two states (10 times as long as on the unpartitioned table).

I understand that when you update a column which is used as a partition key it will cause the row to "move from one partition to another" as it says in another post.

Fine - because that's exactly what I want - logically.

I can also understand that moving from one filegroup (and hence the underlying file) to another must mean that the data has to physically move.

However is the data physically moving whenever you move between partitions or what's going on to cause such a degradation in performance ?

View 6 Replies View Related

Master Data Services :: Table Partitions In MDS?

Apr 13, 2015

Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....

We created table managers_plan in MDS :

year
month
id_manager (domain attr)
POS (domain attr)
plan_sum_USD
plan_unit
----------------------------
Entities:
Managers ~ 800 records
POS ~ 100 000 records

managers_plan Total records for
1 year = 100K x 12 = 1 200 000

managers_plan - table partitions
- Will bemade ?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)

And i have some data:

INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

SQL Server 2008 :: Restoring Database - Delete Data From Partitions

Feb 20, 2015

I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.

View 9 Replies View Related

Data Warehousing :: Will Creating Partitions On Table Increase Insert Speed

Oct 8, 2015

I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?

View 4 Replies View Related

Summarize Unique Changes Of Registration

Aug 21, 2006

I've been trying to solve this problem for better of 4 days:We summarize registrations of students on a daily basis, however theyare net changes.Example:A student registers one class for the first time for the Fall quarteron a Monday. A report would reflect that change for Monday.Next, the same student adds another class on Tuesday. Since the studentwas already counted on Monday, I dont want the student to count onTuesday.On Wednesday, the student decides to drop both classes. Since thestudent no longer has any classes, I wish to decrement the studentcount on Wednesday for that one student.If the same student adds a new class on Friday, then they would counton Friday since their previous classes net to zero.After the end of the session, I would be able to sum up the dailybalance of adds and drops and it would net out to be equal with thetotal number of unique students registered for the quarter.- Students can add and or drop classes on the same day, or on differentdays.- I need to know when the net effect when a student is changed andreflect that quantity on the date for the quarter (SESSION).We have reports on our legacy system written in IBM's Universe (its abusiness basic). Its pretty straight forward as we would traverse thedata using a basic program. However, trying to something in batch inSQL has eluded me.What I am looking to select:What I am looking to select:SESSION DATE STUDENT_ADDS STUDENT_DROPS200602 2005-07-18 1 0200602 2005-08-23 1 02006002 2005-09-30 0 1TIARob(I thought of getting the first registration, and last drop (if any),but it wont work as there can be adds and drops in between)(I ran the DDL this time, and it returned what I expected when Iselected it)CREATE TABLE "DBO"."REGTRACK"("STUDENT_SKEY" INT NOT NULL,"SESSION_ID" CHAR(6) NOT NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"ACTIVITY_CODE" CHAR(1) NOT NULL,"ACTIVITY_DT" DATETIME NOT NULL,"ACTIVITY_COUNT" INT)INSERT INTO REGTRACKVALUES(250,'200602','MAT100001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)INSERT INTO REGTRACKVALUES(250,'200602','ENG200001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)INSERT INTO REGTRACKVALUES(260,'200602','BUS100002011','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105001011','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105002011','A',CONVERT(DAT ETIME,'2005-07-19'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105001011','D',CONVERT(DAT ETIME,'2005-07-19'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105002011','D',CONVERT(DAT ETIME,'2005-09-30'),1)INSERT INTO REGTRACKVALUES(265,'200602','PAD500001024','A',CONVERT(DAT ETIME,'2005-08-26'),1)INSERT INTO REGTRACKVALUES(266,'200602','CIS110001006','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(267,'200602','ECO100001004','A',CONVERT(DAT ETIME,'2005-09-07'),1)INSERT INTO REGTRACKVALUES(267,'200602','BUS520012016','A',CONVERT(DAT ETIME,'2005-09-07'),1)INSERT INTO REGTRACKVALUES(267,'200602','BUS520012016','D',CONVERT(DAT ETIME,'2005-10-10'),1)INSERT INTO REGTRACKVALUES(267,'200602','ECO100001004','D',CONVERT(DAT ETIME,'2005-10-10'),1)INSERT INTO REGTRACKVALUES(275,'200602','ITB300001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(275,'200602','BUS310006016','A',CONVERT(DAT ETIME,'2005-08-31'),1)INSERT INTO REGTRACKVALUES(288,'200602','FIN100002016','A',CONVERT(DAT ETIME,'2005-07-28'),1)INSERT INTO REGTRACKVALUES(288,'200602','POL300003016','A',CONVERT(DAT ETIME,'2005-07-28'),1)INSERT INTO REGTRACKVALUES(288,'200602','FIN100002016','D',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(288,'200602','MKT200002016','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(321,'200602','CIS105004010','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(321,'200602','BUS100005010','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(321,'200602','CIS105004010','D',CONVERT(DAT ETIME,'2005-11-15'),1)INSERT INTO REGTRACKVALUES(321,'200602','BUS100005010','D',CONVERT(DAT ETIME,'2005-11-28'),1)INSERT INTO REGTRACKVALUES(243172,'200602','ENG102001001','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(243172,'200602','CIS105023016','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(243172,'200602','ACC100002010','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(334,'200602','MAT300009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(334,'200602','CIS111009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(256542,'200602','CIS460002016','A',CONVERT( DATETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(256542,'200602','CIS500019016','A',CONVERT( DATETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(349,'200602','CIS500001003','A',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(255713,'200602','BUS520008016','A',CONVERT( DATETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(359,'200602','BUS531001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(359,'200602','CIS514001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-09-16'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','D',CONVERT(DAT ETIME,'2005-10-26'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-11-08'),1)INSERT INTO REGTRACKVALUES(368,'200602','CIS110003016','A',CONVERT(DAT ETIME,'2005-08-16'),1)INSERT INTO REGTRACKVALUES(368,'200602','HUM300001016','A',CONVERT(DAT ETIME,'2005-08-16'),1)INSERT INTO REGTRACKVALUES(369,'200602','BUS530011016','A',CONVERT(DAT ETIME,'2005-09-13'),1)INSERT INTO REGTRACKVALUES(381,'200602','BUS100026016','A',CONVERT(DAT ETIME,'2005-08-02'),1)INSERT INTO REGTRACKVALUES(381,'200602','ECO405001016','A',CONVERT(DAT ETIME,'2005-08-02'),1)INSERT INTO REGTRACKVALUES(385,'200602','BUS100002008','A',CONVERT(DAT ETIME,'2005-07-27'),1)INSERT INTO REGTRACKVALUES(385,'200602','BUS107001008','A',CONVERT(DAT ETIME,'2005-07-27'),1)INSERT INTO REGTRACKVALUES(249922,'200602','ECO405008016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','POL300011016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','HUM100022016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','HUM100022016','D',CONVERT( DATETIME,'2005-10-03'),1)INSERT INTO REGTRACKVALUES(395,'200602','HUM400011016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499002016','A',CONVERT(DAT ETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001016','D',CONVERT(DAT ETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001015','A',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499002016','D',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG095001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG096001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG097001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(398,'200602','HUM200005016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(398,'200602','HUM400004016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(398,'200602','CIS427001016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(406,'200602','ECO550008016','A',CONVERT(DAT ETIME,'2005-08-01'),1)INSERT INTO REGTRACKVALUES(406,'200602','MAT540004016','A',CONVERT(DAT ETIME,'2005-08-01'),1)INSERT INTO REGTRACKVALUES(406,'200602','MAT540004016','D',CONVERT(DAT ETIME,'2005-11-14'),1)INSERT INTO REGTRACKVALUES(429,'200602','POL300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(429,'200602','SOC300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(429,'200602','ACC403003016','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(429,'200602','SOC300006016','D',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','MAT540001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS531001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','D',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(433,'200602','ENG102001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS533001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS531001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','MAT540001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','ENG102001021','D',CONVERT(DAT ETIME,'2005-09-29'),1)INSERT INTO REGTRACKVALUES(448,'200602','ENG102013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(448,'200602','HUM101013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(459,'200602','HUM101002010','A',CONVERT(DAT ETIME,'2005-08-25'),1)INSERT INTO REGTRACKVALUES(459,'200602','BUS310001010','A',CONVERT(DAT ETIME,'2005-08-25'),1)INSERT INTO REGTRACKVALUES(466,'200602','HUM100004016','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(466,'200602','CIS111003016','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(479,'200602','BUS100050016','A',CONVERT(DAT ETIME,'2005-09-20'),1)INSERT INTO REGTRACKVALUES(253486,'200602','ENG102001012','A',CONVERT( DATETIME,'2005-10-05'),1)INSERT INTO REGTRACKVALUES(253486,'200602','MAT105001012','A',CONVERT( DATETIME,'2005-10-05'),1)INSERT INTO REGTRACKVALUES(490,'200602','BUS532001003','A',CONVERT(DAT ETIME,'2005-09-20'),1)INSERT INTO REGTRACKVALUES(509,'200602','ENG102021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)INSERT INTO REGTRACKVALUES(509,'200602','MAT100021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)INSERT INTO REGTRACKVALUES(511,'200602','LEG100001012','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(556,'200602','LEG100013016','A',CONVERT(DAT ETIME,'2005-09-24'),1)INSERT INTO REGTRACKVALUES(556,'200602','SOC304001003','A',CONVERT(DAT ETIME,'2005-09-24'),1)INSERT INTO REGTRACKVALUES(576,'200602','ACC100002026','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(576,'200602','BUS100043016','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS288001010','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS450001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS286001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(583,'200602','BUS490001017','A',CONVERT(DAT ETIME,'2005-08-09'),1)INSERT INTO REGTRACKVALUES(583,'200602','SOC300004016','A',CONVERT(DAT ETIME,'2005-08-09'),1)INSERT INTO REGTRACKVALUES(583,'200602','BUS490001017','D',CONVERT(DAT ETIME,'2005-09-07'),1)

View 2 Replies View Related

Analysis :: DAX - How To Use Ranks On Top Of Summarize

Jun 2, 2015

I have a query where I'm using summarize to return aggregated tabular data.

EVALUATE
(
SUMMARIZE (
CALCULATETABLE (
'Inscricoes',
'Year'[ID] = VALUE(26)

[code]...

I want to add a measure that ranks them 1,2,3,4. In this case it could be the rownumber, but I'm failing to use RANKX under summarize.

View 6 Replies View Related

Analysis :: Tabular - DAX - SUMMARIZE And RANKX

Sep 2, 2015

According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result.

EVALUATE
CALCULATETABLE(
    SUMMARIZE(
        'Internet Sales',
        Product[Product Name],

[code]....

View 2 Replies View Related

Querying DateTime Field And Summarize Just Using Date

May 9, 2012

I have 2 tables that I would like to summarize a couple of columns for a full day of production(12:00:00 AM to 11:59:59 PM) based on passed variables. Here are my Tables:

Order_Details_tbl
PlantID – IngredientID – AmountBatched – DateTime
1 – 8 – 1000 – 4/30/2012 1:23:12 PM
1 – 8 – 1000 – 4/30/2012 4:23:12 PM
1 – 8 – 1000 – 5/1/2012 1:23:12 PM
1 – 8 – 1000 – 5/1/2012 10:23:12 PM
1 – 8 – 4500 – 5/3/2012 1:23:12 PM
1 – 8 – 11000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/7/2012 10:23:12 AM
1 – 8 – 1000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/8/2012 9:23:12 AM
1 – 8 – 1000 – 5/8/2012 4:23:12 PM
1 – 8 – 1000 – 5/8/2012 2:23:12 PM

Order_Details_Details_tbl
PlantID – IngredientID – AmountBatched – DateTime
1 – 8 – 100 – 4/30/2012 1:23:12 PM
1 – 8 – 11000 – 5/4/2012 11:23:12 PM
1 – 8 – 11000 – 5/7/2012 11:23:12 PM
1 – 8 – 1000 – 5/8/2012 11:23:12 AM
1 – 8 – 1000 – 5/8/2012 1:23:12 AM
1 – 8 – 1000 – 5/8/2012 11:23:12 PM
1 – 8 – 1000 – 5/8/2012 5:23:12 PM
1 – 8 – 1000 – 5/8/2012 2:23:12 PM

I will pass in the @PlantID int, @IngredientID int, and @Days int. I want to sum the AmountBatched from both tables and display the total for each given day. The @Days will indicate the number of days to query off of previous to the current date. I would also like to eliminate weekends from the results. For example when stored procedure is run passing the following values @PlantID = 1, @IngredientID = 8, and @Days = 14. If date procedure is run is 5/9/2012, would like to summarize for 4/25/2012 to 5/8/2012 excluding weekends if possible.

Results
Date – AmountBatched
4/25/2012 – 0
4/26/2012 – 0
4/27/2012 -- 0
4/30/2012 -- 2100
5/1/2012 -- 2000
5/2/2012 -- 0
5/3/2012 -- 4500
5/4/2012 -- 11000
5/7/2012 -- 25000
5/8/2012 – 8000

Notice 4/28, 4/29, 5/5, and 5/6 are eliminated from the results, which are weekends. Is this possible in a sql stored procedure? I am writing an app in vb .net and am hoping to get the results I need in a single call to sql server and not have to make several calls back. I have not worked with advanced datetime methods in sql server before.

View 6 Replies View Related

Stored Procedure That Needs To Loop Through DataSet And Summarize Based On TypeCode

Apr 27, 2004

Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.

This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.

Region FullName SHARP Year Ann Goal YTD Goal YTDActual
Region1 Doe10, John X 2003 20400 5100 0 Select
Region1 Doe10, John X 2003 0 0 3987 Select
Region1 Doe11, John X 2003 29645 7411.25 0 Select
Region1 Doe11, John X 2003 0 0 5377 Select

Here's my stored procedure:

CREATE PROCEDURE spFilterRegion

@RIDsent As Integer,
@StatusSent As Integer,
@SelectedRegion As NVARCHAR (50) Output

AS
SELECT Region.CountryID,
Employee.RegionID,
Employee.StatusID,
Employee.SHARP,
CASE
When Employee.SHARP = 1 THEN "X"
ELSE ""
END AS SHARPresult,
Employee.LastName,
Employee.FirstName,
Employee.LastName + ', ' + FirstName AS FullName,
Employee.EmployeeID,
Region.RegionName,
ProducerRevenue.RevenueTypeID,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS AnnGoal,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE())
ELSE 0
END AS YTDGoal,
CASE
When ProducerRevenue.RevenueTypeID = 2 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS Actual,
ProducerRevenue.YearID
FROM Employee
LEFT OUTER JOIN ProducerRevenue
ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND
ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND
ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND
ProducerRevenue.StatusID = 1 AND
ProducerRevenue.RevenueTypeID <= 2
LEFT OUTER JOIN Region
ON Employee.RegionID = Region.RegionID
WHERE Employee.StatusID = @StatusSent AND
Employee.RegionID = @RIDsent AND
Employee.RoleID = 1
GROUP BY Region.CountryID,
Employee.RegionID,
Region.RegionName,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
ORDER BY Region.CountryID,
Employee.RegionID,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID

View 1 Replies View Related

Raw Partitions

Jan 9, 2002

Does anyone have any statistics on the performance gains one can get using raw partitions. The database in question is very IO intensive and performs about 1,000,000 inserts/updates per select.

Thanks...

View 2 Replies View Related

SQL Installed On Both Partitions

Oct 12, 1999

Please help!

I have a server that has SQL Server installed on both C and D drives. The SQL Server software is currently running from the C drive and the live databases and backups are stored on the D drive.

I need to have everything on the D drive. Is there an easy way to make the registry point to the D drive without reinstalling SQL Server? The software will needs to run from the D drive because the C drive is running out of disk space. I will also need to delete the whole C:mssql directory.

Thanks.

View 2 Replies View Related

Moving Partitions From One DB To Another

Jun 19, 2008

assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???

i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...

ALTER TABLE [Production].[TransactionHistory]
SWITCH PARTITION 1
TO [Production].[TransactionHistoryArchive] PARTITION 2;

SlayerS_`BoxeR` + [ReD]NaDa

View 9 Replies View Related

Configurations For Partitions.

Aug 23, 2007

Hi experts,

In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.

Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )

Any real time experience (other than referring links) would be really helpful for me.

Thanks in advance,

Hariarul

View 1 Replies View Related

Merging Partitions

May 21, 2008

Hi There,

I have a quick question regarding merging cube partitions.
If I create partitions sliced by date (let's assume we have year level partitions like 2006,2007,2008...)
Later, if I want to merge selected partition to another partition , for example I have history partition and 2006 partition and I want to merge 2006 to history partition then I can simply merge them using ' merge partition' through Management Studio.

My question is that in script, History partition has condition which is where clause and restricted by year level (i.g. WHERE date < '01-01-2006' ) ; however after merging , script won't change like WHERE date <'01-01-2007').

If so, whenever I merge partitions then I have to alter the script as well based on selected merge partion?
I need to refresh history partition once a month;however even if I merge 2006 partition , once I reprocess history partition then it only process what it was wrote in script. So, after reprocessing 2006 data won't appear in this history partition.
So, wondering it's mentatory to alter the script once partition is merged.
Please give me some comments on this issue.
Thanks in advance.

View 4 Replies View Related

Horizontal Partitions - Not Working As Described

Apr 16, 2002

I am testing horizontal partitions to see whether it is a feasible
option for a project. IF I have a composite Primary Key and the
constraint column (a part of the Primary Key) that helps the
partitioned view is defined with DateTime Data Type, select on a restricted set of data through a partitioned view still tries to access all the tables instead of just one table that contains the data. Is this the case or am I
missing something ?

Any help on this is appreciated.

Here is what I am trying to do.

************************************************** **************

CREATE TABLE [dbo].[tst01] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL ,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tst02] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [PK_tst01] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [PK_tst02] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [CK_tst01] CHECK (Dt between '11/1/2002' and '11/30/2002')
GO

ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [CK_tst02] CHECK (Dt between '12/1/2002' and '12/31/2002')
GO

insert into tst01 values('11/1/2002', 1, 'SS')
insert into tst01 values('11/2/2002', 2, 'KK')
insert into tst01 values('11/3/2002', 3, 'DD')
Go

insert into tst02 values('12/1/2002', 1, 'LL')
insert into tst02 values('12/2/2002', 2, 'MM')
insert into tst02 values('12/3/2002', 3, 'GG')
Go

CREATE VIEW vtst AS
SELECT * FROM tst01
UNION ALL
SELECT * FROM tst02
Go

SELECT * FROM vtst WHERE dt = '11/1/2002'

************************************************** *********

When I look at the Execution Plan, it shows that clustered index seek
would be performed on both the tables.

StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Nov 30 2002
12:00AM' AND Convert([@1])>='Nov 1 2002 12:00AM')))
| |--Clustered Index
Seek(OBJECT:([test].[dbo].[tst01].[PK_tst01]),
SEEK:([tst01].[Dt]=Convert([@1])) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2002
12:00AM' AND Convert([@1])>='Dec 1 2002 12:00AM')))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[tst02].[PK_tst02]),
SEEK:([tst02].[Dt]=Convert([@1])) ORDERED FORWARD)

Thanks,
Sathish

View 1 Replies View Related

Update Horizontal Partitions

Feb 24, 2004

I'm considering using horizontal partitions to separate my data by year.
For example, SomeTable_2004, SomeTable_2003, etc. This works well for backups, maintenance, etc. because I'm working with 150+ GB of data. I'll be a partitioned view for queries.

However, I'm new at this and have a few questions. I would also like to do partitioned updates or inserts. But I need to make sure that the tables don't use similar primary keys. Does that make sense? I need to make sure that the primary keys from the first table are not used again in the second table.

SomeTable_2003
primary keys: 1,5,8,9,15

SomeTable_2004
primary keys: 2,3,4,10

I don't really care what keys are used on what table, as long as they are different. I have apps that already use this data, and I don't want to change the application logic.

Thanks,
T

View 1 Replies View Related

SQL Server 2014 :: How To Set Up The Partitions

Oct 1, 2015

how to set up the partitions.I have a transaction table with 50 million records that's very hard to query. it holds data for the last 4 years but the application only ever looks at the last 6 months so i believe this is and ideal candidate for partitioning.

Would it be better to

1) create a partition based on each year for all data so would have a 2015, 2014, 2013, 2012?
2) create 1 partition based on month for this years data then 3 based on year so would have jan,feb,march,april,may..., 2014, 2013, 2012

For 1) would you have to perform some maintenance at the turn of each year for accommodating the next years data. For 2) although this would give better performance as query's are mostly in the last 6 months wouldn't this have more maintenance to move month data to year partitions come the turn of the year and then create the next years months partitions.

View 9 Replies View Related

Physical Disk Partitions

May 21, 2007

I just inherited a dev box, and need to do some performance analyzing on a 40 gig db for a client. Time is of the essence!

My question is that this dev box only has one disk partition (c: drive). Is it a huge deal that I don't have the db system files on one drive, with the data files on another, and tempdb on another,etc.....

View 1 Replies View Related

Table Partitions & RAID 5.

Aug 28, 2007

Hi experts,

We have a huge table with around 250 million records and have implemented SQL server 2005's new table partitioning feature. Now the data seems to be evenly spread across 20 different filegroups ( each 5 GB approx ) for the same table that was occupying 100 GB itself in the PRIMARY filegroup earlier.

Still the query response times have not come down drastically but we could see a good improvement in the execution plans now.

WE ARE USING RAID 5 IN OUR PRODUCTION ENVIRONMENT. ANY IDEA / THOUGHT ON HOW TO PLACE THE PARTITIONED FILEGROUPS AND THE LOG FILES IN THE RAID 5 (BTW , I'm very new to RAID concepts , any detailed instruction would be helpful ).

Any help would be greatly appreciated.

Thanks,

Hariarul

View 8 Replies View Related

Better Table Management (partitions?)

Oct 31, 2006

Hi,

For my work I am now learning Sql server 2005 and I have been given a database that has been set up by someone else to work with. It is my job to get the database ready for use in reports.

My problem is that the current database has one huge table with almost 8GB of data. The table contains data from 2004 to present (and growing) from 14 different countries. The reports we use are mostly per country, but we also want to compare the 14 countries to eachother for say, whole 2006. At the moment the table is stored in one single file instead of using partitions.

I believe partitions can give a good performance boost when running the queries. But how do I do this? Currently the country codes are just plain text, can they be used for partitions?

Any advice would be welcome,

Thanks!

View 5 Replies View Related

Moving Indexes To Seperate Partitions

Jul 8, 1999

I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.

Thanks in advance.

Brad Keck

View 2 Replies View Related

SOS - Table Partitions Or Indexed Views.

Feb 26, 2008

Hi Experts,

We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years).

Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database.

Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.

1. Will SQL server table paritioning help in this scenario ?

Or

2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?

Any help would be greatly appreciated.

Thanks in advance,

Hariarul

View 4 Replies View Related

Analysis :: Aggregation Design - Using Partitions

Jul 31, 2015

I have 3 partitions using a year grouping. Current year, previous 4 years, older than 5 years. I have two measure groups, one is a distinct count, so I actually have 6 partitions.I also use usage based optimization to build my aggregations. Should each partition have a separate aggregation or should there be one for each measure group?

View 5 Replies View Related

Transact SQL :: Partitioning Performance With 15 Min Partitions

Oct 27, 2015

We are currently developing an OLTP application, which will need to purge data when it becomes older than 1 hour.Rather than having a process which deletes rows periodically (and risks locking the tables), I am considering using partitioning on a rolling 15 minute window.The idea is to have 5 active partitions, with the 5<sup>th</sup> one being swapped out, merged and a new one split in. This will allow data to live to a max of 1 hour 15 mins,which is acceptable.

Actually, I will have 8 partitions; there will be 4 partitions set in the future, just to ensure when the last partition is split, there isn’t any data movement, as the newest partition will be empty.I am wondering if there will be any performance issues due to partition swapping, merging and splitting every 15 minutes? The application will have a high volume of users when live. I think this should be a better option that continually deleting from the tables.

View 6 Replies View Related

Overlapping Partitions And Join Filters

Oct 2, 2006

I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.

The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.

When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.

What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.

The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.

Am I seeing this because I said my partitions will overlap when I created the table articles?

Thanks for any help

Graham

View 2 Replies View Related

SQL Server 2005 Partitions And SAN Array

Jul 3, 2007

I am looking for information/suggestions in regards to portioning data across a large SAN. The database is several TB in size, and we are looking to partition the data on a date so that the current data is always the fastest available and history can be set as read only but still be available.

If the server has 8 drive letters how can I ensure that the 4 data drives are stripped across the most physical drives for the most through-put??

Are there any specific questions I need to ask the server technicians?

Any pitfalls I should be aware off??

Thanks!!!

View 1 Replies View Related

Dynamically Add Partitions To A SSAS Cube

May 9, 2007

The environment here is SSIS ETL feeding a Fact Table. The Fact Table is pulled into SSAS as a cube and reporting services are handled there. I am on the ETL side and don't pretend to know all the processing that happens with cubes, etc.

What we are trying to accomplish is to add partitions to a Cube via the ETL processing. The partitions should be incremented by Day, i.e. 20070501, 20070502, etc.

This is currently processed manually by the Reporting developer and we are looking for an automated process to reduce errors and hand work.

I have explored the following objects: Partition Processing DataFlow Destination and did not find much documentation or examples on it's use. If you have any information on this stage, please reply with such.

The other option is the Analysis Services Processing Control Flow. I understand that we can process Analysis Server objects as part of our package. Is there a way to incorporate a Partitioning Script in this object? If so, how. Again, I did not find detailed examples on the use of this object.

If you have experience with either of these, please feel free to reply. I appreciate any and all comments.

View 5 Replies View Related