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 they
are net changes.
Example:
A student registers one class for the first time for the Fall quarter
on a Monday. A report would reflect that change for Monday.
Next, the same student adds another class on Tuesday. Since the student
was already counted on Monday, I dont want the student to count on
Tuesday.
On Wednesday, the student decides to drop both classes. Since the
student no longer has any classes, I wish to decrement the student
count on Wednesday for that one student.
If the same student adds a new class on Friday, then they would count
on Friday since their previous classes net to zero.
After the end of the session, I would be able to sum up the daily
balance of adds and drops and it would net out to be equal with the
total number of unique students registered for the quarter.
- Students can add and or drop classes on the same day, or on different
days.
- I need to know when the net effect when a student is changed and
reflect that quantity on the date for the quarter (SESSION).
We have reports on our legacy system written in IBM's Universe (its a
business basic). Its pretty straight forward as we would traverse the
data using a basic program. However, trying to something in batch in
SQL has eluded me.

What I am looking to select:
What I am looking to select:
SESSION DATE STUDENT_ADDS STUDENT_DROPS
200602 2005-07-18 1 0
200602 2005-08-23 1 0
2006002 2005-09-30 0 1

TIA
Rob
(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 I
selected 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 REGTRACK
VALUES(250,'200602','MAT100001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)
INSERT INTO REGTRACK
VALUES(250,'200602','ENG200001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','BUS100002011','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105001011','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105002011','A',CONVERT(DAT ETIME,'2005-07-19'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105001011','D',CONVERT(DAT ETIME,'2005-07-19'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105002011','D',CONVERT(DAT ETIME,'2005-09-30'),1)
INSERT INTO REGTRACK
VALUES(265,'200602','PAD500001024','A',CONVERT(DAT ETIME,'2005-08-26'),1)
INSERT INTO REGTRACK
VALUES(266,'200602','CIS110001006','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','ECO100001004','A',CONVERT(DAT ETIME,'2005-09-07'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','BUS520012016','A',CONVERT(DAT ETIME,'2005-09-07'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','BUS520012016','D',CONVERT(DAT ETIME,'2005-10-10'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','ECO100001004','D',CONVERT(DAT ETIME,'2005-10-10'),1)
INSERT INTO REGTRACK
VALUES(275,'200602','ITB300001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)
INSERT INTO REGTRACK
VALUES(275,'200602','BUS310006016','A',CONVERT(DAT ETIME,'2005-08-31'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','FIN100002016','A',CONVERT(DAT ETIME,'2005-07-28'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','POL300003016','A',CONVERT(DAT ETIME,'2005-07-28'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','FIN100002016','D',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','MKT200002016','A',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','CIS105004010','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','BUS100005010','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','CIS105004010','D',CONVERT(DAT ETIME,'2005-11-15'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','BUS100005010','D',CONVERT(DAT ETIME,'2005-11-28'),1)
INSERT INTO REGTRACK
VALUES(243172,'200602','ENG102001001','A',CONVERT( DATETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(243172,'200602','CIS105023016','A',CONVERT( DATETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(243172,'200602','ACC100002010','A',CONVERT( DATETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(334,'200602','MAT300009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)
INSERT INTO REGTRACK
VALUES(334,'200602','CIS111009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)
INSERT INTO REGTRACK
VALUES(256542,'200602','CIS460002016','A',CONVERT( DATETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(256542,'200602','CIS500019016','A',CONVERT( DATETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(349,'200602','CIS500001003','A',CONVERT(DAT ETIME,'2005-09-22'),1)
INSERT INTO REGTRACK
VALUES(255713,'200602','BUS520008016','A',CONVERT( DATETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(359,'200602','BUS531001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(359,'200602','CIS514001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-09-16'),1)
INSERT INTO REGTRACK
VALUES(367,'200602','ENG102005001','D',CONVERT(DAT ETIME,'2005-10-26'),1)
INSERT INTO REGTRACK
VALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-11-08'),1)
INSERT INTO REGTRACK
VALUES(368,'200602','CIS110003016','A',CONVERT(DAT ETIME,'2005-08-16'),1)
INSERT INTO REGTRACK
VALUES(368,'200602','HUM300001016','A',CONVERT(DAT ETIME,'2005-08-16'),1)
INSERT INTO REGTRACK
VALUES(369,'200602','BUS530011016','A',CONVERT(DAT ETIME,'2005-09-13'),1)
INSERT INTO REGTRACK
VALUES(381,'200602','BUS100026016','A',CONVERT(DAT ETIME,'2005-08-02'),1)
INSERT INTO REGTRACK
VALUES(381,'200602','ECO405001016','A',CONVERT(DAT ETIME,'2005-08-02'),1)
INSERT INTO REGTRACK
VALUES(385,'200602','BUS100002008','A',CONVERT(DAT ETIME,'2005-07-27'),1)
INSERT INTO REGTRACK
VALUES(385,'200602','BUS107001008','A',CONVERT(DAT ETIME,'2005-07-27'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','ECO405008016','A',CONVERT( DATETIME,'2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','POL300011016','A',CONVERT( DATETIME,'2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','HUM100022016','A',CONVERT( DATETIME,'2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','HUM100022016','D',CONVERT( DATETIME,'2005-10-03'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','HUM400011016','A',CONVERT(DAT ETIME,'2005-08-17'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499002016','A',CONVERT(DAT ETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499001016','D',CONVERT(DAT ETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499001015','A',CONVERT(DAT ETIME,'2005-09-22'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499002016','D',CONVERT(DAT ETIME,'2005-09-22'),1)
INSERT INTO REGTRACK
VALUES(397,'200602','ENG095001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(397,'200602','ENG096001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(397,'200602','ENG097001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(398,'200602','HUM200005016','A',CONVERT(DAT ETIME,'2005-08-05'),1)
INSERT INTO REGTRACK
VALUES(398,'200602','HUM400004016','A',CONVERT(DAT ETIME,'2005-08-05'),1)
INSERT INTO REGTRACK
VALUES(398,'200602','CIS427001016','A',CONVERT(DAT ETIME,'2005-08-05'),1)
INSERT INTO REGTRACK
VALUES(406,'200602','ECO550008016','A',CONVERT(DAT ETIME,'2005-08-01'),1)
INSERT INTO REGTRACK
VALUES(406,'200602','MAT540004016','A',CONVERT(DAT ETIME,'2005-08-01'),1)
INSERT INTO REGTRACK
VALUES(406,'200602','MAT540004016','D',CONVERT(DAT ETIME,'2005-11-14'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','POL300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','SOC300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','ACC403003016','A',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','SOC300006016','D',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','MAT540001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','BUS531001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ACC560001021','D',CONVERT(DAT ETIME,'2005-09-27'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ENG102001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','BUS533001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','BUS531001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','MAT540001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ENG102001021','D',CONVERT(DAT ETIME,'2005-09-29'),1)
INSERT INTO REGTRACK
VALUES(448,'200602','ENG102013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)
INSERT INTO REGTRACK
VALUES(448,'200602','HUM101013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)
INSERT INTO REGTRACK
VALUES(459,'200602','HUM101002010','A',CONVERT(DAT ETIME,'2005-08-25'),1)
INSERT INTO REGTRACK
VALUES(459,'200602','BUS310001010','A',CONVERT(DAT ETIME,'2005-08-25'),1)
INSERT INTO REGTRACK
VALUES(466,'200602','HUM100004016','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(466,'200602','CIS111003016','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(479,'200602','BUS100050016','A',CONVERT(DAT ETIME,'2005-09-20'),1)
INSERT INTO REGTRACK
VALUES(253486,'200602','ENG102001012','A',CONVERT( DATETIME,'2005-10-05'),1)
INSERT INTO REGTRACK
VALUES(253486,'200602','MAT105001012','A',CONVERT( DATETIME,'2005-10-05'),1)
INSERT INTO REGTRACK
VALUES(490,'200602','BUS532001003','A',CONVERT(DAT ETIME,'2005-09-20'),1)
INSERT INTO REGTRACK
VALUES(509,'200602','ENG102021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)
INSERT INTO REGTRACK
VALUES(509,'200602','MAT100021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)
INSERT INTO REGTRACK
VALUES(511,'200602','LEG100001012','A',CONVERT(DAT ETIME,'2005-08-29'),1)
INSERT INTO REGTRACK
VALUES(556,'200602','LEG100013016','A',CONVERT(DAT ETIME,'2005-09-24'),1)
INSERT INTO REGTRACK
VALUES(556,'200602','SOC304001003','A',CONVERT(DAT ETIME,'2005-09-24'),1)
INSERT INTO REGTRACK
VALUES(576,'200602','ACC100002026','A',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(576,'200602','BUS100043016','A',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(581,'200602','CIS288001010','A',CONVERT(DAT ETIME,'2005-09-08'),1)
INSERT INTO REGTRACK
VALUES(581,'200602','CIS450001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)
INSERT INTO REGTRACK
VALUES(581,'200602','CIS286001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)
INSERT INTO REGTRACK
VALUES(583,'200602','BUS490001017','A',CONVERT(DAT ETIME,'2005-08-09'),1)
INSERT INTO REGTRACK
VALUES(583,'200602','SOC300004016','A',CONVERT(DAT ETIME,'2005-08-09'),1)
INSERT INTO REGTRACK
VALUES(583,'200602','BUS490001017','D',CONVERT(DAT ETIME,'2005-09-07'),1)

View 2 Replies


ADVERTISEMENT

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

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

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

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

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

Unique Index Vs Unique Constraint

Mar 7, 2001

Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?

Which one do one use ?

thanks
sonali

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?

View 2 Replies View Related

Unique Index Vs Unique Constraints

Mar 26, 2008



hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?

View 12 Replies View Related

How To Select Unique Row When Entire Row Not Unique?

Mar 12, 2008

I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code



Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword





and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0

This could return multiple part numbers and If they have entered notes I want the row with the notes

Thank You
Dominic Mancl

View 1 Replies View Related

What 's Difference Between Unique Key And Unique Index

Nov 13, 2007

What 's difference between Unique key and unique index in SQL server 2005?

View 9 Replies View Related

EM Registration

Feb 26, 2002

New to 2000, I used EM to register the SQL server and the name is in the format of Server nameSQL Server Name. In SQL Sever 7.0 I always had one name which is my preference. How do I do this. I also am having trouble using a ODBC connection and connecting to SQL Sever from EM from other computers. I am wondering if the problem is the name format?

View 3 Replies View Related

New Sql Registration

Sep 28, 2004

I have a database that runs sql 7. Most of our workstations are running on a windows 98 platform, however, a few are running windows 2000. I am trying to set up a new sql registration in enterprise manager, but the connection is failing. All of the 98 machines are logging in with a sql user name, but I was told to log the 2000 machine as a NT server. If you can understand what my problem is, could you please advise me?
thanks :confused:

View 10 Replies View Related

SQL Registration

Oct 6, 2004

Our current production DBs are on windows 2000 and SQL Server 2000.
We just installed SQL Server on new windows 2003 server.
We need to copy the DB on to this new server.
When I tried to register the new server, I am getting the error message as server does not exists or access denied.

Could some one please assist on this why I am getting this error and how to solve this.

Thanks

View 10 Replies View Related

Registration

Sep 6, 2006

I have a client that is running SQL 2005 Standard edition and they purchased 10 more Cal's, do I have to enter new registration keys, and if so where do you enter them?

Thanks in advance.

Joe

View 3 Replies View Related

Run A New Server Registration

Nov 16, 2006

Hi:
My pc is windows 2003 server and I have installed SQL SERVER 2005 EXPRESS.
From Management Studio I made a New Server Registration with Windows Authentication.
I can't run the new server, its icon appears with a white dot (instead of green when a server is running or red when the
server is stopped)
I go to server properties and click the "Test" button and I get the next message error:
Testing the registered server failed. Verify the server name, credentials, an database, an then click Test again.
An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)
what could be wrong?
How can I run the server?
Thanks!

View 5 Replies View Related

New Sql Server Registration

Feb 12, 2008

hi,
when we go for a new sql server registration, then there are two authentication modes i.e either through windows authentication or through sql server authentication.
May anyone please tell me the default password for the windows authentication mode for user "sa".
Thanks & Regards,
Sitangshu Roy

View 1 Replies View Related

Registration Of SQL With WEB Server

Apr 27, 2005

i need to know how can i register my SQL server with WEb based SQL server
i have the address MSSQL2.MINUTESUAE.COM now how can i register my SQL Server with them.
when i m going to conect with this server it register but after that Database not show it take to much time.

View 1 Replies View Related

Server Registration

Oct 5, 1999

Hi

I'm attempting to register a new sql server through the enterprise manager and recieving the following error message: login incorrect, userid not found.

I'm using the sql server wizard to register and selecting the sql server login information as the authentication mode. The login name and password is a valid account on the Windows NT server when i view the domain information.

any responses appreciated

thanks
Todd Minifie

View 1 Replies View Related

Server Registration In Sql

Nov 1, 2004

hi ,

i am trying to upgrade from sql 7 to sql 200 .. i am doing a new clean install using the copy database wizard.

both sql 7 and sql 2000 are on different server connected on the network.

the problem i am facing is that i am not able to see the sql 7 server under the source database.

i have tried doing a new registration on sql 7 .. it does not seem to show up.

i have tried using the ip address. does not work

could you tell me how i can register the sql 7 server so that it is visible to all the other servers on the network..

thanks,

ggupta

View 3 Replies View Related

New Server Registration Help

Mar 27, 2006

Hi Guys,

I was trying to register a new server. When I start the new ser. registration wizzard I've selected a pc with the MSDE installed and when I entered all the correct passwords it doesn't register. Both pc's are the domain administrators.

I tried with windows authent. too and still the same. To be onest I was playing with this for about 2 days ;(

Both PC's are runnoing windows xp pro. I did hoewer manage to connect to the MSDE on the windows 2000 server.

Any help is appreciated.

View 2 Replies View Related

Registration Fails

Jun 30, 2004

For years I've had a server registration using sql server auth set up...now it's failing...can't be found not suthed BS...

I can still see mu other boxes in sql mode, and otyhers in mixed, and I can still see my remote boxes in Ireland and Pennysylvania...we're in NJ..

The person next to me does the same thing, had to reboot, but was fine.

I get ice in the winter...

bizzare-o world.

Any ideas?

Never seen anything like it...

I do know that our tech group has been doing some pushes lately...but I checked my settings and they seem fine..but why would I be able to see everything else but this one instance?

View 14 Replies View Related

SQL Server Registration

Aug 23, 2004

This is bizzare stuff...

Be glad if someone could have a look and comment....

I'd really would like to understand this...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38969

View 1 Replies View Related

Registration Problem

Aug 30, 2007

Dear All,
i've sql server 2005 server in my system. and sql 2000 client is also there.
now my problem is i was unable to register my server in sql server 2000.
but i was able to work with query analyser.
is there any way to register 2005 server ?


thank you very much

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

View 5 Replies View Related

Server Registration

Jan 14, 2007

I want to register a new server,

 I go to Server Management Studio -> Right Click Database Engine -> New -> Server registration

Make up a server name and save

Apears the  "..sql server does not allow remote connections..."

I go to surface area configuration for services and connections, but it doenst have any components...

If you please help me...

Thanx

Best Regards

View 3 Replies View Related

Help With New Server Registration

Apr 19, 2006

I am trying for the first time to learn what to do and how to use SQLServer. I am following instructions in Books on Line to make a New Server Registration. The instructions read as follows:

Connecting to Servers




The toolbar of the Registered Servers component has buttons for the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. You can register any of these server types for convenient management. Try this exercise to register the AdventureWorks database.


To register the AdventureWorks database






On the Registered Servers toolbar, click Database Engine if necessary. (It may already be selected.)


Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens.


In the Server name text box, type the name of your SQL Server instance.


In the Registered server name box, type AdventureWorks.


On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.

I did steps 1 and 2 --- no problem. At step 3, for server name I typed MARKSDESKTOPSQLEXPRESS

At step 4 I typed: Adventureworks

At step 5 I went to Connection Properties and at the "Connect to database drop down box there were 2 choices: <default> or <browse server> (not Adventureworks). If I click on browse server, The browse server for Database window pops up but Adventureworks is not listed there either.

I did a search on my C drive and there are lots of Adventureworks files present so I must have downloaded the database OK.

Does anyone know where I go from here to connect to the Adventureworks database so I can continue with this tutorial?

Please help. Thanks

Mark

View 4 Replies View Related

Registration Of Sqlceoledb

Apr 14, 2008

Hi all.
I'm trying to connect with ADOCE 3.1 to a sdf file (SQLCE v 3.5).
I'm not able to open the connection because I think that sqlceoledb is non registered correctily or at all.
I read some post saying that it should be registered via regsvrce (for CE 5.0 in my case).
This tool is avilable in evC 4.0..I found, but I don't have it.
Is there any other way to achieve this or a place where to find the correct regsvrce(I found prevoius versions that don't work).
Where are informations stored in the registry (I mean which keys)


Below the code I use:

Dim cn as Object
Dim rs as Object

Set cn = CreateObject("ADOCE.Connection.3.1")

cn.Open "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=Flashdisk est est.sdf"

Set rs = CreateObject("ADOCE.Recordset.3.1")

rs.Open "SELECT * FROM TestTable",cn,3,3
...... Thanks for any help

View 3 Replies View Related







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