[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] 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

[Comarch ERP XL] - naprawa zerwanych sesji

 SQL do naprawy zerwanych sesji:

UPDATE A SET SES_Aktywna=2, SES_Stop=DATEDIFF( ss, CONVERT(DATETIME,'19900101',11), getdate()) 

FROM CDN.Sesje A

LEFT JOIN sys.sysprocesses B ON A.SES_ClarionSPID=B.spid AND SES_OpeIdent=CDN.SubstringEx(B.program_name,':',5)

WHERE 

SES_Aktywna in (0)

--SES_Stop=0

AND B.spid is null

[Comarch ERP XL] Odbudowa CDN.Wolne

 Poniżej skrypt do odbudowy CDN.Wolne:

declare @rok int = year(getdate())

exec [CDN].[OdbudujWolne] @rok, 1

[SQL] przykład kursora

 Przykład kursora w mssql:

DECLARE kursor CURSOR FOR
SELECT Ename, Sal FROM Emp WHERE Sal > 200
DECLARE @nazwisko VARCHAR(50), @pensja INT
PRINT 'Pracownicy o pensji wyższej niż 200:'
OPEN kursor 
FETCH NEXT FROM kursor INTO @nazwisko, @pensja 
WHILE @@FETCH_STATUS = 0
   BEGIN 
      PRINT @nazwisko + ' ' + Cast(@pensja As Varchar)
      FETCH NEXT FROM kursor INTO @nazwisko, @pensja 
   END 
CLOSE kursor 
DEALLOCATE kursor

[SQL] Czyszczenie pliku tempdb w sql server

Poniżej przykład:

--zwalnia zasoby tempdb

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREEPROCCACHE

--czysci dany plik

USE [tempdb]

GO

DBCC SHRINKFILE (N'temp6' , EMPTYFILE)

GO

--kasuje dany plik

USE [tempdb]

GO

ALTER DATABASE [tempdb]  REMOVE FILE [temp6]

GO

[Comarch ERP XL] Kod Kontrahenta z Zapisu księgowego

 Zapytanie wyciągające kh z zapisów księgowych

SELECT distinct CDN.NazwaObiektu(TrN_GIDTyp,trn_gidnumer,0,2)AS dok, (select knt_akronim from cdn.kntkarty where knt_gidnumer = TrN_KntNumer) KodKH, 

case when trn_nettor =0 then TrN_NettoP else  TrN_NettoR end KwotaNetto , (select TrV_DataOP from cdn.travat where trv_gidnumer = TrN_GIDNumer) DataVat, 

(select TrV_StawkaPod from cdn.travat where trv_gidnumer = TrN_GIDNumer) StawkaPod

FROM CDN.dekrety A

LEFT OUTER JOIN CDN.DziennikElem B ON A.DT_GIDNumer = B.DEL_GIDNumer

AND A.DT_GIDLp = B.DEL_GIDLp

LEFT OUTER JOIN CDN.dziennik C ON B.DEL_GIDNumer = C.DZK_GIDNumer

LEFT OUTER JOIN CDN.dekrety D ON D.DT_GIDNumer = A.DT_GIDNumer

AND D.DT_GIDLp = A.DT_GIDLp

AND D.DT_Waluta = A.DT_Waluta

AND D.DT_DC = 3 - A.DT_DC

LEFT OUTER JOIN CDN.Zrodla ON CDN.Zrodla.ZRO_DTNumer = a.DT_GIDNumer

AND CDN.Zrodla.ZRO_DTLp = a.DT_GIDLp

LEFT OUTER JOIN CDN.Predekrety ON CDN.Predekrety.PDT_GIDTyp = CDN.Zrodla.ZRO_TRNTyp

AND CDN.Predekrety.PDT_GIDNumer = CDN.Zrodla.ZRO_TRNNumer

LEFT OUTER JOIN CDN.TraNag ON CDN.TraNag.TrN_GIDTyp = CDN.Predekrety.PDT_GIDTyp

AND CDN.TraNag.TrN_GIDNumer = CDN.Predekrety.PDT_GIDNumer

WHERE  DEL_DokumentZrodlowy = 'demo'

[ERP XL] Wyszukanie adresu korenspondencyjnego

 Po niżej zapytanie na znalezienie adresu korenspodencyjnego, nalezy powiazac z knaadresy

select 1 from  cdn.obiektydomyslne where ObD_DomNumer = KnA_GIDNumer AND KnA_KntNumer = ObD_ObiNumer

[SQL] An error occurred in the Microsoft .NET Framework while trying to load assembly id 65675

 Rozwiązanie problemu:

USE <DATABASE>;
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

USE <DATABASE>
GO

EXEC sp_changedbowner 'sa'
USE <DATABASE>
GO

ALTER DATABASE <DATABASE> SET TRUSTWORTHY ON;  

[COMARCH ERP XL] Zmiana treści tytułu maila

Gdy chcemy zmienić treść tytułu maila wysyłanego z ERP XL nalezy zmodyfikowac funkcję 
CDN.TematMailaWydruk
Analogicznie się to dotyczy modyfiacji nazwy pliku w załaczaniku maila  CDN.NazwaZalacznikaWydruk

[Comarch ERP XL] Przywrócenie kontrahenta jednorazowego

Gdy się usunie kontrahenta jednorazowego bez backupu ani rusz. Czynności związane z przywróceniem kontrahenta jednorazowego w ERP Comarch XL

• Insert z kopii bazy kontrahenta z gidid = 0 (cdn.kntkarty)
• Insert z kopii bazy danych z akronim JEDNORAZOWY (cdn.kntgrupy)
• Insert z kopii bazy danych z kodu JEDNORAZOWY (cdn.kntgrupydom)
• Wyszukanie Adresów dla gid_kntNumer =0 (cdn.kntadresy)
• update gdy nie ma knt_gidtyp=864 na jednym nie zarchiwizowanym (sprawdzenie pola KnA_DataArc=0)
• Update kntkarty dla gidid = 0 knaNumer dla którego wybraliśmy domyślny adres

Jeżeli tego nie zrobimy to pojawi się błąd o anonimizacji kontrahenta (RODO).
Więcej informacji można znaleźć w podręcznikach na stronie Comarchu

[SQL] Kilka rekordów w jednym wierszu po przecinku

Poniżej znajduje się przykład zapytania SQL by móc wyświetlić kilka rekordów w jednym wierszu po przecinku.

SELECT STUFF(( SELECT ',' + elmt.value('.', 'nvarchar(max)') 

FROM ( SELECT ( 

/*YOUR QUERY HERE*/

 SELECT DISTINCT mag_kod FROM tab
/*--------------------*/

 FOR XML AUTO ,ELEMENTS XSINIL ,TYPE ) ) AS A(t) CROSS APPLY t.nodes('/*/*')

 AS B(elmt) FOR XML PATH('') ), 1, 1, '')

Działa to w MSSQL

[MYSQL] Różnicowanie pomiędzy rekordami

Ostatnio myślałem jak utworzyć zapytanie wyliczajace różnice pomiędzy rekordami czyli jeżeli mamy coś takiego:

F1
F2
F3
F4
F5

to:

F1-F2
F2-F3
F3-F4
F4-F5

Jako klucza użyłem daty wprowadzenia z powodu iż łatwiej wyszukać ostatnie wprowadzenie rekordu oraz rekordu-1
Poniżej przedstawione zapytanie jak to działa:

SELECT
    t1.id,
    t2.id,
    t1.datawprowadzenia,
    t2.datawprowadzenia,
    t1.ciepla,
    t2.ciepla,
    t1.zimna,
    t2.zimna,
    t1.prad,
    t2.prad,
    t1.gaz,
    t2.gaz,
    t1.ciepla - t2.ciepla AS CieplaR,
    t1.zimna - t2.zimna AS zimnaR,
    t1.prad - t2.prad AS pradR,
    t1.gaz - t2.gaz AS gazR
FROM
    Odczyty t1,
    Odczyty t2
WHERE
    t2.id = (SELECT
            t3.id
        FROM
            Odczyty t3
        WHERE
            t3.datawprowadzenia < t1.datawprowadzenia AND t3.aktywny = 1
        ORDER BY datawprowadzenia DESC
        LIMIT 1)
        AND t2.aktywny = 1
        AND t1.aktywny = 1
ORDER BY t1.datawprowadzenia DESC

[MSSQL]Pobranie losowej wartości z tabeli

Istnieje wiele sposobów, aby wybrać losowy zapis lub wiersz z tabeli bazy danych. Oto kilka przykładów SQL, które nie wymagają dodatkowej logiki aplikacji, ale każdy serwer bazy danych wymaga innej składni SQL. Poniższy przykład jest dla MSSQL:

SELECT TOP 1 column FROM table ORDER BY NEWID()

[MSSQL]Pobranie wartości parametru z XML

Poniżej przykład zastosowania wyciągnięcia wartości z parametru:

SELECT TOP 10 cast(cast(Wzorzec AS VARBINARY(max)) AS XML).query('.').value('(/barcodes/barcode/pages/page/@papername)[1]', 'varchar(40)')
 ,*
FROM SzablonyEtykiet
WHERE Wzorzec IS NOT NULL
 AND aktywny = 1
ORDER BY id DESC

[MSSQL]Użycie CTE - Common Table Expression

CTE – podstawy

CTE to nazwane zapytanie reprezentujące tymczasowy zestaw rekordów definiowany w zasięgu jednego polecenia SELECT, INSERT, UPDATE lub DELETE. Składniowo CTE przypomina podzapytanie w z języka Transact-SQL lub elementy obliczane z języka MDX.

Składnię CTE łatwo rozpoznać po charakterystycznym słowie WITH. Słowo to jest w języku Transact-SQL wykorzystywane między innymi do podawania wskazówek (ang. hint) dla optymalizatora lub do deklarowania przestrzeni nazw XML (klauzula WITH XMLNAMESPACES).

Sposób użycia:

USE AdventureWorks;
GO
WITH MojePierwszeCTE AS (
SELECT Name
FROM HumanResources.Department
)
SELECT * FROM MojePierwszeCTE;

Więcej na temat użycia CTE znajdziesz tutaj.

[MSSQL] Tworzenie kalendarza w SQL

Szukając rozwiązania utworzenia kalendarza w SQL-owym MSSQL znalazłem coś takiego:

IF EXISTS (SELECT * FROM information_schema.tables 
WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO [Calendar]
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

Pokazanie kolejnego dnia roboczego o godz 16

Pokazanie kolejnego dnia roboczego o godz 16:00:

SELECT TRUNC(LEAST(NEXT_DAY(sysdate,'PONIEDZIAŁEK'),
NEXT_DAY(sysdate,'WTOREK'), NEXT_DAY(SYSDATE, 'ŚRODA'),
NEXT_DAY(SYSDATE,'CZWARTEK'),
NEXT_DAY(sysdate,'PIĄTEK')) + 8/24, 'HH')
DAY FROM dual;

Pobranie nazw tabel z bazy Oracle

SELECT distinct table_nameFROM user_tab_colsWHERE table_name like
'PARAM_%'