| Author |
 Topic  |
|
|
Black_Trouble
Starting Member
Turkey
8 Posts |
 Posted - 09/27/2012 : 14:36:41
|
Hello friends. Just joined the forum. I came across a very instructive code. My problem is to get top of the total. I had written the following code, I want to arrive at the conclusion.
SELECT CONVERT(DATETIME,FATHARTAR,104) AS TARIH,FATHARREFNO AS REFERANS,
FATHARCARKOD AS [CARI KOD],FATFIS.FATFISCARUNVAN AS [CARI ÜNVAN],
FATFIS.FATFISEVRAKNO1 AS [EVRAK NO],FATHARSTKKOD AS [STOK KODU],
FATHARSTKCINS AS [STOK CINSI],CONVERT(DECIMAL(18,3),FATHARFIYAT) AS [BIRIM FIYAT],
CONVERT(DECIMAL(18,2),FATHARMIKTAR) AS ADET,
CONVERT(DECIMAL(18,2),FATHARKDVMATRAH) AS MATRAH,CONVERT(DECIMAL(18,2),FATHARKDVTUTAR) AS [KDV TUTAR],
CONVERT(DECIMAL(18,2),FATHARTOPLAMTUT) AS [GENEL TOPLAM],ACIKLAMALAR.ACIKDETAY AS [DETAY] FROM FATHAR
INNER JOIN FATFIS ON FATHAR.FATHARCARKOD=FATFIS.FATFISCARKOD AND FATHAR.FATHARREFNO=FATFIS.FATFISREFNO
INNER JOIN ACIKLAMALAR ON ACIKLAMALAR.ACIKREFNO=FATHAR.FATHARREFNO
WHERE FATHARTAR BETWEEN CONVERT(DATETIME,@tarih,104) AND CONVERT(DATETIME,@tarih1,104)
ORDER BY FATFISCARUNVAN,FATHARTAR ASC
the result:
TARIH |REFERANS|CARI KOD| UNVAN |EVRAK NO|STOK KODU| CINS |BIRIM FIYAT| MIKTAR|MATRAH|KDV |TOPLAM
-------------------------------------------------------------------------------------------------------
2012-09-07 |15 |A001 |AAA |A-171250| 001 |YEMEK | 5,95 | 10 |59,50 |4,76|64,26
2012-09-14 |20 |A001 |AAA |A-171275| 001 |YEMEK | 5,80 | 10 |58,00 |4,64|62,64
2012-09-21 |250 |A001 |AAA |A-171282| 001 |YEMEK | 5,25 | 10 |52,50 |4,20|56,70
2012-09-07 |283 |A002 |BBB |A-171300| 001 |YEMEK | 5,95 | 10 |59,50 |4,76|64,26
2012-09-21 |250 |A002 |BBB |A-171350| 001 |YEMEK | 5,80 | 10 |58,00 |4,64|62,64
2012-09-21 |250 |A002 |BBB |A-171255| 001 |YEMEK | 5,25 | 10 |52,50 |4,20|56,70
------------------------------------------------------------------------------------------------------
I want the result:
TARIH |REFERANS|EVRAK NO|STOK KODU| CINS |BIRIM FIYAT |MIKTAR|MATRAH |KDV |TOPLAM
-------------------------------------------------------------------------------------------
A001 - AAA TOTAL | 30 | 170,00|13,60|183,60
-------------------------------------------------------------------------------------------
2012-09-07 |15 |A-171250| 001 |YEMEK | 5,95 | 10 |59,50 |4,76 | 64,26
2012-09-14 |20 |A-171275| 001 |YEMEK | 5,80 | 10 |58,00 |4,64 | 62,64
2012-09-21 |250 |A-171282| 001 |YEMEK | 5,25 | 10 |52,50 |4,20 | 56,70
-------------------------------------------------------------------------------------------
A002 - BBB TOTAL | 30 | 170,00|13,60|183,60
-------------------------------------------------------------------------------------------
2012-09-07 |283 |A-171300| 001 |YEMEK | 5,95 | 10 |59,50 |4,76 | 64,26
2012-09-21 |250 |A-171350| 001 |YEMEK | 5,80 | 10 |58,00 |4,64 | 62,64
2012-09-21 |250 |A-171255| 001 |YEMEK | 5,25 | 10 |52,50 |4,20 | 56,70
-------------------------------------------------------------------------------------------
Thank you very much in advance can help you to friends.
|
|
|
bitsmed
Posting Yak Master
Denmark
100 Posts |
Posted - 09/27/2012 : 16:13:47
|
Normally you should be doing the formating in your frontend program.
However, you could do something like:
select tarih
,farharrefno as referans
,case
when total='y' then fatharcarkod+' - '+fatfiscarunvan+' total'
else fatfisevrakno1
end as [evrak no]
,fatfiscarunvan as [stok kodu]
,farharstkcins as [stok cinsi]
,fatharfiyat[birim fiyat]
,fatharmiktar as miktar
,fatharkdvmatrah as matrah
,fatharkdvtutar as [kdv tutar]
,fathartoplamtut as [genel toplam]
from (select 'n' as total
,convert(datetime,a.fathartar,104) as tarih
,a.farharrefno as referans
,a.fatharcarkod as [kari kod]
,b.fatfiscarunvan as [cari ünvan]
,b.fatfisevrakno1 as [evrak no]
,a.fatharstkkod as [stok kodu]
,a.farharstkcins as [stok cinsi]
,convert(decimal(18,3),a.fatharfiyat) as [birim fiyat]
,convert(decimal(18,2),a.fatharmiktar) as miktar
,convert(decimal(18,2),a.fatharkdvmatrah) as matrah
,CONVERT(decimal(18,2),a.fatharkdvtutar) as [kdv tutar]
,convert(decimal(18,2),a.fathartoplamtut) as [genel toplam]
from fathar as a
inner join fatfis as b
on b.fatfiscarkod=a.fatharcarkod
and b.fatfisrefno=a.fatharrefno
where a.fathartar between convert(datetime,@tarih,104) and convert(datetime,@tarih1,104)
union all
select 'y' as total
,null as tarih
,null as farharrefno
,a.fatharcarkod as fatharcarkod
,b.fatfiscarunvan as fatfiscarunvan
,null as fatfisevrakno1
,null as fatharstkkod
,null as farharstkcins
,null as fatharfiyat
,sum(convert(decimal(18,2),a.fatharmiktar)) as fatharmiktar
,sum(convert(decimal(18,2),a.fatharkdvmatrah)) as fatharkdvmatrah
,sum(CONVERT(decimal(18,2),a.fatharkdvtutar)) as fatharkdvtutar
,sum(convert(decimal(18,2),a.fathartoplamtut)) as fathartoplamtut
from fathar as a
inner join fatfis as b
on b.fatfiscarkod=a.fatharcarkod
and b.fatfisrefno=a.fatharrefno
where a.fathartar between convert(datetime,@tarih,104) and convert(datetime,@tarih1,104)
group by a.fatharcarkod
,b.fatfiscarunvan
) as a
order by fatharcarkod
,fatfiscarunvan
,total desc
,tarih
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 09/27/2012 : 16:53:00
|
looks like report format to me which can very easily buid using nested containers in reporting tools like SSRS
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Black_Trouble
Starting Member
Turkey
8 Posts |
Posted - 09/28/2012 : 08:34:29
|
| Thank you very much. It looks like exactly what I want. |
|
|
| |
Topic |
|
|
|
SQL server Case Grouping problem
