Last Three Full Months?

Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
DanBreak down the problem. First, you want the first day of the current month:
convert (char 6), getdate, 112) + '01'
Next, you want from 3 months previous, up to but not including the first of
this month. The 3 months previous are:
dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
Now, put these into your WHERE clause:
WHERE
MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
and MyDate < convert (char 6), getdate, 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
Dan|||declare @.date smalldatetime
declare @.start smalldatetime
declare @.end smalldatetime
set @.date = '2004-02-04'
set @.start = dateadd(d, -(datepart(d, @.date)) + 1, dateadd(m, -3, @.date))
set @.end = dateadd(d, -(datepart(d, @.date)) + 1, @.date)
select ... from invoice where invoiceDate >= @.start and invoiceDate < @.end
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
> Hi,
> I have sales line item detail like
> invoicedate, custid, ordernum
> Is there a way using SQL to only return records for the last three full
> months? For example, if today is 2/4/05, I would want records with
> invoicedate between 11/1/04 and 1/31/05.
> TIA,
> Dan|||On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current mont
h:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first o
f
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||Sorry about that. I'm a little distracted by back pain right now. Try
this:
WHERE
MyDate > = dateadd (mm, -3, convert (char (6), getdate(), 112) + '01')
and MyDate < convert (char (6), getdate(), 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:E1nne.498$Wk4.9251@.news.uswest.net...
On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current
> month:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first
> of
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||The conversion to char (6) gives you the year and month only in YYYMM
format. You still need a day, and you supply that by concatenating '01'.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:1lnne.499$Wk4.9860@.news.uswest.net...
On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||On 6/1/2005 1:51 PM, Tom Moreau wrote:
> The conversion to char (6) gives you the year and month only in YYYMM
> format. You still need a day, and you supply that by concatenating '01'.
>
Thanks a bunch!
Dan|||Another alternative:
SELECT DATEADD( m, -3, @.dt - DAY( @.dt ) + 1 ) AS "start",
@.dt - DAY( @.dt ) AS "end"
-- where @.dt is the date value in question
Anith

LAst order date

---

I have a fact table that contains amongst other facts, the orderdate. There is 1 line per order.
I would like to show for each "sold from BP", the last order date, like i do in the example below for the "Transaction amount".
Can anyone give me a hint on how the MDX syntaxt should be?

select NON EMPTY {[Measures].[Transaction amount (EURO)]} ON COLUMNS,
NON EMPTY {[sold from BP.BP(ID)].[All sold from BP(ID)]} ON ROWS
from [finalizedtransactionscube]
where [Transaction type.Transaction type].[PCI+PCC]

Hopefully this gives you an idea on how to proceed with this:

Code Snippet

withmember [measures].[x] as

MAX(

EXISTS(

[Ship Date].[Date].[Date].Members,

{Product.SubCategory.CurrentMember},

'Internet Sales'

),

[Ship Date].[Date].CurrentMember.MemberValue

)

select

[x] on 0,

TAIL(Product.SubCategory.SubCategory.Members,10) on 1

from [Adventure Works]

I use TAIL on Axis(1) to just limit the data returned. The calculated member at the top is where all the work is performed.

What this says is build a set of ship date members associated with the current product subcategory based on the relationship between these two dimensions as defined in the Internet Sales measure group. Take the max value for ship date from this set and return that as the measure's value. The MAX() function was the tricky part.

Good luck,
Bryan

Last line of page printing at top of next page

Anyone else have this problem and know the solution.
--
Thanks,
Danny BaderWhen I put summary info into a page footer, I got the same problem like you.
Now I'm using table footer to show summary info.
"Danny Bader" wrote:
> Anyone else have this problem and know the solution.
> --
> Thanks,
> Danny Bader