Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save tad-lispy/8403100 to your computer and use it in GitHub Desktop.
Save tad-lispy/8403100 to your computer and use it in GitHub Desktop.
SQL query to Currenda Sawa DB to get parties to the lawsuit (plaintiff and defendant), with group concatenated names for each side separated by colon, and with inflexion cases (dative for defendant and genitive for plaintiff) and count for each side.
-- Use this variable to limit scope of search to defendants last name or name in case of corporations
declare @search as varchar(32)
set @search = 'Kowalski'
-- Find lawsuits where pozwany matches search criteria
-- This will be used to limit scope of search.
-- ATM the query is very inefficient. Without this limit and for large data set it can take very long time to execute.
declare @sprawy table (
ident integer
);
Insert into
@sprawy
select top 1000 -- query takes forever if more then that is returned. This is reasonable limit.
sprawa.ident
from
sprawa
inner join strona on strona.id_sprawy = sprawa.ident
inner join dane_strony on strona.id_danych = dane_strony.ident
where
dane_strony.nazwisko like '%' + @search + '%'
and sprawa.czyus = 0
and sprawa.czyzakreslono = 0
and strona.czyus = 0
and dane_strony.czyus = 0
-- Here wa are going to store all parties to all matching lawsuits.
-- Each record contains
-- * lawsuit id
-- * nominative name of party (mianownik)
-- * genitive name of party (dopełniacz)
-- * dative name of party (celownik)
-- * role in a lawsuit (status) - plaintiff (powód) or defendant (pozwany)
declare @strony table (
sprawa integer,
mianownik nvarchar (256),
dopelniacz nvarchar (256),
celownik nvarchar (256),
status nvarchar (256)
);
Insert into
@strony
Select
sprawa.ident
as sprawa,
LTRIM (
-- usuń ew. spację sprzed firmy, która jest wpisywana w polu nazwisko - imienia wtedy nie ma
RTRIM (LTRIM (isnull (dane_strony.imie, '')))
+ ' '
+ RTRIM (LTRIM (dane_strony.nazwisko))
) as mianownik,
LTRIM (
-- usuń ew. spację sprzed firmy, która jest wpisywana w polu nazwisko - imienia wtedy nie ma
RTRIM (LTRIM (isnull (dane_strony.imie_odmien, '')))
+ ' '
+ RTRIM (LTRIM (dane_strony.nazwisko_odmien))
) as dopelniacz,
LTRIM (
-- usuń ew. spację sprzed firmy, która jest wpisywana w polu nazwisko - imienia wtedy nie ma
RTRIM (LTRIM (isnull (dane_strony.imie_odmienc, '')))
+ ' '
+ RTRIM (LTRIM (dane_strony.nazwisko_odmienc))
) as celownik,
rtrim (ltrim (status.nazwa))
as status
from
sprawa
-- połączenie ze stroną
join strona on sprawa.ident = strona.id_sprawy
join dane_strony on strona.id_danych = dane_strony.ident
join status on strona.id_statusu = status.ident
join repertorium on sprawa.repertorium = repertorium.numer
where
repertorium.symbol = 'AmC'
and sprawa.czyus = 0
and sprawa.czyzakreslono = 0
and strona.czyus = 0
order by
sprawa.rok desc,
sprawa.numer desc;
-- Now lets group and concat.
-- group_concat hack from here: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server
-- This is the extremely inefficient, because it's repeated for each party and inflection case.
-- TODO: Use http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server?fid=1848773&df=90&mpp=10&noise=1&prof=True&sort=Position&view=Normal&spc=Relaxed&fr=6
-- It selects:
-- * Lawsuit id
-- * Lawsuit reference sign (sygnatura)
-- * Concatenated nominative names of defendants (pozwani)
-- * Concatenated dative names of defendants (przeciwko)
-- * Defendants' count (liczba pozwanych)
-- * Concatenated nominative names of plaintiffs (powodowie)
-- * Concatenated nominative names of plaintiffs (z powodztwa)
-- * Plaintiffs' count (liczba powodów)
Select
sprawa.ident as id_sprawy,
rtrim (ltrim (repertorium.symbol))
+ ' '
+ cast (sprawa.numer as varchar(max))
+ ' / '
+ right (cast (sprawa.rok as varchar(max)), 2)
as sygnatura,
stuff ((
Select
', ' + mianownik
from
@strony as _strony
where
_strony.sprawa = sprawa.ident
and _strony.status = 'pozwany'
for xml path ('')
), 1, 2, '') as pozwani,
stuff ((
Select
', ' + celownik
from
@strony as _strony
where
_strony.sprawa = sprawa.ident
and _strony.status = 'pozwany'
for xml path ('')
), 1, 2, '') as przeciwko,
(
Select
count(mianownik)
from
@strony as _strony
where
_strony.sprawa = sprawa.ident
and _strony.status = 'pozwany'
) as liczba_pozwanych,
stuff ((
Select
', ' + mianownik
from
@strony as _strony
where
_strony.sprawa = sprawa.ident
and _strony.status = 'powód'
for xml path ('')
), 1, 2, '') as powodowie,
stuff ((
Select
', ' + dopelniacz
from
@strony as _strony
where
_strony.sprawa = sprawa.ident
and _strony.status = 'powód'
for xml path ('')
), 1, 2, '') as z_powodztwa,
(
Select
count(mianownik)
from
@strony as _strony
where
_strony.sprawa = sprawa.ident
and _strony.status = 'powód'
) as liczba_powodow
from
@sprawy as _sprawy
join sprawa on _sprawy.ident = sprawa.ident
join repertorium on sprawa.repertorium = repertorium.numer
where
repertorium.symbol = 'AmC'
order by
sprawa.rok desc,
sprawa.numer desc
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment