Solved

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

Posted on 2011-03-05
9
224 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL convert date to string 4 56
Many to one in one row 2 35
Database Owner 3 14
Need to trim my database size 9 16
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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