September 28, 2022 at 5:07 am
Good Morning,
I have the two tables customers (joined) and their orders (dateoforder- record perday)
CREATE TABLE Customers (
CID int NOT NULL PRIMARY KEY,
GroupName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CustomerStatus SMALLINT, ---1 Means Active, 0 Inactive
CustomerCancelDate Date,
DateofJoin DATE
);
INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,NULL,'01/14/2022')
INSERT INTO Customers Values(101, 'ABC123', 'King',40,0,'02/19/2022','01/19/2022')
INSERT INTO Customers Values(102, 'XYZ456', 'SARA',55,1,'01/28/2022','01/21/2022')
INSERT INTO Customers Values(120, 'XYZ123', 'Jim',45,0,'02/28/2022','01/14/2022')
INSERT INTO Customers Values(121, 'XYZ123', 'Kim',21,1,NULL,'01/31/2022')
INSERT INTO Customers Values(103, 'ABC123', 'Diva',23,1,NULL,'09/04/2022')
INSERT INTO Customers Values(104, 'DEF123', 'Klip',19,1,NULL,'02/17/2022')
INSERT INTO Customers Values(105, 'ABC456', 'BobE',45,1,NULL,'02/27/2022')
INSERT INTO Customers Values(106, 'ABC123', 'MIla',20,1,NULL,'02/16/2022')
INSERT INTO Customers Values(107, 'DEF123', 'Yore',19,1,NULL,'02/19/2022')
INSERT INTO Customers Values(131, 'ABC123', 'Soni',23,1,NULL,'02/04/2022')
INSERT INTO Customers Values(132, 'ABC123', 'Mar',27,0,'02/16/2022','02/16/2022')
INSERT INTO Customers Values(133, 'ABC123', 'Pik',29,1,NULL,'02/11/2022')
INSERT INTO Customers Values(134, 'QQQ123', 'Ygi',33,1,NULL,'02/24/2022')
INSERT INTO Customers Values(135, 'ABCQQQ', 'Bip',45,0,'03/24/2022','02/14/2022')
INSERT INTO Customers Values(108, 'ABC456', 'Hams',21,1,NULL,'03/11/2022')
INSERT INTO Customers Values(109, 'ABC123', 'Zand',33,1,NULL,'03/04/2022')
INSERT INTO Customers Values(110, 'ABC123', 'Loda',44,0,'03/19/2022','03/07/2022')
INSERT INTO Customers Values(111, 'DEF456', 'Mopa',19,1,NULL,'03/24/2022')
INSERT INTO Customers Values(112, 'DEF456', 'Kodi',21,0,'03/31/2022','03/29/2022')
INSERT INTO Customers Values(136, 'DEF456', 'Rav',31,0,'04/31/2022','03/29/2022')
The other table is Orders
CREATE TABLE Orders (
CID int NOT NULL,
DateVal Date,
OrderIndicator SMALLINT, ---1 Means Active, 0 Inactive
NumberofOrders int,
TotalOrderCost decimal (10,2),
loggedin_indicator SMALLINT ---1 means actives /yes , 0 means non active or no
);
INSERT INTO Orders Values(101,'02/01/2022',0,0,0 ,1)
INSERT INTO Orders Values(101,'02/02/2022',1,2,5 ,1)
INSERT INTO Orders Values(101,'02/03/2022',1,1,25 ,1)
INSERT INTO Orders Values(101,'03/15/2022',0,0,0 ,1)
INSERT INTO Orders Values(101,'05/15/2022',1,1,100 ,1)
INSERT INTO Orders Values(101,'06/15/2022',1,2,300 ,1)
--- As per Customers table, #103 opened on Sep 4 2022.
INSERT INTO Orders Values(103,'09/14/2022',0,0,0 ,1)
INSERT INTO Orders Values(103,'09/15/2022',1,2,5 ,1)
INSERT INTO Orders Values(103,'09/16/2022',1,1,25 ,1)
INSERT INTO Orders Values(103,'09/17/2022',0,0,0 ,1)
INSERT INTO Orders Values(103,'09/18/2022',1,1,100 ,1)
INSERT INTO Orders Values(103,'09/19/2022',1,2,300 ,1)
INSERT INTO Orders Values(103,'09/20/2022',1,2,300 ,1)
INSERT INTO Orders Values(108,'03/31/2022',1,2,300 ,1)
INSERT INTO Orders Values(108,'06/20/2022',1,2,300 ,1)
INSERT INTO Orders Values(108,'06/20/2022',1,2,300 ,1)
badased on the above two tables I would like to calculate columns which is CustomerType.
the calculation is how frequently a customer is placing order (based on DateVal in Orders table) after Customer DateofJoin.
If a customer placing orders weekly 3 or more then classify customer as "Daily"
If a customer placing orders weekly 1 to 3 then classify customer as "Weekly"
If a customer placing orders Monthly 1 to 3 then classify customer as "Monthly"
If a customer placing orders quarterly 1 to 3 then classify customer as "Quarterly"
If a customer placing orders first 10days and then do nothing then "Initial Rocker"
If a customer placing orders none at all after opening then "tester"
Please help with it.
Thanks in advance
asiti
September 28, 2022 at 9:04 am
Well done on providing the sample data, though please use one or more code blocks next time. In future, please use temp tables (avoids others having to create and drop permanent tables in a test DB), INSERTS should have column lists and there's no need to repeat INSERT INTO VALUES for every row. It also helps to format your dates as 'YYYYMMDD' so that those with non-US date formats don't have to prat about with SET DATEFORMAT in order to run your code.
Would you also provide your desired output, based on the sample data? This can be as simple as a screen shot from an Excel sheet.
DROP TABLE IF EXISTS #Customers;
CREATE TABLE #Customers
(
CID INT NOT NULL PRIMARY KEY
,GroupName VARCHAR(255) NOT NULL
,FirstName VARCHAR(255)
,Age INT
,CustomerStatus SMALLINT ---1 Means Active, 0 Inactive
,CustomerCancelDate DATE
,DateofJoin DATE
);
INSERT #Customers
(
CID
,GroupName
,FirstName
,Age
,CustomerStatus
,CustomerCancelDate
,DateofJoin
)
VALUES
(100, 'ABC123', 'John', 30, 1, NULL, '01/14/2022')
,(101, 'ABC123', 'King', 40, 0, '02/19/2022', '01/19/2022')
,(102, 'XYZ456', 'SARA', 55, 1, '01/28/2022', '01/21/2022')
,(120, 'XYZ123', 'Jim', 45, 0, '02/28/2022', '01/14/2022')
,(121, 'XYZ123', 'Kim', 21, 1, NULL, '01/31/2022')
,(103, 'ABC123', 'Diva', 23, 1, NULL, '09/04/2022')
,(104, 'DEF123', 'Klip', 19, 1, NULL, '02/17/2022')
,(105, 'ABC456', 'BobE', 45, 1, NULL, '02/27/2022')
,(106, 'ABC123', 'MIla', 20, 1, NULL, '02/16/2022')
,(107, 'DEF123', 'Yore', 19, 1, NULL, '02/19/2022')
,(131, 'ABC123', 'Soni', 23, 1, NULL, '02/04/2022')
,(132, 'ABC123', 'Mar', 27, 0, '02/16/2022', '02/16/2022')
,(133, 'ABC123', 'Pik', 29, 1, NULL, '02/11/2022')
,(134, 'QQQ123', 'Ygi', 33, 1, NULL, '02/24/2022')
,(135, 'ABCQQQ', 'Bip', 45, 0, '03/24/2022', '02/14/2022')
,(108, 'ABC456', 'Hams', 21, 1, NULL, '03/11/2022')
,(109, 'ABC123', 'Zand', 33, 1, NULL, '03/04/2022')
,(110, 'ABC123', 'Loda', 44, 0, '03/19/2022', '03/07/2022')
,(111, 'DEF456', 'Mopa', 19, 1, NULL, '03/24/2022')
,(112, 'DEF456', 'Kodi', 21, 0, '03/31/2022', '03/29/2022')
,(136, 'DEF456', 'Rav', 31, 0, '04/31/2022', '03/29/2022');
DROP TABLE IF EXISTS #Orders;
CREATE TABLE #Orders
(
CID INT NOT NULL
,DateVal DATE
,OrderIndicator SMALLINT ---1 Means Active, 0 Inactive
,NumberofOrders INT
,TotalOrderCost DECIMAL(10, 2)
,loggedin_indicator SMALLINT ---1 means actives /yes , 0 means non active or no
);
INSERT #Orders
VALUES
(101, '02/01/2022', 0, 0, 0, 1)
,(101, '02/02/2022', 1, 2, 5, 1)
,(101, '02/03/2022', 1, 1, 25, 1)
,(101, '03/15/2022', 0, 0, 0, 1)
,(101, '05/15/2022', 1, 1, 100, 1)
,(101, '06/15/2022', 1, 2, 300, 1)
,(103, '09/14/2022', 0, 0, 0, 1)
,(103, '09/15/2022', 1, 2, 5, 1)
,(103, '09/16/2022', 1, 1, 25, 1)
,(103, '09/17/2022', 0, 0, 0, 1)
,(103, '09/18/2022', 1, 1, 100, 1)
,(103, '09/19/2022', 1, 2, 300, 1)
,(103, '09/20/2022', 1, 2, 300, 1)
,(108, '03/31/2022', 1, 2, 300, 1)
,(108, '06/20/2022', 1, 2, 300, 1)
,(108, '06/20/2022', 1, 2, 300, 1);
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 28, 2022 at 1:08 pm
Thank you Phil, I will follow your guidelines to help helpers easy to help me.
Thank you Again
September 28, 2022 at 9:12 pm
Here's a query with some date calculations. The value calculated in CROSS APPLY is the difference in days between the customer's DateofJoin and the order's DateVal. Based on this difference integer division is applied to get date groupings. These date groupings are based on fixed integers, 7/30/90 and don't correspond to calendar intervals. Maybe this gets part of the way to a solution. The results could be cross-tabulated (or pivoted) and aggregated by CID across columns of date intervals
DROP TABLE IF EXISTS #Customers;
go
CREATE TABLE #Customers(
CID INT NOT NULL PRIMARY KEY
,GroupName VARCHAR(255) NOT NULL
,FirstName VARCHAR(255)
,Age INT
,CustomerStatus SMALLINT ---1 Means Active, 0 Inactive
,CustomerCancelDate DATE
,DateofJoin DATE);
INSERT #Customers(
CID
,GroupName
,FirstName
,Age
,CustomerStatus
,CustomerCancelDate
,DateofJoin) VALUES
(100, 'ABC123', 'John', 30, 1, NULL, '2022-01-14')
,(101, 'ABC123', 'King', 40, 0, '2022-02-19', '2022-01-19')
,(102, 'XYZ456', 'SARA', 55, 1, '2022-01-28', '2022-01-21')
,(120, 'XYZ123', 'Jim', 45, 0, '2022-02-28', '2022-01-14')
,(121, 'XYZ123', 'Kim', 21, 1, NULL, '2022-01-31')
,(103, 'ABC123', 'Diva', 23, 1, NULL, '2022-09-04')
,(104, 'DEF123', 'Klip', 19, 1, NULL, '2022-02-17')
,(105, 'ABC456', 'BobE', 45, 1, NULL, '2022-02-27')
,(106, 'ABC123', 'MIla', 20, 1, NULL, '2022-02-16')
,(107, 'DEF123', 'Yore', 19, 1, NULL, '2022-02-19')
,(131, 'ABC123', 'Soni', 23, 1, NULL, '2022-02-04')
,(132, 'ABC123', 'Mar', 27, 0, '2022-02-16', '2022-02-16')
,(133, 'ABC123', 'Pik', 29, 1, NULL, '2022-02-11')
,(134, 'QQQ123', 'Ygi', 33, 1, NULL, '2022-02-24')
,(135, 'ABCQQQ', 'Bip', 45, 0, '2022-03-24', '2022-02-14')
,(108, 'ABC456', 'Hams', 21, 1, NULL, '2022-03-11')
,(109, 'ABC123', 'Zand', 33, 1, NULL, '2022-03-04')
,(110, 'ABC123', 'Loda', 44, 0, '2022-03-19', '2022-03-07')
,(111, 'DEF456', 'Mopa', 19, 1, NULL, '2022-03-24')
,(112, 'DEF456', 'Kodi', 21, 0, '2022-03-31', '2022-03-29');
--,(136, 'DEF456', 'Rav', 31, 0, '2022-04-31', '2022-03-29');
DROP TABLE IF EXISTS #Orders;
go
CREATE TABLE #Orders(
CID INT NOT NULL
,DateVal DATE
,OrderIndicator SMALLINT ---1 Means Active, 0 Inactive
,NumberofOrders INT
,TotalOrderCost DECIMAL(10, 2)
,loggedin_indicator SMALLINT); ---1 means actives -yes , 0 means non active or no
INSERT #Orders VALUES
(101, '2022-02-01', 0, 0, 0, 1)
,(101, '2022-02-02', 1, 2, 5, 1)
,(101, '2022-02-03', 1, 1, 25, 1)
,(101, '2022-03-15', 0, 0, 0, 1)
,(101, '2022-05-15', 1, 1, 100, 1)
,(101, '2022-06-15', 1, 2, 300, 1)
,(103, '2022-09-14', 0, 0, 0, 1)
,(103, '2022-09-15', 1, 2, 5, 1)
,(103, '2022-09-16', 1, 1, 25, 1)
,(103, '2022-09-17', 0, 0, 0, 1)
,(103, '2022-09-18', 1, 1, 100, 1)
,(103, '2022-09-19', 1, 2, 300, 1)
,(103, '2022-09-20', 1, 2, 300, 1)
,(108, '2022-03-31', 1, 2, 300, 1)
,(108, '2022-06-20', 1, 2, 300, 1)
,(108, '2022-06-20', 1, 2, 300, 1);
select c.*, o.*,
v.date_diff,
v.date_diff/7 wk_diff,
v.date_diff/30 mo_diff,
v.date_diff/90 qtr_diff
from #Customers c
join #Orders o on c.CID=o.CID
cross apply (values (datediff(day, c.DateofJoin, o.DateVal))) v(date_diff);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply