Solved

Counts by SQL date range

Posted on 2014-04-03
5
984 Views
Last Modified: 2014-04-07
hi, i need some help getting started with the proper SQL to do a count, or group by date range.

I have items that are rented over periods of time, or date ranges. Currently i can say, from 4/1 to 5/30 there are 20 items rented, and 15 not. I would like to enhance this to break out the booking by week, so it would look like the following table:

              4-7-14    4-14-14      4-21-14       5-5-14         5-12-14        5-19-14       5-26-14
Asset type 1
Rented      10            15                25               20             20                 10                5
not rent     20           15                5                 10            10                  20              25

Asset Type n
Rented      10            15                25               20             20                 10                5
not rent     20           15                5                 10            10                  20              25


notice the dates need to start on the Monday of each full week within the range, so while the choosen range here is 4/1-5/31, so the first week range is actually 4/7, because that is the first Monday (4/2 is a tuesday).

I'm just trying to get an idea of the way i need to group and structure the results to make this work? Can i do this in a single query or will it be better to use temp tables/table variables for manipulating the data? I just don't know how to get started to get this output. The table i showed above is the ideal result of an SSRS report - so the column headers there don't have to be the column headers in the query, as long as i can identify the start date, group, and asset type. there are multiple asset types.
0
Comment
Question by:mikegrad7
[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
5 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39975168
It would be something like this:

SET DATEFIRST 1 

SELECT DATEPART ( week , DateFld), ItemId, Count(Rented)
FROM RentalsTable
GROUP BY DATEPART ( week , DateFld), ItemId
ORDER BY DATEPART ( week , DateFld), ItemId

Open in new window



Datefirst ref: http://msdn.microsoft.com/en-us/library/ms181598.aspx
DATEPART ref: http://msdn.microsoft.com/en-us/library/ms174420.aspx
0
 
LVL 1

Author Comment

by:mikegrad7
ID: 39975333
wow, is it that easy? I hope so, i'm going to give it a shot and see.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39975804
Would need to see the "rent" table structure.  I suspect you will need a version of the "Gaps and Islands" queries researched by Itzik Ben-Gan.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39975869
If you want to count the 4/1-4/7 values as on 4/7 and 4/8-4/14 values as on 4/14, you can try like this.

This gives the next Monday of today. If today is Monday, it simply gives that date.
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), CASE WHEN DATEPART(dw,GETDATE()) <> 2 THEN 7 ELSE 0 END)

Open in new window

You can use this in your query.
SELECT NextMonday,COUNT(Rented) RentedCnt
  FROM (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, DateFld), CASE WHEN DATEPART(dw,DateFld) <> 2 THEN 7 ELSE 0 END) NextMonday,
			   Rented
		  FROM RentalsTable) T1
 GROUP BY NextMonday
 ORDER BY NextMonday

Open in new window

0
 
LVL 1

Author Closing Comment

by:mikegrad7
ID: 39983856
accepting the first solution as thats what i looked at before going off and making the changes. All were good !
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
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.

762 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