StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
7677573
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-10-06T16:36:03.527
FavoriteCount
0
LastActivityDate
2011-10-06T16:52:45.820
LastEditDate
2011-10-06T16:52:45.820
LastEditorUserId
941418
OwnerUserId
941418
ParentId
7676178
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
I started to think about it and wondered if there was a way to streamline it and do so without temp tables. Since everything I've been doing lately seems to involved CTEs I worked up a solution going that route. This was the result, it might not be the most efficient way to go about it from a CTE perspective but I prefer it to a bunch of temp tables and it leaves most of the nasty casting out of the final <code>SELECT</code>: <pre><code> ;WITH CompanyDays AS ( SELECT Company ,MIN(ActualStartTime) AS StartDate ,MAX(endtime) AS EndDate FROM Companies WHERE finaljobstatus = '19' OR (finaljobstatus = '3' AND tasktypeid = '100') GROUP BY Company ) ,StartFileSize AS ( SELECT c.Company ,c.totaldatasizebytes AS StartFileSize ,cd.StartDate ,cd.EndDate FROM Companies AS c JOIN CompanyDays AS cd ON c.actualstarttime= cd.StartDate WHERE (finaljobstatus = '19' OR (finaljobstatus = '3' AND tasktypeid = '100')) ) ,EndFileSize AS ( SELECT c.Company ,CAST(s.StartFileSize AS DECIMAL(18,0)) AS StartFileSize ,CAST(c.totaldatasizebytes AS DECIMAL(18,0)) AS EndFileSize ,DATEDIFF(dd,cd.StartDate,cd.EndDate) AS DayDiff ,CAST(c.totaldatasizebytes AS DECIMAL(18,0)) - CAST(s.StartFileSize AS DECIMAL(18,0)) AS FileDiff ,cd.StartDate ,cd.EndDate FROM Companies AS c JOIN CompanyDays AS cd ON c.endtime= cd.EndDate JOIN StartFileSize AS s ON c.Company = s.Company WHERE (finaljobstatus = '19' OR (finaljobstatus = '3' AND tasktypeid = '100')) ) SELECT e.Company ,DayDiff ,FileDiff ,(FileDiff/e.StartFileSize)/DayDiff AS AveragePercentageChange FROM EndFileSize AS e </code></pre> Note that <code>DATEDIFF</code> on the actual start and end dates with times might skew things slightly because it only counts the actual day boundaries that are crossed (i.e., Midnight). In most cases, that will be perfectly fine. If not, you can take another measure like datediff in hours and divide the result by 24; minutes and divide it by 1440; seconds and divide it by 86400 depending on exactly how precise you need the day difference to be. EDIT: I realized my math was off. What if there was a decrease between the two days? Fixed that and moved some more casting into the CTE.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POComplicated query involving calculation of date ranges
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USWil
UserOwnerUserId
1. USWil
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.