꿈꾸는 개발자, DBA 커뮤니티 구루비

mssql 롤업정렬질문입니다

by 지메이비 [SQL Query] MSSQL [2022.11.24 13:55:58]

select 
		case 1 when grouping(refstockinday) then '총 계' when grouping(dealercode) then '일별합계' else refStockInDay end refstockinday, 
		dbo.UF_STORENAME(dealerCode,refStoreCode) as storeName, 
		sum(isNull(materialQty,'0')-isNull(rtnQty,'0')) as realQty, 
		sum(isNull(buyingAmt,'0')) as buyingAmt, 
		sum(isNull(buyingVat,'0')) as buyingAmt, 
		sum(isNull(buyingAmt,'0')+isNull(buyingVat,'0')) as buyingTotal 
	from orders 
	where dealerCode = '2002' 
	and refstockinday between '20170306'  and '20170315'
	and materialCode= '1000250' 
	and storeCode = '9999' 
	and refStoreCode is not null 
	and refStockInday is not null 
	group by rollup((refstockinday,refstorecode),(dealercode))

쿼리는 이렇구요 정렬방식은

dbo.UF_STORENAME(dealerCode,refStoreCode) as storeName -> refstockinday 순입니다..

order by에 grouping으로 묶으려고 해도

스칼라함수에서 가져오는 storename 때문에 뭐가 안되더라구요

어떤방법으로 해야하나요??

by 마농 [2022.11.24 14:40:06]

SELECT ...
  FROM orders a
 WHERE ...
 ORDER BY GROUPING(a.refstorecode), storeName, a.refstorecode, a.refstockinday
        , GROUPING(a.dealercode), a.dealercode
;

by 지메이비 [2022.11.24 15:27:08]

알려주신대로 했는데

20170307   구립서부중앙어린이집   3.500   15750   0   15750
20170308   구립서부중앙어린이집   1.000   4500   0   4500
20170309   구립서부중앙어린이집   1.000   4500   0   4500
20170308   구립은아새어린이집   1.000   4500   0   4500
20170310   구립은아새어린이집   2.000   9000   0   9000
20170313   구립은아새어린이집   3.000   13500   0   13500
20170307   농수산물공사강서지사   12.000   56520   0   56520
20170308   농수산물공사강서지사   8.000   37680   0   37680
20170310   농수산물공사강서지사   1.000   4710   0   4710
20170306   은곡유치원(중식)   1.500   6750   0   6750
20170307   은곡유치원(중식)   1.800   8100   0   8100
20170308   은곡유치원(중식)   1.000   4500   0   4500
20170309   은곡유치원(중식)   1.700   7650   0   7650
20170313   은곡유치원(중식)   3.500   15750   0   15750
20170314   은곡유치원(중식)   2.000   9000   0   9000
20170315   은곡유치원(중식)   1.700   7650   0   7650
20170309   청계숲유치원(중식)   3.000   13500   0   13500
20170314   청계숲유치원(중식)   3.000   13500   0   13500
20170315   청계숲유치원(중식)   3.000   13500   0   13500
총 계   NULL   54.700   250560   0   250560
일별합계   NULL   1.500   6750   0   6750
일별합계   NULL   17.300   80370   0   80370
일별합계   NULL   11.000   51180   0   51180
일별합계   NULL   5.700   25650   0   25650
일별합계   NULL   3.000   13710   0   13710
일별합계   NULL   6.500   29250   0   29250
일별합계   NULL   5.000   22500   0   22500
일별합계   NULL   4.700   21150   0   21150

이런식으로 깨져서 나옵니다

by 마농 [2022.11.24 16:22:25]

적용한 쿼리 보여주세요..
원하는 결과도 보여주세요.
그리고 쿼리를 다시 살펴보니 롤업 구문도 이상하네요. 롤업 구문 자체에도 문제가 있어 보입니다.

by 지메이비 [2022.11.24 16:27:16]

ALTER  Procedure [dbo].[US_PURCHASE_REPORT_NEIS_DELIVERYORDERLIST01_DETAIL_ubi](
@A_dealerCode	varchar(4),
@A_storeCode	varchar(4),
@A_materialCode	varchar(8),
@A_startDay	varchar(8),
@A_endDay	varchar(8)
)
as
begin

	declare @qry varchar(4000)
	
	set @qry = ''
	set @qry = @qry+char(13)+'	select '
	set @qry = @qry+char(13)+'		case 1 when grouping(refstockinday) then ''총 계'' when grouping(dealercode) then ''일별합계'' else refStockInDay end refstockinday, '
	set @qry = @qry+char(13)+'		dbo.UF_STORENAME(dealerCode,refStoreCode) as storeName, '
	set @qry = @qry+char(13)+'		sum(isNull(materialQty,''0'')-isNull(rtnQty,''0'')) as realQty, '
	set @qry = @qry+char(13)+'		sum(isNull(buyingAmt,''0'')) as buyingAmt, '
	set @qry = @qry+char(13)+'		sum(isNull(buyingVat,''0'')) as buyingAmt, '
	set @qry = @qry+char(13)+'		sum(isNull(buyingAmt,''0'')+isNull(buyingVat,''0'')) as buyingTotal '
	set @qry = @qry+char(13)+'	from orders '
	set @qry = @qry+char(13)+'	where dealerCode = '''+@A_dealerCode+''' '
	set @qry = @qry+char(13)+'	and refstockinday between '''+@A_startDay+'''  and '''+@A_endDay+'''' 
	set @qry = @qry+char(13)+'	and materialCode= '''+@A_materialCode+''' '

	if(@A_storeCode <> 'zzzz')
	begin
	set @qry = @qry+char(13)+'	and refStoreCode = '''+@A_storeCode+ ''' '
	end

	set @qry = @qry+char(13)+'	and storeCode = ''9999'' '
	set @qry = @qry+char(13)+'	and refStoreCode is not null '
	set @qry = @qry+char(13)+'	and refStockInday is not null '
	set @qry = @qry+char(13)+'	group by rollup((refstockinday,refstorecode),(dealercode)) '
	set @qry = @qry+char(13)+'  ORDER BY GROUPING(refstorecode), storeName, refstorecode, refstockinday, GROUPING(dealercode), dealercode'
	
	print(@qry)
	exec(@qry)

end

쿼리입니다

by 마농 [2022.11.24 16:33:20]

알려드린 것과 다르네요. a. 이 안보이네요.

by 지메이비 [2022.11.24 16:41:53]

USE [hub]
GO
/****** Object:  StoredProcedure [dbo].[US_PURCHASE_REPORT_NEIS_DELIVERYORDERLIST01_DETAIL_ubi]    Script Date: 2022-11-24 오후 1:20:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/**
'입고일자','지점명','실수량','매입금액','매입부가세','매입총액'

execute hub..US_PURCHASE_REPORT_NEIS_DELIVERYORDERLIST01_DETAIL '2002','1000077','20100701','20100826'
**/

ALTER  Procedure [dbo].[US_PURCHASE_REPORT_NEIS_DELIVERYORDERLIST01_DETAIL_ubi](
@A_dealerCode	varchar(4),
@A_storeCode	varchar(4),
@A_materialCode	varchar(8),
@A_startDay	varchar(8),
@A_endDay	varchar(8)
)
as
begin

	declare @qry varchar(4000)
	
	set @qry = ''
	set @qry = @qry+char(13)+'	select '
	set @qry = @qry+char(13)+'		case 1 when grouping(refstockinday) then ''총 계'' when grouping(dealercode) then ''일별합계'' else refStockInDay end refstockinday, '
	set @qry = @qry+char(13)+'		dbo.UF_STORENAME(dealerCode,refStoreCode) as storeName, '
	set @qry = @qry+char(13)+'		sum(isNull(materialQty,''0'')-isNull(rtnQty,''0'')) as realQty, '
	set @qry = @qry+char(13)+'		sum(isNull(buyingAmt,''0'')) as buyingAmt, '
	set @qry = @qry+char(13)+'		sum(isNull(buyingVat,''0'')) as buyingAmt, '
	set @qry = @qry+char(13)+'		sum(isNull(buyingAmt,''0'')+isNull(buyingVat,''0'')) as buyingTotal '
	set @qry = @qry+char(13)+'	from orders a'
	set @qry = @qry+char(13)+'	where dealerCode = '''+@A_dealerCode+''' '
	set @qry = @qry+char(13)+'	and refstockinday between '''+@A_startDay+'''  and '''+@A_endDay+'''' 
	set @qry = @qry+char(13)+'	and materialCode= '''+@A_materialCode+''' '

	if(@A_storeCode <> 'zzzz')
	begin
	set @qry = @qry+char(13)+'	and refStoreCode = '''+@A_storeCode+ ''' '
	end

	set @qry = @qry+char(13)+'	and storeCode = ''9999'' '
	set @qry = @qry+char(13)+'	and refStoreCode is not null '
	set @qry = @qry+char(13)+'	and refStockInday is not null '
	set @qry = @qry+char(13)+'	group by rollup((refstockinday,refstorecode),(dealercode)) '
	set @qry = @qry+char(13)+'  ORDER BY GROUPING(a.refstorecode), storeName, a.refstorecode, a.refstockinday
        , GROUPING(a.dealercode), a.dealercode '
	
	print(@qry)
	exec(@qry)

end

알려주신대로 하고 안되어서 수정해보다가 마지막결과물을 드렸네요;; 죄송합니다

by 마농 [2022.11.24 16:37:34]

롤업구문서부터 잘못 된 것 같네요.
ROLLUP((dealercode, refstorecode), refstockinday) -- 이게 맞을 듯 합니다.

by 지메이비 [2022.11.24 16:47:55]

제가원하는 결과물이랑 틀려져서 저는 날짜별,지점별 합계를 따로 내는 결과를 나타내고싶어요

20170306   은곡유치원(중식)   1.500   6750   0   6750
일별합계   NULL   1.500   6750   0   6750
20170307   구립서부중앙어린이집   3.500   15750   0   15750
일별합계   NULL   3.500   15750   0   15750
20170307   은곡유치원(중식)   1.800   8100   0   8100
일별합계   NULL   1.800   8100   0   8100
20170307   농수산물공사강서지사   12.000   56520   0   56520
일별합계   NULL   12.000   56520   0   56520
20170308   구립은아새어린이집   1.000   4500   0   4500
일별합계   NULL   1.000   4500   0   4500
20170308   구립서부중앙어린이집   1.000   4500   0   4500
일별합계   NULL   1.000   4500   0   4500
20170308   은곡유치원(중식)   1.000   4500   0   4500
일별합계   NULL   1.000   4500   0   4500
20170308   농수산물공사강서지사   8.000   37680   0   37680
일별합계   NULL   8.000   37680   0   37680
20170309   구립서부중앙어린이집   1.000   4500   0   4500
일별합계   NULL   1.000   4500   0   4500
20170309   은곡유치원(중식)   1.700   7650   0   7650
일별합계   NULL   1.700   7650   0   7650
20170309   청계숲유치원(중식)   3.000   13500   0   13500
일별합계   NULL   3.000   13500   0   13500
20170310   구립은아새어린이집   2.000   9000   0   9000
일별합계   NULL   2.000   9000   0   9000
20170310   농수산물공사강서지사   1.000   4710   0   4710
일별합계   NULL   1.000   4710   0   4710
20170313   구립은아새어린이집   3.000   13500   0   13500
일별합계   NULL   3.000   13500   0   13500
20170313   은곡유치원(중식)   3.500   15750   0   15750
일별합계   NULL   3.500   15750   0   15750
20170314   은곡유치원(중식)   2.000   9000   0   9000
일별합계   NULL   2.000   9000   0   9000
20170314   청계숲유치원(중식)   3.000   13500   0   13500
일별합계   NULL   3.000   13500   0   13500
20170315   은곡유치원(중식)   1.700   7650   0   7650
일별합계   NULL   1.700   7650   0   7650
20170315   청계숲유치원(중식)   3.000   13500   0   13500
일별합계   NULL   3.000   13500   0   13500
총 계   NULL   54.700   250560   0   250560

by 마농 [2022.11.25 09:29:35]

잘못 생각하고 있는 부분이 많습니다.
일별합계 는 일자별로 한번만 나와야 하는데 여러번 나오고 있구요.
정렬순서도 storeName, refstockinday 라고 생각한는 듯 한데
반대로 refstockinday, storeName 순서가 되어야 할 것 같습니다.
롤업 순서도 마찬가지입니다.

SELECT CASE 1 WHEN GROUPING(refstockinday) THEN '총 계'
              WHEN GROUPING(dealercode)    THEN '일별합계'
              ELSE refStockInDay END refstockinday
     , dbo.UF_STORENAME(dealerCode, refStoreCode) storeName
     , ISNULL(SUM(materialQty), 0) 
     - ISNULL(SUM(rtnQty     ), 0) AS realQty
     , ISNULL(SUM(buyingAmt  ), 0) AS buyingAmt
     , ISNULL(SUM(buyingVat  ), 0) AS buyingAmt
     , ISNULL(SUM(buyingAmt  ), 0) 
     + ISNULL(SUM(buyingVat  ), 0) AS buyingTotal
  FROM orders a
 WHERE dealerCode = '2002'
   AND refstockinday BETWEEN '20170306' AND '20170315'
   AND materialCode = '1000250'
   AND storeCode = '9999'
   AND refStoreCode  IS NOT NULL
   AND refStockInday IS NOT NULL
 GROUP BY ROLLUP(refstockinday, (dealercode, refstorecode))
 ORDER BY GROUPING(a.refstockinday), a.refstockinday
        , GROUPING(a.refstorecode), storeName
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변