• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

How do I make a query for each date we have in the month

Hello,
I need assistant to make a query only date we have for  a month.

I have a db which have data inserted every day at every seconds, so during month end, I need to make sure are all data are inserted. Usually I will run below but this does not tell me if got any date that I missing data in between of min and max.

select min (sm_timestamp),max (sm_timestamp) from MPXLog4 with ( nolock)

results.

2013-10-01 00:00:00.000      2013-10-28 23:59:59.000
0
motioneye
Asked:
motioneye
  • 8
  • 4
  • 2
  • +5
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
I don't have sample data to test it but I'm pretty sure this would work:

Select	distinct
	year(sm_timestamp) + month(sm_timestamp) + day(sm_timestamp) as 'entryDate'
from	MPXLog4 with (nolock)
order by sm_timestamp

Open in new window

0
 
rshqCommented:
Hi
 Or
  You can use this query to see number of record every day
   
  select  str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp)) as     'entryDate',count(*)
   from      MPXLog4 with (nolock)
   group by str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp))
   order by str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp))
0
 
MohitPanditCommented:
Hello,

Please check below code to find missing dates. You may customize further based on that.
Please note, I've used temp tables with comments so please make change accordingly.

-- Assume, your temp table with three records only table 
CREATE TABLE #MPXLog4 (sm_timestamp DateTime)
INSERT INTO #MPXLog4 (sm_timestamp)
VALUES ('2013-10-01'), ('2013-10-15 21:59:59.000'), ('2013-10-28 23:59:59.000')

-- Solution
-- 1. Create temp table for solution
CREATE TABLE #Temp_Date(Date_Val Date)

DECLARE 
	@Row_Count Int -- To fetch total number of date between date range.
	, @Row_Num Int = 1 -- To be used in iteration and default value is 1.
	, @Effective_Date Date -- To store effective date i.e. min date
	, @Expiry_Date Date -- To store end  date i.e. min date
	, @Start_Date Date -- To store effective date for iteration
	
-- 2. Fetch min & max date from #MPXLog4 table
	-- & store in @Effective_Date & @Expiry_Date respectively.
SELECT 
	@Effective_Date = MIN (sm_timestamp),
	@Expiry_Date = MAX (sm_timestamp) 
FROM #MPXLog4 WITH (NOLOCK)

SET @Row_Count = CASE WHEN @Effective_Date = @Expiry_Date THEN 1 ELSE DATEDIFF(DAY,@Effective_Date, @Expiry_Date) + 1 END
SET @Start_Date = @Effective_Date -- Initilize @Start_Date with effective date

-- 3. Store each date based on min & max date
WHILE(@Row_Count > 0)
BEGIN
	INSERT INTO #Temp_Date(Date_Val)
	VALUES (@Start_Date)
	
	SET @Start_Date = DATEADD(DAY,1,@Start_Date)

	SET @Row_Count = @Row_Count - 1
	SET @Row_Num = @Row_Num + 1
END

-- 4. Find out which dates are not in #MPXLog4 table
SELECT
	Missing_Dates = #Temp_Date.Date_Val
FROM
(
	SELECT
		DISTINCT sm_timestamp = CONVERT(Date,sm_timestamp)
	FROM #MPXLog4 -- First get distinct date as mentioned record can be every second.
) Table1
RIGHT OUTER JOIN #Temp_Date
ON Table1.sm_timestamp = #Temp_Date.Date_Val
WHERE Table1.sm_timestamp IS NULL -- Fetch those dates which are missing.

--SELECT * FROM #MPXLog4
--SELECT * FROM #Temp_Date

DROP TABLE #MPXLog4
DROP TABLE #Temp_Date

Open in new window


Best Regards,
Mohit Pandit
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Koen Van WielinkIT ConsultantCommented:
Oops, completely forgot the string command.  Rshq's query is more accurate. It also doesn't use "distinct" so it would probably run faster too.
0
 
motioneyeAuthor Commented:
Hi ,
It turn with wrong results, but it must appears with every date till the date I run the script

The result must be similar below, but my scripts only takes start date and end data which I run the report, I would like to have the query output appears fr each day

select min (sm_timestamp),max (sm_timestamp) from MPXLog4 with ( nolock)


2013-10-01 00:00:00.000      2013-10-28 23:59:59.000

Actually I can do like below to query by changing  -1  and subsequently, but this need  to repeat 30 times to get each date for result in month, so I'm thing single query to output a similar result

select min (sm_timestamp),max (sm_timestamp)  - 1 from MPXLog4 with ( nolock)
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

how you storing data ?

like this..

2013-10-01 00:00:01.000
2013-10-01 00:00:02.000
2013-10-01 00:00:03.000
2013-10-01 00:00:04.000
2013-10-01 00:00:05.000
2013-10-01 00:00:06.000
2013-10-01 00:00:07.000
2013-10-01 00:00:08.000

OR

2013-10-01 00:00:01.000
2013-10-02 00:00:02.000
2013-10-03 00:00:03.000
2013-10-04 00:00:04.000
2013-10-05 00:00:05.000
0
 
motioneyeAuthor Commented:
Hi,
I prefer like below

2013-10-01 00:00:00.000      2013-10-01 23:59:59.000
2013-10-02 00:00:00.000      2013-10-02 23:59:59.000
2013-10-03 00:00:00.000      2013-10-03 23:59:59.000
.
.
.
.
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

based on my understanding, you have one column containing date.
and you are looking out for missing dates in between.

correct me if wrong.

in below case,

2013-10-01 00:00:01.000
2013-10-02 00:00:02.000
2013-10-04 00:00:04.000
2013-10-05 00:00:05.000


2013-10-03 00:00:04.000 is missing.
0
 
PortletPaulCommented:
an entry per second for the previous month is a predictable number.
does this assist?
DECLARE @s datetime, @e datetime, @n int

SET @s = DATEADD(MONTH,-1,getdate())
SET @s = DATEADD(DAY, - (DAY(@s) - 1), DATEADD(DAY, DATEDIFF(DAY,0, @s ), 0))
SET @e = DATEADD(MONTH,1,@s)

SET @n = DATEDIFF(DAY,@s,@e) * (24*60*60)

--SELECT
--  @s
--, @e
--, DATEDIFF(DAY,@s,@e)
--, @n
--;

SELECT
        MIN (sm_timestamp)
      , @n - count( DISTINCT sm_timestamp ) AS missing_stamps
      , MAX (sm_timestamp)
FROM MPXLog4 WITH ( nolock)
WHERE ( sm_timestamp > = @s AND sm_timestamp < @e )
;

Open in new window

0
 
motioneyeAuthor Commented:
Yes,
I have one  column containing date. our system are running 24 hrs non stop  if I see data like below

2013-10-01 00:00:00.000      2013-10-01 23:59:59.000
2013-10-02 00:00:00.000      2013-10-02 23:59:59.000
2013-10-03 00:00:00.000      2013-10-03 23:59:59.000

2013-10-05 00:00:00.000      2013-10-05 23:59:59.000
2013-10-06 00:00:00.000      2013-10-03 10:00:5.000


hence I missed to import data in 2013-10-04 and data was partially imported on 2013-10-06
.
0
 
SharathData EngineerCommented:
How come you have past date for 2013-10-06? Is that a typo?

2013-10-01 00:00:00.000      2013-10-01 23:59:59.000
2013-10-02 00:00:00.000      2013-10-02 23:59:59.000
2013-10-03 00:00:00.000      2013-10-03 23:59:59.000

2013-10-05 00:00:00.000      2013-10-05 23:59:59.000
2013-10-06 00:00:00.000      2013-10-03 10:00:5.000

Also, how did you get this output? can you show your query?
0
 
motioneyeAuthor Commented:
Hi,
Oh that is typo

How come you have past date for 2013-10-06? Is that a typo?

2013-10-01 00:00:00.000      2013-10-01 23:59:59.000
2013-10-02 00:00:00.000      2013-10-02 23:59:59.000
2013-10-03 00:00:00.000      2013-10-03 23:59:59.000

2013-10-05 00:00:00.000      2013-10-05 23:59:59.000
2013-10-06 00:00:00.000      2013-10-06 10:00:05.000

Also, how did you get this output? can you show your query?
0
 
SharathData EngineerCommented:
You did not answer my second question. Do you have one column or two columns?
Are you running a query to get min and max timestamp for each date?
0
 
motioneyeAuthor Commented:
we only have one column ( sm_timestamp)

select min (sm_timestamp),max (sm_timestamp)   from MPXLog4 with ( nolock)
0
 
PortletPaulCommented:
You could use a CTE to generate a range of dates then use it like this
-- establish date range
DECLARE @fromdate datetime
      , @todate datetime
SELECT
        @fromdate = '20131001' -- or -- '2013-09-01'
      , @todate   = '20131101' -- or -- '2013-10-01'

;WITH
        DateCTE (sDate, eDate)
        AS (
                SELECT
                        @fromdate AS sDate, dateadd(DAY,1,@fromdate) AS eDate
                UNION ALL
                SELECT
                        DATEADD(DAY, 1, sDate), DATEADD(DAY, 1, eDate)
                FROM DateCTE
                WHERE sDate < dateadd(DAY,-1,@todate)
                )
SELECT
        DateCTE.sDate
      , MIN (sm_timestamp)                          AS min_timestamp
      , (24*60*60) - count( DISTINCT sm_timestamp ) AS missing_stamps
      , MAX (sm_timestamp)                          AS max_timestamp
FROM DateCTE
LEFT JOIN MPXLog4 ON MPXLog4.sm_timestamp >= DateCTE.sDate
                  AND MPXLog4.sm_timestamp <  DateCTE.eDate
GROUP BY
        DateCTE.sDate
HAVING
       (24*60*60) - count( DISTINCT sm_timestamp ) > 0
    OR MIN (sm_timestamp) IS NULL
;

-- http://sqlfiddle.com/#!3/96606/5

Open in new window

0
 
jogosCommented:
A select to have all dates in a month
declare @MinTransDate Date;
declare @MinTransDate Date;
declare @MaxTransDate Date;
SET @MinTransDate ='2013/10/01 00:00:00.000 ';
SET @MinTransDateEnd ='2013/10/01 23:59:59.000 ';
SET @MaxTransDate ='2013/10/31 00:00:00.000';


;With Date_CTE (DateStart,DateEnd)
AS
(
SELECT  @MinTransDate as  DateStart,  @MinTransDateEnd as DateEnd 
UNION ALL
SELECT DATEADD(dd,1,DateStart),DATEADD(dd,1,DateEnd)
FROM Date_CTE
WHERE DATEADD(dd,1,Date)<= @MaxTransDate 
)
SELECT DateStart,DateEnd
FROM Date_CTE

Open in new window


Then change the last part to what you want
SELECT d.DateStart,d.DateEnd,        MIN (l.sm_timestamp) as min_d
      , count( DISTINCT l.sm_timestamp ) AS loggings
      , MAX (sm_timestamp) as max_d

FROM Date_CTE d
Left join SELECT
FROM MPXLog4  as l on l.sm_timestamp between d.DateStart and d.DateEnd
group by d.DateStart,d.DateEnd

Open in new window

0
 
motioneyeAuthor Commented:
Hi Jogos,
The result seems not the correct output.
declare @MinTransDate Datetime;
declare @MinTransDateEnd Datetime;
declare @MaxTransDate Datetime;
SET @MinTransDate ='2013/08/01 00:00:00.000';
SET @MinTransDateEnd ='2013/08/01 23:59:59.000';
SET @MaxTransDate ='2013/09/01 00:00:00.000';


;With Date_CTE (DateStart,DateEnd)
AS
(
SELECT  @MinTransDate as  DateStart,  @MinTransDateEnd as DateEnd 
UNION ALL
SELECT DATEADD(dd,1,DateStart),DATEADD(dd,1,DateEnd)
FROM Date_CTE
WHERE DATEADD(dd,1,Dateend)<= @MaxTransDate 
)
SELECT d.DateStart,d.DateEnd, MIN (l.sm_timestamp) as min_d ,count( DISTINCT l.sm_timestamp ) AS loggings,MAX (sm_timestamp) as max_d
FROM Date_CTE d
Left join 
--SELECT * FROM
sMPXLog4   as l on l.sm_timestamp between d.DateStart and d.DateEnd
group by d.DateStart,d.DateEnd

Open in new window




DateStart      DateEnd      min_d      loggings      max_d
2013-08-01 00:00:00.000      2013-08-01 23:59:59.000      NULL      0      NULL
2013-08-02 00:00:00.000      2013-08-02 23:59:59.000      NULL      0      NULL
2013-08-03 00:00:00.000      2013-08-03 23:59:59.000      NULL      0      NULL
2013-08-04 00:00:00.000      2013-08-04 23:59:59.000      NULL      0      NULL
2013-08-05 00:00:00.000      2013-08-05 23:59:59.000      NULL      0      NULL
2013-08-06 00:00:00.000      2013-08-06 23:59:59.000      NULL      0      NULL
2013-08-07 00:00:00.000      2013-08-07 23:59:59.000      NULL      0      NULL
2013-08-08 00:00:00.000      2013-08-08 23:59:59.000      NULL      0      NULL
2013-08-09 00:00:00.000      2013-08-09 23:59:59.000      NULL      0      NULL
2013-08-10 00:00:00.000      2013-08-10 23:59:59.000      NULL      0      NULL
2013-08-11 00:00:00.000      2013-08-11 23:59:59.000      NULL      0      NULL
2013-08-12 00:00:00.000      2013-08-12 23:59:59.000      NULL      0      NULL
2013-08-13 00:00:00.000      2013-08-13 23:59:59.000      NULL      0      NULL
2013-08-14 00:00:00.000      2013-08-14 23:59:59.000      NULL      0      NULL
2013-08-15 00:00:00.000      2013-08-15 23:59:59.000      NULL      0      NULL
2013-08-16 00:00:00.000      2013-08-16 23:59:59.000      NULL      0      NULL
2013-08-17 00:00:00.000      2013-08-17 23:59:59.000      NULL      0      NULL
2013-08-18 00:00:00.000      2013-08-18 23:59:59.000      NULL      0      NULL
2013-08-19 00:00:00.000      2013-08-19 23:59:59.000      NULL      0      NULL
2013-08-20 00:00:00.000      2013-08-20 23:59:59.000      NULL      0      NULL
2013-08-21 00:00:00.000      2013-08-21 23:59:59.000      NULL      0      NULL
2013-08-22 00:00:00.000      2013-08-22 23:59:59.000      NULL      0      NULL
2013-08-23 00:00:00.000      2013-08-23 23:59:59.000      NULL      0      NULL
2013-08-24 00:00:00.000      2013-08-24 23:59:59.000      NULL      0      NULL
2013-08-25 00:00:00.000      2013-08-25 23:59:59.000      NULL      0      NULL
2013-08-26 00:00:00.000      2013-08-26 23:59:59.000      NULL      0      NULL
2013-08-27 00:00:00.000      2013-08-27 23:59:59.000      NULL      0      NULL
2013-08-28 00:00:00.000      2013-08-28 23:59:59.000      NULL      0      NULL
2013-08-29 00:00:00.000      2013-08-29 23:59:59.000      NULL      0      NULL
2013-08-30 00:00:00.000      2013-08-30 23:59:59.000      NULL      0      NULL
2013-08-31 00:00:00.000      2013-08-31 23:59:59.000      NULL      0      NULL
0
 
rshqCommented:
Please use this query

select min (sm_timestamp),max (sm_timestamp)   from  MPXLog4
 where str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp))
in
(
select  str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp)) 
   from      MPXLog4 with (nolock)
   group by str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp))
)
   order by str(year(sm_timestamp)) + str(month(sm_timestamp)) + str(day(sm_timestamp)) 

Open in new window

0
 
PortletPaulCommented:
This result  (on assumed data):
|                          SDATE |                  MIN_TIMESTAMP | EXISTING_STAMPS | MISSING_STAMPS |                  MAX_TIMESTAMP |
|--------------------------------|--------------------------------|-----------------|----------------|--------------------------------|
| October, 01 2013 00:00:00+0000 | October, 01 2013 00:00:00+0000 |              49 |          86351 | October, 01 2013 23:59:50+0000 |
| October, 02 2013 00:00:00+0000 | October, 02 2013 00:00:00+0000 |               2 |          86398 | October, 02 2013 23:56:59+0000 |
| October, 03 2013 00:00:00+0000 | October, 03 2013 00:00:00+0000 |               2 |          86398 | October, 03 2013 23:49:59+0000 |
| October, 04 2013 00:00:00+0000 |                         (null) |               0 |          86400 |                         (null) |
| October, 05 2013 00:00:00+0000 | October, 05 2013 00:00:00+0000 |               2 |          86398 | October, 05 2013 22:59:59+0000 |
| October, 06 2013 00:00:00+0000 | October, 06 2013 00:00:00+0000 |               2 |          86398 | October, 06 2013 10:00:05+0000 |
| October, 07 2013 00:00:00+0000 |                         (null) |               0 |          86400 |                         (null) |
... summarised result

Open in new window

Was produced by the following query:
-- establish date range
DECLARE @fromdate datetime
      , @todate datetime
SELECT
        @fromdate = '20131001' -- or -- '2013-09-01'
      , @todate   = '20131101' -- or -- '2013-10-01'

;WITH
        DateCTE (sDate, eDate)
        AS (
                SELECT
                        @fromdate AS sDate, dateadd(DAY,1,@fromdate) AS eDate
                UNION ALL
                SELECT
                        DATEADD(DAY, 1, sDate), DATEADD(DAY, 1, eDate)
                FROM DateCTE
                WHERE sDate < dateadd(DAY,-1,@todate)
                )
SELECT
        DateCTE.sDate
      , MIN (sm_timestamp)                          AS min_timestamp
      , count( DISTINCT sm_timestamp )              AS existing_stamps
      , (24*60*60) - count( DISTINCT sm_timestamp ) AS missing_stamps
      , MAX (sm_timestamp)                          AS max_timestamp
FROM DateCTE
LEFT JOIN MPXLog4 ON MPXLog4.sm_timestamp >= DateCTE.sDate
                  AND MPXLog4.sm_timestamp <  DateCTE.eDate
GROUP BY
        DateCTE.sDate
HAVING
       (24*60*60) - count( DISTINCT sm_timestamp ) > 0
    OR MIN (sm_timestamp) IS NULL
;
    
********* data used **********

CREATE TABLE MPXLog4
    ([sm_timestamp] datetime)
;
    
INSERT INTO MPXLog4
    ([sm_timestamp])
VALUES
    ('2013-10-01 00:00:00'),
    ('2013-10-01 01:00:00'),
    ('2013-10-01 02:00:00'),
    ('2013-10-01 03:00:00'),
    ('2013-10-01 04:00:00'),
    ('2013-10-01 05:00:00'),
    ('2013-10-01 06:00:00'),
    ('2013-10-01 07:00:00'),
    ('2013-10-01 08:00:00'),
    ('2013-10-01 09:00:00'),
    ('2013-10-01 10:00:00'),
    ('2013-10-01 11:00:00'),
    ('2013-10-01 12:00:00'),
    ('2013-10-01 13:00:00'),
    ('2013-10-01 14:00:00'),
    ('2013-10-01 15:00:00'),
    ('2013-10-01 16:00:00'),
    ('2013-10-01 17:00:00'),
    ('2013-10-01 18:00:00'),
    ('2013-10-01 19:00:00'),
    ('2013-10-01 20:00:00'),
    ('2013-10-01 21:00:00'),
    ('2013-10-01 22:00:00'),
    ('2013-10-01 23:00:00'),

    ('2013-10-01 00:01:00'),
    ('2013-10-01 01:01:00'),
    ('2013-10-01 02:01:00'),
    ('2013-10-01 03:01:00'),
    ('2013-10-01 04:01:00'),
    ('2013-10-01 05:01:00'),
    ('2013-10-01 06:01:00'),
    ('2013-10-01 07:01:00'),
    ('2013-10-01 08:01:00'),
    ('2013-10-01 09:01:00'),
    ('2013-10-01 10:01:00'),
    ('2013-10-01 11:01:00'),
    ('2013-10-01 12:01:00'),
    ('2013-10-01 13:01:00'),
    ('2013-10-01 14:01:00'),
    ('2013-10-01 15:01:00'),
    ('2013-10-01 16:01:00'),
    ('2013-10-01 17:01:00'),
    ('2013-10-01 18:01:00'),
    ('2013-10-01 19:01:00'),
    ('2013-10-01 20:01:00'),
    ('2013-10-01 21:01:00'),
    ('2013-10-01 22:01:00'),
    ('2013-10-01 23:01:00'),

    ('2013-10-01 23:59:50'),
    ('2013-10-02 00:00:00'),
    ('2013-10-02 23:56:59'),
    ('2013-10-03 00:00:00'),
    ('2013-10-03 23:49:59'),
    ('2013-10-05 00:00:00'),
    ('2013-10-05 22:59:59'),
    ('2013-10-06 00:00:00'),
    ('2013-10-06 10:00:05')
;

Open in new window

And it may be tested/used at http://sqlfiddle.com/#!3/16b06/2
0
 
motioneyeAuthor Commented:
Hi PortletPaul
Its really working like expected, just need little modification to meet my needs.

Thanks for the scripts, it look complex though :)
0
 
PortletPaulCommented:
not realy that complex.

1. setup some variables

2. use a "recursive CTE" to build a date range (one record per day)

3. Using that CTE we now have all the days, left join your log data to this, and we can now reveal if a day is missed and by counting we can deduce if any timestamp has been missed.

If you aren't familiar with point 2, yes, these can bend your head at first.
The trick is: "it calls itself" (see "the guts" parts 1 and 2)

;WITH
DateCTE (sDate, eDate) -- part 0, gives itself a name
AS (
    -- the guts, part 1, establishment
    SELECT
            @fromdate AS sDate, dateadd(DAY,1,@fromdate) AS eDate
    UNION ALL
   
    -- the guts, part 2,  is RECURSIVE, it calls itself (by name)
    SELECT
            DATEADD(DAY, 1, sDate), DATEADD(DAY, 1, eDate)
    FROM DateCTE
   
    -- the guts, part 3, whoa there! - the get out clause
    WHERE sDate < dateadd(DAY,-1,@todate)
   
    )

the head bending bit is:
part 2 looks at part 1, does some stuff and result grows
part 2 looks at results so far, does some stuff and result grows
part 2 looks at results so far, does some stuff and result grows
part 2 looks at results so far, does some stuff and result grows
... continues
until get out clause is reached
0
 
motioneyeAuthor Commented:
Thanks for the explanation....Its always fun learning from others :)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 8
  • 4
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now