It's not so simple for me but it will be for you experts.
In the sample data below, if the ShipmentID is the same AND AuditTitle IN('Rated', 'Carrier') AND DateDiff for CreateDT is less than 1 second ........ select those rows
Hope my request makes sense. Thanks.
As always, data in an image is useless to us. We need data in a directly usable format, i.e., CREATE TABLE and INSERT statement(s).
Sorry @ScottPletcher
CREATE TABLE [dbo].[ShipmentChangeAudits](
[ShipmentId] [int] NOT NULL,
[AuditTitle] [varchar](100) NOT NULL,
[AuditDetail] [varchar](5000) NOT NULL,
[CreateByUserId] [int] NOT NULL,
[CreateDT] [datetime2](3) NOT NULL)
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(896537,
'Rated',
'New: Shipment was rated. Alternate carrier 10557 was selected.',
52,
'2022-10-03 09:25:23.557')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(896537,
'Carrier',
'From: PANTHER EXPEDITED SERVICES To: ARCBEST',
52,
'2022-10-03 09:25:23.584')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(896537,
' Commodities',
'1 change(s)',
52,
'2022-10-03 09:24:32.480')
What exactly have you yourself tried that has not worked?
You been on this site for a while now. You way past rookie now.
Time to start contributing champ
You have to put a length on varchar columns or it defaults to 1; therefore your script doesn't run. I corrected this for you last time but you're too experienced that we should have to keep writing basic set up for you.
@yosiasz and @ScottPletcher - So this is what I copied from my Word doc. Sorry I failed to notice that, when I pasted it, the length did not transfer:
CREATE TABLE [dbo].[ShipmentChangeAudits](
[ShipmentId] [int] NOT NULL,
[AuditTitle] [varchar](100) NOT NULL,
[AuditDetail] [varchar](5000) NOT NULL,
[CreateByUserId] [int] NOT NULL,
[CreateDT] [datetime2](3) NOT NULL)
I have tried using TOP 1 with a GROUP BY but I'm no where close to having a solution.
I really only need to return 1 row - the row containing "Carrier" in AuditTitle.
Thanks.
use 3 ticks to comment your code.
3 of these ---> ` <--- before and after your code.
[AuditTitle] varchar(50) NOT NULL,
This one
Thanks!
Copy paste 3 of these ---> ` <--- before and after your code.
not '
CREATE TABLE [dbo].[ShipmentChangeAudits](
[ShipmentId] [int] NOT NULL,
[AuditTitle] [varchar](100) NOT NULL,
[AuditDetail] [varchar](5000) NOT NULL,
[CreateByUserId] [int] NOT NULL,
[CreateDT] [datetime2](3) NOT NULL)
The sample DML data you provided is different than your original image data which prevents us from helping you. Come on dude.
What do you mean by 1 second? 1 second between 1 row to the other or 1 second compared to current dste time?
I was trying to reduce the rows but here is the code to insert what I originally posted as sample data:
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(773328,
'Rated',
'New: Shipment was rated. Contract 15480 was selected.',
608,
'2022-07-21 13:24:03:070')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(773328,
'Rated',
'New: Shipment was rated. Contract 13362 was selected.',
937,
'2022-07-21 10:30:05.666')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(773328,
'Carrier',
'From: SAIA INC. TO: OLD DOMINION FREIGHT LINE INC',
937,
'2022-07-21 10:30:05.736')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(773328,
'Rated',
'New: Shipment was rated. Contract 13362 was selected.',
937,
'2022-07-21 10:31:38.452')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(773328,
'Rated',
'New: Shipment was rated. Contract 13362 was selected.',
937,
'2022-07-21 10:32:27.191')
INSERT INTO [dbo].[ShipmentChangeAudits]
([ShipmentId]
,[AuditTitle]
,[AuditDetail]
,[CreateByUserId]
,[CreateDT])
VALUES
(773328,
'Rated',
'New: Shipment was rated. Contract 13362 was selected.',
937,
'2022-07-21 10:32:49.432')
What do you mean by 1 second? 1 second between 1 row to the other or 1 second compared to current dste time?
Maybe this?!:
;WITH cte_Shipments AS (
SELECT *,
LAG(CreateDT, 1) OVER(PARTITION BY ShipmentID ORDER BY CreateDT) AS prev_CreateDT,
LEAD(CreateDT, 1) OVER(PARTITION BY ShipmentID ORDER BY CreateDT) AS next_CreateDT
FROM dbo.ShipmentChangeAudits
WHERE AuditTitle IN ('Carrier', 'Rated')
)
SELECT *
FROM cte_Shipments
WHERE DATEDIFF(MS, prev_CreateDT, CreateDT) < 1000 OR
DATEDIFF(MS, CreateDT, next_CreateDT) < 1000
ORDER BY ShipmentID, CreateDT
Good question. Looking for rows where the Carrier rows' time is less than 1 second from the Rated time.
The Carrier row has 2022-07-21 10:30:05.736 is within 1 second
of the maximum Rated time which is 2022-07-21 10:30:05.666.
Ideally, I need to only return the row for Carrier in this scenario.
Thanks.
Hmm, that's a different requirement than what you originally stated:
"In the sample data below, if the ShipmentID is the same AND AuditTitle IN('Rated', 'Carrier') AND DateDiff for CreateDT is less than 1 second ........ select those rows"
@ScottPletcher This looks to be very close to, if not exactly, what I need. It returns 2 rows for each Shipment - 1 for Carrier and 1 for Rated where the time diff is less than 1 second.
Thanks very much.
You might want to think about finding the DATEDIFF in milliseconds, rather than seconds, as DATEDIFF counts boundaries, not the actual difference in time intervals. Run the following a few times, you'll see a 1 returned:
select datediff(second,getdate(),dateadd(ms,450,getdate()))
If you are specifically asking for rows created within 1000 milliseconds of each other, then you don't want to use DATEDIFF(second).
Per the suggestion by robert, I've changed to MS from SECOND.
Thanks @robert_volk and @ScottPletcher. I'm using the revised code.