Tezak upit za uporne

Hm, mozda sam malo preterao sa naslovom, ali hteo sam da vas zainteresujem :-)

Tabele:
[ARTIKLI]
id_artikla int,
naziv_artikla nvarchar(50)
grupa char(5),
podgrupa char(5),


[RABATI]
id_komitenta int,
id_artikla int,
grupa char(5),
podgrupa char(5),
rabat numeric

U tabeli [RABATI] se nalaze rabati za pojedine artikle ka nekom komitentu. Dakle, ne moraju dva komitenta imati iste rabati za jedan artikal.
Cim otvorimo novog komitenta (u trecoj tabeli, ovde nije bitna), dajemo inicijalni rabat koji se upisuje u tabelu [RABATI] kao:

<id_komitenta1>, NULL, NULL, NULL, <rabat1>


Da stvar bude gora, imamo i grupe i podgrupe artikala koji se nude kao pomoc u radu sa artiklima. Ako se zada rabat 5% jednoj grupi artikala za komitenta X, a potom se izvuce artikal A, i da se rabat 10% samo za taj artikal (a on slucajno pripada onoj grupi artikala koja je gore navedena), trebalo bi da rezultat bude da svi artikli iz grupe, osim artikla A, imaju 5% rabati, a artikal A 10% rabati.

Dakle, prioritet ima niza, preciznija odrednica. Ako trazim tacno rabat za jedan artikal prema jednom komitentu, trazicu sledecim redosledom:
1. da li postoji u tabeli [RABATI] slog sa id_komitent i id_artikla kojeg zelim. Ako postoji, uzimam taj rabat i zavrsavam posao
2. da li postoji u tabeli [RABATI] slog sa id_komitent i podgrupa (ona kojoj pripada artikal) koje zelim, ako ima, uzimam rabat i gotovo
3. da li postoji u tabeli [RABATI] slog sa id_komitent i grupa (ona kojoj pripada artikal) koje zelim, ako ima, uzimam rabat i gotovo
4. uzimam iz tabele rabat koja se pruza tom komitentu, mimo svih ostalih kriterijuma (dakle, to mi je neka DEFAULT vrednost, ona unesena prilikom kreiranja samog komitenta)

Ideja je napraviti sproc (koristim sql server 2005) kojem se prosledjuju parametri
@id_artikla = null,
@id_komitenta = null,
@grupa = null,
@podgrupa = null,
(dakle, DEFAULT vrednosti su NULL)

koja ce:
1. ako joj se prosledi samo id_artikla, naci sve njene rabati po kojima se prosledjuje svim komitentima
2. ako joj se prosledi samo komitent, naci sve artikle sa rabatima koje idu tom komitentu
3. grupa, podgrupa, to su specijalni slucajevi ovog gore

Problem je izdvojiti slucajeve, a nikako to ne uspevam da uradim selectom i joinom. Postoje artikli koji ne pripadaju nijednoj podgrupi ili grupi, a zatim, postoje podgrupe i grupe kojima nije eksplicitno data rabat u tabeli vec se oslanjaju na default vrednost.

Ubedljivo najgora zamisao mi je da idem petljom ili CURSORom kroz artikle i pitam ih "hej, da li ima rabat za tebe? nema!? ok, kojoj podgrupi pripadas? Da li ima rabat za nju?..." Nadam se da neko ima neku bolju zamisao mozda koriscenje neke funkcije koju ja ne znam (padala mi je na um COALESCE, ali... pf...)
Umoram sam, idem kuci, sad kad citam sta sam vam ovde napisao, nije ni meni najjasnije, valjda ce neko biti odmorniji, pa ce makar malo razmisliti o problemu

---

Moras da nam das CREATE TABLE za tabele i da nam das nekoliko INSERT INTO naredbi, sa test podacima. Test podaci treba da su takvi da pokrivaju sve slucajeve koje zelis da resavamo.

Zadatak je interesantan.

Ne zaboravi da se rabat menja i u vremenu. t.j. da je za jednog komitenta i jedan artikl (ili grupu) različit u različitim periodima. Zato u tabelu RABATI treba dodati i polje za datum od koga rabat važi.

@Fitopatolog: Sacekajmo da dobijemo tabele i test podatke. U mnogim slucajevima mozgalica ne bi bila mozgalica kad bi sistem bio perfektno projektovan. Medjutim, iz ko zna koajih razloga, opravdanih i neopravdanih, sistemi su takvi kakvi su i moramo s njima da radimo. Zato radimo nekad vratolomije da nateramo ne tako srecno projektovan sistem da radi kao da je sve u redu.

Koliko treba da budemo uporni da bi smo dobili definicije tabela i test podatke? Ili je problem resen, pa je autor mozgalice izgubio interesovanje?

Ops, evo me
Izvinite, cak ste me malo i zbuniti sto ste toliko zainteresovani :-) Sad je kraj radnog vremena, pa mogu malo...

Dakle, ako ste procitali prvi post, necu da se ponavljam

CREATE TABLE [Artikli] (
id_artikla int identity not null,
naziv_artikla nvarchar(50) not null
grupa char(5) null,
podgrupa char(5) null
CONSTRAINT [PK_Artikli] PRIMARY KEY CLUSTERED
(
[id_artikla] ASC
)
)

CREATE TABLE RABATI (
id_komitenta int not null,
id_artikla int not null,
grupa char(5) null,
podgrupa char(5) null,
rabat numeric(5,2) not null
CONSTRAINT [PK_Rabati] PRIMARY KEY CLUSTERED
(
[id_komitenta] ASC,
[id_artikla] ASC
)
)

Tako bi, valjda, trebalo da izgledaju definicije tabela
Sad zamislimo 2 komitenta: 1 i 2
Artilkli se dele u 3 grupe: G1, G2, G3
Grupa G1 ima 3 podgrupe PG1, PG2, PG3
Artikle cu oznacavati (trocifrenim) brojevima 101, 102, 103... i po potrebi cu ih dodavati

-------------------------------------------------------
--KOD:
--Za pocetak, dacu komitentu 1 default rabat 3%, a komitentu 2, default rabat 5%:

insert into Rabati (id_komitenta, id_artikla, grupa, podgrupa, rabat)
values(1, null, null, null, 3)

insert into Rabati (id_komitenta, id_artikla, grupa, podgrupa, rabat)
values(2, null, null, null, 5)


--Sada cu prema komitentu 1, sve artikle koji pripadaju grupi G1, (jer on to dobro prodaje) dati rabat 8%

insert into Rabati (id_komitenta, id_artikla, grupa, podgrupa, rabat)
values(1, null, G1, null, 8)

--Ipak, jako mi je skupa podgrupa PG3 i za taj mali broj artikala spusticu rabat na 6 %

insert into Rabati (id_komitenta, id_artikla, grupa, podgrupa, rabat)
values(1, null, G1, PG3, 6)

--Na kraju, izvesni artikal koji pripada toj grupi G1 i njenoj podgrupu PG3, a oznaka mu je 113, je na zescoj rasprodaji, jer mu rok istice za vrlo brzo :-)

insert into Rabati (id_komitenta, id_artikla, grupa, podgrupa, rabat)
values(1, 113, null, null, 20)
insert into Rabati (id_komitenta, id_artikla, grupa, podgrupa, rabat)
values(2, 113, null, null, 20)

--Ovde ne moram stavljati G1 i PG3, jer je setovan artikal - to je najuza odrednica, jer je id_artikla jedinstveni identifikator
--(za razliku od podgrupe, jer moze postojati podgrupa PG1 i u G1 i u G2 i u G3, pa je za identifikaciju neophodan par (grupa, podgrupa)

/*
E, sada, slicno tabele IP rutiranja, kad trazimo za jedan artikal, prema jednom komitentu odgovarajuci rabat, provericemo da li je konkretno tom
artiklu dodeljen rabat, ako ne, videcemo da li je dodeljen njegovoj podgrupi, ako ne, videcemo za njegovu grupu i na kraju, kao default, dacemo mu
rabat koji je na samom pocetku, kao obavezan korak, dat "celom" komitentu
Neka je komitent 1, a artikal 101
*/

select COALESCE(
select rabat from Rabati where id_komitenta=1 and id_artikla=101, --1

select rabat from Rabati as R --2
inner join Artikli as A on R.id_artikla=A.id_artikla
where R.id_komitenta=1 and R.grupa=A.grupa and R.podgrupa=A.podgrupa,

select rabat from Rabati as R --3
inner join Artikli as A on R.id_artikla=A.id_artikla
where R.id_komitenta=1 and R.grupa=A.grupa,

select rabat from Rabati where id_komitenta=1 --4
)

/*
Ovo gore je neki, nazovi, pseudo kod, jer nisam ovo realizovao. Sustina je da COALESCE vraca prvu NENULL vrednost od izraza
Ako prodje odrednica prema komitentu i artiklu, prolazi prvi izraz i prica je gotova;
ako on vrati NULL, izvrsava se drugi statement koji vezuje po grupi i podgupi;
treci izraz se izvrsava ako drugi vrati NULL, a
cervrti izraz vraca DEFAULT rabat za citavog komitenta

Ako postavimo id_artikla=113, trebalo bi da se izvrsi samo prvi red, a ako stavimo neki koji ne pripada nijednoj eksplicitno navedenoj grupi ili podgrupi,
ide do kraja do navedenih 3%
*/

-----------------------------------------------------------------------------------------

U cemu je problem?
Kod unosa pojedinih artikala, stvar je trivijalna, kao i kod vadjenja rabati za jedan artikal, npr za fakturu (to bi radilo kao ono gore).
I kod unosa, uopste, grupa, podgrupa, stvar je jasna.

Problem je kad mi komercijalista kaze:
"Hocu u izvestaju sve artikle grupe G1 prema komitentu 1 da mi izlistas rabati"
Dakle?
Treba izvuci sve artikle koji pripadaju toj grupi, proveriti za svakog od njih da li je eksplicitno zadat rabat za njega, ako nije, videti kojoj podgrupi pripada,
pa grupi, pa mu dati na kraju default rabat.
A tako za svakog.
Padaju mi na pamet CURSORi, koji ce fetchovati artikle date grupe i pozivati neku funkciju koja lici na onu gore, ali to bi, slozicete se, biti previse sporo

Slicno je i sa zahtevom "Zanima me za artikal 202, sa kojim rabatima ide ka kom komitentu?"
Opet mi pada na pamet CURSOR kojeg fetchujem iz tabele komitenata pa svaki kursor ponaosob guram kao parametar gornjoj funkciji

ne vidim koji JOIN da stavim izmedju [Artikli] i [Rabati] da se poklopi ono sto zelim?

hvala na interesovanju
(mislim da sam bio prilicno precizan u objasnjavanju problema, gotovo profesorski :-) )

---

Je l' ti nas zavitlavas ili sta?

CREATE TABLE [Artikli] javlja gresku, nedostaje ti zarez.



Dobro, to smo nekako resili i kreirali tabelu. Druga prolazi OK.

Medjutim, vecina INSERT naredbi ne prolazi jer nemas id_artikla za koji si rekao da je NOT NULL.


Ispada da u tabelu Rabati ne mozes da spakujes 'inicijalni slucaj'. Ako dozvolis da ID_Artikla bude NULL, moze se desiti da imas vise 'inicijalnih slucajeva' za istog komitenta, jer nicim ne sprecavas takve duplikate. Prema tome, dizajn tabela nije 'donekel imperfektan' nego uopste ne moze da podrzi ono sto si zamislio onako kako si zamislio. Stogae zadatak ne moze postaviti valjano.

Ponovo se potvrdjuje staro pravilo: ako izgleda da nesto ne moze da se uradi u SQL, onda je po sredi greska u dizajnu modela baze podataka.

Hajde lepo smisli sta hoces, licno unesi podatke, daj nam ispravne skripte, pa da onda pricamo. pitanje je bilo "kako za ovako postavljene tabele odgovoriti na sledeca pitanja". Mozda je bolje "kako da napravim tabele da bih mogao da odgovaram na sledeca pitanja"

OK, OK, stidim se... :-)
Tabele nisam realizovao na taj nacin, nego drugacije, ali sam tako hteo, zato sam improvizovao ovo sto sam napisao.
U tabeli [Rabati] PK nije navedeni par, nego moze biti posebni id_rabata, int, identity

---

Nemoj da improvizujes. Smisli kako hoces da napravis tabele, napravi ih, popuni ih test podacima i onda nam sve to posalji. Jako je tesko raditi sa nedorecenim idejama i improvizacijama. Evo vec treci dan ne mozemo da pocnemo da resavamo mozgalicu jer ne postujes pravilo o zadavanju mozgalice. Svi mi koji saradjujemo na forumu nemamo vremena za bacanje i ocekujemo da se barem to vreme poštuje.

Ja bih sve te tabele spojio left join-om, a zatim iz procedure vratio prvo polje od polja sa rabatima koje nije prazno.
Mislim da je brze da spajanjem povuces i neke nepotrebne vrednosti, nego da pretrazujes svaku tabelu za svaki rabat.