January 14, 2010 at 6:59 pm
The strucutre of the table is:
INSERT INTO [SF].[dbo].[PackageData]
([Package Grp No]
,[Account]
,[Package Item No]
,[Opportunity Name]
,[Opportunity Stage]
,[Course Start Date]
,[Opportunity ID]
,[Group: ID]
,[PackageID]
)
VALUES
(<Package Grp No, nvarchar(255),>
,<Account, nvarchar(255),>
,<Package Item No, nvarchar(255),>
,<Opportunity Name, nvarchar(255),>
,<Opportunity Stage, nvarchar(255),>
,<Course Start Date, datetime,>
,<Opportunity ID, nvarchar(255),>
,<Group: ID, nvarchar(255),>
,<PackageID, nvarchar(255),>
)
The data is:
PGN-0312638,aa, PIT-0068897,000725G, Invoiced, 30/9/2009, 0062000000Af3Wx ,a0520000007P1fb ,a0E200000046Xsm
PGN-0312638,aa, PIT-0068898,001920G, Offer Declined by Student, 20/7/2010, 0062000000Af3ZN ,a0520000007P1fb ,a0E200000046Xsn
PGN-0312639,bb, PIT-0068899,062024A, Invoiced, 7/3/2011, 0062000000Af7To ,a0520000007P59f ,a0E200000046efk
PGN-0312639,bb, PIT-0068900,059619M, Invoiced, 7/12/2009, 0062000000AeX5V, a0520000007P59f, a0E200000046efl
PGN-0312640,cc, PIT-0068902,063957C, 9 - Conditional COE Received, 7/2/2011, 0062000000Af7lx, a0520000007P59p ,a0E200000046egy
PGN-0312640,cc, PIT-0068901,061399M, 9 - Conditional COE Received, 6/2/2013, 0062000000Af7n1, a0520000007P59p, a0E200000046egx
PGN-0312641,dd, PIT-0068903,003896G ,Invoiced, 1/3/2010, 0062000000Af7na, a0520000007P59z ,a0E200000046ehC
PGN-0312641,dd, PIT-0068904,013905G, 9 - Conditional COE Received , 19/7/2010, 00620000007iFxT, a0520000007P59z, a0E200000046ehD
PGN-0312642,ee, PIT-0068905,061397B, 9 - Conditional COE Received , 26/7/2010, 0062000000Af842, a0520000007P5Ai, a0E200000046el6
PGN-0312642,ee, PIT-0068906,032417K, Invoiced, 4/1/2010, 0062000000Af859 ,a0520000007P5Ai, a0E200000046el7
PGN-0312643,ff, PIT-0068907,053607G, 11 - Finalised 7/6/2010, 0062000000Af91C ,a0520000007P5Ax, a0E200000046emv
PGN-0312643,ff, PIT-0068908,032410F, 11 - Finalised 4/1/2010, 0062000000Af8E9, a0520000007P5Ax, a0E200000046emw
PGN-0312643,ff, PIT-0068909,006487A, 10 - Unconditional Offer Accepted, 25/7/2011, 0062000000Af9rJ, a0520000007P5Ax ,a0E200000046emx
The defeniton for duplicatess:
if [Package Grp No] and [Account] are same and no other [Package Grp No] for that account, that record consider as the duplicate.
so the first two rows as duplicates as per my law.
Then for that duplicates we need to update the following fields according to the [Opportunity Stage] and [Course Start Date]
1. Update Accept_Offer__c ='True'
2.Update Offer_Letter_Name__c= The first [opportunity name] in the package base on the [course start date]- That means the Min([course start date]) group by [Package Grp No] and [Account]
3. for other opportunities in this group, if
[Opportunity Stage]=6, then set to 10
[Opportunity Stage]=7, then set to 8
Please help to fix it.
January 14, 2010 at 11:40 pm
i tried this proc to add a serial number and updating one field, but just thinking how can i update the other three fields..
Please give ur thoughts.
ALTER PROCEDURE [dbo].[Update_S]
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PrevPakageGroupID varchar(255)
DECLARE @account varchar(255)
DECLARE @ItemSrNoCount INT
DECLARE @AcceptOffer bit
DECLARE @stagevalue varchar(255)
-- update the serial number
UPDATE dbo.test
SET @ItemSrNoCount = ItemSrNo =
CASE
WHEN [Package Grp No] = @PrevPakageGroupID
THEN @ItemSrNoCount + 1
ELSE 1
END,
@PrevPakageGroupID = [Package Grp No] FROM dbo.test WITH (TABLOCKX) OPTION (MAXDOP 1)
-- update the true field
UPDATE dbo.PackageData
SET @AcceptOffer = [Update Accept_Offer__c] =
CASE
WHEN [Package Grp No] = @PrevPakageGroupID
THEN 0
ELSE 1
END,
@PrevPakageGroupID = [Package Grp No] FROM dbo.PackageData WITH (TABLOCKX) OPTION (MAXDOP 1)
--
-- what can i do for updating the other fields???
END
January 15, 2010 at 3:51 am
Please set up your data as a series of INSERT statements, this will help users to begin work on your problem.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 17, 2010 at 3:47 pm
The sample data as per your request is:
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312638','aa', 'PIT-0068898','001920G', 'Offer Declined by Student', '20/7/2010', '0062000000Af3ZN' ,'a0520000007P1fb' ,'a0E200000046Xsn')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312639','bb',' PIT-0068899','062024A','Invoiced', '7/3/2011', ' 0062000000Af7To' ,'a0520000007P59f' ,'a0E200000046efk')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312639','bb', 'PIT-0068900','059619M', 'Invoiced', '7/12/2009',' 0062000000AeX5V', 'a0520000007P59f', 'a0E200000046efl')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312640','cc', 'PIT-0068902','063957C', ' 9 - Conditional COE Received', '7/2/2011', ' 0062000000Af7lx', 'a0520000007P59p' ,'a0E200000046egy')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312640','cc', 'PIT-0068901','061399M', '9 - Conditional COE Received', '6/2/2013', ' 0062000000Af7n1', 'a0520000007P59p',' a0E200000046egx')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312641','dd', 'PIT-0068903','003896G' ,'Invoiced', ' 1/3/2010', '0062000000Af7na', 'a0520000007P59z' ,'a0E200000046ehC')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312641','dd', 'PIT-0068904','013905G', ' 9 - Conditional COE Received ' , '19/7/2010', '00620000007iFxT', 'a0520000007P59z',' a0E200000046ehD')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312642','ee', 'PIT-0068905','061397B', '9 - Conditional COE Received' , '26/7/2010', '0062000000Af842', 'a0520000007P5Ai', 'a0E200000046el6')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312642','ee',' PIT-0068906','032417K', 'Invoiced', '4/1/2010', '0062000000Af859' ,'a0520000007P5Ai', 'a0E200000046el7')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068907','053607G', '11 - Finalised ', '7/6/2010', '0062000000Af91C' ,'a0520000007P5Ax', 'a0E200000046emv')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068908','032410F', '11 - Finalised', '4/1/2010', ' 0062000000Af8E9', 'a0520000007P5Ax', 'a0E200000046emw')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068909','006487A', '10 - Unconditional Offer Accepted', '25/7/2011', '0062000000Af9rJ', 'a0520000007P5Ax' ,'a0E200000046emx')
January 18, 2010 at 3:02 am
Biz (1/17/2010)
The sample data as per your request is:
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312638','aa', 'PIT-0068898','001920G', 'Offer Declined by Student', '20/7/2010', '0062000000Af3ZN' ,'a0520000007P1fb' ,'a0E200000046Xsn')INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312639','bb',' PIT-0068899','062024A','Invoiced', '7/3/2011', ' 0062000000Af7To' ,'a0520000007P59f' ,'a0E200000046efk')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312639','bb', 'PIT-0068900','059619M', 'Invoiced', '7/12/2009',' 0062000000AeX5V', 'a0520000007P59f', 'a0E200000046efl')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312640','cc', 'PIT-0068902','063957C', ' 9 - Conditional COE Received', '7/2/2011', ' 0062000000Af7lx', 'a0520000007P59p' ,'a0E200000046egy')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312640','cc', 'PIT-0068901','061399M', '9 - Conditional COE Received', '6/2/2013', ' 0062000000Af7n1', 'a0520000007P59p',' a0E200000046egx')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312641','dd', 'PIT-0068903','003896G' ,'Invoiced', ' 1/3/2010', '0062000000Af7na', 'a0520000007P59z' ,'a0E200000046ehC')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312641','dd', 'PIT-0068904','013905G', ' 9 - Conditional COE Received ' , '19/7/2010', '00620000007iFxT', 'a0520000007P59z',' a0E200000046ehD')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312642','ee', 'PIT-0068905','061397B', '9 - Conditional COE Received' , '26/7/2010', '0062000000Af842', 'a0520000007P5Ai', 'a0E200000046el6')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312642','ee',' PIT-0068906','032417K', 'Invoiced', '4/1/2010', '0062000000Af859' ,'a0520000007P5Ai', 'a0E200000046el7')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068907','053607G', '11 - Finalised ', '7/6/2010', '0062000000Af91C' ,'a0520000007P5Ax', 'a0E200000046emv')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068908','032410F', '11 - Finalised', '4/1/2010', ' 0062000000Af8E9', 'a0520000007P5Ax', 'a0E200000046emw')
INSERT INTO [SF].[dbo].[PackageData] ([Package Grp No] ,[Account] ,[SrID],[Update Accept_Offer__c] ,[Offer_Letter_Name__c],[New Stage] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068909','006487A', '10 - Unconditional Offer Accepted', '25/7/2011', '0062000000Af9rJ', 'a0520000007P5Ax' ,'a0E200000046emx')
Msg 109, Level 15, State 1, Line 12
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Also, there are more columns in the INSERT statement than exist in the table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 18, 2010 at 4:44 pm
I tested this one and working fine for me.please check.
I really need to get a help ASAP if u can.INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312638','aa', 'PIT-0068898','001920G', 'Offer Declined by Student', '2010/07/20', '0062000000Af3ZN' ,'a0520000007P1fb' ,'a0E200000046Xsn')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312639','bb',' PIT-0068899','062024A','Invoiced', '2011/03/07', ' 0062000000Af7To' ,'a0520000007P59f' ,'a0E200000046efk')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312639','bb', 'PIT-0068900','059619M', 'Invoiced', '2009/12/07',' 0062000000AeX5V', 'a0520000007P59f', 'a0E200000046efl')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312640','cc', 'PIT-0068902','063957C', ' 9 - Conditional COE Received', '2011/02/07', ' 0062000000Af7lx', 'a0520000007P59p' ,'a0E200000046egy')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312640','cc', 'PIT-0068901','061399M', '9 - Conditional COE Received', '2013/02/06', ' 0062000000Af7n1', 'a0520000007P59p',' a0E200000046egx')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312641','dd', 'PIT-0068903','003896G' ,'Invoiced', '2010/03/01', '0062000000Af7na', 'a0520000007P59z' ,'a0E200000046ehC')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312641','dd', 'PIT-0068904','013905G', ' 9 - Conditional COE Received ' , '2010/07/19', '00620000007iFxT', 'a0520000007P59z',' a0E200000046ehD')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312642','ee', 'PIT-0068905','061397B', '9 - Conditional COE Received' , '2010/07/26', '0062000000Af842', 'a0520000007P5Ai', 'a0E200000046el6')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312642','ee',' PIT-0068906','032417K', 'Invoiced', '2010/01/04', '0062000000Af859' ,'a0520000007P5Ai', 'a0E200000046el7')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068907','053607G', '11 - Finalised ', '2010/06/07', '0062000000Af91C' ,'a0520000007P5Ax', 'a0E200000046emv')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068908','032410F', '11 - Finalised', '2010/01/04', ' 0062000000Af8E9', 'a0520000007P5Ax', 'a0E200000046emw')
INSERT INTO [SF].[dbo].[PackageData1] ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID]) VALUES ('PGN-0312643','ff', 'PIT-0068909','006487A', '10 - Unconditional Offer Accepted', '2011/07/25', '0062000000Af9rJ', 'a0520000007P5Ax' ,'a0E200000046emx')
January 19, 2010 at 5:53 am
Hi Biz
Can you please run the following code and identify those rows which you call Duplicates?
I've rehashed the sample data so that it's now a temporary table.
-- Set up sample data
SET NOCOUNT ON
DROP TABLE #PackageData
CREATE TABLE #PackageData
([Package Grp No] nvarchar(255),
[Account] nvarchar(255),
[Package Item No] nvarchar(255),
[Opportunity Name] nvarchar(255),
[Opportunity Stage] nvarchar(255),
[Course Start Date] datetime,
[Opportunity ID] nvarchar(255),
[Group: ID] nvarchar(255),
[PackageID] nvarchar(255) )
INSERT INTO #PackageData ([Package Grp No] ,[Account] ,[Package Item No] ,[Opportunity Name],[Opportunity Stage],[Course Start Date],[Opportunity ID],[Group: ID],[PackageID])
SELECT 'PGN-0312638', 'aa', 'PIT-0068898', '001920G', 'Offer Declined by Student', '2010/07/20', '0062000000Af3ZN' ,'a0520000007P1fb' ,'a0E200000046Xsn' UNION ALL
SELECT 'PGN-0312639', 'bb', 'PIT-0068899', '062024A', 'Invoiced', '2011/03/07', ' 0062000000Af7To' ,'a0520000007P59f' ,'a0E200000046efk' UNION ALL
SELECT 'PGN-0312639', 'bb', 'PIT-0068900', '059619M', 'Invoiced', '2009/12/07',' 0062000000AeX5V', 'a0520000007P59f', 'a0E200000046efl' UNION ALL
SELECT 'PGN-0312640', 'cc', 'PIT-0068902', '063957C', '9 - Conditional COE Received', '2011/02/07', ' 0062000000Af7lx', 'a0520000007P59p', 'a0E200000046egy' UNION ALL
SELECT 'PGN-0312640', 'cc', 'PIT-0068901', '061399M', '9 - Conditional COE Received', '2013/02/06', ' 0062000000Af7n1', 'a0520000007P59p', 'a0E200000046egx' UNION ALL
SELECT 'PGN-0312641', 'dd', 'PIT-0068903', '003896G', 'Invoiced', '2010/03/01', '0062000000Af7na', 'a0520000007P59z' ,'a0E200000046ehC' UNION ALL
SELECT 'PGN-0312641', 'dd', 'PIT-0068904', '013905G', '9 - Conditional COE Received ' , '2010/07/19', '00620000007iFxT', 'a0520000007P59z', 'a0E200000046ehD' UNION ALL
SELECT 'PGN-0312642', 'ee', 'PIT-0068905', '061397B', '9 - Conditional COE Received' , '2010/07/26', '0062000000Af842', 'a0520000007P5Ai', 'a0E200000046el6' UNION ALL
SELECT 'PGN-0312642', 'ee', 'PIT-0068906', '032417K', 'Invoiced', '2010/01/04', '0062000000Af859' ,'a0520000007P5Ai', 'a0E200000046el7' UNION ALL
SELECT 'PGN-0312645', 'ee', 'PIT-0068906', '032417K', 'Invoiced', '2010/01/04', '0062000000Af859' ,'a0520000007P5Ai', 'a0E200000046el7' UNION ALL
SELECT 'PGN-0312643', 'ff', 'PIT-0068907', '053607G', '11 - Finalised ', '2010/06/07', '0062000000Af91C' ,'a0520000007P5Ax', 'a0E200000046emv' UNION ALL
SELECT 'PGN-0312643', 'ff', 'PIT-0068908', '032410F', '11 - Finalised', '2010/01/04', ' 0062000000Af8E9', 'a0520000007P5Ax', 'a0E200000046emw' UNION ALL
SELECT 'PGN-0312643', 'ff', 'PIT-0068909', '006487A', '10 - Unconditional Offer Accepted', '2011/07/25', '0062000000Af9rJ', 'a0520000007P5Ax' , 'a0E200000046emx'
SET NOCOUNT OFF
/* check it
SELECT [Package Grp No],
[Account],
[Package Item No],
[Opportunity Name],
[Opportunity Stage],
[Course Start Date],
[Opportunity ID],
[Group: ID],
[PackageID]
FROM #PackageData -- (13 row(s) affected)
*/
-- \Set up sample data
SELECT [Package Grp No], [Account], [Row_Count] = COUNT(*)
FROM #PackageData
GROUP BY [Package Grp No], [Account]
ORDER BY [Package Grp No], [Account]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 19, 2010 at 4:17 pm
The rowcount >1 is the duplicates for me.
so the duplicates are:
PGN-0312639bb2
PGN-0312640cc2
PGN-0312641dd2
PGN-0312642ee2
PGN-0312643ff3
January 24, 2010 at 6:02 pm
Any help please??
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply