SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL server Case Grouping problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 09/27/2012 :  14:36:41  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

323 Posts

Posted - 09/27/2012 :  16:13:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/27/2012 :  16:53:00  Show Profile  Reply with Quote
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/

Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 09/28/2012 :  08:34:29  Show Profile  Reply with Quote
Thank you very much. It looks like exactly what I want.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000