Genarate SQL query for counting

Question

1.20/5 (3 votes)

See more:

SQL-Server-2008

Ramit -1 
Vickey - 1
Vivek-1
vikash-1
alok-1
ajay-1

I have two table like -

tbl1

owner_id         owner_name
1                   a
2                   b
3                   c

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

date will pass dynamic 1 to 31st and have to get count records datewise of each employee?

Posted 10-Mar-14 1:33am

rummer

Updated 28-Apr-14 10:11am

Maciej Los

v2

Add a Solution

Comments

Corporal Agarn 10-Mar-14 8:26am

How are your tables related? What does the "Ramit -1" information have to do with the question?

King Fisher 17-May-14 8:58am

what have you tried?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

riteshkiitian · Answer 1 · 2014-04-28T08:47:00

write your script something like this:

SQL

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