처음 쿼리는 등록된 데이터가 없을때 그냥 표시 안하고 나온 쿼리고
두번째는 빠진 날짜를 0건으로 어떻게 한번 표시 할려고 했는데 너무 힘드네요 이거 정말 방법 없을까요?
SELECT CONVERT(CHAR(10),T1.DUMYDATE,126) AS DDATE,COUNT(*)
FROM DATEDUMY T1 FULL OUTER JOIN CMS_NEWS_CONTENT T2 ON CONVERT(CHAR(10),T1.DUMYDATE,126)=CONVERT(CHAR(10),T2.CREATE_DATE,126)
WHERE
'2009-04-01'<=CONVERT(CHAR(10),T1.DUMYDATE,126)
AND CONVERT(CHAR(10),T1.DUMYDATE,126)<='2009-07-01'
GROUP BY CONVERT(CHAR(10),T1.DUMYDATE,126)
ORDER BY CONVERT(CHAR(10),T1.DUMYDATE,126) DESC
2009-07-01 267
2009-06-30 281
2009-06-29 289
2009-06-28 124
2009-06-26 156
2009-06-25 250
2009-06-24 270
2009-06-23 268
2009-06-22 258
2009-06-21 144
2009-06-19 146
2009-06-18 278
2009-06-17 293
2009-06-16 292
2009-06-15 269
2009-06-14 131
2009-06-12 145
2009-06-11 310
2009-06-10 239
SELECT CONVERT(CHAR(10),T1.DUMYDATE,126) AS DDATE,COUNT(*)
FROM DATEDUMY T1 LEFT OUTER JOIN CMS_NEWS_CONTENT T2 ON CONVERT(CHAR(10),T1.DUMYDATE,126)=CONVERT(CHAR(10),T2.CREATE_DATE,126)
WHERE
T'2009-04-01'<=CONVERT(CHAR(10),T1.DUMYDATE,126)
AND CONVERT(CHAR(10),T1.DUMYDATE,126)<='2009-07-01'
GROUP BY CONVERT(CHAR(10),T1.DUMYDATE,126)
ORDER BY CONVERT(CHAR(10),T1.DUMYDATE,126) DESC
UNION
SELECT CONVERT(CHAR(10),DUMYDATE,126) AS DDATE FROM DATEDUMY
2009-07-01 0
2009-07-01 267
2009-06-30 0
2009-06-30 281
2009-06-29 0
2009-06-29 289
2009-06-28 0
2009-06-28 124
2009-06-27 0
2009-06-26 0
2009-06-26 156
2009-06-25 0
2009-06-25 250
2009-06-24 0
2009-06-24 270
2009-06-23 0
2009-06-23 268
2009-06-22 0
2009-06-22 258
2009-06-21 0
|