How to summarise, listing on same line

• timotech

  SSCrazy

  Points: 2698

  More actions

  February 14, 2011 at 2:36 am

  #233539

  Hi all i have this query i want to use for a summary operation:

  SELECT AdDate, COUNT(Duration) AS [Number Of Spots], @Scheduled AS ScheduledSpots, @BeginTime + '-' + @EndTime AS ScheduledTime, AdTime As CapturedTime

  FROM dbo.mytable

  WHERE (AdDate IN (@BeginDate, @EndDate)) AND (FK_StationId = @Station) AND (AdTime BETWEEN @BeginTime AND @EndTime) AND (Duration = @Duration)

  GROUP BY AdDate, AdTime

  i want to achieve this kind of result:

  --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable

  CREATE TABLE #mytable (

  AdDate DATETIME,

  [Number Of Spots] INT,

  ScheduledSpots INT,

  ScheduledTime NVARCHAR(25),

  CaturedTime NVARCHAR(MAX)

  )

  SET DATEFORMAT YMD

  insert Into mytable(AdDate,[Number Of Spots],ScheduledSpots,ScheduledTime,CaturedTime) Values (

  Select '2010-06-02', '4', '3', '05:55:00-12:05:00', '05:55:48, 05:57:55, 06:07:49')

  But what i am getting is:

  insert Into mytable(AdDate,[Number Of Spots],ScheduledSpots,ScheduledTime,CaturedTime) Values (

  Select '2010-06-02','1','3', '05:55:00-12:05:00','05:55:48' union all

  Select '2010-06-02','1','3', '05:55:00-12:05:00','05:57:55' union all

  Select '2010-06-03','1','3', '05:55:00-12:05:00','06:07:49' union all

  Select '2010-06-03','1','3', '05:55:00-12:05:00','06:45:05'

  I have 3 questions:

  1. How can i list the captured times on a single line

  2. How can i use a parameter list with the 'IN' Statement above for a number of parameters

  3. How can i put the extra time in another column like this:

  CapturedExtra

  06:45:05

  Please any suggestion is appreciated.

  Thanks

  Timotech

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  February 14, 2011 at 6:11 pm

  #1286809

  Use FOR XML PATH to concatenate the data. If you want a coded answer, take a peek at the article at the first link in my signature line below for how to post readily consumable data.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• timotech

  SSCrazy

  Points: 2698

  More actions

  February 15, 2011 at 6:21 am

  #1287004

  Thanks for your reply, how do i use the FOR PATH XML?

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  February 15, 2011 at 12:25 pm

  #1287265

  timotech (2/15/2011)

  ---

  Thanks for your reply, how do i use the FOR PATH XML?

  Thanks

  Sorry. I'm at work right now and can't do a detailed example until I get home tonight. In the meantime, do a search for "Concatenate For XML Path" and see what you get.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• timotech

  SSCrazy

  Points: 2698

  More actions

  February 15, 2011 at 12:41 pm

  #1287272

  Thanks so much, i already googled it, and i'll get back to you on my findings

  Timotech

• timotech

  SSCrazy

  Points: 2698

  More actions

  February 16, 2011 at 3:14 am

  #1287510

  I tried something like this:

  SELECT AdDate, COUNT(Duration) AS [Number Of Spots], @Scheduled AS ScheduledSpots, @BeginTime + '-' + @EndTime AS ScheduledTime,

  (Select AdTime as "CapturedTime()" from tbl_radio where (AdDate IN (@BeginDate, @EndDate)) AND (FK_StationId = @Station) AND (AdTime BETWEEN @BeginTime AND @EndTime) AND (Duration = @Duration) For Xml Path(' '))

  FROM dbo.tbl_Radio

  WHERE (AdDate IN (@BeginDate, @EndDate)) AND (FK_StationId = @Station) AND (AdTime BETWEEN @BeginTime AND @EndTime) AND (Duration = @Duration)

  GROUP BY AdDate

  For Xml Path('Reconciliation')

  its giving me this error:

  Row name ' ' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

  What should i do?

  Thanks

  Timotech

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  February 16, 2011 at 6:47 am

  #1287615

  Have a look here. Wayne S. wrote a nice bit of "SQL Spackle" which explains how to do it.

  http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 16, 2011 at 6:52 am

  #1287621

  You dint provide sample data yet.. you are providing us only Expected Result and the current result.. PLease post the sample data, then we will straightaway work on this 🙂

• timotech

  SSCrazy

  Points: 2698

  More actions

  February 16, 2011 at 9:02 am

  #1287744

  Ok, this is some sample data:

  CREATE TABLE [dbo].[tbl_Radio](

  [AdDate] [datetime] NULL,

  [AdTime] [nvarchar](8) NULL CONSTRAINT [DF_tbl_Radio_1_Upper_Time] DEFAULT (N'19000101 00:00:00'),

  [Duration] [float] NULL,

  [FK_BrandId] [nvarchar](4) NULL,

  [FK_BrandADId] [nvarchar](2) NULL,

  [FK_StationId] [nvarchar](5) NULL,

  [FK_ProductId] [nvarchar](6) NULL

  )

  Insert into tbl_Radio(AdDate, AdTime, Duration, FK_BrandId, FK_BrandADId, Fk_StationId, FK_ProductId) Values (

  Select '2010-06-02','11:52:30', '60','53','N8','B97','GSM002' union all

  Select '2010-06-02','12:02:36', '960','259','A1','B97','SEV006' union all

  Select '2010-06-02','12:08:00', '45','2','30','B97','DRI003' union all

  Select '2010-06-02','12:12:28', '45','2','30','B97','DRI003' union all

  Select '2010-06-02','14:02:09', '480','341','A1','B97','SEV006' union all

  Select '2010-06-02','14:10:20', '6720','158','A2','B97','SEV012' union all

  Select '2010-06-02','14:10:48', '60','53','N8','B97','GSM002' union all

  Select '2010-06-03','20:33:28', '45','18','BC','B97','DRI001' union all

  Select '2010-06-03','20:45:43', '45','18','BC','B97','DRI001' union all

  Select '2010-06-03','20:47:53', '45','18','BC','B97','DRI001' union all

  Select '2010-06-03','21:01:50', '780','109','1','B97','SEV012' union all

  Select '2010-06-03','21:04:18', '47','52','C7','B97','GSM002' union all

  Select '2010-06-03','21:05:05', '45','2','30','B97','DRI003' union all

  Select '2010-06-04','11:50:43', '33','19','1','B97','FOD010' union all

  Select '2010-06-04','12:01:52', '840','341','A1','B97','SEV006' union all

  Select '2010-06-04','12:08:50', '45','2','30','B97','DRI003' union all

  Select '2010-06-04','12:12:30', '45','2','30','B97','DRI003' union all

  Select '2010-06-04','12:15:51', '6420','158','A2','B97','SEV012' union all

  Select '2010-06-04','14:03:24', '420','341','A1','B97','SEV006' union all

  Select '2010-06-04','14:10:32', '30','53','LT','B97','GSM002' union all

  Select '2010-06-04','14:11:02', '47','52','C7','B97','GSM002'

  Thanks

  Timotech

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 16, 2011 at 10:58 am

  #1287800

  Yes we have got the sample data, but what do u want to achieve from that? I mean, can u be still more clear on your requirement.

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  February 16, 2011 at 5:54 pm

  #1288005

  ColdCoffee (2/16/2011)

  ---

  Yes we have got the sample data, but what do u want to achieve from that? I mean, can u be still more clear on your requirement.

  The "Rosetta Stone" for this problem is in the first and second code block of the original post. I'll see what I can do but don't let that hold anyone up if they beat me to it.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  February 16, 2011 at 6:22 pm

  #1288009

  Ok... peeling one potato at a time, here's how to concatenate the CapturedTime's. I haven't tested it because I couldn't figure out how to populate the variables so they would match your expected result from the test data you provided ESPECIALLY where Duration = @Duration is concerned.

  SELECT AdDate,

  COUNT(Duration) AS [Number Of Spots],

  @Scheduled AS ScheduledSpots,

  @BeginTime + '-' + @EndTime AS ScheduledTime,

  STUFF(CAST((

  SELECT ', ' + AdTime

  FROM dbo.MyTable mt2

  WHERE mt2.AdDate = mt1.AdDate

  AND mt2.FK_StationId = @Station

  AND mt2.AdTime BETWEEN @BeginTime AND @EndTime

  AND mt2.Duration = @Duration

  FOR XML PATH(''),TYPE

  ) AS NVARCHAR(MAX)),1,2,'') AS CapturedTime

  FROM dbo.MyTable mt1

  WHERE AdDate BETWEEN @BeginDate AND @EndDate

  AND FK_StationId = @Station

  AND AdTime BETWEEN @BeginTime AND @EndTime

  AND Duration = @Duration

  GROUP BY AdDate

  As a side bar, I have no idea what you mean by "Extra Time" because I don't understand why you're looking for a particular duration instead of a sum of durations.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 16, 2011 at 6:43 pm

  #1288015

  How about this?

  DECLARE @Scheduled VARCHAR(2) = '3'

  ,@BeginTime VARCHAR(8) = '05:55:00'

  ,@EndTime VARCHAR(8) = '12:30:00'

  ,@Station VARCHAR(5) = 'B97'

  ,@Duration FLOAT = 45

  ,@BeginDate DATE = '2010-06-02 00:00:00.000'

  ,@EndDate DATE = '2010-06-02 00:00:00.000'

  ;WITH CTE AS

  (

  SELECT AdDate

  ,AdTime

  FROM dbo.tbl_Radio Rad_Outer

  WHERE AdDate BETWEEN @BeginDate AND @EndDate

  AND CAST( AdTime AS TIME) BETWEEN @BeginTime AND @EndTime

  AND FK_StationId = @Station

  AND Duration = @Duration

  )

  SELECT AdDate

  ,COUNT(*) [Number Of Spots]

  ,@Scheduled AS ScheduledSpots

  ,@BeginTime + '-' + @EndTime AS ScheduledTime

  ,STUFF ( (SELECT ',' + Rad_Inner.AdTime

  FROM CTE Rad_Inner

  FOR XML PATH(''))

  ,1,1,'') AS CapturedTime

  FROM CTE

  GROUP BY AdDate

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 16, 2011 at 6:45 pm

  #1288016

  Oops, Jeff had already posted the same solution 🙂 Sorry Jeff,dint see it 🙂

• timotech

  SSCrazy

  Points: 2698

  More actions

  February 17, 2011 at 1:58 am

  #1288086

  Thanks Guys, I'm working on it.