Single Sum, not cumulative sum

• oolawole

  SSC Veteran

  Points: 285

  More actions

  July 31, 2012 at 8:34 am

  #392864

  Hi,

  I have these tables; Regns, PJs, Smps and His

  CREATE TABLE Regns

  (

  sn INT NOT NULL

  IDENTITY(1, 1) ,

  Regn NVARCHAR(50) NOT NULL

  CONSTRAINT pk_Regns PRIMARY KEY CLUSTERED (sn ASC)

  )

  Go

  Sample data:

  SELECT 'La' UNION ALL

  SELECT 'Be' UNION ALL

  SELECT 'Ib' UNION ALL

  SELECT 'En' UNION ALL

  CREATE TABLE PJs(

  sn int IDENTITY(1,1) NOT NULL,

  acc int NOT NULL,

  pj nvarchar(100) NULL,

  CONSTRAINT PK_PJs PRIMARY KEY CLUSTERED (acc ASC))

  Go

  Sample data:

  SELECT '7002','Home' UNION ALL

  SELECT '7047','GGG' UNION ALL

  SELECT '7364','Discover' UNION ALL

  CREATE TABLE Smps(

  sn int IDENTITY(1,1) NOT NULL,

  acc int NOT NULL,

  regn int NOT NULL,

  smp int NOT NULL

  )

  GO

  ALTER TABLE Smps WITH CHECK ADD CONSTRAINT FK_Smp_PJs FOREIGN KEY(acc)

  REFERENCES PJs (acc)

  ON UPDATE CASCADE

  GO

  ALTER TABLE Smps CHECK CONSTRAINT FK_Smps_PJs

  GO

  ALTER TABLE Smps WITH CHECK ADD CONSTRAINT FK_Smps_Regns FOREIGN KEY(regn)

  REFERENCES Regns (sn)

  ON UPDATE CASCADE

  GO

  ALTER TABLE Smps CHECK CONSTRAINT FK_Smps_Regns

  GO

  Sample data:

  SELECT '7002','560','1' UNION ALL

  SELECT '7002','160','2' UNION ALL

  SELECT '7002','320','3' UNION ALL

  SELECT '7002','320','4' UNION ALL

  SELECT '7047','3582','1' UNION ALL

  SELECT '7047','2035','2' UNION ALL

  SELECT '7047','4772','3' UNION ALL

  SELECT '7047','3741','4' UNION ALL

  SELECT '7364','40','1' UNION ALL

  SELECT '7364','40','3' UNION ALL

  SELECT '7364','40','4' UNION ALL

  CREATE TABLE His(

  sn int IDENTITY(1,1) NOT NULL,

  cst int NOT NULL,

  acc int NOT NULL,

  amt decimal(17, 2) NOT NULL,

  regn int NOT NULL,

  CONSTRAINT PK_His PRIMARY KEY CLUSTERED (sn ASC))

  GO

  ALTER TABLE His WITH CHECK ADD CONSTRAINT FK_His_PJs FOREIGN KEY(acc)

  REFERENCES PJs (acc)

  ON UPDATE CASCADE

  GO

  ALTER TABLE His CHECK CONSTRAINT FK_His_PJs

  GO

  ALTER TABLE His WITH CHECK ADD CONSTRAINT FK_His_Regns FOREIGN KEY(regn)

  REFERENCES Regns (sn)

  ON UPDATE CASCADE

  GO

  ALTER TABLE His CHECK CONSTRAINT FK_His_Regns

  GO

  Sample data:

  SELECT '29','7002','98000.00','1' UNION ALL

  SELECT '27','7002','30100.00','3' UNION ALL

  SELECT '25','7002','31750.00','4' UNION ALL

  SELECT '29','7002','98000.00','1' UNION ALL

  SELECT '27','7002','30100.00','3' UNION ALL

  SELECT '25','7002','31750.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','93250.00','1' UNION ALL

  SELECT '32','7002','3500.00','1' UNION ALL

  SELECT '38','7002','1000.00','1' UNION ALL

  SELECT '27','7002','15500.00','2' UNION ALL

  SELECT '32','7002','1000.00','2' UNION ALL

  SELECT '38','7002','1000.00','2' UNION ALL

  SELECT '27','7002','35100.00','3' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','35300.00','4' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '29','7047','6000.00','1' UNION ALL

  SELECT '27','7047','6000.00','1' UNION ALL

  SELECT '11','7047','13600.00','1' UNION ALL

  SELECT '16','7047','30000.00','1' UNION ALL

  SELECT '29','7047','30000.00','3' UNION ALL

  SELECT '27','7047','45200.00','3' UNION ALL

  SELECT '29','7047','5750.00','2' UNION ALL

  SELECT '27','7047','3200.00','2' UNION ALL

  SELECT '11','7047','5400.00','4' UNION ALL

  SELECT '16','7047','10000.00','4' UNION ALL

  SELECT '15','7047','2500.00','1' UNION ALL

  SELECT '15','7047','2000.00','2' UNION ALL

  SELECT '15','7047','4200.00','3' UNION ALL

  SELECT '15','7047','3600.00','4' UNION ALL

  SELECT '26','7047','54000.00','1' UNION ALL

  SELECT '29','7047','13000.00','1' UNION ALL

  SELECT '27','7047','5000.00','1' UNION ALL

  SELECT '29','7047','38000.00','4' UNION ALL

  SELECT '27','7047','36000.00','4' UNION ALL

  SELECT '27','7047','67400.00','4' UNION ALL

  SELECT '27','7047','11250.00','1' UNION ALL

  SELECT '32','7047','500.00','1' UNION ALL

  SELECT '27','7047','21250.00','1' UNION ALL

  SELECT '32','7047','500.00','1' UNION ALL

  SELECT '32','7047','1000.00','1' UNION ALL

  SELECT '27','7047','31350.00','1' UNION ALL

  SELECT '32','7047','1500.00','1' UNION ALL

  SELECT '27','7047','31450.00','1' UNION ALL

  SELECT '32','7047','6000.00','1' UNION ALL

  SELECT '29','7047','34500.00','1' UNION ALL

  SELECT '29','7047','70300.00','1' UNION ALL

  SELECT '29','7047','120225.00','1' UNION ALL

  SELECT '29','7047','146300.00','3' UNION ALL

  SELECT '29','7047','103050.00','4' UNION ALL

  SELECT '29','7047','281250.00','1' UNION ALL

  SELECT '29','7047','268600.00','3' UNION ALL

  SELECT '29','7047','223100.00','4' UNION ALL

  SELECT '29','7047','57750.00','2' UNION ALL

  SELECT '27','7047','20000.00','2' UNION ALL

  SELECT '11','7047','400.00','2' UNION ALL

  SELECT '32','7047','4000.00','2' UNION ALL

  SELECT '29','7047','52500.00','3' UNION ALL

  SELECT '27','7047','20000.00','3' UNION ALL

  SELECT '11','7047','400.00','3' UNION ALL

  SELECT '32','7047','4000.00','3' UNION ALL

  SELECT '29','7047','52500.00','4' UNION ALL

  SELECT '27','7047','19000.00','4' UNION ALL

  SELECT '11','7047','400.00','4' UNION ALL

  SELECT '32','7047','4000.00','4' UNION ALL

  SELECT '5','7364','12800.00','1' UNION ALL

  SELECT '5','7364','9100.00','4' UNION ALL

  SELECT '6','7364','8400.00','1' UNION ALL

  SELECT '6','7364','8400.00','4' UNION ALL

  SELECT '32','7364','500.00','1' UNION ALL

  SELECT '32','7364','1000.00','4' UNION ALL

  SELECT '27','7364','16240.00','4' UNION ALL

  SELECT '11','7364','20000.00','4' UNION ALL

  SELECT '7','7364','2000.00','4' UNION ALL

  SELECT '27','7364','34900.00','4' UNION ALL

  SELECT '6','7364','12000.00','4' UNION ALL

  SELECT '5','7002','500.00','1' UNION ALL

  SELECT '19','7002','1500.00','1' UNION ALL

  SELECT '19','7002','2000.00','1' UNION ALL

  SELECT '19','7002','4000.00','1' UNION ALL

  SELECT '19','7002','3000.00','1' UNION ALL

  SELECT '19','7002','3000.00','1' UNION ALL

  SELECT '19','7002','6000.00','1' UNION ALL

  SELECT '19','7002','6000.00','1' UNION ALL

  SELECT '19','7002','15000.00','1' UNION ALL

  SELECT '19','7002','1350.00','1' UNION ALL

  SELECT '27','7047','1500.00','1' UNION ALL

  SELECT '19','7047','3000.00','2' UNION ALL

  SELECT '10','7047','8000.00','1' UNION ALL

  SELECT '15','7364','3400.00','1' UNION ALL

  SELECT '27','7364','40000.00','1' UNION ALL

  SELECT '12','7364','3000.00','1' UNION ALL

  SELECT '5','7002','255220.00','1' UNION ALL

  SELECT '5','7002','146000.00','3' UNION ALL

  SELECT '5','7002','192270.00','4' UNION ALL

  SELECT '5','7002','318600.00','1' UNION ALL

  SELECT '5','7002','146000.00','3' UNION ALL

  SELECT '5','7002','192250.00','4' UNION ALL

  SELECT '5','7002','318600.00','1' UNION ALL

  SELECT '5','7002','146000.00','3' UNION ALL

  SELECT '5','7002','192250.00','4' UNION ALL

  SELECT '5','7002','318600.00','1' UNION ALL

  SELECT '5','7002','146000.00','3' UNION ALL

  SELECT '5','7002','192250.00','4' UNION ALL

  SELECT '5','7002','318600.00','1' UNION ALL

  SELECT '5','7002','146000.00','3' UNION ALL

  SELECT '5','7002','192250.00','4' UNION ALL

  SELECT '5','7002','318600.00','1' UNION ALL

  SELECT '5','7002','146000.00','3' UNION ALL

  SELECT '5','7002','192250.00','4' UNION ALL

  SELECT '44','7002','71143.00','1' UNION ALL

  SELECT '5','7047','3000.00','1' UNION ALL

  SELECT '5','7047','628205.00','1' UNION ALL

  SELECT '5','7047','159777.00','3' UNION ALL

  SELECT '5','7047','125766.00','4' UNION ALL

  SELECT '5','7047','110100.00','1' UNION ALL

  SELECT '5','7047','80200.00','3' UNION ALL

  SELECT '5','7047','63300.00','4' UNION ALL

  SELECT '5','7364','72384.00','1' UNION ALL

  SELECT '5','7364','-4490.00','1' UNION ALL

  SELECT '5','7364','-28160.00','1' UNION ALL

  SELECT '5','7047','11000.00','1' UNION ALL

  SELECT '5','7047','6500.00','3' UNION ALL

  SELECT '5','7047','65500.00','4' UNION ALL

  SELECT '5','7047','129200.00','1' UNION ALL

  SELECT '5','7047','121700.00','3' UNION ALL

  SELECT '5','7047','114500.00','4' UNION ALL

  SELECT '5','7047','61000.00','1' UNION ALL

  SELECT '5','7047','14200.00','3' UNION ALL

  SELECT '5','7047','287900.00','1' UNION ALL

  SELECT '5','7047','245800.00','3' UNION ALL

  SELECT '5','7047','125400.00','4' UNION ALL

  SELECT '5','7047','115910.00','1' UNION ALL

  SELECT '5','7047','39400.00','3' UNION ALL

  SELECT '5','7047','26600.00','4' UNION ALL

  SELECT '5','7002','500.00','1' UNION ALL

  SELECT '5','7002','4500.00','3' UNION ALL

  SELECT '5','7002','20250.00','4' UNION ALL

  SELECT '5','7002','16500.00','1' UNION ALL

  SELECT '32','7002','4500.00','1' UNION ALL

  SELECT '38','7002','2000.00','1' UNION ALL

  SELECT '27','7002','91500.00','1' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','26100.00','3' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','26250.00','4' UNION ALL

  SELECT '27','7002','17250.00','1' UNION ALL

  SELECT '27','7002','9000.00','3' UNION ALL

  SELECT '27','7002','9050.00','4' UNION ALL

  SELECT '27','7002','17250.00','1' UNION ALL

  SELECT '27','7002','9000.00','3' UNION ALL

  SELECT '27','7002','9050.00','4' UNION ALL

  SELECT '27','7002','17250.00','1' UNION ALL

  SELECT '27','7002','9000.00','3' UNION ALL

  SELECT '27','7002','9050.00','4' UNION ALL

  SELECT '27','7002','17250.00','1' UNION ALL

  SELECT '27','7002','9000.00','3' UNION ALL

  SELECT '27','7002','9050.00','4' UNION ALL

  SELECT '27','7002','17250.00','1' UNION ALL

  SELECT '27','7002','9000.00','3' UNION ALL

  SELECT '27','7002','9050.00','4' UNION ALL

  SELECT '32','7002','4500.00','1' UNION ALL

  SELECT '38','7002','2000.00','1' UNION ALL

  SELECT '27','7002','91500.00','1' UNION ALL

  SELECT '32','7002','2500.00','3' UNION ALL

  SELECT '38','7002','1500.00','3' UNION ALL

  SELECT '27','7002','26100.00','3' UNION ALL

  SELECT '32','7002','2500.00','4' UNION ALL

  SELECT '38','7002','3000.00','4' UNION ALL

  SELECT '27','7002','26250.00','4' UNION ALL

  SELECT '16','7364','100000.00','1' UNION ALL

  SELECT '19','7364','22000.00','1' UNION ALL

  SELECT '25','7364','10500.00','1' UNION ALL

  SELECT '21','7364','78000.00','1' UNION ALL

  SELECT '19','7364','3000.00','1' UNION ALL

  SELECT '16','7364','60000.00','1' UNION ALL

  SELECT '19','7364','23000.00','1' UNION ALL

  SELECT '5','7364','1088.00','1' UNION ALL

  SELECT '5','7002','500.00','1' UNION ALL

  SELECT '32','7002','500.00','3' UNION ALL

  SELECT '27','7002','21250.00','3' UNION ALL

  SELECT '32','7002','500.00','4' UNION ALL

  SELECT '27','7002','3500.00','4' UNION ALL

  SELECT '27','7002','9750.00','1' UNION ALL

  SELECT '32','7002','500.00','1' UNION ALL

  SELECT '5','7002','500.00','1' UNION ALL

  SELECT '27','7002','21250.00','3' UNION ALL

  SELECT '32','7002','500.00','3' UNION ALL

  SELECT '45','7364','100500.00','3' UNION ALL

  SELECT '5','7047','95712.00','1' UNION ALL

  SELECT '5','7047','75876.00','3' UNION ALL

  SELECT '5','7047','116917.00','4' UNION ALL

  SELECT '27','7002','3000.00','3' UNION ALL

  SELECT '32','7002','500.00','3' UNION ALL

  SELECT '27','7002','21750.00','4' UNION ALL

  SELECT '32','7002','500.00','4' UNION ALL

  SELECT '29','7002','15750.00','2' UNION ALL

  SELECT '27','7002','4500.00','2' UNION ALL

  SELECT '32','7002','500.00','2' UNION ALL

  Then I created this view:

  CREATE VIEW His_Vw

  AS

  SELECT His.sn, His.cst, His.acc, PJs.pj, His.amt, Regns.regn, Smps.smp

  FROM His INNER JOIN Projects ON His.acc = PJs.acc

  INNER JOIN Regns ON His.regn = Regns.sn

  INNER JOIN Smps ON PJs.acc = Smps.acc AND Regns.sn = Smps.regn

  GO

  I wrote this query:

  SELECT cst

  , SUM(amt)

  , tot_smp = (CASE

  WHEN COUNT(DISTINCT acc) = 1 THEN CASE

  WHEN COUNT(DISTINCT regn) = 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT regn) > 1 THEN CASE

  WHEN COUNT(DISTINCT smp) > 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT smp) = 1 THEN COUNT(DISTINCT regn) * SUM(DISTINCT smp)

  END

  END

  WHEN COUNT(DISTINCT acc) > 1 THEN CASE

  WHEN COUNT(DISTINCT regn) = 1 THEN CASE

  WHEN COUNT(DISTINCT smp) > 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT smp) = 1 THEN COUNT(DISTINCT acc) * SUM(DISTINCT smp)

  END

  WHEN COUNT(DISTINCT regn) > 1 THEN CASE

  WHEN COUNT(DISTINCT smp) > 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT smp) = 1 THEN COUNT(DISTINCT regn) * SUM(DISTINCT smp)

  END

  END

  END)

  FROM His_Vw

  GROUP BY cst

  I expect to sum an smp for an acc and regn only once.

  My query works well (ie, sums the smp correctly) for cases where the cst has one acc with different regns but not for cst with more than one acc.

  Any help?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 31, 2012 at 9:46 am

  #1519810

  oolawole (7/31/2012)

  ---

  ...

  Any help?

  Fix your sample data script which is incomplete and full of errors and your view definition which references a table which doesn't exist and I'd be glad to help.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• oolawole

  SSC Veteran

  Points: 285

  More actions

  July 31, 2012 at 10:30 am

  #1519842

  Sorry for the errors.

  SET IDENTITY_INSERT Regns ON

  INSERT INTO Regns (sn,Regn)

  SELECT '1','La' UNION ALL

  SELECT '2','Be' UNION ALL

  SELECT '3','Ib' UNION ALL

  SELECT '4','En'

  SET IDENTITY_INSERT Regns ON

  SET IDENTITY_INSERT PJs ON

  INSERT INTO PJs (sn,acc,pj)

  SELECT '1','7002','Home' UNION ALL

  SELECT '2','7047','GGG' UNION ALL

  SELECT '3','7364','Discover'

  SET IDENTITY_INSERT PJs ON

  SET IDENTITY_INSERT Smps ON

  INSERT INTO Smps (sn,acc,Regn,Smp)

  SELECT '1','7002','1','560' UNION ALL

  SELECT '2','7002','2','160' UNION ALL

  SELECT '3','7002','3','320' UNION ALL

  SELECT '5','7002','4','320' UNION ALL

  SELECT '29','7047','1','3582' UNION ALL

  SELECT '30','7047','2','2035' UNION ALL

  SELECT '31','7047','3','4772' UNION ALL

  SELECT '32','7047','4','3741' UNION ALL

  SELECT '38','7364','1','40' UNION ALL

  SELECT '39','7364','3','40' UNION ALL

  SELECT '40','7364','4','40'

  SET IDENTITY_INSERT Smps ON

  SET IDENTITY_INSERT Smps ON

  INSERT INTO His (sn,cst,acc,amt,Regn)

  SELECT '16','29','70002','98000.00','1' UNION ALL

  SELECT '17','27','70002','30100.00','3' UNION ALL

  SELECT '18','25','70002','31750.00','4' UNION ALL

  SELECT '23','29','70002','98000.00','1' UNION ALL

  SELECT '24','27','70002','30100.00','3' UNION ALL

  SELECT '25','25','70002','31750.00','4' UNION ALL

  SELECT '31','27','70002','93250.00','1' UNION ALL

  SELECT '32','32','70002','3500.00','1' UNION ALL

  SELECT '33','38','70002','1000.00','1' UNION ALL

  SELECT '34','27','70002','15500.00','2' UNION ALL

  SELECT '35','32','70002','1000.00','2' UNION ALL

  SELECT '36','38','70002','1000.00','2' UNION ALL

  SELECT '37','27','70002','35100.00','3' UNION ALL

  SELECT '38','32','70002','2500.00','3' UNION ALL

  SELECT '39','38','70002','1500.00','3' UNION ALL

  SELECT '40','27','70002','35300.00','4' UNION ALL

  SELECT '41','32','70002','2500.00','4' UNION ALL

  SELECT '42','38','70002','3000.00','4' UNION ALL

  SELECT '55','27','70002','93250.00','1' UNION ALL

  SELECT '56','32','70002','3500.00','1' UNION ALL

  SELECT '57','38','70002','1000.00','1' UNION ALL

  SELECT '58','27','70002','15500.00','2' UNION ALL

  SELECT '59','32','70002','1000.00','2' UNION ALL

  SELECT '60','38','70002','1000.00','2' UNION ALL

  SELECT '61','27','70002','35100.00','3' UNION ALL

  SELECT '62','32','70002','2500.00','3' UNION ALL

  SELECT '63','38','70002','1500.00','3' UNION ALL

  SELECT '64','27','70002','35300.00','4' UNION ALL

  SELECT '65','32','70002','2500.00','4' UNION ALL

  SELECT '66','38','70002','3000.00','4' UNION ALL

  SELECT '149','27','70002','93250.00','1' UNION ALL

  SELECT '150','32','70002','3500.00','1' UNION ALL

  SELECT '151','38','70002','1000.00','1' UNION ALL

  SELECT '152','27','70002','15500.00','2' UNION ALL

  SELECT '153','32','70002','1000.00','2' UNION ALL

  SELECT '154','38','70002','1000.00','2' UNION ALL

  SELECT '155','27','70002','35100.00','3' UNION ALL

  SELECT '156','32','70002','2500.00','3' UNION ALL

  SELECT '157','38','70002','1500.00','3' UNION ALL

  SELECT '158','27','70002','35300.00','4' UNION ALL

  SELECT '159','32','70002','2500.00','4' UNION ALL

  SELECT '160','38','70002','3000.00','4' UNION ALL

  SELECT '173','27','70002','93250.00','1' UNION ALL

  SELECT '174','32','70002','3500.00','1' UNION ALL

  SELECT '175','38','70002','1000.00','1' UNION ALL

  SELECT '176','27','70002','15500.00','2' UNION ALL

  SELECT '177','32','70002','1000.00','2' UNION ALL

  SELECT '178','38','70002','1000.00','2' UNION ALL

  SELECT '179','27','70002','35100.00','3' UNION ALL

  SELECT '180','32','70002','2500.00','3' UNION ALL

  SELECT '181','38','70002','1500.00','3' UNION ALL

  SELECT '182','27','70002','35300.00','4' UNION ALL

  SELECT '183','32','70002','2500.00','4' UNION ALL

  SELECT '184','38','70002','3000.00','4' UNION ALL

  SELECT '197','27','70002','93250.00','1' UNION ALL

  SELECT '198','32','70002','3500.00','1' UNION ALL

  SELECT '199','38','70002','1000.00','1' UNION ALL

  SELECT '200','27','70002','15500.00','2' UNION ALL

  SELECT '201','32','70002','1000.00','2' UNION ALL

  SELECT '202','38','70002','1000.00','2' UNION ALL

  SELECT '203','27','70002','35100.00','3' UNION ALL

  SELECT '204','32','70002','2500.00','3' UNION ALL

  SELECT '205','38','70002','1500.00','3' UNION ALL

  SELECT '206','27','70002','35300.00','4' UNION ALL

  SELECT '207','32','70002','2500.00','4' UNION ALL

  SELECT '208','38','70002','3000.00','4' UNION ALL

  SELECT '221','27','70002','93250.00','1' UNION ALL

  SELECT '222','32','70002','3500.00','1' UNION ALL

  SELECT '223','38','70002','1000.00','1' UNION ALL

  SELECT '224','27','70002','15500.00','2' UNION ALL

  SELECT '225','32','70002','1000.00','2' UNION ALL

  SELECT '226','38','70002','1000.00','2' UNION ALL

  SELECT '227','27','70002','35100.00','3' UNION ALL

  SELECT '228','32','70002','2500.00','3' UNION ALL

  SELECT '229','38','70002','1500.00','3' UNION ALL

  SELECT '230','27','70002','35300.00','4' UNION ALL

  SELECT '231','32','70002','2500.00','4' UNION ALL

  SELECT '232','38','70002','3000.00','4' UNION ALL

  SELECT '245','27','70002','93250.00','1' UNION ALL

  SELECT '246','32','70002','3500.00','1' UNION ALL

  SELECT '247','38','70002','1000.00','1' UNION ALL

  SELECT '248','27','70002','15500.00','2' UNION ALL

  SELECT '249','32','70002','1000.00','2' UNION ALL

  SELECT '250','38','70002','1000.00','2' UNION ALL

  SELECT '251','27','70002','35100.00','3' UNION ALL

  SELECT '252','32','70002','2500.00','3' UNION ALL

  SELECT '253','38','70002','1500.00','3' UNION ALL

  SELECT '254','27','70002','35300.00','4' UNION ALL

  SELECT '255','32','70002','2500.00','4' UNION ALL

  SELECT '256','38','70002','3000.00','4' UNION ALL

  SELECT '269','27','70002','93250.00','1' UNION ALL

  SELECT '270','32','70002','3500.00','1' UNION ALL

  SELECT '271','38','70002','1000.00','1' UNION ALL

  SELECT '272','27','70002','15500.00','2' UNION ALL

  SELECT '273','32','70002','1000.00','2' UNION ALL

  SELECT '274','38','70002','1000.00','2' UNION ALL

  SELECT '275','27','70002','35100.00','3' UNION ALL

  SELECT '276','32','70002','2500.00','3' UNION ALL

  SELECT '277','38','70002','1500.00','3' UNION ALL

  SELECT '278','27','70002','35300.00','4' UNION ALL

  SELECT '279','32','70002','2500.00','4' UNION ALL

  SELECT '280','38','70002','3000.00','4' UNION ALL

  SELECT '293','27','70002','93250.00','1' UNION ALL

  SELECT '294','32','70002','3500.00','1' UNION ALL

  SELECT '295','38','70002','1000.00','1' UNION ALL

  SELECT '296','27','70002','15500.00','2' UNION ALL

  SELECT '297','32','70002','1000.00','2' UNION ALL

  SELECT '298','38','70002','1000.00','2' UNION ALL

  SELECT '299','27','70002','35100.00','3' UNION ALL

  SELECT '300','32','70002','2500.00','3' UNION ALL

  SELECT '301','38','70002','1500.00','3' UNION ALL

  SELECT '302','27','70002','35300.00','4' UNION ALL

  SELECT '303','32','70002','2500.00','4' UNION ALL

  SELECT '304','38','70002','3000.00','4' UNION ALL

  SELECT '318','29','70047','6000.00','1' UNION ALL

  SELECT '319','27','70047','6000.00','1' UNION ALL

  SELECT '320','11','70047','13600.00','1' UNION ALL

  SELECT '321','16','70047','30000.00','1' UNION ALL

  SELECT '322','29','70047','30000.00','3' UNION ALL

  SELECT '323','27','70047','45200.00','3' UNION ALL

  SELECT '324','29','70047','5750.00','2' UNION ALL

  SELECT '325','27','70047','3200.00','2' UNION ALL

  SELECT '326','11','70047','5400.00','4' UNION ALL

  SELECT '327','16','70047','10000.00','4' UNION ALL

  SELECT '334','15','70047','2500.00','1' UNION ALL

  SELECT '335','15','70047','2000.00','2' UNION ALL

  SELECT '336','15','70047','4200.00','3' UNION ALL

  SELECT '337','15','70047','3600.00','4' UNION ALL

  SELECT '342','26','70047','54000.00','1' UNION ALL

  SELECT '343','29','70047','13000.00','1' UNION ALL

  SELECT '344','27','70047','5000.00','1' UNION ALL

  SELECT '345','29','70047','38000.00','4' UNION ALL

  SELECT '346','27','70047','36000.00','4' UNION ALL

  SELECT '347','27','70047','67400.00','4' UNION ALL

  SELECT '348','27','70047','11250.00','1' UNION ALL

  SELECT '349','32','70047','500.00','1' UNION ALL

  SELECT '350','27','70047','21250.00','1' UNION ALL

  SELECT '351','32','70047','500.00','1' UNION ALL

  SELECT '352','32','70047','1000.00','1' UNION ALL

  SELECT '353','27','70047','31350.00','1' UNION ALL

  SELECT '354','32','70047','1500.00','1' UNION ALL

  SELECT '355','27','70047','31450.00','1' UNION ALL

  SELECT '356','32','70047','6000.00','1' UNION ALL

  SELECT '357','29','70047','34500.00','1' UNION ALL

  SELECT '358','29','70047','70300.00','1' UNION ALL

  SELECT '359','29','70047','120225.00','1' UNION ALL

  SELECT '360','29','70047','146300.00','3' UNION ALL

  SELECT '361','29','70047','103050.00','4' UNION ALL

  SELECT '366','29','70047','281250.00','1' UNION ALL

  SELECT '367','29','70047','268600.00','3' UNION ALL

  SELECT '368','29','70047','223100.00','4' UNION ALL

  SELECT '405','29','70047','57750.00','2' UNION ALL

  SELECT '406','27','70047','20000.00','2' UNION ALL

  SELECT '407','11','70047','400.00','2' UNION ALL

  SELECT '408','32','70047','4000.00','2' UNION ALL

  SELECT '409','29','70047','52500.00','3' UNION ALL

  SELECT '410','27','70047','20000.00','3' UNION ALL

  SELECT '411','11','70047','400.00','3' UNION ALL

  SELECT '412','32','70047','4000.00','3' UNION ALL

  SELECT '413','29','70047','52500.00','4' UNION ALL

  SELECT '414','27','70047','19000.00','4' UNION ALL

  SELECT '415','11','70047','400.00','4' UNION ALL

  SELECT '416','32','70047','4000.00','4' UNION ALL

  SELECT '443','5','70364','12800.00','1' UNION ALL

  SELECT '448','5','70364','9100.00','4' UNION ALL

  SELECT '449','6','70364','8400.00','1' UNION ALL

  SELECT '454','6','70364','8400.00','4' UNION ALL

  SELECT '455','32','70364','500.00','1' UNION ALL

  SELECT '460','32','70364','1000.00','4' UNION ALL

  SELECT '462','27','70364','16240.00','4' UNION ALL

  SELECT '464','11','70364','20000.00','4' UNION ALL

  SELECT '466','7','70364','2000.00','4' UNION ALL

  SELECT '468','27','70364','34900.00','4' UNION ALL

  SELECT '470','6','70364','12000.00','4' UNION ALL

  SELECT '541','5','70002','500.00','1' UNION ALL

  SELECT '542','19','70002','1500.00','1' UNION ALL

  SELECT '543','19','70002','2000.00','1' UNION ALL

  SELECT '544','19','70002','4000.00','1' UNION ALL

  SELECT '545','19','70002','3000.00','1' UNION ALL

  SELECT '546','19','70002','3000.00','1' UNION ALL

  SELECT '547','19','70002','6000.00','1' UNION ALL

  SELECT '548','19','70002','6000.00','1' UNION ALL

  SELECT '549','19','70002','15000.00','1' UNION ALL

  SELECT '550','19','70002','1350.00','1' UNION ALL

  SELECT '551','27','70047','1500.00','1' UNION ALL

  SELECT '556','19','70047','3000.00','2' UNION ALL

  SELECT '557','10','70047','8000.00','1' UNION ALL

  SELECT '558','15','70364','3400.00','1' UNION ALL

  SELECT '559','27','70364','40000.00','1' UNION ALL

  SELECT '560','12','70364','3000.00','1' UNION ALL

  SELECT '561','5','70002','255220.00','1' UNION ALL

  SELECT '562','5','70002','146000.00','3' UNION ALL

  SELECT '563','5','70002','192270.00','4' UNION ALL

  SELECT '568','5','70002','318600.00','1' UNION ALL

  SELECT '569','5','70002','146000.00','3' UNION ALL

  SELECT '570','5','70002','192250.00','4' UNION ALL

  SELECT '575','5','70002','318600.00','1' UNION ALL

  SELECT '576','5','70002','146000.00','3' UNION ALL

  SELECT '577','5','70002','192250.00','4' UNION ALL

  SELECT '582','5','70002','318600.00','1' UNION ALL

  SELECT '583','5','70002','146000.00','3' UNION ALL

  SELECT '584','5','70002','192250.00','4' UNION ALL

  SELECT '589','5','70002','318600.00','1' UNION ALL

  SELECT '590','5','70002','146000.00','3' UNION ALL

  SELECT '591','5','70002','192250.00','4' UNION ALL

  SELECT '596','5','70002','318600.00','1' UNION ALL

  SELECT '597','5','70002','146000.00','3' UNION ALL

  SELECT '598','5','70002','192250.00','4' UNION ALL

  SELECT '603','44','70002','71143.00','1' UNION ALL

  SELECT '605','5','70047','3000.00','1' UNION ALL

  SELECT '606','5','70047','628205.00','1' UNION ALL

  SELECT '607','5','70047','159777.00','3' UNION ALL

  SELECT '608','5','70047','125766.00','4' UNION ALL

  SELECT '613','5','70047','110100.00','1' UNION ALL

  SELECT '614','5','70047','80200.00','3' UNION ALL

  SELECT '615','5','70047','63300.00','4' UNION ALL

  SELECT '621','5','70364','72384.00','1' UNION ALL

  SELECT '643','5','70047','11000.00','1' UNION ALL

  SELECT '644','5','70047','6500.00','3' UNION ALL

  SELECT '645','5','70047','65500.00','4' UNION ALL

  SELECT '650','5','70047','129200.00','1' UNION ALL

  SELECT '651','5','70047','121700.00','3' UNION ALL

  SELECT '652','5','70047','114500.00','4' UNION ALL

  SELECT '657','5','70047','61000.00','1' UNION ALL

  SELECT '658','5','70047','14200.00','3' UNION ALL

  SELECT '663','5','70047','287900.00','1' UNION ALL

  SELECT '664','5','70047','245800.00','3' UNION ALL

  SELECT '665','5','70047','125400.00','4' UNION ALL

  SELECT '670','5','70047','115910.00','1' UNION ALL

  SELECT '671','5','70047','39400.00','3' UNION ALL

  SELECT '672','5','70047','26600.00','4' UNION ALL

  SELECT '705','5','70002','500.00','1' UNION ALL

  SELECT '706','5','70002','4500.00','3' UNION ALL

  SELECT '707','5','70002','20250.00','4' UNION ALL

  SELECT '719','5','70002','16500.00','1' UNION ALL

  SELECT '720','32','70002','4500.00','1' UNION ALL

  SELECT '721','38','70002','2000.00','1' UNION ALL

  SELECT '722','27','70002','91500.00','1' UNION ALL

  SELECT '723','32','70002','2500.00','3' UNION ALL

  SELECT '724','38','70002','1500.00','3' UNION ALL

  SELECT '725','27','70002','26100.00','3' UNION ALL

  SELECT '726','32','70002','2500.00','4' UNION ALL

  SELECT '727','38','70002','3000.00','4' UNION ALL

  SELECT '728','27','70002','26250.00','4' UNION ALL

  SELECT '741','27','70002','17250.00','1' UNION ALL

  SELECT '742','27','70002','9000.00','3' UNION ALL

  SELECT '743','27','70002','9050.00','4' UNION ALL

  SELECT '748','27','70002','17250.00','1' UNION ALL

  SELECT '749','27','70002','9000.00','3' UNION ALL

  SELECT '750','27','70002','9050.00','4' UNION ALL

  SELECT '755','27','70002','17250.00','1' UNION ALL

  SELECT '756','27','70002','9000.00','3' UNION ALL

  SELECT '757','27','70002','9050.00','4' UNION ALL

  SELECT '762','27','70002','17250.00','1' UNION ALL

  SELECT '763','27','70002','9000.00','3' UNION ALL

  SELECT '764','27','70002','9050.00','4' UNION ALL

  SELECT '769','27','70002','17250.00','1' UNION ALL

  SELECT '770','27','70002','9000.00','3' UNION ALL

  SELECT '771','27','70002','9050.00','4' UNION ALL

  SELECT '789','32','70002','4500.00','1' UNION ALL

  SELECT '790','38','70002','2000.00','1' UNION ALL

  SELECT '791','27','70002','91500.00','1' UNION ALL

  SELECT '792','32','70002','2500.00','3' UNION ALL

  SELECT '793','38','70002','1500.00','3' UNION ALL

  SELECT '794','27','70002','26100.00','3' UNION ALL

  SELECT '795','32','70002','2500.00','4' UNION ALL

  SELECT '796','38','70002','3000.00','4' UNION ALL

  SELECT '797','27','70002','26250.00','4' UNION ALL

  SELECT '810','16','70364','100000.00','1' UNION ALL

  SELECT '812','19','70364','22000.00','1' UNION ALL

  SELECT '813','25','70364','10500.00','1' UNION ALL

  SELECT '854','21','70364','78000.00','1' UNION ALL

  SELECT '855','19','70364','3000.00','1' UNION ALL

  SELECT '856','16','70364','60000.00','1' UNION ALL

  SELECT '858','19','70364','23000.00','1' UNION ALL

  SELECT '859','5','70364','1088.00','1' UNION ALL

  SELECT '860','5','70002','500.00','1' UNION ALL

  SELECT '861','32','70002','500.00','3' UNION ALL

  SELECT '862','27','70002','21250.00','3' UNION ALL

  SELECT '863','32','70002','500.00','4' UNION ALL

  SELECT '864','27','70002','3500.00','4' UNION ALL

  SELECT '871','27','70002','9750.00','1' UNION ALL

  SELECT '872','32','70002','500.00','1' UNION ALL

  SELECT '873','5','70002','500.00','1' UNION ALL

  SELECT '874','27','70002','21250.00','3' UNION ALL

  SELECT '875','32','70002','500.00','3' UNION ALL

  SELECT '884','5','70047','95712.00','1' UNION ALL

  SELECT '885','5','70047','75876.00','3' UNION ALL

  SELECT '886','5','70047','116917.00','4' UNION ALL

  SELECT '918','27','70002','3000.00','3' UNION ALL

  SELECT '919','32','70002','500.00','3' UNION ALL

  SELECT '920','27','70002','21750.00','4' UNION ALL

  SELECT '921','32','70002','500.00','4' UNION ALL

  SELECT '926','29','70002','15750.00','2' UNION ALL

  SELECT '927','27','70002','4500.00','2' UNION ALL

  SELECT '928','32','70002','500.00','2' UNION ALL

  SELECT '936','45','70364','443520.00','1' UNION ALL

  SELECT '939','45','70364','443520.00','1' UNION ALL

  SELECT '942','18','70364','50000.00','4' UNION ALL

  SELECT '943','22','70364','91000.00','4' UNION ALL

  SELECT '944','19','70364','47000.00','4' UNION ALL

  SELECT '945','16','70364','55000.00','4' UNION ALL

  SELECT '946','17','70364','30000.00','4' UNION ALL

  SELECT '947','23','70364','4000.00','4' UNION ALL

  SELECT '948','9','70364','3000.00','4' UNION ALL

  SELECT '949','25','70364','9000.00','4' UNION ALL

  SELECT '950','19','70364','8000.00','1' UNION ALL

  SELECT '951','12','70364','8000.00','1' UNION ALL

  SELECT '952','19','70364','32000.00','1' UNION ALL

  SELECT '953','12','70364','3400.00','1' UNION ALL

  SELECT '964','19','70364','7800.00','1'

  SET IDENTITY_INSERT His ON

  And here's the view

  CREATE VIEW His_Vw

  AS

  SELECT His.sn, His.cst, His.acc, PJs.pj, His.amt, Regns.regn, Smps.smp

  FROM His INNER JOIN PJs ON His.acc = PJs.acc

  INNER JOIN Regns ON His.regn = Regns.sn

  INNER JOIN Smps ON PJs.acc = Smps.acc AND Regns.sn = Smps.regn

  GO

  Thanks

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  July 31, 2012 at 12:16 pm

  #1519890

  Try running your code in an empty database. There are dozens of errors. It would also be preferred if you can use IFCode shortcuts (over on the left when posting). It is what makes those nice clean code sections in posts.

  Once you post ddl and sample data in a format that actually works you will get some help.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• oolawole

  SSC Veteran

  Points: 285

  More actions

  August 1, 2012 at 3:23 am

  #1520150

  Hi all.

  Sorry for the errors, I have corrected them now in this.

  CREATE TABLE Regns

  (

  sn INT NOT NULL

  IDENTITY(1, 1) ,

  Regn NVARCHAR(50) NOT NULL

  CONSTRAINT pk_Regns PRIMARY KEY CLUSTERED (sn ASC)

  )

  Go

  SET IDENTITY_INSERT Regns ON

  INSERT INTO Regns (sn,Regn)

  SELECT '1','La' UNION ALL

  SELECT '2','Be' UNION ALL

  SELECT '3','Ib' UNION ALL

  SELECT '4','En'

  SET IDENTITY_INSERT Regns OFF

  CREATE TABLE PJs(

  sn int IDENTITY(1,1) NOT NULL,

  acc int NOT NULL,

  pj nvarchar(100) NULL,

  CONSTRAINT PK_PJs PRIMARY KEY CLUSTERED (acc ASC))

  SET IDENTITY_INSERT PJs ON

  INSERT INTO PJs (sn,acc,pj)

  SELECT '1','7002','Home' UNION ALL

  SELECT '2','7047','GGG' UNION ALL

  SELECT '3','7364','Discover'

  SET IDENTITY_INSERT PJs OFF

  CREATE TABLE Smps(

  sn int IDENTITY(1,1) NOT NULL,

  acc int NOT NULL,

  regn int NOT NULL,

  smp int NOT NULL

  )

  GO

  ALTER TABLE Smps WITH CHECK ADD CONSTRAINT FK_Smps_PJs FOREIGN KEY(acc)

  REFERENCES PJs (acc)

  ON UPDATE CASCADE

  GO

  ALTER TABLE Smps CHECK CONSTRAINT FK_Smps_PJs

  GO

  ALTER TABLE Smps WITH CHECK ADD CONSTRAINT FK_Smps_Regns FOREIGN KEY(regn)

  REFERENCES Regns (sn)

  ON UPDATE CASCADE

  GO

  ALTER TABLE Smps CHECK CONSTRAINT FK_Smps_Regns

  GO

  SET IDENTITY_INSERT Smps ON

  INSERT INTO Smps (sn,acc,Regn,Smp)

  SELECT '1','7002','1','560' UNION ALL

  SELECT '2','7002','2','160' UNION ALL

  SELECT '3','7002','3','320' UNION ALL

  SELECT '5','7002','4','320' UNION ALL

  SELECT '29','7047','1','3582' UNION ALL

  SELECT '30','7047','2','2035' UNION ALL

  SELECT '31','7047','3','4772' UNION ALL

  SELECT '32','7047','4','3741' UNION ALL

  SELECT '38','7364','1','40' UNION ALL

  SELECT '39','7364','3','40' UNION ALL

  SELECT '40','7364','4','40'

  SET IDENTITY_INSERT Smps OFF

  CREATE TABLE His(

  sn int IDENTITY(1,1) NOT NULL,

  cst int NOT NULL,

  acc int NOT NULL,

  amt decimal(17, 2) NOT NULL,

  regn int NOT NULL,

  CONSTRAINT PK_His PRIMARY KEY CLUSTERED (sn ASC))

  GO

  ALTER TABLE His WITH CHECK ADD CONSTRAINT FK_His_PJs FOREIGN KEY(acc)

  REFERENCES PJs (acc)

  ON UPDATE CASCADE

  GO

  ALTER TABLE His CHECK CONSTRAINT FK_His_PJs

  GO

  ALTER TABLE His WITH CHECK ADD CONSTRAINT FK_His_Regns FOREIGN KEY(regn)

  REFERENCES Regns (sn)

  ON UPDATE CASCADE

  GO

  ALTER TABLE His CHECK CONSTRAINT FK_His_Regns

  GO

  SET IDENTITY_INSERT His ON

  INSERT INTO His (sn,cst,acc,amt,Regn)

  SELECT '16','29','7002','98000.00','1' UNION ALL

  SELECT '17','27','7002','30100.00','3' UNION ALL

  SELECT '18','25','7002','31750.00','4' UNION ALL

  SELECT '23','29','7002','98000.00','1' UNION ALL

  SELECT '24','27','7002','30100.00','3' UNION ALL

  SELECT '25','25','7002','31750.00','4' UNION ALL

  SELECT '31','27','7002','93250.00','1' UNION ALL

  SELECT '32','32','7002','3500.00','1' UNION ALL

  SELECT '33','38','7002','1000.00','1' UNION ALL

  SELECT '34','27','7002','15500.00','2' UNION ALL

  SELECT '35','32','7002','1000.00','2' UNION ALL

  SELECT '36','38','7002','1000.00','2' UNION ALL

  SELECT '37','27','7002','35100.00','3' UNION ALL

  SELECT '38','32','7002','2500.00','3' UNION ALL

  SELECT '39','38','7002','1500.00','3' UNION ALL

  SELECT '40','27','7002','35300.00','4' UNION ALL

  SELECT '41','32','7002','2500.00','4' UNION ALL

  SELECT '42','38','7002','3000.00','4' UNION ALL

  SELECT '55','27','7002','93250.00','1' UNION ALL

  SELECT '56','32','7002','3500.00','1' UNION ALL

  SELECT '57','38','7002','1000.00','1' UNION ALL

  SELECT '58','27','7002','15500.00','2' UNION ALL

  SELECT '59','32','7002','1000.00','2' UNION ALL

  SELECT '60','38','7002','1000.00','2' UNION ALL

  SELECT '61','27','7002','35100.00','3' UNION ALL

  SELECT '62','32','7002','2500.00','3' UNION ALL

  SELECT '63','38','7002','1500.00','3' UNION ALL

  SELECT '64','27','7002','35300.00','4' UNION ALL

  SELECT '65','32','7002','2500.00','4' UNION ALL

  SELECT '66','38','7002','3000.00','4' UNION ALL

  SELECT '149','27','7002','93250.00','1' UNION ALL

  SELECT '150','32','7002','3500.00','1' UNION ALL

  SELECT '151','38','7002','1000.00','1' UNION ALL

  SELECT '152','27','7002','15500.00','2' UNION ALL

  SELECT '153','32','7002','1000.00','2' UNION ALL

  SELECT '154','38','7002','1000.00','2' UNION ALL

  SELECT '155','27','7002','35100.00','3' UNION ALL

  SELECT '156','32','7002','2500.00','3' UNION ALL

  SELECT '157','38','7002','1500.00','3' UNION ALL

  SELECT '158','27','7002','35300.00','4' UNION ALL

  SELECT '159','32','7002','2500.00','4' UNION ALL

  SELECT '160','38','7002','3000.00','4' UNION ALL

  SELECT '173','27','7002','93250.00','1' UNION ALL

  SELECT '174','32','7002','3500.00','1' UNION ALL

  SELECT '175','38','7002','1000.00','1' UNION ALL

  SELECT '176','27','7002','15500.00','2' UNION ALL

  SELECT '177','32','7002','1000.00','2' UNION ALL

  SELECT '178','38','7002','1000.00','2' UNION ALL

  SELECT '179','27','7002','35100.00','3' UNION ALL

  SELECT '180','32','7002','2500.00','3' UNION ALL

  SELECT '181','38','7002','1500.00','3' UNION ALL

  SELECT '182','27','7002','35300.00','4' UNION ALL

  SELECT '183','32','7002','2500.00','4' UNION ALL

  SELECT '184','38','7002','3000.00','4' UNION ALL

  SELECT '197','27','7002','93250.00','1' UNION ALL

  SELECT '198','32','7002','3500.00','1' UNION ALL

  SELECT '199','38','7002','1000.00','1' UNION ALL

  SELECT '200','27','7002','15500.00','2' UNION ALL

  SELECT '201','32','7002','1000.00','2' UNION ALL

  SELECT '202','38','7002','1000.00','2' UNION ALL

  SELECT '203','27','7002','35100.00','3' UNION ALL

  SELECT '204','32','7002','2500.00','3' UNION ALL

  SELECT '205','38','7002','1500.00','3' UNION ALL

  SELECT '206','27','7002','35300.00','4' UNION ALL

  SELECT '207','32','7002','2500.00','4' UNION ALL

  SELECT '208','38','7002','3000.00','4' UNION ALL

  SELECT '221','27','7002','93250.00','1' UNION ALL

  SELECT '222','32','7002','3500.00','1' UNION ALL

  SELECT '223','38','7002','1000.00','1' UNION ALL

  SELECT '224','27','7002','15500.00','2' UNION ALL

  SELECT '225','32','7002','1000.00','2' UNION ALL

  SELECT '226','38','7002','1000.00','2' UNION ALL

  SELECT '227','27','7002','35100.00','3' UNION ALL

  SELECT '228','32','7002','2500.00','3' UNION ALL

  SELECT '229','38','7002','1500.00','3' UNION ALL

  SELECT '230','27','7002','35300.00','4' UNION ALL

  SELECT '231','32','7002','2500.00','4' UNION ALL

  SELECT '232','38','7002','3000.00','4' UNION ALL

  SELECT '245','27','7002','93250.00','1' UNION ALL

  SELECT '246','32','7002','3500.00','1' UNION ALL

  SELECT '247','38','7002','1000.00','1' UNION ALL

  SELECT '248','27','7002','15500.00','2' UNION ALL

  SELECT '249','32','7002','1000.00','2' UNION ALL

  SELECT '250','38','7002','1000.00','2' UNION ALL

  SELECT '251','27','7002','35100.00','3' UNION ALL

  SELECT '252','32','7002','2500.00','3' UNION ALL

  SELECT '253','38','7002','1500.00','3' UNION ALL

  SELECT '254','27','7002','35300.00','4' UNION ALL

  SELECT '255','32','7002','2500.00','4' UNION ALL

  SELECT '256','38','7002','3000.00','4' UNION ALL

  SELECT '269','27','7002','93250.00','1' UNION ALL

  SELECT '270','32','7002','3500.00','1' UNION ALL

  SELECT '271','38','7002','1000.00','1' UNION ALL

  SELECT '272','27','7002','15500.00','2' UNION ALL

  SELECT '273','32','7002','1000.00','2' UNION ALL

  SELECT '274','38','7002','1000.00','2' UNION ALL

  SELECT '275','27','7002','35100.00','3' UNION ALL

  SELECT '276','32','7002','2500.00','3' UNION ALL

  SELECT '277','38','7002','1500.00','3' UNION ALL

  SELECT '278','27','7002','35300.00','4' UNION ALL

  SELECT '279','32','7002','2500.00','4' UNION ALL

  SELECT '280','38','7002','3000.00','4' UNION ALL

  SELECT '293','27','7002','93250.00','1' UNION ALL

  SELECT '294','32','7002','3500.00','1' UNION ALL

  SELECT '295','38','7002','1000.00','1' UNION ALL

  SELECT '296','27','7002','15500.00','2' UNION ALL

  SELECT '297','32','7002','1000.00','2' UNION ALL

  SELECT '298','38','7002','1000.00','2' UNION ALL

  SELECT '299','27','7002','35100.00','3' UNION ALL

  SELECT '300','32','7002','2500.00','3' UNION ALL

  SELECT '301','38','7002','1500.00','3' UNION ALL

  SELECT '302','27','7002','35300.00','4' UNION ALL

  SELECT '303','32','7002','2500.00','4' UNION ALL

  SELECT '304','38','7002','3000.00','4' UNION ALL

  SELECT '318','29','7047','6000.00','1' UNION ALL

  SELECT '319','27','7047','6000.00','1' UNION ALL

  SELECT '320','11','7047','13600.00','1' UNION ALL

  SELECT '321','16','7047','30000.00','1' UNION ALL

  SELECT '322','29','7047','30000.00','3' UNION ALL

  SELECT '323','27','7047','45200.00','3' UNION ALL

  SELECT '324','29','7047','5750.00','2' UNION ALL

  SELECT '325','27','7047','3200.00','2' UNION ALL

  SELECT '326','11','7047','5400.00','4' UNION ALL

  SELECT '327','16','7047','10000.00','4' UNION ALL

  SELECT '334','15','7047','2500.00','1' UNION ALL

  SELECT '335','15','7047','2000.00','2' UNION ALL

  SELECT '336','15','7047','4200.00','3' UNION ALL

  SELECT '337','15','7047','3600.00','4' UNION ALL

  SELECT '342','26','7047','54000.00','1' UNION ALL

  SELECT '343','29','7047','13000.00','1' UNION ALL

  SELECT '344','27','7047','5000.00','1' UNION ALL

  SELECT '345','29','7047','38000.00','4' UNION ALL

  SELECT '346','27','7047','36000.00','4' UNION ALL

  SELECT '347','27','7047','67400.00','4' UNION ALL

  SELECT '348','27','7047','11250.00','1' UNION ALL

  SELECT '349','32','7047','500.00','1' UNION ALL

  SELECT '350','27','7047','21250.00','1' UNION ALL

  SELECT '351','32','7047','500.00','1' UNION ALL

  SELECT '352','32','7047','1000.00','1' UNION ALL

  SELECT '353','27','7047','31350.00','1' UNION ALL

  SELECT '354','32','7047','1500.00','1' UNION ALL

  SELECT '355','27','7047','31450.00','1' UNION ALL

  SELECT '356','32','7047','6000.00','1' UNION ALL

  SELECT '357','29','7047','34500.00','1' UNION ALL

  SELECT '358','29','7047','70300.00','1' UNION ALL

  SELECT '359','29','7047','120225.00','1' UNION ALL

  SELECT '360','29','7047','146300.00','3' UNION ALL

  SELECT '361','29','7047','103050.00','4' UNION ALL

  SELECT '366','29','7047','281250.00','1' UNION ALL

  SELECT '367','29','7047','268600.00','3' UNION ALL

  SELECT '368','29','7047','223100.00','4' UNION ALL

  SELECT '405','29','7047','57750.00','2' UNION ALL

  SELECT '406','27','7047','20000.00','2' UNION ALL

  SELECT '407','11','7047','400.00','2' UNION ALL

  SELECT '408','32','7047','4000.00','2' UNION ALL

  SELECT '409','29','7047','52500.00','3' UNION ALL

  SELECT '410','27','7047','20000.00','3' UNION ALL

  SELECT '411','11','7047','400.00','3' UNION ALL

  SELECT '412','32','7047','4000.00','3' UNION ALL

  SELECT '413','29','7047','52500.00','4' UNION ALL

  SELECT '414','27','7047','19000.00','4' UNION ALL

  SELECT '415','11','7047','400.00','4' UNION ALL

  SELECT '416','32','7047','4000.00','4' UNION ALL

  SELECT '443','5','7364','12800.00','1' UNION ALL

  SELECT '448','5','7364','9100.00','4' UNION ALL

  SELECT '449','6','7364','8400.00','1' UNION ALL

  SELECT '454','6','7364','8400.00','4' UNION ALL

  SELECT '455','32','7364','500.00','1' UNION ALL

  SELECT '460','32','7364','1000.00','4' UNION ALL

  SELECT '462','27','7364','16240.00','4' UNION ALL

  SELECT '464','11','7364','20000.00','4' UNION ALL

  SELECT '466','7','7364','2000.00','4' UNION ALL

  SELECT '468','27','7364','34900.00','4' UNION ALL

  SELECT '470','6','7364','12000.00','4' UNION ALL

  SELECT '541','5','7002','500.00','1' UNION ALL

  SELECT '542','19','7002','1500.00','1' UNION ALL

  SELECT '543','19','7002','2000.00','1' UNION ALL

  SELECT '544','19','7002','4000.00','1' UNION ALL

  SELECT '545','19','7002','3000.00','1' UNION ALL

  SELECT '546','19','7002','3000.00','1' UNION ALL

  SELECT '547','19','7002','6000.00','1' UNION ALL

  SELECT '548','19','7002','6000.00','1' UNION ALL

  SELECT '549','19','7002','15000.00','1' UNION ALL

  SELECT '550','19','7002','1350.00','1' UNION ALL

  SELECT '551','27','7047','1500.00','1' UNION ALL

  SELECT '556','19','7047','3000.00','2' UNION ALL

  SELECT '557','10','7047','8000.00','1' UNION ALL

  SELECT '558','15','7364','3400.00','1' UNION ALL

  SELECT '559','27','7364','40000.00','1' UNION ALL

  SELECT '560','12','7364','3000.00','1' UNION ALL

  SELECT '561','5','7002','255220.00','1' UNION ALL

  SELECT '562','5','7002','146000.00','3' UNION ALL

  SELECT '563','5','7002','192270.00','4' UNION ALL

  SELECT '568','5','7002','318600.00','1' UNION ALL

  SELECT '569','5','7002','146000.00','3' UNION ALL

  SELECT '570','5','7002','192250.00','4' UNION ALL

  SELECT '575','5','7002','318600.00','1' UNION ALL

  SELECT '576','5','7002','146000.00','3' UNION ALL

  SELECT '577','5','7002','192250.00','4' UNION ALL

  SELECT '582','5','7002','318600.00','1' UNION ALL

  SELECT '583','5','7002','146000.00','3' UNION ALL

  SELECT '584','5','7002','192250.00','4' UNION ALL

  SELECT '589','5','7002','318600.00','1' UNION ALL

  SELECT '590','5','7002','146000.00','3' UNION ALL

  SELECT '591','5','7002','192250.00','4' UNION ALL

  SELECT '596','5','7002','318600.00','1' UNION ALL

  SELECT '597','5','7002','146000.00','3' UNION ALL

  SELECT '598','5','7002','192250.00','4' UNION ALL

  SELECT '603','44','7002','71143.00','1' UNION ALL

  SELECT '605','5','7047','3000.00','1' UNION ALL

  SELECT '606','5','7047','628205.00','1' UNION ALL

  SELECT '607','5','7047','159777.00','3' UNION ALL

  SELECT '608','5','7047','125766.00','4' UNION ALL

  SELECT '613','5','7047','110100.00','1' UNION ALL

  SELECT '614','5','7047','80200.00','3' UNION ALL

  SELECT '615','5','7047','63300.00','4' UNION ALL

  SELECT '621','5','7364','72384.00','1' UNION ALL

  SELECT '643','5','7047','11000.00','1' UNION ALL

  SELECT '644','5','7047','6500.00','3' UNION ALL

  SELECT '645','5','7047','65500.00','4' UNION ALL

  SELECT '650','5','7047','129200.00','1' UNION ALL

  SELECT '651','5','7047','121700.00','3' UNION ALL

  SELECT '652','5','7047','114500.00','4' UNION ALL

  SELECT '657','5','7047','61000.00','1' UNION ALL

  SELECT '658','5','7047','14200.00','3' UNION ALL

  SELECT '663','5','7047','287900.00','1' UNION ALL

  SELECT '664','5','7047','245800.00','3' UNION ALL

  SELECT '665','5','7047','125400.00','4' UNION ALL

  SELECT '670','5','7047','115910.00','1' UNION ALL

  SELECT '671','5','7047','39400.00','3' UNION ALL

  SELECT '672','5','7047','26600.00','4' UNION ALL

  SELECT '705','5','7002','500.00','1' UNION ALL

  SELECT '706','5','7002','4500.00','3' UNION ALL

  SELECT '707','5','7002','20250.00','4' UNION ALL

  SELECT '719','5','7002','16500.00','1' UNION ALL

  SELECT '720','32','7002','4500.00','1' UNION ALL

  SELECT '721','38','7002','2000.00','1' UNION ALL

  SELECT '722','27','7002','91500.00','1' UNION ALL

  SELECT '723','32','7002','2500.00','3' UNION ALL

  SELECT '724','38','7002','1500.00','3' UNION ALL

  SELECT '725','27','7002','26100.00','3' UNION ALL

  SELECT '726','32','7002','2500.00','4' UNION ALL

  SELECT '727','38','7002','3000.00','4' UNION ALL

  SELECT '728','27','7002','26250.00','4' UNION ALL

  SELECT '741','27','7002','17250.00','1' UNION ALL

  SELECT '742','27','7002','9000.00','3' UNION ALL

  SELECT '743','27','7002','9050.00','4' UNION ALL

  SELECT '748','27','7002','17250.00','1' UNION ALL

  SELECT '749','27','7002','9000.00','3' UNION ALL

  SELECT '750','27','7002','9050.00','4' UNION ALL

  SELECT '755','27','7002','17250.00','1' UNION ALL

  SELECT '756','27','7002','9000.00','3' UNION ALL

  SELECT '757','27','7002','9050.00','4' UNION ALL

  SELECT '762','27','7002','17250.00','1' UNION ALL

  SELECT '763','27','7002','9000.00','3' UNION ALL

  SELECT '764','27','7002','9050.00','4' UNION ALL

  SELECT '769','27','7002','17250.00','1' UNION ALL

  SELECT '770','27','7002','9000.00','3' UNION ALL

  SELECT '771','27','7002','9050.00','4' UNION ALL

  SELECT '789','32','7002','4500.00','1' UNION ALL

  SELECT '790','38','7002','2000.00','1' UNION ALL

  SELECT '791','27','7002','91500.00','1' UNION ALL

  SELECT '792','32','7002','2500.00','3' UNION ALL

  SELECT '793','38','7002','1500.00','3' UNION ALL

  SELECT '794','27','7002','26100.00','3' UNION ALL

  SELECT '795','32','7002','2500.00','4' UNION ALL

  SELECT '796','38','7002','3000.00','4' UNION ALL

  SELECT '797','27','7002','26250.00','4' UNION ALL

  SELECT '810','16','7364','100000.00','1' UNION ALL

  SELECT '812','19','7364','22000.00','1' UNION ALL

  SELECT '813','25','7364','10500.00','1' UNION ALL

  SELECT '854','21','7364','78000.00','1' UNION ALL

  SELECT '855','19','7364','3000.00','1' UNION ALL

  SELECT '856','16','7364','60000.00','1' UNION ALL

  SELECT '858','19','7364','23000.00','1' UNION ALL

  SELECT '859','5','7364','1088.00','1' UNION ALL

  SELECT '860','5','7002','500.00','1' UNION ALL

  SELECT '861','32','7002','500.00','3' UNION ALL

  SELECT '862','27','7002','21250.00','3' UNION ALL

  SELECT '863','32','7002','500.00','4' UNION ALL

  SELECT '864','27','7002','3500.00','4' UNION ALL

  SELECT '871','27','7002','9750.00','1' UNION ALL

  SELECT '872','32','7002','500.00','1' UNION ALL

  SELECT '873','5','7002','500.00','1' UNION ALL

  SELECT '874','27','7002','21250.00','3' UNION ALL

  SELECT '875','32','7002','500.00','3' UNION ALL

  SELECT '884','5','7047','95712.00','1' UNION ALL

  SELECT '885','5','7047','75876.00','3' UNION ALL

  SELECT '886','5','7047','116917.00','4' UNION ALL

  SELECT '918','27','7002','3000.00','3' UNION ALL

  SELECT '919','32','7002','500.00','3' UNION ALL

  SELECT '920','27','7002','21750.00','4' UNION ALL

  SELECT '921','32','7002','500.00','4' UNION ALL

  SELECT '926','29','7002','15750.00','2' UNION ALL

  SELECT '927','27','7002','4500.00','2' UNION ALL

  SELECT '928','32','7002','500.00','2' UNION ALL

  SELECT '936','45','7364','443520.00','1' UNION ALL

  SELECT '939','45','7364','443520.00','1' UNION ALL

  SELECT '942','18','7364','50000.00','4' UNION ALL

  SELECT '943','22','7364','91000.00','4' UNION ALL

  SELECT '944','19','7364','47000.00','4' UNION ALL

  SELECT '945','16','7364','55000.00','4' UNION ALL

  SELECT '946','17','7364','30000.00','4' UNION ALL

  SELECT '947','23','7364','4000.00','4' UNION ALL

  SELECT '948','9','7364','3000.00','4' UNION ALL

  SELECT '949','25','7364','9000.00','4' UNION ALL

  SELECT '950','19','7364','8000.00','1' UNION ALL

  SELECT '951','12','7364','8000.00','1' UNION ALL

  SELECT '952','19','7364','32000.00','1' UNION ALL

  SELECT '953','12','7364','3400.00','1' UNION ALL

  SELECT '964','19','7364','7800.00','1'

  SET IDENTITY_INSERT His OFF

  CREATE VIEW His_Vw

  AS

  SELECT His.sn, His.cst, His.acc, PJs.pj, His.amt, Regns.regn, Smps.smp

  FROM His INNER JOIN PJs ON His.acc = PJs.acc

  INNER JOIN Regns ON His.regn = Regns.sn

  INNER JOIN Smps ON PJs.acc = Smps.acc AND Regns.sn = Smps.regn

  GO

  And here's my query that is not doing all I want.

  SELECT cst

  , SUM(amt)

  , tot_smp = (CASE

  WHEN COUNT(DISTINCT acc) = 1 THEN CASE

  WHEN COUNT(DISTINCT regn) = 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT regn) > 1 THEN CASE

  WHEN COUNT(DISTINCT smp) > 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT smp) = 1 THEN COUNT(DISTINCT regn) * SUM(DISTINCT smp)

  END

  END

  WHEN COUNT(DISTINCT acc) > 1 THEN CASE

  WHEN COUNT(DISTINCT regn) = 1 THEN CASE

  WHEN COUNT(DISTINCT smp) > 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT smp) = 1 THEN COUNT(DISTINCT acc) * SUM(DISTINCT smp)

  END

  WHEN COUNT(DISTINCT regn) > 1 THEN CASE

  WHEN COUNT(DISTINCT smp) > 1 THEN SUM(DISTINCT smp)

  WHEN COUNT(DISTINCT smp) = 1 THEN COUNT(DISTINCT regn) * SUM(DISTINCT smp)

  END

  END

  END)

  FROM His_Vw

  GROUP BY cst

  Thanks for the patience.

• oolawole

  SSC Veteran

  Points: 285

  More actions

  August 1, 2012 at 6:53 am

  #1520224

  I have corrected the errors and even tested it

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  August 1, 2012 at 8:19 am

  #1520304

  oolawole (8/1/2012)

  ---

  I have corrected the errors and even tested it

  OK so now we have ddl and sample data. What is the question? Can you tell us what the desired output is based on your sample data?

  It would REALLY help the readability of your posts if you can use the IFCode Shortcuts (on the left when posting) so your code can go into a nice clean code window. Makes the posts not crazy long.

  <offtopic>

  Maybe being a bit nitpicky but I would recommend that your object and column names not be so abbreviated. It really makes no difference in the grand scheme of things but it sure makes your stuff easier for others to work on. The more clarity you can add through naming the easier your system is to work with. When somebody looks at your column and object names you have no idea what any of them mean.

  </offtopic>

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• oolawole

  SSC Veteran

  Points: 285

  More actions

  August 1, 2012 at 9:08 am

  #1520339

  To make it easy then, I will say amt is amount, acc is an account, smp is a sample, and regn is a region. The table His is for history, PJs is for Projects, Regns is for regions and Smps is for samples by regions.

  Let me try explain from the query I wrote. The tot_smp is expected to be the total sample across regions and accounts for each cst, no multiple sum of any sample in a region for an account.

  For clarity, try this

  SELECT sn,cst,acc,pj,amt,regn,smp

  FROM [dbo].[His_Vw]

  WHERE cst=19

  (Sorry I didn't get the use of the IFCode well)

  From the result you see, Tot_Smp should be 560 + 2035 + 40 + 40 =2675, not 2635 that my initial query is giving for cst=19.

  Let me know if further explanation is needed.

  Thanks

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  August 1, 2012 at 9:31 am

  #1520355

  oolawole (8/1/2012)

  ---

  To make it easy then, I will say amt is amount, acc is an account, smp is a sample, and regn is a region. The table His is for history, PJs is for Projects, Regns is for regions and Smps is for samples by regions.

  The comments were not made to make things easier for me. It was merely a suggestion in improving your object names. It makes it easier for others (think about the next person who will inherit your code). When you have to explain what a table is or what a column is it is a good sign you need to think a little bit more about the name.

  Let me try explain from the query I wrote. The tot_smp is expected to be the total sample across regions and accounts for each cst, no multiple sum of any sample in a region for an account.

  For clarity, try this

  SELECT sn,cst,acc,pj,amt,regn,smp

  FROM [dbo].[His_Vw]

  WHERE cst=19

  (Sorry I didn't get the use of the IFCode well)

  From the result you see, Tot_Smp should be 560 + 2035 + 40 + 40 =2675, not 2635 that my initial query is giving for cst=19.

  Let me know if further explanation is needed.

  Thanks

  I think I now understand what you are after. The following produces the desired output based on your example.

  select cst, sum(smp) as TotalSMP

  from

  (

  SELECT regn, acc, smp, cst

  FROM [dbo].[His_Vw]

  group by regn, acc, smp, cst

  ) x

  where x.cst = 19

  group by cst

  Hope that helps.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• oolawole

  SSC Veteran

  Points: 285

  More actions

  August 1, 2012 at 10:09 am

  #1520385

  Thanks so much, that did the job very well.

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  August 1, 2012 at 10:13 am

  #1520388

  oolawole (8/1/2012)

  ---

  Thanks so much, that did the job very well.

  You are quite welcome. Glad that worked!!! Thanks for letting us know.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/