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 때문에 뭐가 안되더라구요
어떤방법으로 해야하나요??
알려주신대로 했는데
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
이런식으로 깨져서 나옵니다
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
쿼리입니다
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 알려주신대로 하고 안되어서 수정해보다가 마지막결과물을 드렸네요;; 죄송합니다
제가원하는 결과물이랑 틀려져서 저는 날짜별,지점별 합계를 따로 내는 결과를 나타내고싶어요
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
잘못 생각하고 있는 부분이 많습니다.
일별합계 는 일자별로 한번만 나와야 하는데 여러번 나오고 있구요.
정렬순서도 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 ;