Solved

Complie a list of dates and count of how many within these dates

Posted on 2011-03-05
9
225 Views
Last Modified: 2012-05-11
Hi,

I have a field in my DB that contains a dates, I would like to output a stored procedure a list of dates in a weekly format (and possibly a monthly format) with the count of many fall into these dates, I have attached a sample workbook in excel that shows a sample of field containing the dates and the required output.

Any help is appreciated.

Thanks,

 sample-data.xls
0
Comment
Question by:sanjshah12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35043627
like this?

the standard master.dbo.spt_values type=4 has a range of 0-2047  is that sufficient?
Select rangename as [date],count|(*) as requiredcount
  from youtable as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as rangename
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12
             ) as x
   on a.daterequired 
      between x.startdate and x.enddate
group by rangename
order by 1

Open in new window

0
 

Author Comment

by:sanjshah12
ID: 35043657
Apologies but I do not know what master.dbo.spt_values refers to but if this refers to number rows outputed there should not be more than 1 year.

I'll test the code.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35043706
master.dbo.spt_values is a system table in the master database of sql server...
it can be used as a source of a list of numbers ...
Select rangename as [date],count|(*) as requiredcount
  from youtable as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as rangename
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12  --<  set you max number of weeks (max value 2047)
             ) as x
   on a.daterequired 
      between x.startdate and x.enddate
group by rangename
order by 1

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:sanjshah12
ID: 35044208
Hi lowspeed, I've tried the code as:

Select daterequired as [date],count(*) as requiredcount
  from tbl_test as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as daterequired
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12  --<  set you max number of weeks (max value 2047)
             ) as x
   on a.daterequired
      between x.startdate and x.enddate
group by tbl_test.daterequired
order by 1

but recieve an error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tbl_test.daterequired" could not be bound.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'daterequired'.

I'm not I've added the column name in the correct [places.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35044565
That is because daterequired belongs to x and not to tbl_test.  In other words, change:
group by tbl_test.daterequired
for:
group by x.daterequired
0
 

Author Comment

by:sanjshah12
ID: 35044608
aceperkins,

I have changed this as suggested:

Select datecompleted as [date],count(*) as requiredcount
  from tbl_test as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as daterequired
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12  --<  set you max number of weeks (max value 2047)
             ) as x
   on a.datecompleted
      between x.startdate and x.enddate
group by group by x.daterequired
order by 1

but now recieve the following error:

Msg 8120, Level 16, State 1, Line 2
Column 'tbl_test.datecompleted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35044937
Can you double check your expected result? With your sample set, I got this output.

Try this qury.
DECLARE  @Date  TABLE( 
                      Min_dateRequired DATETIME, 
                      Max_dateRequired DATETIME 
                      ) 

INSERT @Date 
SELECT MIN(dateRequired) Min_dateRequired, 
       MAX(dateRequired) Max_dateRequired 
  FROM your_table; 

WITH DateCTE 
     AS (SELECT Min_dateRequired, 
                DATEADD(DAY,7,Min_dateRequired) NextDay, 
                Max_dateRequired 
           FROM @Date 
         UNION ALL 
         SELECT DATEADD(DAY,7,NextDay), 
                DATEADD(DAY,7,NextDay) NextDay, 
                Max_dateRequired 
           FROM DateCTE 
          WHERE NextDay <= Max_dateRequired) 
SELECT CONVERT(VARCHAR,Min_dateRequired,3) [Date], 
       (SELECT COUNT(* ) 
          FROM your_table t2 
         WHERE t2.dateRequired <= t1.NextDay) RequiredCount 
  FROM DateCTE t1
/*
Date	RequiredCount
07/02/11	12
21/02/11	24
28/02/11	46
07/03/11	60
14/03/11	70
21/03/11	76
28/03/11	82
04/04/11	85
*/

Open in new window

0
 

Author Comment

by:sanjshah12
ID: 35062615
Sharath_123,

that's excellent - working exactly as needed! thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35062854
Glad I could help you.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Not listening to where 1 42
Merge join vs exist 3 37
Insert multiple records into a table 4 37
T-SQL: Need to trim a single leading space 7 62
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question