July 23, 2008 at 10:59 am
I have a date filed like
2006-01-01 00:00:00
2006-02-01 00:00:00
2006-03-01 00:00:00
2007-01-01 00:00:00
2007-02-01 00:00:00
I need a search critirea where i can get values only for the quarter month. ie' march,june,sept and so on.
July 23, 2008 at 11:07 am
I think this worked well for me.
where month(date) in (3,6,9,12)
July 23, 2008 at 12:57 pm
select period,Hours from table Emp
REsult set
period Hours
2005-09-01 00:00:00 9.25
2005-07-01 00:00:00 878.5
2005-03-01 00:00:00 930
2006-01-01 00:00:00 920.25
2007-02-01 00:00:00 169
2005-10-01 00:00:00 278
How can i calculate sum of hours for each quarter, like for every 3 months in a year i want total hours.
Expecting Result Set
For year 2005 Quarter1 Hours(Jan+Feb+March) = 1230 hrs
July 23, 2008 at 1:00 pm
Take a look at the DatePart() function using qq or q as the datepart argument.
--SJT--
July 23, 2008 at 1:03 pm
How can i get sum of quarters for each year with datepart function.
thanks
July 23, 2008 at 1:32 pm
Like this
SELECT
quartervalue = DATEPART(qq,datetimefield),
quartersum = SUM(salesvalues)
FROM
Sales_data
WHERE
datepart(yy,datetimefield) = somevalue
GROUP BY
DATEPART(qq,datetimefield)
July 23, 2008 at 1:34 pm
From the hip:
SELECT DatePart(yyyy,period), DatePart(qq,period), Sum(Hours)
FROM EMP
GROUP BY DatePart(yyyy,period), DatePart(qq,period)
ORDER BY DatePart(yyyy,period), DatePart(qq,period)
COMPUTE Sum(Hours) BY DatePart(yyyy,period)
or without the COMPUTE with quarters first:
SELECTDatePart(qq,period),
DatePart(yyyy,period),
Sum(hours)
FROMdbo.emp
GROUP BYDatePart(YYYY,period),
DatePart(QQ,period)
Edit: added second option.
If you would like to format it in a more useful fashion, we can probably work up a CTE version.
--SJT--
July 23, 2008 at 2:00 pm
here sum(Hours) will sum all the hours, i just need sum for each quarter like
sum(1,2,3) as qtr 1
sum(4,5,6) as qtr 2
How would a CTE version look like?
July 23, 2008 at 2:17 pm
Mike Levan (7/23/2008)
here sum(Hours) will sum all the hours, i just need sum for each quarter likesum(1,2,3) as qtr 1
sum(4,5,6) as qtr 2
If I understand you, then you want the sum of hours by quarter, irrespective of year, arranged vertically. If that is the case, we can remove the DatePart(yyyy,hours) from the SELECT and GROUP BY clauses from the second query in my earlier post. This will net the following:
12019.25
3887.75
4278
The sample data had nothing in Q2 (months 4, 5, or 6), so there's no result for that.
If this isn't what you're after, or I've missed something, please post as such.
--SJT--
July 23, 2008 at 2:24 pm
that was only little data, i'll put some data for u
2005-09-01 00:00:00 9.25
2005-07-01 00:00:00 878.5
2005-03-01 00:00:00 930
2006-01-01 00:00:00 920.25
2007-02-01 00:00:00 169
2005-10-01 00:00:00 278
2007-02-01 00:00:00 169
2007-02-01 00:00:00 123
2007-02-01 00:00:00 175
2007-02-01 00:00:00 145
2007-02-01 00:00:00 139
2005-03-01 00:00:00 930
2005-03-01 00:00:00 912
2005-03-01 00:00:00 923
2005-03-01 00:00:00 986
so here we see many records for the 1st 3 months and i wud like to sum all the values from 1st 3 months into 1 qtr.
July 23, 2008 at 2:58 pm
Mike,
The second set of data provided also contains nothing for Quarter 2 (April, May, June). Using the second set of data, here's how I get the Sum of all hours by Quarter:
CREATE TABLE #emp (period datetime, hours float)
INSERT INTO #emp
SELECT'2005-09-01 00:00:00',9.25 UNION
SELECT'2005-07-01 00:00:00', 878.5 UNION
SELECT'2005-03-01 00:00:00', 930 UNION
SELECT'2006-01-01 00:00:00', 920.25 UNION
SELECT'2007-02-01 00:00:00', 169 UNION
SELECT'2005-10-01 00:00:00', 278 UNION
SELECT'2007-02-01 00:00:00', 169 UNION
SELECT'2007-02-01 00:00:00', 123 UNION
SELECT'2007-02-01 00:00:00', 175 UNION
SELECT'2007-02-01 00:00:00', 145 UNION
SELECT'2007-02-01 00:00:00', 139 UNION
SELECT'2005-03-01 00:00:00', 930 UNION
SELECT'2005-03-01 00:00:00', 912 UNION
SELECT'2005-03-01 00:00:00', 923 UNION
SELECT'2005-03-01 00:00:00', 986
SELECTDatePart(qq,period) AS 'Quarter',
Sum(hours) AS 'SumOfHours'
FROM#emp
GROUP BYDatePart(qq,period)
This results in:
QuarterSumOfHours
15422.25
3887.75
4278
So for all years you get the sum of hours in each quarter. If you want the sum of hours in each year:
SELECTDatePart(yyyy,period) AS 'Year',
DatePart(qq,period) AS 'Quarter',
Sum(hours) AS 'SumOfHours'
FROM#emp
GROUP BYDatePart(yyyy,period),
DatePart(qq,period)
results in:
YearQuarterSumOfHours
200513751
20061920.25
20071751
20053887.75
20054278
A Quarter value of 1 is Jan,Feb,March; 2 is Apr,May,Jun; etc...
If this isn't helpful, you may want to fire up a query window and experiment with some results.
--SJT--
July 23, 2008 at 3:12 pm
This is my original Query
SELECT DatePart(yyyy,CompanySnapShot.Period)as Year,
DatePart(qq,CompanySnapShot.Period)as Quarter,
CompanySnapShot.DivId AS DivisionId,
CASE
WHEN SUM(TotalHoursWorked) > 0
THEN (SUM(ReportableCount) * 200000) / SUM(TotalHoursWorked)
ELSE 0 END AS RR
FROM vwSiteReportableRates_Stripped AS RRates INNER JOIN
dbo.CompanySnapShot ON DATEPART(year,CompanySnapShot.Period) = RRates.ReportableYear
AND CompanySnapShot.ProjectId = RRates.ProjectCode
GROUP BY DatePart(yyyy,CompanySnapShot.Period), DatePart(qq,CompanySnapShot.Period),dbo.CompanySnapShot.DivId
I get the following result from ur query
Year Quarter Hours
200549.0291497069138
200547.85937015738663
200544.6854181003558
200542.35522544827826
200546.45166303414457
200545.06111733437623
200614.07233484773286
200613.91416745697332
200612.7778088104923
200612.62812463486862
200615.74985833222822
200612.85668188917445
200615.80391438960263
200624.07233484773286
200623.81554585699821
200622.76296229154851
200622.63252093234623
200625.74985833222822
200622.84994564020163
200625.73792528336684
200634.07233484773286
200634.05606623745449
200632.83077432906638
20063 2.55290299774369
July 23, 2008 at 3:26 pm
Hmm,
Are you adding the value CompanySnapShot.DivId (or some other expression) into the GROUP BY clause of the query I provided? That would show an additional row in the result for every distinct value in CompanySnapShot.DivId, even if Year and Quarter didn't 'change'.
The rule for aggregates is that non-aggregate expressions in SELECT have to be in the GROUP BY... but expressions in GROUP BY don't have to show up in SELECT.
Regards,
--SJT--
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply