Data from previous quarter

I am trying to extract data from the previous quarter, however, when the
previous quarter is also the previous year, I need to compensate for that.
Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
syntax.
SELECT Region, ServiceDate
FROM dbo.CommunityService
WHERE Case When (DatePart(q,GetDate())) = 1 Then
DatePart(q, ServiceDate) = 4 AND
Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
Year(ServiceDate) = Year(GetDate())
End
--
What am I missing?If you first determine the start and ending date for the period you want,
then the where condition will be more simple. For example, the following
would be Nov 2005 - Jan 2006:
select @.StartDate = '2005/11/01'
select @.EndDate = '2005/02/01'
. . .
where
ServiceDate >= @.StartDate and ServiceDate < @.EndDate
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>I am trying to extract data from the previous quarter, however, when the
> previous quarter is also the previous year, I need to compensate for that.
> Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
> syntax.
> --
> SELECT Region, ServiceDate
> FROM dbo.CommunityService
> WHERE Case When (DatePart(q,GetDate())) = 1 Then
> DatePart(q, ServiceDate) = 4 AND
> Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
> Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
> Year(ServiceDate) = Year(GetDate())
> End
> --
> What am I missing?|||Ken,
I would do it as follows. Your thinking is fine & the logic here is the same
as your own. Obviously I can't test this as you've posted no DDL etc., but
it does parse.
Cheers,
Robert
SELECT cs.Region, cs.ServiceDate
FROM dbo.CommunityService cs
WHERE
(DATEPART(Q, cs.ServiceDate) =
(/* Expression = Relevant Quarter based on current date */
CASE (DATEPART(Q, GETDATE())-1)
WHEN 0 THEN 4 ELSE (DATEPART(Q, GETDATE())-1) END
))
AND
(YEAR(cs.ServiceDate)=
(/* Expression = Relevant Year based on current date */
CASE (DATEPART(Q, GETDATE())-1)
WHEN 0 THEN (YEAR(GETDATE())-1) ELSE YEAR(GETDATE()) END
))
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>I am trying to extract data from the previous quarter, however, when the
> previous quarter is also the previous year, I need to compensate for that.
> Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
> syntax.
> --
> SELECT Region, ServiceDate
> FROM dbo.CommunityService
> WHERE Case When (DatePart(q,GetDate())) = 1 Then
> DatePart(q, ServiceDate) = 4 AND
> Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
> Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
> Year(ServiceDate) = Year(GetDate())
> End
> --
> What am I missing?|||Robert,
This is precisely what I am looking for. It worked like a charm. I will be
keeping this code for use anytime I need previous qtr or mon. Thank you so
much for your help.
Have a great day..
Ken
"Robert Ellis" wrote:

> Ken,
> I would do it as follows. Your thinking is fine & the logic here is the sa
me
> as your own. Obviously I can't test this as you've posted no DDL etc., but
> it does parse.
> Cheers,
> Robert
> SELECT cs.Region, cs.ServiceDate
> FROM dbo.CommunityService cs
> WHERE
> (DATEPART(Q, cs.ServiceDate) =
> (/* Expression = Relevant Quarter based on current date */
> CASE (DATEPART(Q, GETDATE())-1)
> WHEN 0 THEN 4 ELSE (DATEPART(Q, GETDATE())-1) END
> ))
> AND
> (YEAR(cs.ServiceDate)=
> (/* Expression = Relevant Year based on current date */
> CASE (DATEPART(Q, GETDATE())-1)
> WHEN 0 THEN (YEAR(GETDATE())-1) ELSE YEAR(GETDATE()) END
> ))
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>
>