Collapse record and get the number of days

Question

Collapse record and get the number of days

Angela4eva

Old Hand

Points: 356
More actions
April 27, 2020 at 2:21 pm
Go to Answer
#3746657

here is my data. I am trying to get the dates in single row like below. Any help appreciated.
result I need
Jeff Moden

SSC Guru

Points: 1003863
More actions
April 27, 2020 at 3:41 pm
Answer
#3746708
In the future, please provide the data in a readily consumable format so that we can test the code we provide as an answer. See the first link in my signature line below for one way to do that. It'll help us help you better and faster. Thanks.
In the meantime, try this with the understanding that I've not tested it, don't know what datatypes are actually being used, and don't even know what your table name is...
```
 SELECT  OrderNo
        ,LineNo
        ,OrderDate = MAX(CASE WHEN EventType = 'ORDER' THEN EventDate ELSE NULL END)
        ,ShipDate  = MAX(CASE WHEN EventType = 'SHIP'  THEN EventDate ELSE NULL END)
    FROM dbo.YourTableNameHere
  GROUP BY OrderNo, LineNo
  ORDER BY OrderNo, LineNo
;
```
That's an ancient "Black Arts" method known as a CROSSTAB. It can also be done using a PIVOT operator, which I won't cover because I don't care for it for many reasons.
I strongly recommend that you read the following article so that you 1) understand what the code above is doing and 2) you can do your next one on your own.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

--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)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Assuming that for each order line you have either an order date or an order date and a ship date (and no other rows), something like this should work. If you need any further assistance, please provide the data in the form of CREATE TABLE and INSERT statements so that we can test any code before posting.

SELECT
    OrderNo
,   lineno
,   MIN(eventdate) AS orderdate
,   CASE -- only want ShipDate populated if there are two rows
        WHEN COUNT(*) = 2 THEN MAX(eventdate)
        WHEN COUNT(*) = 1 THEN NULL
    END AS Shipdate
FROM MyTable
GROUP BY
    OrderNo
,   lineno;

John

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

GROUP BY is your friend here, and John has a great solution. If you need something else, please give us the table DDL, some INSERT statements for this data, and then something like this for results:

CREATE TABLE #Results
( OrderNo int
,   lineno int
,   orderdate date
,   Shipdate date
)
go
insert #results (orderno, lineno, orderdate, shipdate)
 values ( 1122, 1, '11/5/2019', '12/16/2019')

That helps us set things up and also helps you add data for other result cases as you find them

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 3

>> here is my data. I am trying to get the dates in single row like below. Any help appreciated. <<

The help you need is to actually learn how to do SQL in RDBMS. You're trying to format data in the database and set up in a presentation layer. And you also happen to be spitting on Dr. Codd s grave by violating first normal form. It would also help you if you bothered to learn that the only display format allowed in the ANSI/ISO standards for SQL is "yyyy-mm-dd" and not your local dialect.

Based on nothing you posted, I'm going to guess you might want to look at an article of mine that dealt with status changes. It's on Redgate and you can Google it easily.

Please post DDL and follow ANSI/ISO standards when asking for help.