A프로젝트 2014-01-01 ~ 2014-12-31 12,000,000회사에서 진행 매출이란걸 적 12000000 / 365 ×31한다고 해서 계약기간에 따라 월별 매출액을 산정해야합니다.
사업명 계약기간 매출액
A프로젝트 2014-01-01 ~ 2014-12-31 12,000,000
이렇게 정보가 들어있습니다.
이걸
이를 월별로 쪼갠 금액을 구하려면 어떻게 할까요?
| 월 | 일자 | 총금액 | 월별금액 |
| 2014-01 | 31 | 1200000 | 38709.67742 |
| 2014-02 | 28 | 1200000 | 42857.14286 |
| 2014-03 | 31 | 1200000 | 38709.67742 |
| 2014-04 | 30 | 1200000 | 40000 |
| 2014-05 | 31 | 1200000 | 38709.67742 |
| 2014-06 | 30 | 1200000 | 40000 |
| 2014-07 | 31 | 1200000 | 38709.67742 |
| 2014-08 | 30 | 1200000 | 40000 |
| 2014-09 | 31 | 1200000 | 38709.67742 |
| 2014-10 | 30 | 1200000 | 40000 |
| 2014-11 | 31 | 1200000 | 38709.67742 |
| 2014-12 | 30 | 1200000 | 40000 |
이렇게 결과값이 나오게 쿼리문을 작성을 할 수 있을까요?
등으로 계산이 되어 나오려면...
가능할까요?
:
:
아랭와 같으뉴계약테이블 값을 참조하여 월별 매출금액을 산출해야 합니다.
사업명 계약기간 매출액
A프로젝트 2014-01-01 ~ 2014-12-31 12,000,000
B프로젝트 2014-03-01 ~ 2015-12-31 63,000,000
=======================================================
구간에 해당하는 월별 일수를 뽑는건 얼마전에 질문을 해서 적용을 해봤는데요... 어떻게.. 이와 묶을 수 있을까요?
declare @tbl table (seq int);
declare @seq int;
set @seq = 0;
while @seq <= 3700
begin
insert into @tbl(seq) values(@seq);
set @seq = @seq + 1;
end
select LEFT(dt, 7) as mon, COUNT(*) as cnt
from (
select convert(varchar(10), CAST('2010-01-01' as datetime) + seq, 121) as dt
from @tbl
) x
where dt between '2010-01-01' and '2020-12-31'
group by LEFT(dt, 7)
===============================================================
Comment 4
-
taz2315
2014.04.22 10:42
-
건우아빠
2014.04.22 15:06
달력 테이블 만드시고 그걸 이용하시는게 편할듯 하네요...
WITH
RES AS
(
SELECT 'A프로젝트' [사업명] , '20140101' [계약기간1] ,'20141231' [계약기간2] , 12000000 [매출액]
) ,
CALT AS
(
SELECT '2014'+MM+DD YYMMDD , '2014'+MM YYMM
FROM ( SELECT RIGHT('0'+CAST( NUMBER + 1 AS VARCHAR(2) ),2) MM
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P' AND NUMBER < 12 ) MM ,
( SELECT RIGHT('0'+CAST( NUMBER + 1 AS VARCHAR(2) ),2) DD
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P' AND NUMBER < 31 ) DD
WHERE ISDATE('2014'+MM+DD ) = 1
)
SELECT A.[사업명], A.[매출액]
, B.YYMM
, COUNT(*) [일수]
, ( A.[매출액] / 365. ) * COUNT(*) [월별금액]
FROM RES A JOIN CALT B ON B.YYMMDD BETWEEN A.[계약기간1] AND A.[계약기간2]
GROUP BY A.[사업명], A.[매출액] , B.YYMM
-
taz2315
2014.04.22 17:01
이부분이 이렇게 될건데요. 계약기간이 년이 넘어가고... 합니다.
WITH
RES AS
(
SELECT 'A프로젝트' [사업명] , '20140101' [계약기간1] ,'20141231' [계약기간2] , 12000000 [매출액]
Union all
SELECT 'B프로젝트' [사업명] , '20120101' [계약기간1] ,'20130831' [계약기간2] , 5000000 [매출액]
Union all
SELECT 'C프로젝트' [사업명] , '20130101' [계약기간1] ,'20151231' [계약기간2] , 34000000 [매출액]
)
-
건우아빠
2014.04.22 19:47
년 부분을 하나 더 넣어주시면 됩니다. (기존은 2014고정)
기간도 일정치 안으므로 일수 산정을 해야함.
,
CALT AS
(
SELECT YY+MM+DD YYMMDD , YY+MM YYMM
FROM ( SELECT RIGHT('0'+CAST( NUMBER + 2010 AS VARCHAR(4) ),4) YY
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P' AND NUMBER < 10 ) YY ,
( SELECT RIGHT('0'+CAST( NUMBER + 1 AS VARCHAR(2) ),2) MM
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P' AND NUMBER < 12 ) MM ,
( SELECT RIGHT('0'+CAST( NUMBER + 1 AS VARCHAR(2) ),2) DD
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P' AND NUMBER < 31 ) DD
WHERE ISDATE(YY+MM+DD ) = 1
)
SELECT A.[사업명], A.[매출액] , DATEDIFF(DD,[계약기간1],[계약기간2]) + 1 PDAY
, B.YYMM
, MIN(B.YYMMDD ) [시작일]
, MAX(B.YYMMDD ) [종료일]
, COUNT(*) [일수]
, ( A.[매출액] / ( (DATEDIFF(DD,[계약기간1],[계약기간2]) + 1) * 1.) ) * COUNT(*) [월별금액]
FROM RES A JOIN CALT B ON B.YYMMDD BETWEEN A.[계약기간1] AND A.[계약기간2]
GROUP BY A.[사업명], A.[매출액] , [계약기간1],[계약기간2] , B.YYMM
ORDER BY A.[사업명] , B.YYMM
관련해서 전에 질문올린게 있어서.. 좀 변형을 해서 해보려고 했는데... 결과값이 원하는데로 안나오네요...
문제점은 알겠는데 수정을 어떻게 해야 할지 모르겠네요.. .
WITH LoginTable AS
(
SELECT 'A' AS '제목', CONVERT(DATETIME,'2010-03-29') AS '시작시간', CONVERT(DATETIME,'2012-03-29') AS '종료시간'
UNION ALL
SELECT 'B' AS '제목', CONVERT(DATETIME,'2012-04-01') AS '시작시간', CONVERT(DATETIME,'2012-04-05') AS '종료시간'
)
SELECT
DAT.제목
, convert(varchar(10),(CASE WHEN DATEADD(MONTH,AAA.NO-1, 시작시간) > DAT.시작시간 THEN AAA.StartDate ELSE DAT.시작시간 END), 120) as 시작
, convert(varchar(10),(CASE WHEN DATEADD(MONTH,AAA.NO, 시작시간) > DAT.종료시간 THEN DAT.종료시간 ELSE AAA.EndDate END) , 120) as 종료
FROM
(
SELECT
*
, DATEDIFF(MONTH,시작시간,종료시간) + 1 AS TotalCount
FROM LoginTable
) DAT
INNER JOIN
(
SELECT TOP 300
ROW_NUMBER() OVER(ORDER BY number) AS NO
, DATEADD(Month, ROW_NUMBER() OVER(ORDER BY number) - 1, CONVERT(DATETIME,'2012-01-01')) AS StartDate
, DATEADD(Month, ROW_NUMBER() OVER(ORDER BY number) - 1, CONVERT(DATETIME,'2012-01-31')) AS EndDate
FROM master.dbo.spt_values
) AAA
ON DAT.TotalCount >= AAA.NO
order by 제목