SQL server Case Grouping problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL server Case Grouping problem

Author

Topic

Black_Trouble
Starting Member

21 Posts

Posted - 2012-09-27 : 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.

[CODE]
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
[/CODE]

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
Aged Yak Warrior

545 Posts

Posted - 2012-09-27 : 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

52326 Posts

Posted - 2012-09-27 : 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

21 Posts

Posted - 2012-09-28 : 08:34:29

Thank you very much. It looks like exactly what I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources