CODEWITHSUNDEEP
sqldatabasedata-analysis
G. Dan
G. Dan

Reputation: 93

Compare month over month data and find difference in SQL

How do I dynamically calculate month over month difference from same table.

I have below table and I am trying to Compare FT(Full Time Employee) and TE(Total Employee) for each month (monthly comparison 01 to 12) and find difference. If FT exceeds TE for that month then display ID,Company,FT, and TE else pass. Can anyone help me with this query.

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
th, td {
  padding: 5px;
  text-align: left;    
}
</style>
</head>
<body>

<p>FT = Full Time Employee <br>
TE = Total Employee <br>
01-12 = month
</p>

<table style="width:100%">
  <tr>
    <th>ID</th>
    <th>Company</th>
    <th>FT01</th>
    <th>FT02</th>
    <th>FT03</th>
    <th>FT04</th>
    <th>FT05</th>
    <th>FT06</th>
    <th>FT07</th>
    <th>FT08</th>
    <th>FT09</th>
    <th>FT10</th>
    <th>FT11</th>
    <th>FT12</th>
    <th>TE01</th>
    <th>TE02</th>
    <th>TE03</th>
    <th>TE04</th>
    <th>TE05</th>
   <th>TE06</th>
    <th>TE07</th>
   <th>TE08</th>
   <th>TE09</th>
   <th>TE10</th>
    <th>TE11</th>
    <th>TE12</th>
    
  </tr>
  <tr>
    <td>100</td>
    <td>A</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>3</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
    <td>4</td>
  </tr>
    <tr>
    <td>101</td>
    <td>B</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
  </tr>
      <tr>
    <td>102</td>
    <td>C</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>7</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
    <td>6</td>
  </tr>
  
</table>

</body>
</html>

enter image description here

Upvotes: 0

Views: 624

Answers (1)

Kevin
Kevin

Reputation: 2243

Break the problem into parts!

This question is actually several "How do I?"'s rolled into one, to where the question almost amounts to a "Write this code for me" request.

So, instead, I'm going to help out with the first problem: the data structure.

You've got a non-normalized table, and while restructuring the database would help out, sometimes it's not possible to do that. But if you could get the data into something normalized, it'd make the query a lot easier.

So let's do that!

The problem would be much easier if the table looked like:

ID
Company
MonthNum
TotalEmployees
FullTimeEmployees

... so let's write a query that gets us the data in that format:

select ID, Company, 1 as MonthNum, TE01 as TotalEmployees, FT01 as FullTimeEmployees from ...
UNION ALL
select ID, Company, 2 as MonthNum, TE02 as TotalEmployees, FT02 as FullTimeEmployees from ...
UNION ALL
select ID, Company, 3 as MonthNum, TE03 as TotalEmployees, FT03 as FullTimeEmployees from ...
-- etc, going up to 12.

Now we can perform queries against that data by using it as a subquery:

select WhateverColumns from
(
   -- that query from the previous section
) as normalizedData
where WhateverConditionsYouWant

Make sense? Break the problem down into concrete steps, and then start solving the steps. The first one is to get the data in a format that's easier to work with, and I showed how to solve that. Now you need to continue writing the query to get the specific data you're after. Best of luck!

Upvotes: 1

Related Questions