non mi e' ben chiaro cosa vuoi ottenere... quindi provo a procedere per step successivi per vedere se rispondo alla tua richiesta...
ah.. non hai indicato che tipologia di server SQL stai usando, quindi per mia parte ti indico che utilizzo SQL Server 2014, che dispone di funzionalita' di windowing comunque presenti anche in altri engines...
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.Utenti (
Id int PRIMARY KEY IDENTITY,
Nome varchar(10),
Data_Registrazione date,
Status char(3)
);
GO
INSERT INTO dbo.Utenti
VALUES
('a1', '2016-01-01', 'ON'), ('a2', '2016-01-01', 'OFF'),
('b1', '2016-01-02', 'ON'), ('b2', '2016-01-02', 'ON'), ('b3', '2016-01-02', 'OFF'),
('c1', '2016-01-03', 'OFF'),
('d1', '2016-01-04', 'ON'), ('d2', '2016-01-04', 'ON'), ('d3', '2016-01-04', 'ON'), ('d4', '2016-01-04', 'ON'), ('d5', '2016-01-04', 'OFF') ;
GO
DECLARE @Data_Registrazione date = '2016-01-06';
PRINT 'conteggio generico raggruppato';
SELECT u.[Data_Registrazione], COUNT(u.[Id]) AS [cnt]
FROM dbo.Utenti u
WHERE u.[Status] = 'ON'
AND u.[Data_Registrazione] <= @Data_Registrazione
GROUP BY u.[Data_Registrazione];
PRINT 'conteggio generico raggruppato con Running Total';
WITH cte AS (
SELECT u.[Data_Registrazione], COUNT(u.[Id]) AS [cnt]
FROM dbo.Utenti u
WHERE u.[Status] = 'ON'
AND u.[Data_Registrazione] <= @Data_Registrazione
GROUP BY u.[Data_Registrazione]
)
SELECT c.[Data_Registrazione], c.[cnt],
-- utilizziamo una funzione di windowing per sommare il conteggio dalla riga iniziale alla riga corrente
SUM(c.[cnt]) OVER(ORDER BY c.[Data_Registrazione] ROWS UNBOUNDED PRECEDING) AS [RunningTotal]
FROM cte c
ORDER BY c.[Data_Registrazione];
GO
PRINT '----------------------';
DECLARE @data_minima date= '2015-12-31';
DECLARE @data_massima date = '2016-01-06';
PRINT 'common table expression (ridotta per questo utilizzo) preparata da Itzik Ben Gan per popolare un calendario';
DECLARE @NoDays int = DATEDIFF(DAY, @data_minima, @data_massima) +1;
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
,
Calender AS (
SELECT [SeqNo] = t.N,
[Date] = dt.DT
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N -1)*SIGN(@NoDays), @data_minima)
) dt
)
SELECT *
FROM Calender c
ORDER BY c.[Date];
GO
PRINT 'conteggio per TUTTE le date, anche ''assenti'' nella tabella';
PRINT 'di origine con raggruppamento e Running Total utilizzando la';
PRINT 'common table expression di cui sopra per popolare un calendario';
PRINT 'che useremo in JOIN per ottenere le date assenti';
DECLARE @data_minima date= '2015-12-31';
DECLARE @data_massima date = '2016-01-07';
DECLARE @NoDays int = DATEDIFF(DAY, @data_minima, @data_massima) +1;
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
,
Calender AS (
SELECT [SeqNo] = t.N,
[Date] = dt.DT
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N -1)*SIGN(@NoDays), @data_minima)
) dt
),
-- la nostra tabella di conteggio
cte AS (
SELECT ca.[Date] AS [Data_Registrazione], ISNULL(COUNT(u.[Id]), 0) AS [cnt]
FROM dbo.Utenti u
RIGHT JOIN Calender ca ON u.[Data_Registrazione] = ca.[Date]
WHERE u.[Status] = 'ON'
AND u.[Data_Registrazione] <= @data_massima
GROUP BY ca.[Date]
),
cteAllDates AS (
SELECT ca.[Date] AS [Data_Registrazione], ISNULL(u.[cnt], 0) AS [cnt]
FROM cte u
RIGHT JOIN Calender ca ON u.[Data_Registrazione] = ca.[Date]
)
SELECT u.[Data_Registrazione], u.[cnt]
, SUM(u.[cnt]) OVER(ORDER BY u.[Data_Registrazione] ROWS UNBOUNDED PRECEDING) AS [RunningTotal]
FROM cteAllDates u
ORDER BY u.[Data_Registrazione];
GO
DROP TABLE dbo.Utenti;
--<----------------
conteggio generico raggruppato
Data_Registrazione cnt
------------------ -----------
2016-01-01 1
2016-01-02 2
2016-01-04 4
conteggio generico raggruppato con Running Total
Data_Registrazione cnt RunningTotal
------------------ ----------- ------------
2016-01-01 1 1
2016-01-02 2 3
2016-01-04 4 7
----------------------
common table expression (ridotta per questo utilizzo) preparata da Itzik Ben Gan per popolare un calendario
SeqNo Date
-------------------- ----------
1 2015-12-31
2 2016-01-01
3 2016-01-02
4 2016-01-03
5 2016-01-04
6 2016-01-05
7 2016-01-06
conteggio per TUTTE le date, anche 'assenti' nella tabella
di origine con raggruppamento e Running Total utilizzando la
common table expression di cui sopra per popolare un calendario
che useremo in JOIN per ottenere le date assenti
Data_Registrazione cnt RunningTotal
------------------ ----------- ------------
2015-12-31 0 0
2016-01-01 1 1
2016-01-02 2 3
2016-01-03 0 3
2016-01-04 4 7
2016-01-05 0 7
2016-01-06 0 7
2016-01-07 0 7