MS SQL >> SQL Abfrage als DTS Paket

[Leuselator]

Kleine Hilfsfunktion, die ich sehr liebe:

SQL-Code:

CREATE FUNCTION fx_EncodeDate
( @Y   int
, @Mon int
, @D   int
, @H   int = 0
, @Min int = 0
, @S   int = 0
, @MS  int = 0
)
RETURNS datetime
AS
BEGIN
  DECLARE @RESULT datetime
      SET @RESULT = dateadd(year,@Y-1900,dateadd(month,@Mon,dateadd(day,@D,-32)))
      SET @RESULT = dateadd(hh,@H,dateadd(n,@Min,dateadd(s,@S,dateadd(ms,@MS,@RESULT))))
   RETURN @RESULT
END

und dann:

SQL-Code:

  SELECT article
       , COUNT(article)    AS Auftraege
       , SUM(DISTINCT qty) AS Prints 
    FROM etikett_positions
   WHERE creation >= dbo.fx_EncodeDate(Datepart(Year ,creation),
                                       Datepart(Month,creation),
                                       Datepart(Day  ,creation)-1,
                                       18,0,0,0) 
     AND creation <= GETDATE()
GROUP BY article

alternativ, ohne Funktion (liest sich nicht so schön):

SQL-Code:

  SELECT article
       , COUNT(article)    AS Auftraege
       , SUM(DISTINCT qty) AS Prints 
    FROM etikett_positions
   WHERE creation >= dateadd(hh,18,round(convert(float,creation-1),0,1))
     AND creation <= GETDATE()
GROUP BY article

Gruß