select case 1 when grouping(a.dealerCode) then ''총계'' when grouping(b.largeCode) then ''소계'' else DBO.UF_BASE_BigSellerCode(a.dealerCode,a.storeCode,a.sellerCode,b.largeCode)end BigSellerCode, ' ' ' ' ' ' ' group by rollup( (DBO.UF_BASE_BigSellerCode(a.dealerCode,a.storeCode,a.sellerCode,b.largeCode),a.dealerCode), (a.storeCode,a.sellerCode,b . . . .. . )) order by case when DBO.UF_BASE_BigSellerCode(a.dealerCode,a.storeCode,a.sellerCode,b.largeCode) is null then 1 else 0 end desc, DBO.UF_BASE_BigSellerCode(a.dealerCode,a.storeCode,a.sellerCode,b.largeCode) desc , . . . . .
bigsellercode | storename | modifierdate | stockinday |
A0173789 | a3호)호연숙(보건대본관) | 20170228 | 20170302 |
A0173789 | a3호)호연숙(보건대본관) | 20170228 | 20170302 |
소계 | NULL | NULL | NULL |
A0173361 | 9호)경기여성능력개발센터 | 20220520 | 20170302 |
A0173361 | 9호)경기여성능력개발센터 | 20220520 | 20170302 |
소계 | NULL | NULL | NULL |
미지정입니다. | 2호)세원화성 | 20170228 | 20170302 |
총계 NULL NULL NULL
이 결과를 desc로 만들었는데요.. 이상태에서 또
A0173361 , 9호)경기여성능력개발센터 를 맨위로 가게 할순 없나요? 다시 오름차순으로 영어로 시작하는것들만 한에서는 오름차순으로 하고싶어요
select case 1 when grouping(a.dealerCode) then ''총계'' when grouping(b.largeCode) then ''소계'' ' else DBO.UF_BASE_BigSellerCode(a.dealerCode,a.storeCode,a.sellerCode,b.largeCode)end BigSellerCode, if(@A_dealerCode='1050') begin set @qry=@qry+char(13)+' isnull(DBO.UF_STORENAME(a.dealerCode,a.storeCode),''오산물류센터'') storeName, end else begin DBO.UF_STORENAME(a.dealerCode,a.storeCode) storeName, end convert(varchar,a.modifierdate,112) modifierdate, stockinday, ' case grouping(a.dealerCode) when 0 then (case when a.orders_restrictcount is null then isnull(b.restrictCount,'''') else a.orders_restrictcount end)end as restrictCount, isNull(b.sellerMaterialCode,'''') as sellerMaterialCode, b.materialName, b.dimension, sum((a.materialqty-a.rtnqty)) as RealQty, DBO.UF_UNITNAME(b.unitcode) as unitName, a.remark, a.modifierdate, b.isuse, DBO.UF_DealerInfoName(a.dealerCode,a.storeCode) as DealerInfoName, a.storeCode, ' a.sellingPrice, ' sum(a.sellingAmt) sellingAmt, ' sum(a.sellingVat) sellingVat, ' a.sellerCode, ' DBO.UF_SELLERNAME(a.dealercode,a.sellerCode) as sellerName, ' a.dealerCode, ' (select isnull(anotherclientcode,'''') from client where dealercode='''+@A_dealerCode+''' and clientcode=a.sellercode ) anothersellercode ' from orders a,material b ' where a.dealercode = '''+@A_dealerCode+'''' and a.stockinday between '''+@A_startDay+''' and '''+@A_endDay+'''' and a.dealercode = b.dealerCode ' and a.materialCode = b.materialCode'
전체쿼리는 이렇습니다!
소계총계는 넵 그대로 아래쪽에 위치하면 됩니다!
modifierdate , stockinday 둘다 orders 테이블입니다.