retreive data in different view

• wertyui

  SSC Veteran

  Points: 275

  More actions

  January 27, 2012 at 3:27 am

  #267332

  How to create query statement (only one statement) to retreive data below

  dateacodebcodeamt

  14/09/201160190021427.1969

  14/09/20116019001454.3912

  14/09/2011601900299.6004

  result in any view below

  View summary by acode

  dateacodebcodeamt

  14/09/20116019multiple1981.1885

  View summary by acode and bcode

  dateacodebcodeamt

  14/09/20116019001454.3912

  14/09/201160190021526.7973

  View in detail

  dateacodebcodeamt

  14/09/201160190021427.1969

  14/09/20116019001454.3912

  14/09/2011601900299.6004

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 27, 2012 at 3:40 am

  #1439242

  any chance that you might provide some set up script and sample data pls...we can then easily cut and paste into SSMS to give you a tried and tested answer.

  please can you clarify what you mean by "only one statement"...

  eg....

  ---=== EXAMPLE SET UP SCRIPT ===---

  CREATE TABLE [dbo].[Data](

  [DateId] [datetime] NOT NULL,

  [Data] [int] NOT NULL

  ) ON [PRIMARY]

  INSERT INTO [dbo].[Data]([DateId], [Data])

  SELECT '20120101 00:00:00.000', 100 UNION ALL

  SELECT '20120103 00:00:00.000', 50

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  January 27, 2012 at 3:49 am

  #1439248

  Not sure what you mean by one statement only but this looks like straightforward aggregation using GROUP BY

  SET DATEFORMAT DMY

  DECLARE @t TABLE([date] DATETIME,acode INT,bcode CHAR(3),amt FLOAT)

  INSERT INTO @t([date],acode,bcode,amt)

  SELECT '14/09/2011',6019,'002',1427.1969 UNION ALL

  SELECT '14/09/2011',6019,'001',454.3912 UNION ALL

  SELECT '14/09/2011',6019,'002',99.6004;

  SELECT [date],

  acode,

  CASE WHEN COUNT(DISTINCT bcode)>1 THEN 'Multiple' ELSE MAX(bcode) END AS bcode,

  SUM(amt) AS amt

  FROM @t

  GROUP BY [date],acode

  SELECT [date],

  acode,

  bcode,

  SUM(amt) AS amt

  FROM @t

  GROUP BY [date],acode,bcode

  SELECT [date],

  acode,

  bcode,

  amt

  FROM @t

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• wertyui

  SSC Veteran

  Points: 275

  More actions

  January 27, 2012 at 4:32 am

  #1439258

  CREATE TABLE [dbo].[TransData](

  [date] [smalldatetime] NOT NULL,

  [acode] [varchar](6) NOT NULL,

  [bcode] [varchar](30) NULL,

  [amt] [numeric](15, 4) NOT NULL DEFAULT ((0))

  )

  INSERT INTO [dbo].[TransData](date, acode, bcode, amt)

  SELECT '20110914', '6019','002',1427.1969

  SELECT '20110914', '6019','001',454.3912

  SELECT '20110914', '6019','002',99.6004

  SELECT '20110914', '1019','',47.3012

  query data with input parameter

  - viewby

  - acode

  - bcode

  I need a result from query

  1. View data summary by acode

  (return amt as sum of all amt in same acode, and show bcode as "multiple")

  dateacodebcodeamt

  14/09/20116019multiple1981.1885

  2. View summary by acode and bcode

  (return amt as sum of all amt in same acode and same bcode, and show bcode as bcode)

  dateacodebcodeamt

  14/09/20116019001454.3912

  14/09/201160190021526.7973

  and if specified bcode eg bcode="002"

  dateacodebcodeamt

  14/09/201160190021526.7973

  3. View in detail

  (return all record in the same acode)

  dateacodebcodeamt

  14/09/201160190021427.1969

  14/09/20116019001454.3912

  14/09/2011601900299.6004

  and if specified bcode eg bcode="002"

  dateacodebcodeamt

  14/09/201160190021427.1969

  14/09/2011601900299.6004

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 27, 2012 at 4:58 am

  #1439269

  doesn't Mark's code above provide your solution?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• wertyui

  SSC Veteran

  Points: 275

  More actions

  January 27, 2012 at 5:49 am

  #1439286

  Thank you both of you for this help.