Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-05
9
Medium Priority
?
229 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Screencast - Getting to Know the Pipeline

971 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