Quarterly Values

i have a date heirarchy

dim date

Year

Quarter

Month

and measures SalesVolume

how can i create salesvolume calculated measure for prev quarter, prev 3 quarters and so on.

Dear Chilukurisri,

try to use the same logic of the other post and try to use the function QTD too.

Helped?

Regards!

|||thanx pedroCGD, but QTD will give Quarters to date value so if we select the current member then it will give the sum of quarters for all the values starting beginning of the year but what i am looking is for a formula to calculate last 3 quarters irrespective of wheather i select what member in the current year i.e end of the quarter or middle of the quarter. How can i achieve that?

Quarterly Schedule Fails

My data driven report has been scheduled to send last quarters data on the
fifth day of a new quarter.
I set the schedule to send:
Monthly for: Jan, April, July, Oct and on
Calendar Day: 5 at 2am.
For some reason I received the report today, which happens to be May 5.
What am I doing wrong?I also check the system date on the Reporting Services Server, so that's not
the problem. Is there another way to schedule Quarterly report delivery?
"John Broomfield" wrote:
> My data driven report has been scheduled to send last quarters data on the
> fifth day of a new quarter.
> I set the schedule to send:
> Monthly for: Jan, April, July, Oct and on
> Calendar Day: 5 at 2am.
> For some reason I received the report today, which happens to be May 5.
> What am I doing wrong?
>

Quarterly Chart

Hi, i have a table with column for quarter and year (just like the sample Report - Company Sales).
now, i'm trying to create a chart which shows the quarter as the series group /x-axis... how can i show the label of the quarter and the year. currently, it can only use either year or quarter.
so, what i want the x-axis label to be like this:
e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
2003 2004If you want the quarter to show up on the x-axis, then it has to be a
category group, not a series group. Just use two category groupings (one for
quarter, one for year) and you should get the desired labels.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> Hi, i have a table with column for quarter and year (just like the sample
Report - Company Sales).
> now, i'm trying to create a chart which shows the quarter as the series
group /x-axis... how can i show the label of the quarter and the year.
currently, it can only use either year or quarter.
> so, what i want the x-axis label to be like this:
> e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> 2003 2004|||ah yes, i meant the category. however, the label can only show either quarter or year. not both.
i need to differentiate between the first q1 and the second q1. I can only put on field for the 'label'. if i leave it blank, it will only show it by the quarter.
"Robert Bruckner [MSFT]" wrote:
> If you want the quarter to show up on the x-axis, then it has to be a
> category group, not a series group. Just use two category groupings (one for
> quarter, one for year) and you should get the desired labels.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> > Hi, i have a table with column for quarter and year (just like the sample
> Report - Company Sales).
> > now, i'm trying to create a chart which shows the quarter as the series
> group /x-axis... how can i show the label of the quarter and the year.
> currently, it can only use either year or quarter.
> >
> > so, what i want the x-axis label to be like this:
> > e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> > 2003 2004
>
>|||Please try and investigate the attached example which gives you two levels
of groupings on the category axis (inner level: month, outer level: year).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Type>Circle</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales / Cost</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="YearGroup">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="MonthGroup">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.625in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||got it... i didn't pay attention to the category groups, instead i put it in the group on inside the category group.
"Robert Bruckner [MSFT]" wrote:
> Please try and investigate the attached example which gives you two levels
> of groupings on the category axis (inner level: month, outer level: year).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="TotalSalesByYear">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style />
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <Color>Brown</Color>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel />
> <Marker>
> <Type>Circle</Type>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=(Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)+8000)*1.15</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> <Visible>true</Visible>
> </DataLabel>
> <Marker>
> <Type>Diamond</Type>
> <Size>10pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> <PlotType>Line</PlotType>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>100</PointWidth>
> <Type>Line</Type>
> <Title>
> <Caption>Sales / Cost</Caption>
> <Style>
> <FontSize>14pt</FontSize>
> <FontWeight>700</FontWeight>
> </Style>
> </Title>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="YearGroup">
> <GroupExpressions>
> <GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label />
> </DynamicCategories>
> </CategoryGrouping>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="MonthGroup">
> <GroupExpressions>
> <GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderDate.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>6.125in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <StaticSeries>
> <StaticMember>
> <Label>Cost</Label>
> </StaticMember>
> <StaticMember>
> <Label>Sales</Label>
> </StaticMember>
> </StaticSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundGradientEndColor>White</BackgroundGradientEndColor>
> <BackgroundGradientType>TopBottom</BackgroundGradientType>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <ValueAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <MinorTickMarks>Outside</MinorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>6.5in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.625in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
> Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>

Quarter() function in SSRS

I am uisng VS 2005 to build reports for SQL 2005. The problem is with getting the Quarter() function, mentioned in the BOL, to work in the report builder. When I build an expression and try to use the function as noted in the BOL I receive an error stating that the name "Quarter" is not declaired. When I use the expression builder to build the expression the function is not available in the builder, which is probably why I get the error.

I am assuming there is some sort of service pack of hot fix I need to apply to my VS but have not been able to locate which one. I have verified that I have all of the recent service packs applied for VS and SQL server 2005 but I am still unable to see/use this function.

Any help would be greatly appreciated. I need this function for my financial reports.

Using the QUARTER(datetime) function does not seem to be available in SQL 2005 SSRS;

=Quarter(today())

Results in an error.

After playing with the date part function I was able to resolve my problem. If you are having a similar problem use the following function to extract the quarter from a date

=DatePart(DateInterval.Quarter,today())

Replace Today() with the date you want to get the value for. You can test this by creating a report and placing a textbox anywhere on the report and setting the above code as the expression for the value of the textbox.

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
> > How can I create stored procedure with data grouped by quarter?
> > I am converting database from Access to SQL and learn SQL in the
same
> > time.
> > In Access that is simple because there is function for that.
> >
> > I was looking in the different news groups and found few answers
like
> > truncating data, but cannot find it it T-SQL.
> >
> > Thank you
> >
> >

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:

> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
>
same[vbcol=seagreen]
like[vbcol=seagreen]

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank you
Hi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegr oups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:

> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
same[vbcol=seagreen]
like[vbcol=seagreen]

quarter date

How can I calculate the first day of the quarter based on the current date?
Ex. Today's date is 5/30/06. I'm trying to get 4/1/06.
Thanks!maybe something like this..
declare @.a datetime
set @.a = getdate()
select @.a
select cast(cast(year(@.a) as varchar)+ '0' + cast((datepart(qq,@.a)-1)*3 + 1
as varchar) + '01' as datetime)|||Like this...
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Boy.. excellent piece of code :)
"SQL Menace" wrote:

> Like this...
> SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Juz one nagging doubt. Why yuu need that +0
wouldn't this work good enuf'
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)|||Perfect, thanks so much!
"SQL Menace" wrote:

> Like this...
> SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks
Denis the SQL Menace
http://sqlservercode.blogspot.com/