I am working on SQL server 2005 and Delphi xe7 and using adoquery.
We have sales invoices for our customer. As the goverment wish we have to report totals of customers every month over 5000 (excluded from VAT).
This can seems easy but we have different VAT values in invoices so i have to get correct values If the invoice has One VAT value or Two or Three.
Here is the basic select sentence of me.
(mf.fis_aciklama1 like '%U SFA%')or (mf.fis_aciklama1 like '%U ÝAF%')
This where clause is enough to getting Sales invoices. This is ok. At the below select ;
I have read next row for 600 accounting number for shipment total and one next row for get the VAT Value. You can see the ABS function lines and subselects for this.
Now the question;
"How can i learn if i have to read next 2 row" or not?
How can create select sentence like this with "if".
Actually i have an idea but i dont have pratice how the create sentence for select this.
IF the fis_meblag0 of first row>fis_meblag0 of next+ fis_meblag0 next+ next;
Then we have to read more row but but to write this in sql.
select mf.fis_tarih as Tarih,mf.fis_hesap_kod as HesapKod,
mh.muh_hesap_isim1 as Unvan,
mf.fis_aciklama1 as Aciklama,
mf.fis_yevmiye_no as Yno,
mf.fis_sira_no as Sno,
mf.fis_meblag0 as Tutar,
ABS((Select top 1 mf1.fis_meblag0 from dbo.MUHASEBE_FISLERI mf1 where
mf.fis_yevmiye_no=mf1.fis_yevmiye_no and
mf.fis_tur=mf1.fis_tur and
mf1.fis_RECno=mf.fis_RECno+1 and left(mf1.fis_hesap_kod,3)='600')) as Matrah,
Abs((Select top 1 mf2.fis_meblag0 from dbo.MUHASEBE_FISLERI mf2 where
mf.fis_yevmiye_no=mf2.fis_yevmiye_no and
mf.fis_tur=mf2.fis_tur and
mf2.fis_RECno=mf.fis_RECno+2 and left(mf2.fis_hesap_kod,3)='391')) as KDV,
cr.cari_VergiKimlikNo as Vno
from dbo.MUHASEBE_FISLERI mf
left join dbo.MUHASEBE_HESAP_PLANI mh on mf.fis_hesap_kod=mh.muh_hesap_kod
left join dbo.CARI_HESAPLAR cr ON mf.fis_hesap_kod=cr.cari_kod
where
mf.fis_tarih>='20130501' and mf.fis_tarih<='20130531'
and
((mf.fis_aciklama1 like '%U SFA%') or (mf.fis_aciklama1 like '%U ÝAF%')) order by mf.fis_hesap_kod
Aucun commentaire:
Enregistrer un commentaire