View creation with conditions

• team.bernard

  Old Hand

  Points: 300

  More actions

  September 28, 2017 at 1:36 pm

  #402544

  Hello,

  I have an application that create some meetings inside a tool and some freelancers will do the job.
  Each meeting is linked to an idMeeting that the client send to the tool.

  They send a different idMeeting (the id is different, but many have the same dates) by a web service and I execute some C# code that logs some lines.
  The idMeeting is for one time of 8h-12h or 12h-16h or 16h-20h.

  To create one, 3 SQL lines and a specific structure is needed.
  To update some infos, 2 SQL lines and a specific structure is needed as well, but different.

  The structure is :
  -A table with infos about Begin and End dates about each idMeeting
  -A bigger table with all logs of creation and update

  I need a view to say like for each idMeeting present in the logs table, if it’s linked to a Meeting
  If you use the script, I want a result like :
  
  I have this script to describe my issue :
  
Create table logs
(
logid int,
idMeeting varchar(20) NOT NULL,
method_name varchar(20),
filter varchar(20),
beginTransactionDate varchar(2) NULL,
endTransactionDate varchar(2) NULL,
parentlogid int NULL
);
 
--Worst case
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate) VALUES (1,'0001','confirmMeeting','B1');
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (2,'0006','confirmMeeting','B2','E2');
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (3,'0005','confirmMeeting','B3','E3');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,parentlogid) VALUES (4,'0005','confirmMeeting','B4',3);
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (5,'0004','confirmMeeting','B5','E5');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (6,'0004','confirmMeeting','B6','E6',5);
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (7,'0003','confirmMeeting','B7','E7');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (8,'0003','confirmMeeting','B8','E8',7);
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,parentlogid) VALUES (9,'0003','confirmMeeting','B9',8);
 
--Best case
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (10,'0002','confirmMeeting','B10','E10');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (11,'0002','confirmMeeting','B11','E11',10);
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (12,'0002','confirmMeeting','B12','E12',11);
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate) VALUES (13,'0007','updateMeeting','Success','B13','E13');
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (14,'0007','updateMeeting','Success','B14','E14',13);
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (15,'0008','updateMeeting','Bad','B15','E15',14);
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (16,'0008','updateMeeting','Bad','B16','E16',15);
--Another case for another meeting with Bad at the first try
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate) VALUES (17,'0009','updateMeeting','Bad','B17','E17');
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (18,'0009','updateMeeting','Bad','B18','E18',17);
 
Create Table TimeStampMeeting
(id varchar(20),
BeginDate varchar(20),
EndDate varchar(20)
);
 
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0001','01/10 8','01/10 12');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0002','01/10 12','01/10 16');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0003','01/10 16','01/10 20');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0004','02/10 8','02/10 12');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0005','02/10 12','02/10 16');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0006','02/10 16','02/10 20');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0007','03/10 8','03/10 12');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0008','03/10 12','03/10 16');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0009','03/10 16','03/10 20');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0010','01/10 12','03/10 16');

  I have this document that show what’s is correct :
  
  Use this sample with inner join :
  Select * FROM logs l1
Inner Join logs l2 on l2.parentlogid = l1.logId
--Inner Join logs l3 on l3.parentlogid = l2.logid

  The aim is to make a query like with the 0010 and do something like
  Select * FROM my view where Begin = (Select BeginDate from TimeStamp where id = ‘0010’) and End = (Select EndDate from TimeStamp where id = ‘0010’) 

  To now if the current idMeeting can be use to create a new meeting at the same date as a previous one.
  In this sample it's not possible because a meeting is already using the 01/10 12h-16h

  Thanks for your help 

  Regards

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 28, 2017 at 2:22 pm

  #1961476

  team.bernard - Thursday, September 28, 2017 1:36 PM

  Hello,

  I have an application that create some meetings inside a tool and some freelancers will do the job.
  Each meeting is linked to an idMeeting that the client send to the tool.

  They send a different idMeeting (the id is different, but many have the same dates) by a web service and I execute some C# code that logs some lines.
  The idMeeting is for one time of 8h-12h or 12h-16h or 16h-20h.

  To create one, 3 SQL lines and a specific structure is needed.
  To update some infos, 2 SQL lines and a specific structure is needed as well, but different.

  The structure is :
  -A table with infos about Begin and End dates about each idMeeting
  -A bigger table with all logs of creation and update

  I need a view to say like for each idMeeting present in the logs table, if it’s linked to a Meeting
  If you use the script, I want a result like :
  
  I have this script to describe my issue :
  
Create table logs
(
logid int,
idMeeting varchar(20) NOT NULL,
method_name varchar(20),
filter varchar(20),
beginTransactionDate varchar(2) NULL,
endTransactionDate varchar(2) NULL,
parentlogid int NULL
);
 
--Worst case
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate) VALUES (1,'0001','confirmMeeting','B1');
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (2,'0006','confirmMeeting','B2','E2');
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (3,'0005','confirmMeeting','B3','E3');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,parentlogid) VALUES (4,'0005','confirmMeeting','B4',3);
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (5,'0004','confirmMeeting','B5','E5');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (6,'0004','confirmMeeting','B6','E6',5);
 
--No meeting created
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (7,'0003','confirmMeeting','B7','E7');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (8,'0003','confirmMeeting','B8','E8',7);
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,parentlogid) VALUES (9,'0003','confirmMeeting','B9',8);
 
--Best case
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate) VALUES (10,'0002','confirmMeeting','B10','E10');
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (11,'0002','confirmMeeting','B11','E11',10);
INSERT INTO logs (logid,idMeeting,method_name,beginTransactionDate,endTransactionDate,parentlogid) VALUES (12,'0002','confirmMeeting','B12','E12',11);
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate) VALUES (13,'0007','updateMeeting','Success','B13','E13');
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (14,'0007','updateMeeting','Success','B14','E14',13);
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (15,'0008','updateMeeting','Bad','B15','E15',14);
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (16,'0008','updateMeeting','Bad','B16','E16',15);
--Another case for another meeting with Bad at the first try
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate) VALUES (17,'0009','updateMeeting','Bad','B17','E17');
 
INSERT INTO logs (logid,idMeeting,method_name,filter,beginTransactionDate,endTransactionDate,parentlogid) VALUES (18,'0009','updateMeeting','Bad','B18','E18',17);
 
Create Table TimeStampMeeting
(id varchar(20),
BeginDate varchar(20),
EndDate varchar(20)
);
 
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0001','01/10 8','01/10 12');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0002','01/10 12','01/10 16');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0003','01/10 16','01/10 20');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0004','02/10 8','02/10 12');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0005','02/10 12','02/10 16');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0006','02/10 16','02/10 20');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0007','03/10 8','03/10 12');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0008','03/10 12','03/10 16');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0009','03/10 16','03/10 20');
INSERT INTO TimeStampMeeting(id, BeginDate, EndDate) VALUES ('0010','01/10 12','03/10 16');

  I have this document that show what’s is correct :
  
  Use this sample with inner join :
  Select * FROM logs l1
Inner Join logs l2 on l2.parentlogid = l1.logId
--Inner Join logs l3 on l3.parentlogid = l2.logid

  The aim is to make a query like with the 0010 and do something like
  Select * FROM my view where Begin = (Select BeginDate from TimeStamp where id = ‘0010’) and End = (Select EndDate from TimeStamp where id = ‘0010’) 

  To now if the current idMeeting can be use to create a new meeting at the same date as a previous one.
  In this sample it's not possible because a meeting is already using the 01/10 12h-16h

  Thanks for your help 

  Regards

  I don't mean to be rude, but your data structure is just not going to do a very good job of supporting your objective.  If I understand you correctly, you want to be able to track up to 3 meetings on any given day, which occur between 8am and noon, noon and 4pm, or 4pm and 8pm.   You're going to want a calendar table hanging around so that you can impose things like holidays and weekends and still maintain data integrity.   Knowing whether a meeting can be accepted is a matter of having all possible meeting dates in your table in advance, with a field to indicate whether or not that meeting is spoken for, and by whom.  Don't assume you'll always only have one source.   Nowhere near enough thinking has gone into the data design for this.   You also should be using some form of date, time, or datetime data types for the columns indicating what date and time the meetings are.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• team.bernard

  Old Hand

  Points: 300

  More actions

  September 28, 2017 at 2:25 pm

  #1961477

  Hello,

  I forgot to mention that varchar for dates are datetime in my production environment.
  My database structure can't change because it's use in production and receive 1000 meetings a day.

  Regards

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 29, 2017 at 7:03 am

  #1961619

  team.bernard - Thursday, September 28, 2017 2:25 PM

  Hello,

  I forgot to mention that varchar for dates are datetime in my production environment.
  My database structure can't change because it's use in production and receive 1000 meetings a day.

  Regards

  That's nice, but your code is going to either truncate your beginTransactionDate and endTransactionDate values because the INSERT statement provide 3 characters for each of them, and they're set up as varchar(2).   Clearly, you'd need some kind of translation between those values and actual dates for this to work at all, and you've not provided that information, so I'm not particularly inclined to make an attempt, as actual datetime values are no more storage than the 3 bytes of varchar you'd need to handle the data you provided.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)