生成SQL查询以进行计数 [英] Genarate SQL query for counting

问题描述

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屋！