# Subtract data from the first day vs. the last day of the month in SQL of all months of the year

**URL:** <https://forum.inductiveautomation.com/t/subtract-data-from-the-first-day-vs-the-last-day-of-the-month-in-sql-of-all-months-of-the-year/85757>\
**Category:** Ignition\
**Tags:** ignition79, database, vision\
**Created:** [February 15, 2024, 2:45pm UTC](https://forum.inductiveautomation.com/t/subtract-data-from-the-first-day-vs-the-last-day-of-the-month-in-sql-of-all-months-of-the-year/85757 "2024-02-15T14:45:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![antonio.sanchez](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@antonio.sanchez](https://forum.inductiveautomation.com/u/antonio.sanchez)\
**Post date:** [February 15, 2024, 2:45pm UTC](https://forum.inductiveautomation.com/t/subtract-data-from-the-first-day-vs-the-last-day-of-the-month-in-sql-of-all-months-of-the-year/85757/1 "2024-02-15T14:45:47Z")

</div>

I need a query for to know the Subtract data from the first day vs. the last day of the month in SQL of all months of the year.

I used SQL Server and this is my current query

```auto
DECLARE @FechaInicial DATE = '2024-01-08';
DECLARE @FechaFinal DATE = '2024-02-10';

SELECT 
    dm.Fecha,
    SUM(CASE 
            WHEN dm.Linea = 'ELFO_006_Oficinas'
                THEN dm.Dato - ISNULL(dp.Dato, 0)
            ELSE 0
        END) AS ELFO_006_Oficinas_Diferencia,
    SUM(CASE 
            WHEN dm.Linea = 'ELFO_007_Rim'
                THEN dm.Dato - ISNULL(dp.Dato, 0)
            ELSE 0
        END) AS ELFO_007_Rim_Diferencia,
    SUM(CASE 
            WHEN dm.Linea = 'ELFO_008_ED'
                THEN dm.Dato - ISNULL(dp.Dato, 0)
            ELSE 0
        END) AS ELFO_008_ED_Diferencia,
    SUM(CASE 
            WHEN dm.Linea = 'ELFO_009_FBW'
                THEN dm.Dato - ISNULL(dp.Dato, 0)
            ELSE 0
        END) AS ELFO_009_FBW_Diferencia,
    SUM(CASE 
            WHEN dm.Linea = 'ELFO_010_Disco'
                THEN dm.Dato - ISNULL(dp.Dato, 0)
            ELSE 0
        END) AS ELFO_010_Disco_Diferencia
FROM 
    Dato_Medidor dm
LEFT JOIN 
    Dato_Medidor dp ON DATEADD(YY, - 1, dm.Fecha) = dp.Fecha
                    AND dm.Linea = dp.Linea
                    AND dp.Fecha BETWEEN DATEADD(DAY, 1, EOMONTH(@FechaInicial, -1)) AND DATEADD(DAY, 1, EOMONTH(@FechaFinal,-1))
                    AND DATEPART(HOUR, dp.Fecha) = 7
                    AND DATEPART(MINUTE, dp.Fecha) = 0
WHERE 
    dm.Fecha BETWEEN EOMONTH(@FechaInicial) AND EOMONTH(@FechaFinal)
    AND DATEPART(HOUR, dm.Fecha) = 7
    AND DATEPART(MINUTE, dm.Fecha) = 0
GROUP BY 
    dm.Fecha
ORDER BY 
    dm.Fecha

```

This is my current result:

| MONTH -- | ELFO\_010\_Disco | ELFO\_009\_FBW |
| --- | --- | --- |
| 01/01/2024 | 100 | 300 |
| 01/02/2024 | 100 | 400 |
| 01/03/2024 | 400 | 500 |

and I need this:

| MONTH | ELFO\_010\_Disco | ELFO\_009\_FBW |
| --- | --- | --- |
| JENUARY | 100 | 200 |
| February | 300 | 100 |
| March | 0 | 0 |

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 15, 2024, 2:52pm UTC](https://forum.inductiveautomation.com/t/subtract-data-from-the-first-day-vs-the-last-day-of-the-month-in-sql-of-all-months-of-the-year/85757/2 "2024-02-15T14:52:35Z")

</div>

This is a duplicate of what you asked two days ago. Keep discussions in one topic, per [this forum's rules](https://forum.inductiveautomation.com/faq).

> [@Compare start of month data and end of month data](https://forum.inductiveautomation.com/t/compare-start-of-month-data-and-end-of-month-data/85598):
>
> HI I have a currently query for compares a previous day against a current day, but now I need to compare the start day of the month against the last day of the month. this my currently query: DECLARE @FechaInicial DATE = '2024-01-08'; DECLARE @FechaFinal DATE = '2024-02-08'; SELECT dm.Fecha, SUM(CASE WHEN dm.Linea = 'ELFO\_006\_Oficinas' THEN dm.Dato - ISNULL( dp.Dato, 0) ELSE 0 END) AS ELFO\_006\_Oficinas\_Diferencia, SUM(CASE WHEN dm.Linea = 'ELFO\_007\_Rim' THEN dm.Dato - ISNULL( …

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 15, 2024, 2:52pm UTC](https://forum.inductiveautomation.com/t/subtract-data-from-the-first-day-vs-the-last-day-of-the-month-in-sql-of-all-months-of-the-year/85757/3 "2024-02-15T14:52:43Z")

</div>

Closed on February 15, 2024, 2:52pm UTC