Soci (Soczó Zsolt) szakmai blogja

2015.01.16.

SQL fejtörő 6.

Filed under: Adatbázisok,Szakmai élet,Teaser — Soczó Zsolt @ 08:00

Ez a fejtörő most sokkal nehezebb lesz. Adottak számlák és számla tételek. A számla tételek különböző ÁFA kategóriákba tartoznak. Elveszett viszont, hogy melyik tétel melyik ÁFA kategóriába tartozik. Csak összesítve tudjuk, hogy egy adott számlán mennyi volt az egyes ÁFA tételű termékek össz értéke. A feladat visszaállítani minden egyes tétel ÁFA kulcsát. Az adatok úgy vannak összerakva, hogy a feladat egyértelműen megoldható legyen.

Bemeneti adatok:

Számlák:

ID	Szamlaszam	OsszAr	Ado
1	Szamla1	500.00	Afa27
2	Szamla1	300.00	Afa16
3	Szamla2	4000.00	Afa38

Számla tételek:

ID	Szamlaszam	Ar
1	Szamla1	20.00
2	Szamla1	111.00
3	Szamla1	250.00
4	Szamla1	20.00
5	Szamla1	15.00
6	Szamla1	189.00
7	Szamla1	100.00
8	Szamla1	95.00
9	Szamla2	1000.00
10	Szamla2	3000.00

Elvárt kimenet, Adó, számlaszám és id szerint van rendezve:

id	Szamlaszam	Ar	Ado
2	Szamla1	111.00	Afa16
6	Szamla1	189.00	Afa16
1	Szamla1	20.00	Afa27
3	Szamla1	250.00	Afa27
4	Szamla1	20.00	Afa27
5	Szamla1	15.00	Afa27
7	Szamla1	100.00	Afa27
8	Szamla1	95.00	Afa27
9	Szamla2	1000.00	Afa38
10	Szamla2	3000.00	Afa38

Többféle megoldás is adható, mindegyik legalább 15 soros.

use tempdb;

IF OBJECT_ID('Szamlatetel','U') IS NOT NULL BEGIN
  DROP TABLE Szamlatetel
END 
GO

CREATE TABLE Szamlatetel(
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	Szamlaszam [VARCHAR](20) NOT NULL,
	Ar [DECIMAL](18,2) NOT NULL	
)
GO

INSERT INTO Szamlatetel(Szamlaszam,Ar) 
SELECT 'Szamla1', 20 UNION ALL
SELECT 'Szamla1', 111 UNION ALL
SELECT 'Szamla1', 250 UNION ALL
SELECT 'Szamla1', 20  UNION ALL
SELECT 'Szamla1', 15  UNION ALL
SELECT 'Szamla1', 189 UNION ALL
SELECT 'Szamla1', 100 UNION ALL
SELECT 'Szamla1', 95 UNION ALL
SELECT 'Szamla2', 1000 UNION ALL
SELECT 'Szamla2', 3000
go

IF OBJECT_ID('Szamlafej','U') IS NOT NULL BEGIN
  DROP TABLE Szamlafej
END 
GO

CREATE TABLE Szamlafej(
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	Szamlaszam [VARCHAR](20) NOT NULL,
	OsszAr [DECIMAL](18,2) NOT NULL DEFAULT(0),
	[Ado] [VARCHAR](5) NOT NULL
)
GO

INSERT INTO Szamlafej(Szamlaszam, OsszAr, Ado) 
SELECT 'Szamla1', 500, 'Afa27' UNION ALL
SELECT 'Szamla1', 300, 'Afa16' UNION ALL
SELECT 'Szamla2', 4000,  'Afa38'

SELECT * FROM Szamlafej;
GO
SELECT * FROM Szamlatetel order by id;
GO

A megfejtéseket szokás szerint kommentben várom, amelyeket ezúttal a feladat nehézsége miatt 5 nap múlva engedek ki.

A Test Driven Development tanfolyam következő felvonása február kilencedikén lesz, szeretettel várom az érdeklődőket.

3 Comments

  1. declare
    @Osszeg decimal(18,2),
    @SzamlaTetelId int

    declare @Kombinacio table
    (
    Id int identity(1,1) not null primary key,
    ElozoId int null,
    OsszAr decimal(18,2) not null,
    SzamlaTetelId int not null
    )

    set @SzamlaTetelId = 0

    while 1 = 1
    begin
    select top 1
    @Osszeg = T.Ar
    ,@SzamlaTetelId = T.ID
    from
    Szamlatetel T
    where
    T.ID > @SzamlaTetelId order by T.ID

    if @@rowcount = 0
    break

    insert into @Kombinacio(ElozoId, OsszAr, SzamlaTetelId)
    select
    S.Id
    ,S.OsszAr + @Osszeg
    ,@SzamlaTetelId
    from
    (select OsszAr, Id from @Kombinacio union select 0, null) S
    where
    S.OsszAr + @Osszeg not in (select OsszAr from @Kombinacio)
    end

    ;with Elozmeny as
    (
    select
    K.Id, K.ElozoId, K.SzamlaTetelId, K.OsszAr
    from
    @Kombinacio K
    inner join Szamlafej F on F.OsszAr = K.OsszAr
    union all
    select
    K.Id, K.ElozoId, K.SzamlaTetelId, E.OsszAr
    from
    Elozmeny E
    inner join @Kombinacio K on E.ElozoId = K.Id
    )
    select
    SzamlaTetelId = T.ID
    ,Szamlaszam = F.Szamlaszam
    ,Ar = T.Ar
    ,Ado = F.Ado
    from
    Elozmeny E
    inner join Szamlafej F on F.OsszAr = E.OsszAr
    inner join Szamlatetel T on T.ID = E.SzamlaTetelId
    order by
    E.OsszAr, T.ID

    Comment by Atcom — 2015.01.16. @ 19:05

  2. WITH CTE as
    ( SELECT OsszAr,
    Ado,
    t.ID,
    Ar,
    cast(Ar as decimal(18,2)) as CSum,
    right(‘0′ + CAST(t.Id as Varchar(max)),2) as path
    from Szamlatetel t inner join Szamlafej f on t.Szamlaszam=f.Szamlaszam
    UNION all
    SELECT OsszAr,
    Ado,
    Szamlatetel.ID,
    Szamlatetel.Ar,
    cast(Szamlatetel.Ar+CTE.CSum as decimal(18,2)) as CSum,
    CTE.path+’,’+ right(‘0’ + CAST(Szamlatetel.Id as Varchar(max)),2) as path
    from Szamlatetel
    JOIN CTE on Szamlatetel.Ar+CTE.CSum<=OsszAr and CTE.ID<Szamlatetel.ID
    )
    select Szamlatetel.ID, Szamlaszam, Szamlatetel.Ar, Ado
    from Szamlatetel, CTE
    where CTE.path like '%' + right('0' + CAST(Szamlatetel.ID as Varchar(max)),2) + '%' and CTE.CSum=OsszAr
    order by Ado, Szamlaszam, Szamlatetel.ID

    Comment by Laci — 2015.01.19. @ 18:41

  3. […] Ebben a feladatban meg kellett határozni azokat a sorokat, amelyek összege kiad egy sort egy másik táblában. Mivel nem lehet tudni, hány sort kell szummázni, ezért kombinációkat kellett képezni az elemekből. A feladat nehezebb része tehát, hogyan kell SQL-ből kombinációkat képezni? […]

    Pingback by Soci (Soczó Zsolt) szakmai blogja » Blog Archive » SQL fejtörő 6. megoldás — 2015.01.28. @ 23:47

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress