sql - repiten - Cómo contar filas desde una tabla diferente basada en el mismo rango de fechas

sql contar registros condicion

Question

EDITAR Aunque la respuesta aceptada no coincide con lo que estaba buscando en el momento en que escribí esta pregunta, muestra una mejor manera de lo que ya tenía (que es lo que quieren) los datos en las tablas de producción son lo que está mal , lo que significa que los números son incorrectos

Estoy luchando por crear la declaración sql para crear el informe en la parte inferior de esta publicación. He incluido las declaraciones para crear mis tablas y datos de prueba. Para comenzar, aquí está la declaración de creación para 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

Insertar declaración que pone los datos en 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

Vista de tabla de los datos.

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

La segunda tabla picture_comment_stats se puede crear con esto:

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

Para script en los datos:

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

La mesa debería verse así.

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 

Lo que quiero hacer es hacer un informe de los datos de manera que tenga el nombre de usuario, el recuento de las imágenes y el recuento de comentarios en una imagen individual. El picture_stats_id en ambas tablas es en realidad una clave externa que apunta a la tabla ''pictures'' clave principal picture_id. El informe Quiero poder elegir una fecha o un rango de fechas y hacer que se muestre así: Espero que el informe muestre lo siguiente para los diferentes escenarios de 3 días

(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

Hasta ahora todo lo que tengo es

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

EDITAR

La dirección del violín como se dio en los comentarios a continuación: http://sqlfiddle.com/#!3/74e77

Para aclarar los recuentos que trato de obtener. La segunda columna sería la cantidad de imágenes que dicho usuario publicó ese día. la tercera columna es cuántos comentarios se hicieron SOLAMENTE en una imagen de usuario para ese día especificado. Así que aunque X comentó la imagen de A un total de 6 veces (el 18 y el 19), solo quiero contar 4 si filmo para el 18 y 6 si filmo el 18 al 19. Es un poco difícil de seguir, estoy seguro, pero eso es lo que se quiere.

Answer 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

Pruebe esto y obtenga el nombre de usuario de la tabla de búsqueda que tiene. También filtra las fechas que necesitas.