Post New Web Links

Getting MAX Per Day for Months

Posted By: Posted Date: September 03, 2010 Points: 0 Category :Sql Server

I have a table containing measurements taken every 5 minutes for a span of 8 months. What I would like to do, is take the max measurement per day and return it. Right now I'm using the following query which does the trick, but would involve a LOT of typing if I tried to return all the data I need: SELECT Date, MAX(Temperature) AS Temp FROM tblTemp WHERE Date IN ('3/5/2010', '3/6/2010') GROUP BY Date This returns the max temperature for 3/5/2010 and 3/6/2010 fine... but you can see how this would be cumbersome doing it all the way through August :-)

View Complete Post

More Related Resource Links

AGE CALCULATION IN YEARS AND MONTHS

How to calculate childs age,but when i enter childs age as '03/27/2007' it returns 3 years where as actual age is 2 yr 11 months

Display name of months on x-axis in a chart

Could you please let me know if there is any expression which can be used to display the months on x-axis of the column chart. I've tried playing with the Axis option trying to change the interval type to "months" but it is not giving the result. Thank you.

PerformancePoint filters arranging months out of order

I have a pretty simple analytic chart on my sharepoint site. The columns are The name of the location, and the month, and then all of the data is in the rows. so basically it looks like this Place 1 Place 2 jan feb mar april may june jan feb mar april may june row1 row2 row3 You get the point. The problem is, I want to have two filters; one that filters by the locations, and one that filters by the months. When I created the locations filter and connected it to the grid, it made all of the months go in alphabetical order instead of the correct order, like so: place 1 aug dec jul nov oct row1 row2 Obviously, this is not how a calendar is supposed to look. I already changed the properties in my sql cube to make the months go in the correct order in the unfiltered chart. They are ordered by the key, which is mont

Algorithm for Computing Age in Years, Months and Days?

I am generating a crystal report.net that compute age of a person through formula. of course, there is the birthdate and current date. I am familiar with the datediff function but I want to compute is like: "18 Years, 2 Months and fifteen days old." it should include the leap year. Any Algorithm that I can apply to crystal report formula? Your help is very appreciated.

Finding rolling costs for 13 months

Hi Experts, I need urgent help on this issue I have a formula 'Sum(LastPEriods(13, CLosingPeriod([Rolling Date].[Month], [Rolling Date].CurrentMember)), [Measures].[Waste Costs])' which calculates rolling costs for last 13 months e.g today current month + last 12 months ,but when i drill down to hierarchy level 8 it shows me few records of year 2006 also,i think this shuld not be case,coz formula should show me records till july 2009 (assuming go back 13 months from current date) can you please gude me are there any corrrections is formula or if i m going to level 8 -drill down then it will include costs for 2006 also? I'm unable to make any firm conclusion on thisHi Experts, I need urgent help on this issue I have a formula 'Sum(LastPEriods(13, CLosingPeriod([Rolling Date].[Month], [Rolling Date].CurrentMember)), [Measures].[Waste Costs])' which calculates rolling costs for last 13 months e.g today current month + last 12 months ,but when i drill down to hierarchy level 8 it shows me few records of year 2006 also,i think this shuld not be case,coz formula should show me records till july 2009 (assuming go back 13 months from current date) can you please gude me are there any corrrections is formula or if i m going to level 8 -drill down then it will include costs for 2006 also? I'm unable to make any firm conclusion on this

Calculated Measure Help - Comparing Months from two Date Dimensions

Hello: I have a Measure Group that has two Date dimensions, a Reporting Date and a Fee Paid Date. The Reporting Date and the Fee date is based on the same Dimension, which has a standard YQMD hierarchy. It's very typical. The calculation is called "Fees Paid In Same Period", and it would display the Fees paid if the Reporting Month equals the Fees Paid Month. How do i do this? Thanks,

Displaying multiple calendar months in SP 2007

I need to display multiple months using the calendar in SP. For example, my user wants to select a month ( we'll say May ) and have May, June and July displayed. Is this possible?

Help with Percentage Counts for Prior 3 months

I am trying to calculate the counts of issues for the prior 3 months as a percent of the total of all issues for the prior 3 months. eg. For Sept. I would need the counts for Jun, Jul, Aug for each issue type divided by the total number for all issue types for those 3 months..

For the numerator I used the following and have confirmed that it is correct via SQL queries.

CREATE MEMBER CURRENTCUBE.[Measures].[PctUniqueIssuesNumerator] AS Aggregate

( {[IssueDate].[ProgMonth].CurrentMember.Lag(3) : [IssueDate].[ProgMonth].CurrentMember.Lag(1)}

[InvoiceIssueType].[Invoice Issue Type].CurrentMember

[Measures].[Invc Id Distinct Count - IssueFact]

VISIBLE = 1;

For the denominator I am using this MDX but doesnt seem to be correct. Should I be doing it differently? I need the sum of [Invc Id Distinct Count - IssueFact] for all issues types for the prior 3 months.

CREATE MEMBER CURRENTCUBE.[Measures].[PctUniqueIssuesDenominator] AS Sum(

DESCENDANTS([InvoiceIssueType].[Invoice Issue Type].[All],1),

&nbs

function or query to return continuous span of months $ years as rows in query

Hello,

I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?

1/2004

2/2004

3/2004

and so on to

........

6/2010

7/2010

8/2010

Thanks in advance,

John

Pivotal Query to sum up months and quarters together

Hi all,

i need below output do i need to use cursors for this can i do this with case statement.

can some one solve this for me if they have already solved similar problem before.

CREATE TABLE [dbo].[tablesales2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost] [numeric](18, 0) NULL,
[saledate] [datetime] NULL,
)

insert into [tablesales2](name,cost,saledate)values('pen',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pen',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pen',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pen',400,'12/12/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',400,'12/12/2001')

i need to have the o

Calculation of each field with last12 months data

Hi,

Somebody help me with the SQL logic...The current data(Available Values) is derived from a table are shown below....The Hours and REC columns are calculated by sum of last 12 months data(values from available). I need to SQL code or any function in SSRS that does this, so that it matches the Expected values…

Available

Month - Year

Return Last 3 Months data based on the DATE

Using the getdate() function, how do I select the last WHOLE 3 months,

So for example, for todays date (29/09/2010)

I want to return all records with the date between 01/06/2010 and 31/08/2010

Is this possible?

12 months to Last month loaded using MDX

I'm struggling to define a calculated member which sums up the measure from current loaded month in the time dimension to 12 months prior. For example, if the current loaded month into my [Date].[Date Hierarchy] is August 2010 then it should sum up from August 2009 to August 2010.

I tried to get the current loaded month using

CLOSINGPERIOD ([ Date].[Date Hierarchy].[Month Name],
strtomember("[Date].[Date Hierarchy].[" + cstr(year(now())) +"]"))

but it gives me the month of December instead of August.

Any Help?

Thanks,

Parth

Get number of months from number of days

Hello

I have 2 dates (in the form MM/DD/yyyy) representing the start date of the employee and the end date of the fiscal year:

the employee started working on 12/15/2010 and the fiscal year ends on 08/31/2011

I want to calculate the number of months that the employee worked between those 2 dates

in reality this employee will have been working until 08/31/2011 for 8.5 months

datediff function returns 8

how to calculate exact number of months in sql?

thank you in advance

Sum weeks when you are displaying months

Hi,

I'm trying to sum weeks 1+2 together when the display is by month.
The problem is that even when summing just weeks 1+2, I get the total for the whole month!

Not sure why it's happening, but it must be wrong what I'm doing.

Any help is appreciated.

Thanks,
Andrew.

WITH

MEMBER [Measures].[Purchases First Half Month] AS

SUM([Date].[Retail Week].

calendarButtonExtender how to change the names of the months,etc...

Hi All,
calendarButtonExtender how to change the names of the months, the word "Today" and the word "Su" "Mo," "Here," "We", "Th", "Fr" "Sa", how to replace in other words?
Regards,
Dejan

GETDATE( ) : 3 months back

I need to add two parameters to my report... Start Date and End Date, the End Date will todays date which I can get from GETDATE() but how do i get the Start Date, which should be 3 months back from todays date?

POSSIBLE START DATE = SELECT DATEADD(MsONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0)

ENDATE = SELECT GETDATE()


Categories:

ASP.Net	Windows Application	.NET Framework	C#	VB.Net	ADO.Net
Sql Server	SharePoint	Silverlight	Others	All

Hall of Fame Twitter Terms of Service Privacy Policy Contact Us Archives Tell A Friend