[SQL] generowanie okresu od do dla całego roku

Po niżej zapytanie generujące okres miesiąca dla całego roku :

SELECT cast(DATEADD(DD, - (DAY(cast('2022-01-12' AS DATETIME) - 1)), cast('2022-01-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-01-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-01-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-02-12' AS DATETIME) - 1)), cast('2022-02-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-02-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-02-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-03-12' AS DATETIME) - 1)), cast('2022-03-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-03-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-03-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-04-12' AS DATETIME) - 1)), cast('2022-04-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-04-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-04-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-05-12' AS DATETIME) - 1)), cast('2022-05-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-05-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-05-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-06-12' AS DATETIME) - 1)), cast('2022-06-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-06-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-06-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-07-12' AS DATETIME) - 1)), cast('2022-07-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-07-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-07-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-08-12' AS DATETIME) - 1)), cast('2022-08-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-08-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-08-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-09-12' AS DATETIME) - 1)), cast('2022-09-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-09-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-09-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-10-12' AS DATETIME) - 1)), cast('2022-10-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-10-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-10-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-11-12' AS DATETIME) - 1)), cast('2022-11-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-11-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-11-12' AS DATETIME))) AS DATE) AS LastDate
union
SELECT cast(DATEADD(DD, - (DAY(cast('2022-12-12' AS DATETIME) - 1)), cast('2022-12-12' AS DATETIME)) AS DATE) AS FirstDate, cast(DATEADD(DD, - (DAY(cast('2022-12-12' AS DATETIME))), DATEADD(MM, 1, cast('2022-12-12' AS DATETIME))) AS DATE) AS LastDate

[ERP XL] Dodanie konta domenowego gdy jest uszkodzona domena

 przechodzimy w regedit do  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList

następnie wyszukujemy użytkownika (tutaj ważna uwaga: użytkownik musi się zalogować chociaż raz domenowo) i następnie pobieramy z drzewa identyfikator.

Następnym krokiem jest wykonanie update na cdn.OpeKarty:

update cdn.OpeKarty set Ope_NTSid = 'Numer pobrany z rejestru' where Ope_Ident = 'KodKonta'

I mamy podpięte konto do domeny 

[ERP XL] Ilosc zamówien w godzinę

 Poniższe zapytanie reprezentuje zliczanie ilosci zamówień w godzinę

select dateadd(day, ZaN_DataWystawienia, '18001228'), 
CONVERT(CHAR(2), DATEADD(MILLISECOND, (zan_GodzinaWystawienia - 1) * 10, '1990-01-01'), 14) [Godzina_wystawienia], COUNT(1) [IloscZamowien] from CDN.ZamNag group by ZaN_DataWystawienia, CONVERT(CHAR(2), DATEADD(MILLISECOND, (zan_GodzinaWystawienia - 1) * 10, '1990-01-01'), 14)
order by ZaN_DataWystawienia desc, CONVERT(CHAR(2), DATEADD(MILLISECOND, (zan_GodzinaWystawienia - 1) * 10, '1990-01-01'), 14)  desc

[SQL] Wyliczanie marży na podstawie dokumentów

 Wylicznaie marży z dokumentów:

SELECT 

      TrN_GIDTyp                                       AS Seria

  ,tre_twrkod

    , SUM(TrE_Ilosc)                                     AS Ilosc

    , SUM(TrE_KsiegowaNetto)                             AS Wart_Netto

    , SUM(TrE_KsiegowaNetto) - SUM(TrE_KosztRzeczywisty) AS Zysk_netto

    , SUM(TrE_KosztRzeczywisty)                          AS Koszt_netto 

, ((SUM(TrE_KsiegowaNetto) - SUM(TrE_KosztRzeczywisty) )/SUM(TrE_KsiegowaNetto) )*100 as Marza

FROM CDN.TraNag 

    LEFT JOIN CDN.TraElem ON TrE_GIDTyp = TrN_GIDTyp AND TrE_GIDNumer = TrN_GIDNumer 

Where 

    ( 

        (TrN_GIDTyp IN (2003)) OR 

        TrN_GIDTyp IN (2034, 2042) OR 

        ( TrN_GIDTyp IN (2033, 2041) AND TrN_SPITyp IN (2033, 2041)) OR

        ( TrN_GIDTyp IN (2037, 2045) AND TrN_SPITyp IN (2037, 2045)) OR 

        ( TrN_GIDTyp IN (2001, 2009, 2005, 2013) AND TrN_SPITyp <> 0 )

    )

   -- AND TrN_DataMag BETWEEN DateDiff(DD, '18001228', '2014-05-01 00:00:00') AND DateDiff(DD, '18001228', '2014-05-06 23:59:59')

 --  and 1 in (TrN_MagZNumer,TrN_MagDNumer) 

 GROUP BY 

    TrN_GIDTyp,

tre_twrkod

HAVING SUM(TrE_KsiegowaNetto) <> 0 OR SUM(TrE_KosztRzeczywisty) <> 0