|
DECLARE @ORGID VARCHAR(50),
@WDATE VARCHAR(50)
SET @ORGID = 'E0583'
SET @WDATE = '20100720'
SELECT DISTINCT A.ORGNAME AS 학교명,
A.STARTDATE AS 서비스시작일,
(SELECT COUNT(*) FROM TB_MEMBER WITH(INDEX(IX_TB_MEMBER_ORGID))
WHERE ORGID = A.ORGID AND SVCSTAT_CD IN(1)) AS 전체사용자,
(SELECT COUNT(*) FROM TB_ATTEND WITH(INDEX(IX_TB_ATTEND))
WHERE ORGID = A.ORGID AND ATNDIDX = 'A'
AND DATENAME(HOUR,TAGTIME) BETWEEN 06 AND 10
AND CONVERT(CHAR(8),WDATE,112) = CONVERT(CHAR(8),GETDATE(),112)) AS 출석자,
LEFT(CONVERT(float,(SELECT COUNT(*) FROM TB_ATTEND WITH(INDEX(IX_TB_ATTEND))
WHERE ORGID = A.ORGID AND ATNDIDX = 'A'
AND DATENAME(HOUR,TAGTIME) BETWEEN 06 AND 10
AND CONVERT(CHAR(8),WDATE,112) = CONVERT(CHAR(8),GETDATE(),112)) * 100 / CONVERT(float,(SELECT COUNT(*) FROM TB_MEMBER WITH(INDEX(IX_TB_MEMBER_ORGID))
WHERE ORGID = A.ORGID AND SVCSTAT_CD IN(1)))),4) as 출석률, B.WDATE AS 날짜
FROM TB_ORGAN A
JOIN ANYCARE.dbo.TB_ATTEND_201007 B
ON LEFT(B.ORGID,5) + '000' = A.ORGMEM
WHERE CONVERT(CHAR(8),B.WDATE,112) = @WDATE
AND A.ORGID = @ORGID
쿼리출력이 이렇게 나오네요 ㅠㅠ 하나만 나와야할텐데 20100720 날에 관한 하나만 나와야하는데 시간별로 나오네요
부탁드립니다 ㅠㅠ 2가지 일을 맡앗는데 어려움이 많네요 ㅠㅠ
학교 명/서비스시작일/전체사용자/출석자/출석률/날자
대구동도초등학교 20100703 156 13 8.33 2010-07-20 08:54:05.250
대구동도초등학교 20100703 156 13 8.33 2010-07-20 09:00:51.757
대구동도초등학교 20100703 156 13 8.33 2010-07-20 09:14:19.873
대구동도초등학교 20100703 156 13 8.33 2010-07-20 09:14:26.217
대구동도초등학교 20100703 156 13 8.33 2010-07-20 09:22:54.820
대구동도초등학교 20100703 156 13 8.33 2010-07-20 09:23:12.650
|
