Compare 1 to 1 rows in SQL

Question

Compare 1 to 1 rows in SQL

Navigation

#1 by (0 votes)

2

Good, moving forward a little more I came across another problem:

I have the following table:

Tabla1
+----+--------------------+-------------+-------+------------+
| Id | ultima_realizacion | vencimiento | tarea | id_cliente |
+----+--------------------+-------------+-------+------------+

Five tasks are loaded per client (task1, task2, ..., task5).
For each task, only 1 date must be loaded (in last_realization or in due date)

The choice of where to load is because there are:

Monthly tasks (they are loaded in ultima_realizacion)
Annual tasks (charged to expiration)

When I post in one field the other date should be yes or yes in '0000-00-00' That is, if I load a date in " ultima_realizacion ", in the field " expiration " it goes '0000-00-00' and the same If you upload a date in " expiration ", in the field " ultima_realizacion ", go '0000-00-00' . p>

However, some monthly or annual tasks may not apply to a certain client.
Does not apply means that it is not taken into account for that client.
In that case:

If it is monthly, in the field " ultima_realizacion " it goes '0001-01-01' and in " expiration " it remains > '0000-00-00'
If it is annual, in the field " expiration " it goes '0001-01-01' and in " ultima_realizacion " it goes > '0000-00-00'

When I add a new client to this table, the date '0000-00-00' is automatically put to both date fields

That is, it is as follows:

Tabla1
+----+--------------------+-------------+-------+------------+
| Id | ultima_realizacion | vencimiento | tarea | id_cliente |
+----+--------------------+-------------+-------+------------+
 1     0000-00-00           0000-00-00     1       1
 2     0000-00-00           0000-00-00     2       1
 3     0000-00-00           0000-00-00     3       1
 4     0000-00-00           0000-00-00     4       1
 5     0000-00-00           0000-00-00     5       1

If both dates " ultima_realizacion " and " expiration " are in '0000-00-00' , it means that the client was not set .
In that case you should show a message saying it was not set.

Now, if I have the following example:

Tabla1
+----+--------------------+-------------+-------+------------+
| Id | ultima_realizacion | vencimiento | tarea | id_cliente |
+----+--------------------+-------------+-------+------------+
 1     0001-01-01           0000-00-00   tarea1       1
 2     2016-08-10           0000-00-00   tarea2       1
 3     0000-00-00           0001-01-01   tarea3       1
 4     0000-01-01           0000-00-00   tarea4       1
 5     0000-00-00           2016-10-01   tarea5       1

This means:

"task1" is monthly and does not apply
"tarea2" is mensaul and the date of "ultima_realizacion" was '2016-08-10'
"tarea3" is annual and does not apply
"tarea4" is monthly and does not apply
"tarea5" is annual and the "expiration" date was '2016-10-01'

What I need is that:

Show me if the client is set or not. In the previous example, the client is set, since the condition " WHERE ultima_realizacion = 000-00-00 AND expiration = 0000-00-00 " is not fulfilled.
That the tasks that do not apply (which are 0001-01-01) do not influence me in the expiration formula that I will describe later.
Let me know if there is at least 1 task due or not

Expiry formula.
If it's an annual task, I compare today's date with the "due date"
if (expiration

mysql sql php

asked by Pepemujica 20.10.2016 в 22:18

source

1 answer

Get tweets through azure Java- Start general properties for the components

score 0 · Answer 1

Good morning, my first recommendation is that you need three tasks, if possible and because of the complexity of each task, you should do three separate procedures.

For the first part you can do something like this:

SELECT tp.id_cliente, CASE 
   WHEN EXISTS (
      SELECT 1 
      FROM Tabla1 t
      WHERE t.ultima_realizacion = '0000-00-00' 
      AND t.vencimiento = '0000-00-00' AND t.id_cliente = tp.id_cliente) 
   then 'NO SETEADO' 
   else 'SETEADO'
   end
FROM Tabla1 tp
GROUP BY tp.id_cliente
ORDER BY tp.id_cliente

For the second case:

SELECT 'Tarea Atrasada', tp.tarea, tp.id_cliente FROM Table1 tp
WHERE (tp.ultima_realizacion < GETDATE() AND tp.ultima_realizacion <> '0001-01-01') OR
(tp.vencimiento < GETDATE() AND tp.vencimiento  <> '0001-01-01')
GROUP BY tp.id_cliente, tp.tarea 
ORDER BY tp.id_cliente, tp.tarea

For the third case you can make a modification of case two, (I assume you want it per user), adding in the condition a AND and making the comparison with the desired user id.

Finally, I recommend a restructuring of this table, so you do not have to save two dates. Something quick would be to leave the table as follows:

+----+-------------+-------------+-------+------------+
| Id | vencimiento | tipo_tarea | tarea | id_cliente |
+----+-------------+-------------+-------+------------+

The tipo_tarea field is bit type. Consider the following:

If the task is monthly, the annual date is not used and vice versa.
The calculation of whether a task is late is the same for tasks, whether monthly or yearly.
Reviewing if a task is set or does not become easier.
It is more efficient to check and compare a bit, than two dates, to know if the task is annual or monthly.