March 26, 2024 at 10:33 am
Hello, i have this table
CREATE TABLE [dbo].[Passaggi](
[Code] [varchar](6) NOT NULL,
[DataPassaggio] [datetime2](0) NOT NULL,
[idServizio] [smallint] NOT NULL,
[idRgp] [smallint] NOT NULL,
[up] [bit] NOT NULL
) ON [PRIMARY]
GO
with this data for example
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T09:27:27.0000000' AS DateTime2), 1103, 1103, 0)
GO
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T09:30:34.0000000' AS DateTime2), 1096, 1103, 0)
GO
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T09:53:40.0000000' AS DateTime2), 1103, 1103, 0)
GO
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T10:59:52.0000000' AS DateTime2), 1100, 1100, 1)
GO
I wont to calcultate total Up and total down for day ( CAST(DataPassaggio AS Date) )
For the data inserted i wont this result
QrCode DataPassaggio ups downs
kzya9m 2023-04-05 1 2
Now the complex part lies in the fact that for the descents I only consider 2 as I don't have to consider the 09:30:34 passage as there is already an idRgp 1103 present
Instead, I consider the case of the transition to the service (idService) 1103 twice
for this type of data instead
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T09:27:27.0000000' AS DateTime2), 1033, 1103, 0)
GO
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T09:30:34.0000000' AS DateTime2), 1096, 1103, 0)
GO
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T09:53:40.0000000' AS DateTime2), 1103, 1103, 0)
GO
INSERT [dbo].[Passaggi] ([Code], [DataPassaggio], [idServizio], [idRgp], [up]) VALUES (N'kzya9m', CAST(N'2023-04-05T10:59:52.0000000' AS DateTime2), 1100, 1100, 1)
GO
the result would be
QrCode DataPassaggio ups downs
kzya9m 2023-04-05 1 1
Do you know how I can do this without using a cursor?
March 26, 2024 at 11:43 am
I've made the test data generation a little more user-friendly (and changed the 'Code' for the second set of data to differentiate from the first)
DROP TABLE IF EXISTS #Passaggi;
CREATE TABLE #Passaggi
(
Code VARCHAR(6) NOT NULL
,DataPassaggio DATETIME2(0) NOT NULL
,idServizio SMALLINT NOT NULL
,idRgp SMALLINT NOT NULL
,up BIT NOT NULL
);
INSERT #Passaggi
(
Code
,DataPassaggio
,idServizio
,idRgp
,up
)
VALUES
(N'kzya9m', '2023-04-05T09:27:27', 1103, 1103, 0)
,(N'kzya9m', '2023-04-05T09:30:34', 1096, 1103, 0)
,(N'kzya9m', '2023-04-05T09:53:40', 1103, 1103, 0)
,(N'kzya9m', '2023-04-05T10:59:52', 1100, 1100, 1)
,(N'zzya9m', '2023-04-05T09:27:27', 1033, 1103, 0)
,(N'zzya9m', '2023-04-05T09:30:34', 1096, 1103, 0)
,(N'zzya9m', '2023-04-05T09:53:40', 1103, 1103, 0)
,(N'zzya9m', '2023-04-05T10:59:52', 1100, 1100, 1);
SELECT *
FROM #Passaggi p;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 26, 2024 at 3:21 pm
Maybe:
WITH Lags
AS
(
SELECT P.Code, X.DataPassaggio, P.idServizio, P.up
,LAG(idServizio) OVER (PARTITION BY P.Code, X.DataPassaggio ORDER BY P.DataPassaggio) AS PrevidServizio
FROM #Passaggi P
CROSS APPLY (VALUES (DATEADD(day, DATEDIFF(day, '1900', P.DataPassaggio), '1900')) ) X(DataPassaggio)
)
SELECT Code, CAST(DataPassaggio AS date) AS DataPassaggio
,SUM(IIF(up = 0, 0, 1)) AS Ups
,SUM(IIF(up = 0, 1, 0)) AS Downs
FROM Lags
WHERE up = 1
OR (idServizio = 1103 AND COALESCE(PrevidServizio, -1) <> 1103)
GROUP BY Code, DataPassaggio;
If this is not what you want, you will need to post better test data.
March 26, 2024 at 4:34 pm
The problem is that you haven't defined "total up" and "total down".
Is it a change on the field "up"? If so, your data is incomplete. A value can be up, down, or the same as a previous value. You cannot use a two-valued field to express three distinct values.
Is it a change in the field idServizio? If so, why do you want to exclude 09:30:34 because the previous record has the same idRgp, but include 09:53:40 despite the previous record meeting the exact same conditions?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 27, 2024 at 7:53 am
Thank you all for your help, and please excuse my English. I'll try to explain the logic with the attached image and other example
INSERT #Passaggi
(
Code
,DataPassaggio
,idServizio
,idRgp
,up
)
VALUES
(N'aabbcc', '2023-04-07 08:00:00', 1096, 1103, 0)
,(N'aabbcc', '2023-04-07 08:01:00', 1096, 1103, 0)
,(N'aabbcc', '2023-04-07 08:02:00', 1096, 1103, 0)
,(N'aabbcc', '2023-04-07 08:03:00', 1096, 1103, 0)
,(N'aabbcc', '2023-04-07 11:36:27', 1100, 1100, 1)
,(N'aabbcc', '2023-04-07 11:40:15', 1098, 1098, 1)
,(N'aabbcc', '2023-04-07 11:40:57', 1109, 1109, 0)
,(N'aabbcc', '2023-04-07 11:41:08', 1106, 1106, 0)
,(N'aabbcc', '2023-04-07 16:25:00', 1096, 1103, 0)
,(N'aabbcc', '2023-04-07 17:00:00', 1096, 1103, 0);
-- for this example , 2 ups, and 8 down
March 27, 2024 at 10:49 am
There must be a more elegant way of doing this, but I ran out of time. This appears to work with the existing data:
WITH filtered
AS (SELECT p1.Code
,DataPassaggio = CAST (p1.DataPassaggio AS DATE)
,p1.idServizio
,p1.idRgp
,p1.up
FROM #Passaggi p1
WHERE NOT EXISTS
(
SELECT 1
FROM #Passaggi p2
WHERE p1.Code = p2.Code
AND p1.DataPassaggio > p2.DataPassaggio
AND p1.idServizio <> p2.idServizio
AND p1.idRgp = p2.idRgp
AND p1.up = p2.up
)
OR EXISTS
(
SELECT 1
FROM #Passaggi p2
WHERE p1.Code = p2.Code
AND p1.DataPassaggio > p2.DataPassaggio
AND p1.idServizio = p2.idServizio
AND p1.idRgp = p2.idRgp
AND p1.up = p2.up
))
SELECT f.Code
,f.DataPassaggio
,ups = COUNT ( CASE f.up
WHEN 1 THEN
1
ELSE
NULL
END
)
,downs = COUNT ( CASE f.up
WHEN 0 THEN
1
ELSE
NULL
END
)
FROM filtered f
GROUP BY f.Code
,f.DataPassaggio;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 28, 2024 at 4:08 pm
Wow! on the example data it seems to work, now I'll try on many other data that I have here locally.
Thanks anyway, nice work
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply