Re-Arrange Table (summarize only certain rows)

Question

question

samn265 asked Feb 22, '19

Re-Arrange Table (summarize only certain rows)

I have the following Table:

2019-02-21-22-35-59.pdf

Thus, whenever I have common “J No”, I would like to perform the following calculations:

Take only the 1^st value from “Set up Start” and combine it with the last two values from “Run Start” and “Run End” and delete any records in between them.

Here is my Query code:

SELECT TOP (1000) [LineNo]
      ,[J No]
      ,[FC]
      ,[SetupStart]
      ,[UpTimeStart]
      ,[UpTimeEnd]
      ,[Setup]
      ,[UpTime]
      ,[Targ Len]
      ,[Act Len]
      ,[R Up F]
      ,[R Dn F]
      ,[Scrap]
  FROM [Initial].[dbo].[vWGetProductionInfo]
  ORDER BY SetupStart ASC, UpTimeEnd ASC

How would I do modify this query to give me the desired output I have listed above?

sql-server-2012

2019-02-21-22-35-59.pdf (70.7 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI commented · Feb 22, 2019 at 09:38 AM

I'd recommend creating a 'CREATE TABLE' and an INSERT statement so that we can simply create code against your data.

0 ·

Answer 1 · 2019-02-22T15:06:43Z

KenJ answered Feb 22, '19

it looks like you're rolling up a mixture of min and max values grouped by J No. Something like this should get you there...

[J No],
max([FC]) as 'FC',
max([SetupStart]) as 'SetupStart',
min([UpTimeStart]) as 'UpTimeStart',
min([UpTimeEnd]) as 'UpTimeEnd'
... etc...
max([R Dn F]) as R Dn F',
max([Scrap]) as 'Scrap'
FROM[Initial].[dbo].[vWGetProductionInfo]
group by [J No]
...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question