Solved

sql query  last six months

Posted on 2013-06-21
5
1,377 Views
Last Modified: 2013-07-25
I need help writing a  query
 where cdate
is last 6 Mondays
last 4 days
first of last 3 months
0
Comment
Question by:Angela4eva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 15

Assisted Solution

by:gplana
gplana earned 150 total points
ID: 39267046
I think you need to have a look at this link: http://msdn.microsoft.com/en-us/library/aa258863%28v=sql.80%29.aspx

For the last 4 days:

SELECT *
FROM your_table
WHERE date >= dateadd(Day,-4,GETDATE());

For last 6 months:

SELECT *
FROM your_table
WHERE date >= dateadd(Month,-6,GETDATE());
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
ID: 39267532
all of the examples below can be seen working at http://sqlfiddle.com/#!3/1fa93/7883

the following may bend your head a bit, working with date/time can be confusing.

first: getdate() supplies the current date AND time

==== removing time
So when you ask for "last 4 days", what most people mean is "the last 4 COMPLETE days", so to meet "complete" you must remove the time from getdate()

in sql server 2008+ you can use this to remove time
select cast(getdate() as date) -- or
select convert(date, getdate() )
or while the following may look more complex - it is super efficient and works in all sql server versions:
select dateadd(day, datediff(day,0, getdate() ), 0)

==== last 4 complete days e.g.
SELECT *
FROM your_table
WHERE cdate >= dateadd(Day,-4, cast(getdate() as date)  );

===== first of last 3 months

I'm not entirely sure what you mean here, is it "the first day of the month, 3 months before now"?
-- first of last 3 months (?)
select
  dateadd(month,-3,cast(getdate() as date))
      as [3 months ago]
, datepart(day,dateadd(month,-3,getdate()))
      as [day of month 3 months ago]
, dateadd(day,-datepart(day,dateadd(month,-3,getdate()))+1, dateadd(month,-3,cast(getdate() as date)) )
      as [first of month 3 months ago]
;

-- first of last 3 months (? a "quarter")

SELECT
  dateadd(quarter,datediff(quarter,0,getdate()),0)    as [First of this quarter]
, dateadd(quarter,datediff(quarter,0,getdate())+1,-1) as [Last day of this quarter]
;

Open in new window


==== last 6 months
select
  dateadd(month,-6, cast(getdate() as date) ) as [last 6 months by cast]
, dateadd(month,-6, dateadd(day, datediff(day,0, getdate() ), 0) ) as [last 6 months by dateadd/diff]
;
===== (first of) last 6 months
same as the 3 months above, just change the deductions (could use an @variable instead)

, dateadd(day,-datepart(day,dateadd(month,-6,getdate()))+1, dateadd(month,-6,cast(getdate() as date)) )
      as [first of month 6 months ago]
;
====== is last 6 Mondays

This is harder, and needs more information. If you mean, EACH OF the last 6 mondays this may require 6 records in a table or CTE

if you mean, Monday of the week, 6 weeks ago:
select
  dateadd(DAY, datediff(DAY,-7, getdate() ) - (6*7) - (datediff(DAY,0, getdate() ) % 7), 0)
     as [Monday of 6 weeks ago]
;

This last one is harder to explain.
-- to explain the above
select
  datediff(DAY,-7, getdate() ) as [num days to 7days ago]
, -(6*7) as [6 week deduction in days]
, - (datediff(DAY,0, getdate() ) % 7) [if not monday also deduct this num of days]
,   dateadd(DAY, datediff(DAY,-7, getdate() ) - (6*7) - (datediff(DAY,0, getdate() ) % 7), 0)
     as [Monday of 6 weeks ago]

-- as I said, it can bend the mind :)

please do look at the sqlfiddle, seeing is believing
http://sqlfiddle.com/#!3/1fa93/7883

nb: if "is last 6 Mondays" means each 6 dates, this can be done also e.g. this answer
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39277329
Hi, is there any more you need on this question?
sql query last six months

I need help writing a  query
 where cdate
is last 6 Mondays
last 4 days
first of last 3 months
0
 

Author Comment

by:Angela4eva
ID: 39283226
Thankd for the help. Yes I need mondays of last six weeks
 If you mean, EACH OF the last 6 mondays this may require 6 records in a table or CTE
---yes
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39283272
see this working at http://sqlfiddle.com/#!3/1fa93/7960 
-- set number of weeks
declare @numWeeks as int
set @numWeeks = 6

-- next get recent Monday
declare @BeginsAt as datetime

-- truncate time from getdate()
set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)

-- get Monday using modulus
set @BeginsAt = dateadd(day,(-datediff(day,0,@BeginsAt) % 7),@BeginsAt) 

;with Ranger (id, StartAt)
as (
    /* recursively build CTE of Mondays */
    select 1 as id, @BeginsAt
    union all
    select (id + 1) , dateadd(day,-7,StartAt)
    from Ranger
    where dateadd(day,-7,StartAt) >= dateadd(week,-@numWeeks+1,@BeginsAt)
    )
select
id, datename(weekday,StartAt), StartAt
from ranger

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question