Solved

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

Posted on 2011-03-05
9
220 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 90
MSSQL 2014 Query Synthax 8 38
encyps queries mssql 15 27
SQL Update Query - What's wrong with this. 18 13
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now