snipt

Ctrl+h for KB shortcuts

Text only

Consulta para hacer el resumen general por mes

SELECT
        (
         select co.name as padre
         from company co
         where  co.id = cc.parent_id
         ) as outsorce,
       c.name as empresa,
       #opp.name as Grupo_Nomina,
       count(distinct ppmc.condition_id) as colaboradores
FROM paysheet_paysheets ppsc
	INNER JOIN company c ON c.id = ppsc.company_id
  INNER JOIN os_payment_period opp ON  opp.company_id =  c.id and opp.id = ppsc.payment_period_id
	INNER JOIN paysheet_payments ppmc ON ppmc.paysheet_id = ppsc.id AND ppmc.`status` & 64 = 0  AND (ppsc.`status` & 32 > 0 OR ppsc.status & 4 >0)
  INNER JOIN user_condition uc ON uc.id = ppmc.condition_id
  LEFT JOIN company_company cc ON cc.child_id = c.id
  WHERE ppsc.company_id = c.id
  AND ppsc.second_day BETWEEN 'fecha-inicio 00:00:00' AND 'fecha-fin 23:59:59'
	AND ppsc.calculated_at is not null
  AND c.status = 2
  AND c.name NOT LIKE "%demo%" AND c.name NOT LIKE "%ejemplo%" AND c.name NOT LIKE "%winik%"
  AND c.name NOT LIKE "%alanach%" AND c.name NOT LIKE "%catchondo%" AND c.name NOT LIKE "%test%"
  AND c.name NOT LIKE "%abcdef%" AND c.name NOT LIKE "%onboarding%" AND c.name NOT LIKE "%jaime%"
  AND c.name NOT LIKE "%prueba%" AND c.name NOT LIKE "%demo%" AND c.name NOT LIKE "%mau%"  AND c.name != ""
  GROUP BY c.id
  ORDER BY outsorce ASC, empresa;
https://snipt.net/embed/14636a179ce988750e11f834cca1b200/
/raw/14636a179ce988750e11f834cca1b200/
14636a179ce988750e11f834cca1b200
text
Text only
25
2019-06-25T06:23:26
True
False
False
Jun 04, 2019 at 10:22 AM
/api/public/snipt/148456/
consulta-para-hacer-el-resumen-general-por-mes
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>SELECT </span><span id="L-2"><a name="L-2"></a> ( </span><span id="L-3"><a name="L-3"></a> select co.name as padre </span><span id="L-4"><a name="L-4"></a> from company co </span><span id="L-5"><a name="L-5"></a> where co.id = cc.parent_id </span><span id="L-6"><a name="L-6"></a> ) as outsorce, </span><span id="L-7"><a name="L-7"></a> c.name as empresa, </span><span id="L-8"><a name="L-8"></a> #opp.name as Grupo_Nomina, </span><span id="L-9"><a name="L-9"></a> count(distinct ppmc.condition_id) as colaboradores </span><span id="L-10"><a name="L-10"></a>FROM paysheet_paysheets ppsc </span><span id="L-11"><a name="L-11"></a> INNER JOIN company c ON c.id = ppsc.company_id </span><span id="L-12"><a name="L-12"></a> INNER JOIN os_payment_period opp ON opp.company_id = c.id and opp.id = ppsc.payment_period_id </span><span id="L-13"><a name="L-13"></a> INNER JOIN paysheet_payments ppmc ON ppmc.paysheet_id = ppsc.id AND ppmc.`status` &amp; 64 = 0 AND (ppsc.`status` &amp; 32 &gt; 0 OR ppsc.status &amp; 4 &gt;0) </span><span id="L-14"><a name="L-14"></a> INNER JOIN user_condition uc ON uc.id = ppmc.condition_id </span><span id="L-15"><a name="L-15"></a> LEFT JOIN company_company cc ON cc.child_id = c.id </span><span id="L-16"><a name="L-16"></a> WHERE ppsc.company_id = c.id </span><span id="L-17"><a name="L-17"></a> AND ppsc.second_day BETWEEN &#39;fecha-inicio 00:00:00&#39; AND &#39;fecha-fin 23:59:59&#39; </span><span id="L-18"><a name="L-18"></a> AND ppsc.calculated_at is not null </span><span id="L-19"><a name="L-19"></a> AND c.status = 2 </span><span id="L-20"><a name="L-20"></a> AND c.name NOT LIKE &quot;%demo%&quot; AND c.name NOT LIKE &quot;%ejemplo%&quot; AND c.name NOT LIKE &quot;%winik%&quot; </span><span id="L-21"><a name="L-21"></a> AND c.name NOT LIKE &quot;%alanach%&quot; AND c.name NOT LIKE &quot;%catchondo%&quot; AND c.name NOT LIKE &quot;%test%&quot; </span><span id="L-22"><a name="L-22"></a> AND c.name NOT LIKE &quot;%abcdef%&quot; AND c.name NOT LIKE &quot;%onboarding%&quot; AND c.name NOT LIKE &quot;%jaime%&quot; </span><span id="L-23"><a name="L-23"></a> AND c.name NOT LIKE &quot;%prueba%&quot; AND c.name NOT LIKE &quot;%demo%&quot; AND c.name NOT LIKE &quot;%mau%&quot; AND c.name != &quot;&quot; </span><span id="L-24"><a name="L-24"></a> GROUP BY c.id </span><span id="L-25"><a name="L-25"></a> ORDER BY outsorce ASC, empresa; </span></pre></div> </td></tr></table>
--- 
+++ 
@@ -0,0 +1,25 @@
+SELECT
+        (
+         select co.name as padre
+         from company co
+         where  co.id = cc.parent_id
+         ) as outsorce,
+       c.name as empresa,
+       #opp.name as Grupo_Nomina,
+       count(distinct ppmc.condition_id) as colaboradores
+FROM paysheet_paysheets ppsc
+	INNER JOIN company c ON c.id = ppsc.company_id
+  INNER JOIN os_payment_period opp ON  opp.company_id =  c.id and opp.id = ppsc.payment_period_id
+	INNER JOIN paysheet_payments ppmc ON ppmc.paysheet_id = ppsc.id AND ppmc.`status` & 64 = 0  AND (ppsc.`status` & 32 > 0 OR ppsc.status & 4 >0)
+  INNER JOIN user_condition uc ON uc.id = ppmc.condition_id
+  LEFT JOIN company_company cc ON cc.child_id = c.id
+  WHERE ppsc.company_id = c.id
+  AND ppsc.second_day BETWEEN 'fecha-inicio 00:00:00' AND 'fecha-fin 23:59:59'
+	AND ppsc.calculated_at is not null
+  AND c.status = 2
+  AND c.name NOT LIKE "%demo%" AND c.name NOT LIKE "%ejemplo%" AND c.name NOT LIKE "%winik%"
+  AND c.name NOT LIKE "%alanach%" AND c.name NOT LIKE "%catchondo%" AND c.name NOT LIKE "%test%"
+  AND c.name NOT LIKE "%abcdef%" AND c.name NOT LIKE "%onboarding%" AND c.name NOT LIKE "%jaime%"
+  AND c.name NOT LIKE "%prueba%" AND c.name NOT LIKE "%demo%" AND c.name NOT LIKE "%mau%"  AND c.name != ""
+  GROUP BY c.id
+  ORDER BY outsorce ASC, empresa;