none
Alle Monatsletzten einer Tabelle RRS feed

  • Frage

  • Hallo,

    Grundlage ist eine Datenbanktabelle, in der für jeden Tag des Jahres mehrere Zeilen mitgeschrieben werden.
    Für meine Abfrage möchte ich in der where-Klausel so einschränken, dass alle Werte für alle Monatsletzen des laufenden Jahres ausgegeben werden. Anders ausgedrückt benötige ich alle Zeilen, die am 31.01., am letzten Tag des Februars, am 31.03., am 30.04. usw. mitgeschrieben wurden.
    Wie könnte ich das erreichen?

    Vielen Dank für die Hilfe!

    Olaf

    Dienstag, 5. November 2019 13:53

Antworten

  • Hallo Olaf,

    ab SQL Server 2012 kannst Du dafür die Funktion EOMONTH( <Datum> ) verwenden. Frühere Versionen müssen etwas aufwändiger gelöst werden.

    Falls SQL 2012+, in deinem Fall bspw. so:

    SELECT ...
    FROM   <Tabelle>
    WHERE  <Datumspalte> = EOMONTH( <Datumsspalte> )

    In früheren Versionen bspw. so:

    WITH Query AS
    (
    SELECT ...,
           <Datumsspalte>,
           CONVERT(
                   date,
                   DATEADD(
                           DAY,
                           -1,
                           DATEADD(
                                   MONTH,
                                   1,
                                   CONVERT( varchar( 4 ), YEAR( <Datumsspalte>) ) +
                                   RIGHT( '0' + CONVERT( varchar( 2 ), MONTH( <Datumsspalte>) ), 2 ) +
                                   '01'
                                  )
                           )
                  ) AS LastDateInMonth
    FROM   <Tabelle>
    )
    SELECT *
    FROM   Query
    WHERE  <Datumsspalte> = LastDateInMonth
    Du kannst dir aber auch eine Skalarfunktion erstellen, die anhand des übergebenen Datums den letzten Tag im Monat berechnet.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport


    Dienstag, 5. November 2019 14:15
    Moderator
  • Hallo Olaf,

    ein weiterer Vorschlag:

    IF OBJECT_ID(N'tempdb..#Calendar') IS NOT NULL
        DROP TABLE #Calendar;
    GO
    
    DECLARE @Zeitachse CHAR(1) = 'M';        /* für Berichtstyp auf Monats-, Quartals-, oder Jahresbericht ändern; Y = Jahr; Q = Quartal; M = Monat; D = Tag */ 
    DECLARE @DateMin DATETIME = CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'0101' AS DATETIME) ;
    DECLARE @DateMax DATETIME = CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'1231' AS DATETIME) ;
    
    
    CREATE TABLE #Calendar(
       Jahr int NULL,
       Quartal int NULL,
       Monat CHAR (2) NULL,
       Beginn_Periode DATETIME,
       Ende_Periode DATETIME,
       Periode varchar(15) NULL);
          
    WITH Calendar_func AS 
    (
       SELECT @DateMin AS DateDay
       UNION ALL
       SELECT DATEADD(DAY, 1, DateDay)
       FROM Calendar_func
       WHERE DateDay < @DateMax
    )
    INSERT INTO #Calendar(Jahr, Quartal, Monat, Beginn_Periode, Ende_Periode, Periode)
    
    SELECT DISTINCT
       DATEPART(yy, DateDay) AS [Jahr],
       
    	CASE WHEN @Zeitachse = 'Y' 
             THEN NULL
             ELSE CASE WHEN @Zeitachse = 'Q' 
    			       THEN DATEPART(qq, DateDay)
                       ELSE CASE WHEN @Zeitachse = 'M' 
    						     THEN DATEPART(qq, DateDay)
    						     ELSE CASE WHEN @Zeitachse = 'D' 
    						               THEN DATEPART(qq, DateDay)
    						               ELSE NULL
    					              END 
    					     END 
                  END 
       END AS [Quartal],
        
    	CASE WHEN @Zeitachse = 'Y' 
             THEN NULL
             ELSE CASE WHEN @Zeitachse = 'Q' 
    			       THEN NULL
                       ELSE DATEPART(mm, DateDay)
                   END 
        END AS [Monat],
    
    	CASE WHEN @Zeitachse = 'Y' 
             THEN DATEADD(yy,DATEDIFF(yy,0,DateDay),0)
             ELSE CASE WHEN @Zeitachse = 'Q'
    				   THEN DATEADD(qq,DATEDIFF(qq,0,DateDay),0)
    				   ELSE CASE WHEN @Zeitachse = 'M'
    						     THEN DATEADD(mm,DATEDIFF(mm,0,DateDay),0) 
    						     ELSE CASE WHEN @Zeitachse = 'D'
    						               THEN DATEADD(dd,DATEDIFF(dd,0,DateDay),0) 
    						               ELSE NULL
    					              END
    					    END
    			  END 
    	END AS [Beginn_Periorde],
    	
    	CASE WHEN @Zeitachse = 'Y' 
             THEN DATEADD(yy,DATEDIFF(yy,0,DateDay)+1,-1)
             ELSE CASE WHEN @Zeitachse = 'Q'
    				   THEN DATEADD(qq,DATEDIFF(qq,0,DateDay)+1,-1)
    				   ELSE CASE WHEN @Zeitachse = 'M'
    						     THEN DATEADD(mm,DATEDIFF(mm,0,DateDay)+1,-1)
    						     ELSE CASE WHEN @Zeitachse = 'D'
    						               THEN DATEADD(dd,DATEDIFF(dd,0,DateDay)+1,-1)
    						               ELSE NULL
    					              END
    					    END
    			  END 
    	END AS [Ende_Periorde],
    
     	CASE WHEN @Zeitachse = 'Y' 
             THEN CAST (DATEPART(yy, DateDay) AS CHAR (4))
             ELSE CASE WHEN @Zeitachse = 'Q'
    				   THEN CAST (DATEPART(yy, DateDay) AS CHAR (4)) 
    						+ '-Q' 
    						+ CAST (DATEPART(qq, DateDay) AS CHAR (1))
    				   ELSE CASE WHEN @Zeitachse = 'M'
    						     THEN CAST (DATEPART(yy, DateDay) AS CHAR (4)) 
    							 + '-' 
    						     + CASE 
    									WHEN DATEPART(mm, DateDay) < 10 
    									THEN RIGHT('0' + CONVERT( varchar, DATEPART(mm, DateDay) ), 2) 
    									ELSE CONVERT(VARCHAR, DATEPART(mm, DateDay))
    							    END 
    						     ELSE CASE WHEN @Zeitachse = 'D'
    						               THEN CAST (DATEPART(yy, DateDay) AS CHAR (4)) 
    							           + '-'
    							           + CASE 
    									         WHEN DATEPART(mm, DateDay) < 10 
    									         THEN RIGHT('0' + CONVERT( varchar, DATEPART(mm, DateDay) ), 2) 
    									         ELSE CONVERT(VARCHAR, DATEPART(mm, DateDay))
    							              END  
    							           + '-'
    						               + CASE 
    									          WHEN DATEPART(dd, DateDay) < 10 
    									          THEN RIGHT('0' + CONVERT( varchar, DATEPART(dd, DateDay) ), 2) 
    									          ELSE CONVERT(VARCHAR, DATEPART(dd, DateDay))
    							             END
    							       END
    					    END
    			  END 
    	END AS [Periorde]
     
    FROM Calendar_func
    OPTION (MAXRECURSION 0); 
    
    SELECT * FROM #Calendar; /* nur zur Ansicht der Kalendertabelle; kann auskommetiert werden */
    
    -- Eigentliche Abfrage
    SELECT
    c.Ende_Periode,
    [Deine weiteren Spalten]
    FROM #Calendar c
        INNER JOIN [Deine Tabelle] t  ON (c.Ende_Periode = t.[Deine Datumsspalte]  /* alternativ für Zählung in der Periode auch auch BETWEEN t.[Deine Datumsspalte_Beginn] AND t.[Deine Datumsspalte_Ende] 

    Schönen Abend.

    • Als Antwort markiert Olaf113 Mittwoch, 6. November 2019 07:45
    Dienstag, 5. November 2019 18:46

Alle Antworten

  • Hallo Olaf,

    ab SQL Server 2012 kannst Du dafür die Funktion EOMONTH( <Datum> ) verwenden. Frühere Versionen müssen etwas aufwändiger gelöst werden.

    Falls SQL 2012+, in deinem Fall bspw. so:

    SELECT ...
    FROM   <Tabelle>
    WHERE  <Datumspalte> = EOMONTH( <Datumsspalte> )

    In früheren Versionen bspw. so:

    WITH Query AS
    (
    SELECT ...,
           <Datumsspalte>,
           CONVERT(
                   date,
                   DATEADD(
                           DAY,
                           -1,
                           DATEADD(
                                   MONTH,
                                   1,
                                   CONVERT( varchar( 4 ), YEAR( <Datumsspalte>) ) +
                                   RIGHT( '0' + CONVERT( varchar( 2 ), MONTH( <Datumsspalte>) ), 2 ) +
                                   '01'
                                  )
                           )
                  ) AS LastDateInMonth
    FROM   <Tabelle>
    )
    SELECT *
    FROM   Query
    WHERE  <Datumsspalte> = LastDateInMonth
    Du kannst dir aber auch eine Skalarfunktion erstellen, die anhand des übergebenen Datums den letzten Tag im Monat berechnet.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport


    Dienstag, 5. November 2019 14:15
    Moderator
  • Hallo Olaf,

    ein weiterer Vorschlag:

    IF OBJECT_ID(N'tempdb..#Calendar') IS NOT NULL
        DROP TABLE #Calendar;
    GO
    
    DECLARE @Zeitachse CHAR(1) = 'M';        /* für Berichtstyp auf Monats-, Quartals-, oder Jahresbericht ändern; Y = Jahr; Q = Quartal; M = Monat; D = Tag */ 
    DECLARE @DateMin DATETIME = CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'0101' AS DATETIME) ;
    DECLARE @DateMax DATETIME = CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'1231' AS DATETIME) ;
    
    
    CREATE TABLE #Calendar(
       Jahr int NULL,
       Quartal int NULL,
       Monat CHAR (2) NULL,
       Beginn_Periode DATETIME,
       Ende_Periode DATETIME,
       Periode varchar(15) NULL);
          
    WITH Calendar_func AS 
    (
       SELECT @DateMin AS DateDay
       UNION ALL
       SELECT DATEADD(DAY, 1, DateDay)
       FROM Calendar_func
       WHERE DateDay < @DateMax
    )
    INSERT INTO #Calendar(Jahr, Quartal, Monat, Beginn_Periode, Ende_Periode, Periode)
    
    SELECT DISTINCT
       DATEPART(yy, DateDay) AS [Jahr],
       
    	CASE WHEN @Zeitachse = 'Y' 
             THEN NULL
             ELSE CASE WHEN @Zeitachse = 'Q' 
    			       THEN DATEPART(qq, DateDay)
                       ELSE CASE WHEN @Zeitachse = 'M' 
    						     THEN DATEPART(qq, DateDay)
    						     ELSE CASE WHEN @Zeitachse = 'D' 
    						               THEN DATEPART(qq, DateDay)
    						               ELSE NULL
    					              END 
    					     END 
                  END 
       END AS [Quartal],
        
    	CASE WHEN @Zeitachse = 'Y' 
             THEN NULL
             ELSE CASE WHEN @Zeitachse = 'Q' 
    			       THEN NULL
                       ELSE DATEPART(mm, DateDay)
                   END 
        END AS [Monat],
    
    	CASE WHEN @Zeitachse = 'Y' 
             THEN DATEADD(yy,DATEDIFF(yy,0,DateDay),0)
             ELSE CASE WHEN @Zeitachse = 'Q'
    				   THEN DATEADD(qq,DATEDIFF(qq,0,DateDay),0)
    				   ELSE CASE WHEN @Zeitachse = 'M'
    						     THEN DATEADD(mm,DATEDIFF(mm,0,DateDay),0) 
    						     ELSE CASE WHEN @Zeitachse = 'D'
    						               THEN DATEADD(dd,DATEDIFF(dd,0,DateDay),0) 
    						               ELSE NULL
    					              END
    					    END
    			  END 
    	END AS [Beginn_Periorde],
    	
    	CASE WHEN @Zeitachse = 'Y' 
             THEN DATEADD(yy,DATEDIFF(yy,0,DateDay)+1,-1)
             ELSE CASE WHEN @Zeitachse = 'Q'
    				   THEN DATEADD(qq,DATEDIFF(qq,0,DateDay)+1,-1)
    				   ELSE CASE WHEN @Zeitachse = 'M'
    						     THEN DATEADD(mm,DATEDIFF(mm,0,DateDay)+1,-1)
    						     ELSE CASE WHEN @Zeitachse = 'D'
    						               THEN DATEADD(dd,DATEDIFF(dd,0,DateDay)+1,-1)
    						               ELSE NULL
    					              END
    					    END
    			  END 
    	END AS [Ende_Periorde],
    
     	CASE WHEN @Zeitachse = 'Y' 
             THEN CAST (DATEPART(yy, DateDay) AS CHAR (4))
             ELSE CASE WHEN @Zeitachse = 'Q'
    				   THEN CAST (DATEPART(yy, DateDay) AS CHAR (4)) 
    						+ '-Q' 
    						+ CAST (DATEPART(qq, DateDay) AS CHAR (1))
    				   ELSE CASE WHEN @Zeitachse = 'M'
    						     THEN CAST (DATEPART(yy, DateDay) AS CHAR (4)) 
    							 + '-' 
    						     + CASE 
    									WHEN DATEPART(mm, DateDay) < 10 
    									THEN RIGHT('0' + CONVERT( varchar, DATEPART(mm, DateDay) ), 2) 
    									ELSE CONVERT(VARCHAR, DATEPART(mm, DateDay))
    							    END 
    						     ELSE CASE WHEN @Zeitachse = 'D'
    						               THEN CAST (DATEPART(yy, DateDay) AS CHAR (4)) 
    							           + '-'
    							           + CASE 
    									         WHEN DATEPART(mm, DateDay) < 10 
    									         THEN RIGHT('0' + CONVERT( varchar, DATEPART(mm, DateDay) ), 2) 
    									         ELSE CONVERT(VARCHAR, DATEPART(mm, DateDay))
    							              END  
    							           + '-'
    						               + CASE 
    									          WHEN DATEPART(dd, DateDay) < 10 
    									          THEN RIGHT('0' + CONVERT( varchar, DATEPART(dd, DateDay) ), 2) 
    									          ELSE CONVERT(VARCHAR, DATEPART(dd, DateDay))
    							             END
    							       END
    					    END
    			  END 
    	END AS [Periorde]
     
    FROM Calendar_func
    OPTION (MAXRECURSION 0); 
    
    SELECT * FROM #Calendar; /* nur zur Ansicht der Kalendertabelle; kann auskommetiert werden */
    
    -- Eigentliche Abfrage
    SELECT
    c.Ende_Periode,
    [Deine weiteren Spalten]
    FROM #Calendar c
        INNER JOIN [Deine Tabelle] t  ON (c.Ende_Periode = t.[Deine Datumsspalte]  /* alternativ für Zählung in der Periode auch auch BETWEEN t.[Deine Datumsspalte_Beginn] AND t.[Deine Datumsspalte_Ende] 

    Schönen Abend.

    • Als Antwort markiert Olaf113 Mittwoch, 6. November 2019 07:45
    Dienstag, 5. November 2019 18:46
  • Hallo Stefan, hallo Joerg,

    vielen Dank für die schnellen und vor allem hilfreichen Antworten.
    Beide Lösungen funktionieren tadellos - genau so, wie ich mir das vorgestellt habe! SUPER!

    VIELEN DANK!

    Olaf


    • Bearbeitet Olaf113 Mittwoch, 6. November 2019 07:57
    Mittwoch, 6. November 2019 07:45