Skip to content

Instantly share code, notes, and snippets.

View iraklidd's full-sized avatar

Irakli DD iraklidd

2 followers · 9 following
View GitHub Profile
@iraklidd
iraklidd / mapping table
Created April 5, 2023 16:40
IF(OBJECT_ID('tempdb..#MapTable') IS NOT NULL)
DROP TABLE #MapTable
--
DECLARE @t1 TABLE (n1 VARCHAR(5))
DECLARE @t2 TABLE (n2 INT)
DECLARE @t3 TABLE (n3 INT)
INSERT into @t1 VALUES ('4.1.1'), ('4.1.2'), ('4.2.1'), ('4.2.2');
@iraklidd
iraklidd / PROCEDURE select table without NULLs (SQL SERVER)
Last active February 28, 2022 12:45
Select any number of columns tables dynamically - except NULL rows.
CREATE PROCEDURE dbo.notnullcolumns
@t varchar(128)
AS
BEGIN
declare
@c_name varchar(300),
@sql varchar(max)
declare c cursor for
@iraklidd
iraklidd / FUNCTION FOMONTH
Last active February 27, 2022 14:20
(FOMONTH or SOMONTH this is opposite to function EOMONTH in SQL SERVER)
CREATE FUNCTION [dbo].[FOMONTH] (@d date)
returns date
AS
Begin
DECLARE @sd DATE
SET @sd = DATEADD(month, DATEDIFF(month, 0, @d ), 0)
@iraklidd
iraklidd / SELECT without looping (MSSQL, Oracle)
Last active January 5, 2022 22:37
Hierarchical Queries in MSSQL (like an Oracle Connect by Level) loop used by just SELECT
1) numbers from 1 to 10
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10;
WITH numbers(n)
AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM numbers
WHERE n+1 <= 10
@iraklidd
iraklidd / gist:3db493d06156c8bd55b4815895c8a67a
Last active September 7, 2021 16:00
Select seperate list as rows, without pivot, using by dummy DUAL table (PL/SQL Oracle / PIPELINED table function)
0) run for see result:
SELECT regexp_substr(regexp_replace('&STRING', '[^A-Z0-9-,]', ''),
'[^,]+',
1,
LEVEL) a
FROM dual
CONNECT BY regexp_substr(regexp_replace('&STRING', '[^A-Z0-9-,]', ''),
'[^,]+',
1,
@iraklidd
iraklidd / Export worksheets as CSV files using Python
Created July 15, 2021 12:39
save excel sheets as csv files, using python (jupyter notebook)
import os
import pandas as pd
from datetime import date
f_name = 'Samle Data.xlsx' # Enter excel file name (from the same directory)
prfx = os.path.splitext(f_name)[0] # you can change prefix
sffx = datetime.today().strftime('%Y-%m-%d') # or suffix
WS = pd.ExcelFile(f_name)
@iraklidd
iraklidd / gist:e8cf05be65c1c5be0f40c0f86990bd6d
Last active July 1, 2021 19:43
Get NOT NULL rows dynamically from table (MSSQL PROCEDURE)
CREATE PROCEDURE NOTNA_ROWS --get not null rows
@t varchar(128) --your table name
AS
BEGIN
declare
@c_name varchar(128),
@sql varchar(max)
declare c cursor for
@iraklidd
iraklidd / gist:61f37808fac587fa2a0e811ad6f08882
Created April 18, 2021 21:15
generate set of random numbers - mssql
;WITH CTE AS (
SELECT 1 as Number
UNION ALL
SELECT Number+1
FROM CTE
WHERE Number < 100
)
SELECT RAND(CHECKSUM(NEWID() )) AS randnumber FROM CTE
@iraklidd
iraklidd / SQL-Server_Temporary_Tables.sql
Last active January 5, 2022 18:34
SQL Server Temporary Tables, CTE, "Dummy" Table, Pivot - simple example
;WITH CTEearthquakes as (
SELECT
earthquakes.cDate,
earthquakes.Magnitude
FROM (
VALUES
('1/5/2007', 5.8),
('1/1/2008', 5.6),