Solved

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

Posted on 2011-03-05
9
222 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 40

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 40

Expert Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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