May 30, 2019 at 12:00 am
Hi Guys,
I need urgent help. I am working on a SQL query. Below is the sample data.
id B_id Station Client B_Date B_time B_rate
1 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:14:00.000 1150.00
2 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:41:00.000 1150.00
3 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:55:30.000 20.00
4 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:28:06.000 1150.00
5 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:38:06.000 20.00
6 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:48:06.000 20.00
7 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:58:06.000 60.00
8 2030823 BRAVO CASL 2019-05-28 1900-01-01 09:15:30.000 1150.00
9 2030823 BRAVO CASL 2019-06-02 1900-01-01 08:14:00.000 2600.00
10 2030823 BRAVO CASL 2019-06-02 1900-01-01 08:44:00.000 20.00
First step:- I want to group by B_id/Station/Client and B_date
Second Step:- Then I want to compare each B_time if It is 30m different then Sum(b_rate) and total count.
Here is the first example
1 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:14:00.000 1150.00
2 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:41:00.000 1150.00
3 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:55:30.000 20.00
Compare Time:- 08:14 == 08:41 = Yes
08:14 == 08:55 = No
08:41 == 08:14 = Yes
08:41 == 08:55 = Yes
08:55 == 08:14 = No
08:55 == 08:41 = NO (BECAUSE WE already compare this one so it should be NO)
Please help me out to how I accomplished this one.
Create table #tbl_Main(id int identity(1,1),B_id int,Station varchar(20),Client varchar(20),B_Date Date,B_time Datetime,B_rate Decimal(10,2))Insert into #tbl_Main (B_id,Station,Client,B_Date,B_time,B_rate)
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:55:30.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:41:00.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:14:00.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:58:06.000','60.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:48:06.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:38:06.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:28:06.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 09:15:30.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-06-02 00:00:00.000','1900-01-01 08:44:00.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-06-02 00:00:00.000','1900-01-01 08:14:00.000','2600.00'
Select * from #tbl_Main
May 30, 2019 at 2:36 am
Second Step:- Then I want to compare each B_time if It is 30m different then Sum(b_rate) and total count.?
30m different from what? the previous record? Use LAG() for that. Then just use DATEDIFF() Then maybe use CASE WHEN to do the IF part.
May 31, 2019 at 7:30 pm
Have you considered using a relational database, and following basic data modeling rules?
You are using SQL to write a 1960’s magnetic tape file! There is no such crap as an IDENTITY table property in RDBMS. This is an old Sybase count of physical record insertions and not part of a logical data model. People who use it are called “ID-iots” among RDBMS people. We use keys, not record counts in RDBMS.
The affix “tbl_” is a design flaw called a “tibble” and you can read some old Phil Factor columns about it. Identifiers are never numerics because they are a nominal scale. We named tapes “Main” or “master”; tables are sets, so they have real names not meta-data descriptions of their use. You can never have a key if all the columns are NULL-able. Splitting time and date from a timestamp is a design flaw called attribute splitting; this is a single data element. Why did you fail to normalize the schema? Why did you use the old Sybase insertion statements? I think you have done nothing right. Here is a wild guess at correcting this
CREATE TABLE Clients --- normalize!
(client_id CHAR(7) NOT NULL CHECK (client_id LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9]’) -- get a key!
PRIMARY KEY,
station_name VARCHAR(20) NOT NULL,
client_name VARCHAR(20) NOT NULL);
INSERT INTO Clients VALUES ('2030823', 'BRAVO', 'CASL' );
CREATE TABLE Tickets --- why is "B" a good meanful name to you?
(client_id CHAR(7) NOT NULL REFERENCES Clients(client_id),
ticket_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (client_id, ticket_timestamp),
ticket_rate DECIMAL(10, 2) NOT NULL CHECK (ticket_rate >= 0.00));
INSERT INTO Tickets(client_id, station_name, client_name, ticket_timestamp, ticket_rate)
VALUES
('2030823', '2019-05-27 08:55:30' 20.00),
('2030823', '2019-05-27 08:41:00' 1150.00),
('2030823', '2019-05-27 08:14:00' 1150.00),
('2030823', '2019-05-28 08:58:06' 60.00),
('2030823', '2019-05-28 08:48:06' 20.00),
('2030823', '2019-05-28 08:38:06' 20.00),
('2030823', '2019-05-28 08:28:06' 1150.00),
('2030823', '2019-05-28 09:15:30' 1150.00),
('2030823', '2019-06-02 08:44:00' 20.00),
('2030823', '2019-06-02 08:14:00' 2600.00);
Your query can be asked with a LAG() function and DATEDIFF().
Please post DDL and follow ANSI/ISO standards when asking for help.
June 5, 2019 at 7:10 pm
Well,
Leave it to Mr. Celko to be a jerk about this. Admittedly, there are some problems with this setup, and the performance isn't going to be great. Also, it's not totally clear just exactly what values you need to sum, and given that you are having to compare every row in each group, a traditional sum is problematic. I have a starter query that can at least identify the comparable data and serve as a starting point for an eventual solution.
USE master;
GO
CREATE TABLE #tbl_Main (
id int identity(1,1),
B_id int,
Station varchar(20),
Client varchar(20),
B_Date Date,
B_time Datetime,
B_rate Decimal(10,2)
);
INSERT INTO #tbl_Main (B_id, Station, Client, B_Date, B_time, B_rate)
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:55:30.000','20.00' Union
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:41:00.000','1150.00' Union
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:14:00.000','1150.00' Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:58:06.000','60.00' Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:48:06.000','20.00' Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:38:06.000','20.00' Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:28:06.000','1150.00' Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 09:15:30.000','1150.00' Union
Select '2030823','BRAVO','CASL','2019-06-02 00:00:00.000','1900-01-01 08:44:00.000','20.00' Union
Select '2030823','BRAVO','CASL','2019-06-02 00:00:00.000','1900-01-01 08:14:00.000','2600.00';
WITH RAW_DATA AS (
SELECT
TM.B_id,
TM.Station,
TM.Client,
TM.B_Date,
CONVERT(time, TM.B_time) AS B_time,
TM.B_rate,
ROW_NUMBER() OVER(PARTITION BY TM.B_id, TM.Station, TM.Client, TM.B_Date ORDER BY TM.B_time) AS RowNum,
COUNT(TM.id) OVER(PARTITION BY TM.B_id, TM.Station, TM.Client, TM.B_Date) AS GROUP_COUNT
FROM #tbl_Main AS TM
)
SELECT
RD.B_id,
RD.Station,
RD.Client,
RD.GROUP_COUNT,
RD.RowNum,
RD.B_Date,
RD.B_time,
RD.B_rate,
NSRD.RowNum AS Compare_RowNum,
NSRD.B_Date AS Compare_B_Date,
NSRD.B_time AS Compare_B_time,
NSRD.B_rate AS Compare_B_rate,
CASE
WHEN DATEDIFF(minute, RD.B_time, NSRD.B_time) <= 30 THEN 1
ELSE 0
END AS WITHIN_30_MINS
FROM RAW_DATA AS RD
OUTER APPLY (
SELECT RD2.*
FROM RAW_DATA AS RD2
WHERERD2.B_id = RD.B_id
AND RD2.Station = RD.Station
AND RD2.Client = RD.Client
AND RD2.B_Date = RD.B_Date
AND RD2.RowNum <> RD.RowNum
) AS NSRD
WHERE NOT EXISTS (
SELECT 1
FROM RAW_DATA AS RDA
INNER JOIN (
SELECT RD3.*
FROM RAW_DATA AS RD3
) AS NSRD2
ON RDA.B_id = NSRD2.B_id
AND RDA.Station = NSRD2.Station
AND RDA.Client = NSRD2.Client
AND RDA.B_Date = NSRD2.B_Date
AND RDA.RowNum > NSRD.RowNum
WHERERDA.B_id = RD.B_id
AND RDA.Station = RD.Station
AND RDA.Client = RD.Client
AND RDA.B_Date = RD.B_Date
AND RDA.RowNum = RD.RowNum
)
ORDER BY
RD.B_id,
RD.Station,
RD.Client,
RD.B_Date,
RD.RowNum,
NSRD.RowNum;
DROP TABLE #tbl_Main;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply