Kwa wataalamu wa database karibuni

Kwa wataalamu wa database karibuni

Hujaeleza backend unatumia language ipi, unataka just a query ambayo inakuja na date automatically kupitia mysql?

Mfano kwenye column yako inaonekana
Viazi villingia 240 tarehe 21 feb alafu vikatoka 70 tarehe 17 may maana yake vimebaki viazi 170.
Kutafuta difference si unachukua tu hizo tarehe mbili unatoa. Language zote zina date library, ukisubtract unachagua unataka upate results in days au hours, minutes e.t.c

Hujaeleza unataka data katika format ipi, sasa inakua ngumu kukujibu, usielezee juu juu inakua ngumu sana kukujibu.
Njia rahisi zaidi ni hii, mfano wa kutumia viazi

Code: 
SELECT * FROM <your table> WHERE item_id = "viazi001"

Ukishapata all your results unachofanya ni kuloop through. Mfano data zako umezipokea kwenye variable inaitwa viazi

Code: 
viazi[0] = { type: in, date: 1/1/2018, qty: 100 }
viazi[1] = { type: out, date: 2/1/2018, qty:3 }

Ukichukua viazi[1].date - viazi[0].date unapata viazi 3 vilichukua siku 1 kuuzwa, hapa sasa subtract inategemenea na language unayotumia, kila language ina library tofauti. Unaloop through results zote huku unareturn the differences, unaweza pia tafuta average days to sell 1 kiazi e.t.c. You can do quite a lot, inategemea tu unataka kutumia data kufanya nini.

Hapa tatizo linakuja kuna IN na OUT, sasa leo vimeingia, vikiingia vingine wiki ijayo kabla ya hivi kuisha then hapo utarudisha tofauti ipi? utatumia siku ya leo au wiki ijayo?
Ingekua vinaisha vyote kabla ya kununua vingine then ingekua rahisi, unaloop hadi ambapo remaining quantity ni zero then unareturn kua viliuzwa vyote kwa siku kadhaa.

Muulize huyo anayetaka hilo jibu akwambie in details anachokitaka hasa ni kipi.
 
Nafikiri kitu cha kwanza inabidi ufanye normalization ya database yako. Utakuwa na row insert nyingi sana (unnecessary).
Ushauri wangu ondoa column ya handling_type. Column zako ziwe item_id, handling_IN_date, handling_OUT_date and quantity. Hii inamaanisha single item itakuwa na single row badala ya single item kuwa na row 2 ya IN na OUT.

Kwa hiyo sasa, item ikiingia, date itawekwa kwenye handling_IN_date na siku ikitoka uta update hiyo item row (badala ya kuinsert another row) kwenye handling_OUT_date.

Then, kitu kingine, database unayotumia ni muhimu kuspecify, queries azifanani across different databases.

Lastly, unataka data zije kwenye form ya report kama document, excel au kwenye php au whatever?

Leta mrejesho usaidiwe.
 
Graph said:
Hujaeleza backend unatumia language ipi, unataka just a query ambayo inakuja na date automatically kupitia mysql?

Mfano kwenye column yako inaonekana
Viazi villingia 240 tarehe 21 feb alafu vikatoka 70 tarehe 17 may maana yake vimebaki viazi 170.
Kutafuta difference si unachukua tu hizo tarehe mbili unatoa. Language zote zina date library, ukisubtract unachagua unataka upate results in days au hours, minutes e.t.c

Hujaeleza unataka data katika format ipi, sasa inakua ngumu kukujibu, usielezee juu juu inakua ngumu sana kukujibu.
Njia rahisi zaidi ni hii, mfano wa kutumia viazi

Code: 
SELECT * FROM <your table> WHERE item_id = "viazi001"

Ukishapata all your results unachofanya ni kuloop through. Mfano data zako umezipokea kwenye variable inaitwa viazi

Code: 
viazi[0] = { type: in, date: 1/1/2018, qty: 100 }
viazi[1] = { type: out, date: 2/1/2018, qty:3 }

Ukichukua viazi[1].date - viazi[0].date unapata viazi 3 vilichukua siku 1 kuuzwa, hapa sasa subtract inategemenea na language unayotumia, kila language ina library tofauti. Unaloop through results zote huku unareturn the differences, unaweza pia tafuta average days to sell 1 kiazi e.t.c. You can do quite a lot, inategemea tu unataka kutumia data kufanya nini.

Hapa tatizo linakuja kuna IN na OUT, sasa leo vimeingia, vikiingia vingine wiki ijayo kabla ya hivi kuisha then hapo utarudisha tofauti ipi? utatumia siku ya leo au wiki ijayo?
Ingekua vinaisha vyote kabla ya kununua vingine then ingekua rahisi, unaloop hadi ambapo remaining quantity ni zero then unareturn kua viliuzwa vyote kwa siku kadhaa.

Muulize huyo anayetaka hilo jibu akwambie in details anachokitaka hasa ni kipi.
Click to expand...
mkuu nimekupata kuna kitu nilipuuzia ila umakini wako umefumbua kosa langu nilidhani mtazingatia item_id na handling_date pekee lakini quantity nayo imeleta jambo la kufikirisha nikiangalia kwenye table ni kuwa mzigo ukifika IN na ukitoka OUT unatoka wote hivo kwenye quantity ya out ilitakiwa kuwa sawa na quantity ya IN sorry kwa kuchanganya hivyo vikija viazi vingine IN basi vitatumia item_id viazi002 kwaiyo hata kutoka OUT itakuwa viazi002.

Note: najua kutafuta date difference endapo tarehe ya in na out zingekuwa kwenye column tofauti. ningetumia item_id ku match

kinachotakiwa hasa kujua ni jee viazi001 viliingia tarehe 12-09-2018 na kutoka tarehe 20-09-2018 je vilikaa siku ngapi kwetu kabla ya kutoka shida inakuja tarehe ya kuingia mzigo na kutoka ipo kwenye column moja kwaiyo inakuwa shida kuandika query.
 
Habari mkuu,

Hii structure kama wewe ndiye admin unaweza kuibadilisha, na wala sio kazi kubwa. Itakurahisishia kazi sana.

Mimi ningefanya hivi:

date_of_arrival = SELECT handling_date FROM table WHERE item_id = VIAZI001 AND handling_type = IN;
date_of_departure = SELECT handling_date FROM table WHERE item_id = VIAZI001 AND handling_type = OUT;

Then the diffrence in days will be:

SELECT DATEDIFF(day, date_of_arrival, date_of_departure);
 
shalet said:
mkuu nimekupata kuna kitu nilipuuzia ila umakini wako umefumbua kosa langu nilidhani mtazingatia item_id na handling_date pekee lakini quantity nayo imeleta jambo la kufikirisha nikiangalia kwenye table ni kuwa mzigo ukifika IN na ukitoka OUT unatoka wote hivo kwenye quantity ya out ilitakiwa kuwa sawa na quantity ya IN sorry kwa kuchanganya hivyo vikija viazi vingine IN basi vitatumia item_id viazi002 kwaiyo hata kutoka OUT itakuwa viazi002.

Note: najua kutafuta date difference endapo tarehe ya in na out zingekuwa kwenye column tofauti. ningetumia item_id ku match

kinachotakiwa hasa kujua ni jee viazi001 viliingia tarehe 12-09-2018 na kutoka tarehe 20-09-2018 je vilikaa siku ngapi kwetu kabla ya kutoka shida inakuja tarehe ya kuingia mzigo na kutoka ipo kwenye column moja kwaiyo inakuwa shida kuandika query.
Click to expand...

Kama ni hivyo then mbona kazi imekua rahisi sana.
Fanya query uselect all items ila usort by item_id.
Unaweza fanya hii calculation kwa mysql tena ukatunza kabisa procedure ila I recommend kutumia programming language uwe more flexible.

Then loop through all of them unaangalia in date na out unatoa, unaweza andaa table nyingine tofauti kabisa ukatunza results, colums zaweza kua item_id alafu sold_after_days ukawa unaloop through huku una-save.

We una tarehe mbili kabisa unashindwa vipi kutoa kupata jibu in days? No offense ila kama hujui kutafuta the difference between two dates sidhani kama unatakiwa uchukue hiyo kazi maana ni kitu very basic kuna library nyingi mno, usijeua database ya watu.
 
Graph said:
Kama ni hivyo then mbona kazi imekua rahisi sana.
Fanya query uselect all items ila usort by item_id.
Unaweza fanya hii calculation kwa mysql tena ukatunza kabisa procedure ila I recommend kutumia programming language uwe more flexible.

Then loop through all of them unaangalia in date na out unatoa, unaweza andaa table nyingine tofauti kabisa ukatunza results, colums zaweza kua item_id alafu sold_after_days ukawa unaloop through huku una-save.

We una tarehe mbili kabisa unashindwa vipi kutoa kupata jibu in days? No offense ila kama hujui kutafuta the difference between two dates sidhani kama unatakiwa uchukue hiyo kazi maana ni kitu very basic kuna library nyingi mno, usijeua database ya watu.
Click to expand...
mkuu kutumia programing lang ndiyo njia tuliyotumia niliamua kushare ili kupata maoni tofauti ya wadau ilikuwa ni issue serios sababu kuna mizigo mingi ilikuwa inakaa zaidi ya siku zinazoruhusiwa bila taarifa hivo tulitakiwa andika orodha ya mizigo na siku ilizokaa.
 
Hiyo DateDiff ni SQL Server specific so utabadili kutokana na DB gani unatumia na column zako ni za type gani.

Table:
Code: 
+---+-----------+------------------+---------------+-----------+
| id|  item_id  |  handling_type   | handling_date | quantity  |
+---+-----------+------------------+---------------+-----------+
| 1 | viazi_001 | IN               |  2018-01-10   | 200       |
+---+-----------+------------------+---------------+-----------+
| 2 | mayai_001 | IN               |  2018-01-01   | 150       |
+---+-----------+------------------+---------------+-----------+
| 3 | viazi_001 | OUT              |  2018-02-11   | 200       |
+---+-----------+------------------+---------------+-----------+



Code: 
--Vitu ambavyo bado havijatoka na vimekaa siku ngapi
select item_id, quantity, handling_date,(DATEDIFF(DAY,handling_date,GETDATE())) as [Number of Days in Storage]
from handling where handling_type='IN'
and item_id not in (
select item_id from handling where handling_type='OUT'
)

--Vitu vilivyotoka na vilikaa siku ngapi
select h.item_id, h.quantity, h.handling_date,(DATEDIFF(DAY,h.handling_date,h2.handling_date)) as [Number of Days in Storage] 
from handling h
JOIN handling h2 ON h.item_id = h2.item_id
WHERE h.handling_type='IN'
AND h2.handling_type='OUT'
 
Kang said:
Hiyo DateDiff ni SQL Server specific so utabadili kutokana na DB gani unatumia na column zako ni za type gani.

Table:
Code: 
+---+-----------+------------------+---------------+-----------+
| id|  item_id  |  handling_type   | handling_date | quantity  |
+---+-----------+------------------+---------------+-----------+
| 1 | viazi_001 | IN               |  2018-01-10   | 200       |
+---+-----------+------------------+---------------+-----------+
| 2 | mayai_001 | IN               |  2018-01-01   | 150       |
+---+-----------+------------------+---------------+-----------+
| 3 | viazi_001 | OUT              |  2018-02-11   | 200       |
+---+-----------+------------------+---------------+-----------+



Code: 
--Vitu ambavyo bado havijatoka na vimekaa siku ngapi
select item_id, quantity, handling_date,(DATEDIFF(DAY,handling_date,GETDATE())) as [Number of Days in Storage]
from handling where handling_type='IN'
and item_id not in (
select item_id from handling where handling_type='OUT'
)

--Vitu vilivyotoka na vilikaa siku ngapi
select h.item_id, h.quantity, h.handling_date,(DATEDIFF(DAY,h.handling_date,h2.handling_date)) as [Number of Days in Storage]
from handling h
JOIN handling h2 ON h.item_id = h2.item_id
WHERE h.handling_type='IN'
AND h2.handling_type='OUT'
Click to expand...
Mkuu query zako ziko sahihi 100% shukran kwa kuchangia hii kazi ilinitoa jasho lakini nadhani sikuwa na muda wa kutosha na presha za wakubwa.
 
shalet said:
mkuu nimeikuta hiyo database ya kampuni ni mtihani kweli kweli hapo kuna field nimeziondoa ila zinazotumika ni hizo cha ajabu vendor ukimpata task hiyo anakupa majibu lakin query hakupi na unalipia kila ukimpa task.
Click to expand...
Basi fanya kama nilivokueleza
 
Graph said:
Hujaeleza backend unatumia language ipi, unataka just a query ambayo inakuja na date automatically kupitia mysql?

Mfano kwenye column yako inaonekana
Viazi villingia 240 tarehe 21 feb alafu vikatoka 70 tarehe 17 may maana yake vimebaki viazi 170.
Kutafuta difference si unachukua tu hizo tarehe mbili unatoa. Language zote zina date library, ukisubtract unachagua unataka upate results in days au hours, minutes e.t.c

Hujaeleza unataka data katika format ipi, sasa inakua ngumu kukujibu, usielezee juu juu inakua ngumu sana kukujibu.
Njia rahisi zaidi ni hii, mfano wa kutumia viazi

Code: 
SELECT * FROM <your table> WHERE item_id = "viazi001"

Ukishapata all your results unachofanya ni kuloop through. Mfano data zako umezipokea kwenye variable inaitwa viazi

Code: 
viazi[0] = { type: in, date: 1/1/2018, qty: 100 }
viazi[1] = { type: out, date: 2/1/2018, qty:3 }

Ukichukua viazi[1].date - viazi[0].date unapata viazi 3 vilichukua siku 1 kuuzwa, hapa sasa subtract inategemenea na language unayotumia, kila language ina library tofauti. Unaloop through results zote huku unareturn the differences, unaweza pia tafuta average days to sell 1 kiazi e.t.c. You can do quite a lot, inategemea tu unataka kutumia data kufanya nini.

Hapa tatizo linakuja kuna IN na OUT, sasa leo vimeingia, vikiingia vingine wiki ijayo kabla ya hivi kuisha then hapo utarudisha tofauti ipi? utatumia siku ya leo au wiki ijayo?
Ingekua vinaisha vyote kabla ya kununua vingine then ingekua rahisi, unaloop hadi ambapo remaining quantity ni zero then unareturn kua viliuzwa vyote kwa siku kadhaa.

Muulize huyo anayetaka hilo jibu akwambie in details anachokitaka hasa ni kipi.
Click to expand...

Huyo aliyetengeneza hiyo DB arudi darasani tu
 
You must log in or register to reply here.

Similar Discussions

Our Community

Coming Soon

Regional Communities

Coming Soon
Back
Top Bottom
Menu
Forums
New posts
Log in
Register