Having Count

• Ant-Green

  SSC Guru

  Points: 113499

  More actions

  March 28, 2012 at 3:43 am

  #1465558

  another way to do it

  CREATE TABLE [dbo].[tab1](

  [NUM] [int] NULL,

  [bdDQLinkID] [nvarchar](255) NULL,

  [bdDQID] [nvarchar](255) NULL,

  [bdID] [int] NOT NULL,

  [bdSalutation] [nvarchar](255) NULL,

  [bdFirstName] [nvarchar](255) NULL,

  [bdMiddleName] [nvarchar](255) NULL,

  [bdSurname] [nvarchar](255) NULL,

  [bdFullName] [nvarchar](255) NULL,

  [bdTitle] [nvarchar](255) NULL,

  [bdPhoneNumber] [nvarchar](255) NULL,

  [bdEmail] [nvarchar](255) NULL,

  [bdWebAddress] [nvarchar](255) NULL,

  [bdOrganisation] [nvarchar](255) NULL,

  [bdAddress1] [nvarchar](255) NULL,

  [bdAddress2] [nvarchar](255) NULL,

  [bdAddress3] [nvarchar](255) NULL,

  [bdAddress4] [nvarchar](255) NULL,

  [bdPostTown] [nvarchar](255) NULL,

  [bdCounty] [nvarchar](255) NULL,

  [bdPostcode] [nvarchar](255) NULL,

  [bdCountry] [nvarchar](255) NULL,

  [bdInvoiceNo] [nvarchar](255) NULL,

  [bdInvtot] [nvarchar](255) NULL,

  [bdVehicleRegNo] [nvarchar](255) NULL,

  [bdPlant] [nvarchar](255) NULL,

  [bdCreatedOn] [datetime] NULL,

  [bdCarMan] [nvarchar](255) NULL,

  [bdCarMod] [nvarchar](255) NULL,

  [bdCarManYear] [nvarchar](255) NULL,

  [bdMileage] [nvarchar](255) NULL,

  [bdCustomer] [nvarchar](255) NULL,

  [bdBranchCode] [nvarchar](255) NULL,

  [bdURNNo] [nvarchar](255) NULL,

  [bdSuccess] [nvarchar](255) NULL,

  [bdBranchType] [nvarchar](255) NULL,

  [bdMonth] [nvarchar](255) NULL,

  [bdMarked] [nvarchar](255) NULL,

  [bdDQFlag] [nvarchar](255) NULL,

  [mdDQID] [nvarchar](255) NULL,

  [mdID] [int] NOT NULL,

  [mdSalutation] [nvarchar](255) NULL,

  [mdFirstName] [nvarchar](255) NULL,

  [mdMiddleName] [nvarchar](255) NULL,

  [mdSurname] [nvarchar](255) NULL,

  [mdFullName] [nvarchar](255) NULL,

  [mdTitle] [nvarchar](255) NULL,

  [mdPhoneNumber] [nvarchar](255) NULL,

  [mdEmail] [nvarchar](255) NULL,

  [mdWebAddress] [nvarchar](255) NULL,

  [mdOrganisation] [nvarchar](255) NULL,

  [mdAddress1] [nvarchar](255) NULL,

  [mdAddress2] [nvarchar](255) NULL,

  [mdAddress3] [nvarchar](255) NULL,

  [mdAddress4] [nvarchar](255) NULL,

  [mdPostTown] [nvarchar](255) NULL,

  [mdCounty] [nvarchar](255) NULL,

  [mdPostCode] [nvarchar](255) NULL,

  [mdCountry] [nvarchar](255) NULL,

  [mdInvoiceNo] [nvarchar](255) NULL,

  [mdInvtot] [nvarchar](255) NULL,

  [mdVehicleRegNo] [nvarchar](255) NULL,

  [mdPlant] [nvarchar](255) NULL,

  [mdCreatedOn] [datetime] NULL,

  [mdCarMan] [nvarchar](255) NULL,

  [mdCarMod] [nvarchar](255) NULL,

  [mdCarManYear] [nvarchar](255) NULL,

  [msMileage] [nvarchar](255) NULL,

  [mdCustomer] [nvarchar](255) NULL,

  [mdBranchCode] [nvarchar](255) NULL,

  [mdURNNo] [nvarchar](255) NULL,

  [mdSuccess] [nvarchar](255) NULL,

  [mdBranchType] [nvarchar](255) NULL,

  [mdMonth] [nvarchar](255) NULL,

  [mdMarked] [nvarchar](255) NULL,

  [mdDQFlag] [nvarchar](255) NULL,

  [mdDQLinkID] [nvarchar](255) NULL

  ) ON [PRIMARY]

  GO

  DECLARE @Table TABLE (Num INT, ID INT)

  INSERT INTO @Table

  SELECT

  COUNT(bd.DQLinkID) AS Num,

  bd.DQLinkID as ID

  FROM

  [Branch data 2009 - 2012] AS bd

  GROUP BY

  bd.DQLinkID

  DECLARE @num INT, @a INT = 1

  SELECT @num = MAX(num) FROM @table

  WHILE @a <= @num

  BEGIN

  INSERT INTO tab1

  SELECT

  @a as NUM,

  bd.DQLinkID as bdDQLinkID,

  bd.DQID as bdDQID,

  bd.ID as bdID,

  bd.Salutation as bdSalutation,

  bd.[First Name] as bdFirstName,

  bd.[Middle Name] as bdMiddleName,

  bd.Surname as bdSurname,

  bd.[Full Name] as bdFullName,

  bd.Title as bdTitle,

  bd.[Phone number] as bdPhoneNumber,

  bd.Email as bdEmail,

  bd.[Web Address] as bdWebAddress,

  bd.Organisation as bdOrganisation,

  bd.[Address Line 1] as bdAddress1,

  bd.[Address Line 2] as bdAddress2,

  bd.[Address Line 3] as bdAddress3,

  bd.[Address Line 4] as bdAddress4,

  bd.[Post Town] as bdPostTown,

  bd.County as bdCounty,

  bd.Postcode as bdPostcode,

  bd.Country as bdCountry,

  bd.[Invoice No] as bdInvoiceNo,

  bd.Invtot as bdInvtot,

  bd.VehicleRegNo as bdVehicleRegNo,

  bd.Plant as bdPlant,

  bd.[Created on] as bdCreatedOn,

  bd.CarManufacturer as bdCarMan,

  bd.[Car model] as bdCarMod,

  bd.CarManufactYear as bdCarManYear,

  bd.Mileage as bdMileage,

  bd.Customer as bdCustomer,

  bd.[branch code] as bdBranchCode,

  bd.[urn no] as bdURNNo,

  bd.success as bdSuccess,

  bd.[branch type] as bdBranchType,

  bd.[Month] as bdMonth,

  bd.Marked as bdMarked,

  bd.DQFlag as bdDQFlag,

  md.DQID as mdDQID,

  md.ID as mdID,

  md.Salutation as mdSalutation,

  md.[First Name] as mdFirstName,

  md.[Middle Name] as mdMiddleName,

  md.Surname as mdSurname,

  md.[Full Name] as mdFullName,

  md.Title as mdTitle,

  md.[Phone number] as mdPhoneNumber,

  md.Email as mdEmail,

  md.[Web Address] as mdWebAddress,

  md.Organisation as mdOrganisation,

  md.[Address Line 1] as mdAddress1,

  md.[Address Line 2] as mdAddress2,

  md.[Address Line 3] as mdAddress3,

  md.[Address Line 4] as mdAddress4,

  md.[Post Town] as mdPostTown,

  md.County as mdCounty,

  md.Postcode as mdPostCode,

  md.Country as mdCountry,

  md.[Invoice No] as mdInvoiceNo,

  md.Invtot as mdInvtot,

  md.VehicleRegNo as mdVehicleRegNo,

  md.Plant as mdPlant,

  md.[Created on] as mdCreatedOn,

  md.CarManufacturer as mdCarMan,

  md.[Car model] as mdCarMod,

  md.CarManufactYear as mdCarManYear,

  md.Mileage as msMileage,

  md.Customer as mdCustomer,

  md.[branch code] as mdBranchCode,

  md.[urn no] as mdURNNo,

  md.success as mdSuccess,

  md.[branch type] as mdBranchType,

  md.[month] as mdMonth,

  md.Marked as mdMarked,

  md.DQFlag as mdDQFlag,

  md.DQLinkID as mdDQLinkID

  FROM

  [Branch data 2009 - 2012] AS bd

  INNER JOIN

  [main mailing data] AS md

  ON

  bd.dqlinkid = md.dqlinkid

  WHERE

  bd.DQLinkID IN (SELECT ID FROM @Table WHERE Num = @a)

  ORDER BY 2,1

  SET @a = @a + 1

  END

  GO

  SELECT * FROM tab1 WHERE num = ? --Where ? is the number of dupes you want, 1,2,3,4,5,6,7,8,9,10,11,12,13 etc etc

  GO

  DROP TABLE tab1

  GO