Transcribing from Mysql to SQL

Asked

Viewed 68 times

0

How does this Mysql code look in SQL (in Dbeaver with Azure connection)?

I just need to change SELECT to work in SQL

CREATION OF TABLES

-- VACANCY TABLE

create table vagas (

idSensor int primary key,

idVagas varchar(5),

fkSetor int,

foreign key(fkSetor) references setor(idSetor),

descVagas varchar(20)
)auto_increment = 10;

-- MOVEMENT TABLE

create table  movimento (

idMovimento int primary key auto_increment,

movimentacao int, -- Alterei aqui pra por como int pq binary não tava rodando, não sei porquê

dataHora datetime,

estadia varchar(20),

fkSensor int, -- foreign key
foreign key (fkSensor) references vagas (idSensor)
);

-- INSERTING DATA INTO THE TABLE

-- VACANCY TABLE

insert into vagas values

(10,'A1',1, 'Coberta'),

(11,'A2',1, 'Descobertas'),

(12,'A3',1, 'PcD'),

(13,'A4',1, 'Idosos'),

(14,'B1',2, 'Coberta'),

(15,'B2',2, 'PcD'),

(16,'B3',2, 'Descobertas'),

(17,'B4',2, 'Coberta'),

(18,'G1',3, 'Descobertas'),

(19,'G2',3, 'Idosos'),

(20,'G3',3, 'PcD'),

(21,'G4',3, 'Coberta'),

(22,'F1',4, 'Idosos'),

(23,'F2',4, 'Coberta'),

(24,'F3',4, 'PcD'),

(25,'F4',4, 'Coberta'),

(26,'A5', 1, 'Idosos'), 

(27,'E1', 4, 'Idosos'),

(28,'E2', 4, 'Idosos');

-- TABLE MOTION

insert into movimento values

                            (null,1,'2020-05-02 15:03:00','15 min',24), 

                            (null,0,'2020-05-02 15:18:00','15 min',24),

                            (null,1,'2020-06-17 13:00:00','1 Hr+',11), 

                            (null,0,'2020-06-17 14:00:00','1 Hr+',11),

                            (null,1,'2020-06-17 14:10:00','45 min',11), 

                            (null,0,'2020-06-17 14:55:00','45 min',11),

                            (null,1,'2020-06-17 15:43:00','30 min',15),

                            (null,0,'2020-06-17 16:13:00','30 min',15),

                            (null,0,'2020-06-17 12:14:00','1 Hr+',13),

                            (null,1,'2020-06-17 19:43:00','1 Hr+',13), 

                            (null,1,'2020-05-02 07:03:00','15 min',12),

                            (null,0,'2020-05-02 17:18:00','15 min',12),   
 
                            (null,1,'2020-05-02 02:03:00','15 min',13), 

                            (null,0,'2020-05-02 02:18:00','15 min',13),

                            (null,1,'2020-05-02 19:03:00','15 min',12), 

                            (null,0,'2020-05-02 19:18:00','15 min',12),

                            (null,1,'2020-05-02 19:03:00','15 min',14),

                            (null,0,'2020-05-02 19:18:00','15 min',14);

-- THIS SELECT HERE Q DOES NOT RUN IN SQL

SET @total = (SELECT COUNT(estadia) FROM movimento where movimentacao = 1);

SET @totalGeral = (SELECT SUM(DISTINCT(@total)) from movimento where movimentacao = 1);   

SELECT -- Total aqui tem 10

CONCAT(TRUNCATE(((SUM(IF(TIME(dataHora) between '06:00:00' and '11:59:59', 1,0 ) and movimentacao = 1)/ @totalGeral) * 100),0),'%') as 'Manhã',

CONCAT(TRUNCATE(((SUM(IF(TIME(dataHora) between '12:00:00' and '18:59:59', 1,0 )and movimentacao = 1)/ @totalGeral) * 100),0),'%') as 'Tarde',

CONCAT(TRUNCATE(((SUM(IF(TIME(dataHora) between '19:00:00' and '23:59:59', 1,0 )and movimentacao = 1)/ @totalGeral) * 100),0),'%') as 'Noite',

CONCAT(TRUNCATE(((SUM(IF(TIME(dataHora) between '00:00:00' and '05:59:59', 1,0 )and movimentacao = 1)/ @totalGeral) * 100),0),'%') as 'Madrugada'
FROM movimento;
mysql sql-server

  • But what does your code do? It’s nice to at least give a summary so the staff can give you suggestions.

  • So, it is a parking system This code counts how many entries they had in X period and sorts them by morning, afternoon, night or dawn Only that this value is shown in percentage

  • Can put the structure of tables (CREATE TABLE) and some data so we can test different queries and suggest solutions?

  • I deleted from here and edited right in the question! I think it gets more organized! I’m sorry the question initially got very vague, it’s my first time here and I didn’t know exactly how I should ask and be clear

1 answer

0


Basically just use the function DATEPART to check what time your DATETIME and so categorize according to what you want. I made a more simplified example below because I did not understand its purpose with the variable totalGeral and with the column movimentacao.

DECLARE @total INT;

SELECT @total = COUNT(1)
  FROM movimento m;

WITH periodo AS (
  SELECT CASE
           WHEN DATEPART(HOUR, m.dataHora) BETWEEN 6 AND 11 THEN 0 -- Manhã
           WHEN DATEPART(HOUR, m.dataHora) BETWEEN 12 AND 18 THEN 1 -- Tarde
           WHEN DATEPART(HOUR, m.dataHora) BETWEEN 17 AND 23 THEN 2 -- Noite
           ELSE 3 -- Madrugada
         END AS periodo
    FROM movimento m
),
total AS (
  SELECT SUM(CASE WHEN p.periodo = 0 THEN 1 ELSE 0 END) AS manha,
         SUM(CASE WHEN p.periodo = 1 THEN 1 ELSE 0 END) AS tarde,
         SUM(CASE WHEN p.periodo = 2 THEN 1 ELSE 0 END) AS noite,
         SUM(CASE WHEN p.periodo = 3 THEN 1 ELSE 0 END) AS madrugada
    FROM periodo p
)
SELECT CAST((t.manha / @total) * 100 AS VARCHAR) + '%' as 'Manhã',
       CAST((t.tarde / @total) * 100 AS VARCHAR) + '%' as 'Tarde',
       CAST((t.noite / @total) * 100 AS VARCHAR) + '%' as 'Noite',
       CAST((t.madrugada / @total) * 100 AS VARCHAR) + '%' as 'Madrugada'
  FROM total t

  • Oh the totalGeral and the total were just to help me turn the calculation into a percentage since I didn’t know exactly how to do it! The movement is a column that indicates if the position is occupied (1) or unoccupied (0) put that only counted when it was 1 pq I just want to count the entries! I tested here and for some reason it returns me all in 0%, I changed to count only when they are found (1/entries) in the movement and agr it returns me all as 0% and only the afternoon as 100% :(

  • I got it fixed here! But I was left with a question, it only does the account there in CAST 'CAST(((t.manha / @total) * 100 AS VARCHAR) + '%' as 'Morning', ' if @total is declared there as float (I could not at all do the account with the value declared as integer) What could that be?

  • @Beatrizbarbosa falls into conversion error, you can not concatenate a VARCHAR with a numeric. If the answer answered you do not forget to mark it as correct by clicking on the V on the left side of the answer.

Browser other questions tagged mysql sql-server

You are not signed in. Login or sign up in order to post.