Geek Answers Handbook

How to count rows from another table based on the same date range

EDIT , although the accepted answer does not match what I was looking for when I wrote this question, it shows the best way that I already had (what they want) the data in the production tables are incorrect, which means that the numbers wrong.

I am trying to create sql status to generate a report at the bottom of this post. I have included entries for creating tables and test data. So, to get started, this is the Creation operator for picture_stats

CREATE TABLE [picture_stats](
    [PICTURE_STATS_ID] [int] NULL,
    [USER_NAME] [varchar](30) NULL,
    [DATE_TIME] [datetime] NULL,
    [SIZE] [float] NULL,
    [CLICK_COUNT] [int] NULL
) ON [PRIMARY]
GO

Insert a statement that puts data in picture_stats

INSERT INTO [picture_stats]
           ([PICTURE_STATS_ID]
           ,[USER_NAME]
           ,[DATE_TIME]
           ,[SIZE]
           ,[CLICK_COUNT])
VALUES
    (1 ,'A','2015-05-18'75,18),
    (2 ,'A','2015-05-18'13,18),
    (3 ,'A','2015-05-18'42,16),
    (4 ,'A','2015-05-18'59,16),
    (5 ,'A','2015-05-18'46,14),
    (6 ,'A','2015-05-18'64,16),
    (7 ,'A','2015-05-18'87,13),
    (8 ,'A','2015-05-18'84,14),
    (9 ,'A','2015-05-18'33,16),
    (10,'A','2015-05-18'59,14),
    (11,'B','2015-05-19'10,17),
    (12,'B','2015-05-19'44,18),
    (13,'B','2015-05-19'29,14),
    (14,'B','2015-05-19'65,19),
    (15,'B','2015-05-19'10,15),
    (16,'B','2015-05-19'55,18),
    (17,'B','2015-05-19'81,11),
    (18,'B','2015-05-19'29,11),
    (19,'B','2015-05-19'58,19),
    (20,'B','2015-05-19'20,17),
    (21,'C','2015-05-20'35,16),
    (22,'C','2015-05-20'70,18),
    (23,'C','2015-05-20'30,13),
    (24,'C','2015-05-20'33,13),
    (25,'C','2015-05-20'43,19),
    (26,'C','2015-05-20'10,15),
    (27,'C','2015-05-20'33,13),
    (28,'C','2015-05-20'23,12),
    (29,'C','2015-05-20'35,18),
    (30,'C','2015-05-20'58,19)
GO

Table of data types.

ID USER_NAME DATE_TIME  SIZE CLICK_COUNT
-- --------- ---------- ---- -----------
1  A         2015-05-18 75   18
2  A         2015-05-18 1    18
3  A         2015-05-18 42   16
4  A         2015-05-18 59   16
5  A         2015-05-18 46   14
6  A         2015-05-18 64   16
7  A         2015-05-18 87   13
8  A         2015-05-18 84   14
9  A         2015-05-18 33   16
10 A         2015-05-18 59   14
11 B         2015-05-19 10   17
12 B         2015-05-19 44   18
13 B         2015-05-19 29   14
14 B         2015-05-19 65   19
15 B         2015-05-19 100  15
16 B         2015-05-19 55   18
17 B         2015-05-19 81   11
18 B         2015-05-19 29   11
19 B         2015-05-19 58   19
20 B         2015-05-19 20   17
21 C         2015-05-20 35   16
22 C         2015-05-20 7    18
23 C         2015-05-20 30   13
24 C         2015-05-20 33   13
25 C         2015-05-20 4    19
26 C         2015-05-20 100  15
27 C         2015-05-20 33   13
28 C         2015-05-20 23   12
29 C         2015-05-20 35   18
30 C         2015-05-20 58   19

The second table picture_comment_stats can be created as follows:

CREATE TABLE [picture_comment_stats](
    [PICTURE_STATS_ID] [int] NULL,
    [USER_NAME] [varchar](30) NULL,
    [DATE_TIME] [datetime] NULL,
    [CLICK_COUNT] [int] NULL,
    [LIKES] [int] NULL,
) ON [PRIMARY]
GO

In the script in the data:

INSERT INTO [picture_comment_stats]
    ([PICTURE_STATS_ID]
    ,[USER_NAME]
    ,[DATE_TIME]
    ,[CLICK_COUNT]
    ,[LIKES])
VALUES
    (1 ,'X','2015-05-18',75,18),
    (2 ,'X','2015-05-18',1 ,18),
    (3 ,'X','2015-05-18',42,16),
    (4 ,'X','2015-05-18',59,16),
    (9 ,'X','2015-05-19',34,16),
    (10,'X','2015-05-19',57,14),
    (11,'Y','2015-05-19',11,17),
    (12,'Y','2015-05-19',44,18),
    (17,'Y','2015-05-20',81,11),
    (18,'Y','2015-05-20',29,11),
    (19,'Y','2015-05-20',55,19),
    (21,'Z','2015-05-20',45,16),
    (20,'Y','2015-05-21',20,17),
    (22,'Z','2015-05-21',7 ,18),
    (23,'Z','2015-05-21',30,13),
    (24,'Z','2015-05-21',39,13),
    (25,'Z','2015-05-21',4 ,19),
    (26,'Z','2015-05-21',10,15),
    (27,'Z','2015-05-21',33,13),
    (28,'Z','2015-05-21',23,12),
    (29,'Z','2015-05-21',35,18),
    (30,'Z','2015-05-21',58,19)
GO

The table should look like this.

ID USER_NAME DATE_TIME  COMMENT_ID LIKES
-- --------- ---------- ---------- -----
1  X         2015-05-18 75         18   
2  X         2015-05-18 1          18   
3  X         2015-05-18 42         16   
4  X         2015-05-18 59         16   
9  X         2015-05-19 34         16   
10 X         2015-05-19 57         14   
11 Y         2015-05-19 11         17   
12 Y         2015-05-19 44         18   
17 Y         2015-05-20 81         11   
18 Y         2015-05-20 29         11   
19 Y         2015-05-20 55         19   
21 Z         2015-05-20 45         16   
20 Y         2015-05-21 20         17   
22 Z         2015-05-21 7          18   
23 Z         2015-05-21 30         13   
24 Z         2015-05-21 39         13   
25 Z         2015-05-21 4          19   
26 Z         2015-05-21 10         15   
27 Z         2015-05-21 33         13   
28 Z         2015-05-21 23         12   
29 Z         2015-05-21 35         18   
30 Z         2015-05-21 58         19

, , , , . Picture_stats_id , picture_id "". : , 3-

(Filtered for Date:2015-05-18)
USER_NAME PICS COMMENTS
--------- ---- --------
A         10   4

(Filtered for Date:2015-05-19)
USER_NAME PICS COMMENTS
--------- ---- --------
B         10   2

(Filtered for Date:2015-05-20)
USER_NAME PICS COMMENTS
--------- ---- --------
C         10   1

(Filtered for Date:2015-05-18 to 2015-05-19)
USER_NAME PICS COMMENTS
--------- ---- --------
A         10   6
B         10   2

(Filtered for Date:2015-05-19 to 2015-05-20)
USER_NAME PICS COMMENTS
--------- ---- --------
B         10   2
C         10   1

(Filtered for Date:2015-05-18 to 2015-05-20)
USER_NAME PICS COMMENTS
--------- ---- --------
A         10   6
B         10   5
C         10   1

, ,

SELECT ps.USER_NAME as USER_NAME,
    COUNT(*) as PICTURE_COUNT,
    SUM(1) AS COMMENT_COUNT -- can't figure out this statement for nothing.
from picture_comment_stats pcs
RIGHT OUTER JOIN picture_stats ps ON ps.PICTURE_STATS_ID = pcs.PICTURE_STATS_ID
--WHERE ps.DATE_TIME BETWEEN @beginFilterDate AND @endFilterDate
GROUP BY ps.USER_NAME
ORDER BY ps.USER_NAME

, : http://sqlfiddle.com/#!3/74e77

, . - , . , . , X 6 (18 19), 4, 18-, 6, 18-19-. , , , .

+4
1
    SELECT --ps.USER_NAME as USER_NAME,
    s.user_name,
    count(distinct c.comment_id) as comments,
    count(s.picture_stats_id) as pics
    --COUNT(*) as PICTURE_COUNT,
    --SUM(1) AS COMMENT_COUNT
    from --commenting.transcription_audit_stats pcs
   -- RIGHT OUTER JOIN commenting.transcription_stats ps ON ps.SURVEY_STATS_ID --= pcs.SURVEY_STATS_ID 
    picture_stats s
    left join picture_comment_stats c
    on s.picture_stats_id = c.picture_stats_id
    and s.date_time = c.date_time
    where s.date_time between --specified date range
    --WHERE ps.DATE_TIME BETWEEN @beginFilterDate AND @endFilterDate    
    --GROUP BY ps.USER_NAME
    --ORDER BY ps.USER_NAME
    group by s.user_name,s.date_time

. , .

+1

More articles:


All Articles