生成SQL查询以进行计数 [英] Genarate SQL query for counting
本文介绍了生成SQL查询以进行计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Ramit -1
Vickey - 1
Vivek-1
vikash-1
alok-1
ajay-1
我有两个桌子-
tbl1
I have two table like -
tbl1
owner_id owner_name
1 a
2 b
3 c
tbl2
tbl2
can_id CAn_Name owner_id createddate
11 aa 1 10/3/2014
12 bb 2 9/3/2014
13 cc 3 8/3/2014
14 dd 3 7/3/2014
15 ee 2 9/3/2014
16 ff 1 5/3/2014
17 gg 2 4/3/2014
我想要的结果是-
I want result as -
CREATEDATE a b c
1/3/2014 0 0 0
2/3/2014 0 0 0
3/3/2014 0 0 0
4/3/2014 0 1 0
5/3/2014 1 0 0
6/3/2014 0 0 0
7/3/2014 0 0 1
8/3/2014 0 0 1
9/3/2014 1 2 0
10/3/2014 0 0 0
11/3/2014 0 0 0
日期将在1号到31号之间动态变化,并且必须获取每个雇员的日期计数记录?
date will pass dynamic 1 to 31st and have to get count records datewise of each employee?
推荐答案
编写脚本,如下所示:
write your script something like this:
DECLARE @dtStartDate AS DATETIME = '3/11/2014'
DECLARE @dtEndDate AS DATETIME = '3/1/2014'
SELECT
CREATEDATE,
CASE WHEN EXISTS(SELECT * FROM tbl2 WHERE createddate = D.CREATEDATE AND owner_id = 1) THEN 1 ELSE 0 END AS a,
CASE WHEN EXISTS(SELECT * FROM tbl2 WHERE createddate = D.CREATEDATE AND owner_id = 2) THEN 1 ELSE 0 END AS b,
CASE WHEN EXISTS(SELECT * FROM tbl2 WHERE createddate = D.CREATEDATE AND owner_id = 3) THEN 1 ELSE 0 END AS c
FROM(
SELECT
CAST(@dtStartDate - langid AS DATE) CREATEDATE
FROM sys.syslanguages
WHERE @dtStartDate -langid >= @dtEndDate
) D
这篇关于生成SQL查询以进行计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文