Find Distinct Records over a period of a month

Question

I have table called DataFile. This keeps all my data records that get stored in a daily bases.

Data in this table looks like the below:

Id      ConfigAccountId Shelf       FileIdentifier  Created
5356341 23              BSAS020006  C200094         28/01/2013
5356342 23              BSAS020006  C200095         28/01/2013
5356343 23              BSAS020006  C200096         28/01/2013
5356344 23              BSAS020006  C200097         28/01/2013
5356345 23              BSAS020006  C200098         28/01/2013
5356346 23              BSAS020006  C200099         28/01/2013
5356347 23              BSAS020006  C200100         28/01/2013
5356348 23              BSAS020006  C200101         28/01/2013
5356349 23              BSAS020006  C200102         28/02/2013
5356350 23              BSAS020006  C200103         28/02/2013
5356351 23              BSAS020006  C200104         28/02/2013
5356352 23              BSAS020006  C200105         28/02/2013
5356353 23              BSAS020006  C200106         28/02/2013
5356354 23              BSAS020006  C200107         28/02/2013
5356355 23              BSAS020007  C200108         28/02/2013
5356356 23              BSAS020007  C200109         28/02/2013

If you look at the data, the shelf column will change number only when it is full, however I need to know how many unique shelf codes I have for the month. The problem is that shelf BSAS020006 runs over two month period so if I run a distinct for February it will count shelf BSAS020006 again(I hope I'm making sense). I need a unique Shelf count every month. So if a shelf number has already been reported on in Jan, and it runs over to Feb it must only show the count for Jan.

This is the code I have so far:

select distinct Shelf
from DataFile
where Created Between convert(datetime, '2015-10-01 00:00:01', 102) and 
                      convert(Datetime, '2015-10-31 23:59:59', 102)

My Output must show, But please note that a shelf can run over to a new month, and therefore must not be shown in that month as well.

Month    Shelf Count
January       15
February      16
March         10

what *exactly* is the output you're looking for? (and show the relevant input that will generate this output) — Amit, Nov 10 '15 at 07:30
No, you didn't. Read [ask]. You need to show an explicit example of the input (relevant rows in the table) and an explicit matching output (exact rows, with columns & values, that you want in the output). Then you need to show what you tried, where it got you, and why that's wrong. — Amit, Nov 10 '15 at 08:38
If you notice from the answers you got, people are ***guessing*** what you want, and that's not good. — Amit, Nov 10 '15 at 08:38

davestevens · Accepted Answer · 2015-11-10T10:00:23.850

1

If I understand your problem correctly, I think a combination of distinct and min before counting will work for you:

SELECT COUNT(Shelf) AS UniqueCodesForShelf, FirstMonthForShelf AS Month
FROM
(
    SELECT Shelf, MIN(AllMonthsForShelf) AS FirstMonthForShelf
    FROM
    (
        SELECT DISTINCT
            Shelf,
            DATEADD(m, DATEDIFF(m, 0, Created), 0) AS AllMonthsForShelf
        FROM DataFile
    ) AS T1
    GROUP BY Shelf
) AS T2
GROUP BY FirstMonthForShelf

The output of the Month column is the date of the first day of the month.

edited Nov 10 '15 at 10:00

answered Nov 10 '15 at 08:03

davestevens

2,233
2
16
19

1

Hi @dst, correct me if I'm wrong, but I don't see a table selected in your script? – user3309798 Nov 10 '15 at 08:29
@user3309798: thanks for that. I've edited my answer. – davestevens Nov 10 '15 at 08:58
thank you for that, I get the following error Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'GROUP'. – user3309798 Nov 10 '15 at 09:06
@ dst, I see what you mean with the 12 month period, because I have date from 2012 till present, is there anyway we can report it to show the month, year and count? – user3309798 Nov 10 '15 at 09:44
http://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server for example gives a few ways to do that. I've updated my answer. – davestevens Nov 10 '15 at 09:53
Thinking about this some more, I think the innermost DISTINCT is redundant - you can remove it and replace it with the MIN(DATEADD(...)) and it should give the same result. – davestevens Nov 13 '15 at 19:54

score 0 · Answer 2 · edited Nov 12 '15 at 20:27

I woukld suggest iterating through a range of dates and populating a temp table with "Shelf" only when it does not already exist in the temp table.

Something like this (untidy and untested)....

DECLARE @fromDate DateTime = 'A date in the past'

DECLARE @startDateTime DateTime =  DATEADD(day, DATEDIFF(day, 0, @fromDate), 0) ;
DECLARE @periodEnd DateTime;

Create TABLE #tmpShelf ( perisodStart DateTime, PeriodEnd DateTime, shelf nvarchar(100));

WHILE @startDateTime <= GetDate()
BEGIN
  SET @periodEnd = DATEADD( MONTH, 1, @startDateTime)

  INSERT INTO #tmpShelf SELECT @startDateTime, @periodEnd, DISTINCT Shelf
  FROM DataFile D 
  WHERE D.Created BETWEEN @startDateTime AND @periodEnd
  AND NOT EXISTS (SELECT 1 FROM #tmpShelf WHERE shelf = D.shelf)

  SET @startDateTime = DATEADD( MONTH, 1, @startDateTime)
END

score 0 · Answer 3 · answered Nov 10 '15 at 08:58

Try this,

Declare @To Datetime='2013-02-28 23:59:59'
DECLARE @t TABLE (
    Id INT
    ,ConfigAccountId INT
    ,Shelf VARCHAR(20)
    ,FileIdentifier VARCHAR(20)
    ,Created DATETIME
    )

INSERT INTO @t
VALUES

(5356341, 23,'BSAS020006','C200094','01/28/2013')
,(5356342, 23,'BSAS020006','C200095','01/28/2013')
,(5356343, 23,'BSAS020006','C200096','01/28/2013')
,(5356344, 23,'BSAS020006','C200097','01/28/2013')
,(5356345, 23,'BSAS020006','C200098','01/28/2013')
,(5356346, 23,'BSAS020006','C200099','01/28/2013')
,(5356347, 23,'BSAS020006','C200100','01/28/2013')
,(5356348, 23,'BSAS020006','C200101','01/28/2013')
,(5356349, 23,'BSAS020006','C200102','02/28/2013')
,(5356350, 23,'BSAS020006','C200103','02/28/2013')
,(5356351, 23,'BSAS020006','C200104','02/28/2013')
,(5356352, 23,'BSAS020006','C200105','02/28/2013')
,(5356353, 23,'BSAS020006','C200106','02/28/2013')
,(5356354, 23,'BSAS020006','C200107','02/28/2013')
,(5356355, 23,'BSAS020007','C200108','02/28/2013')
,(5356356, 23,'BSAS020007','C200109','02/28/2013')
SELECT *
FROM @t A
WHERE Created BETWEEN @From
        AND @To
    AND NOT EXISTS (
        SELECT Shelf
        FROM @t B
        WHERE B.Shelf = A.Shelf
            AND Created NOT BETWEEN @From
                AND @To
        )

Hi @KumarHarsh,, thank you for your response, will I have to add the data every date as shown above? — user3309798, Nov 10 '15 at 09:07

Find Distinct Records over a period of a month

3 Answers3