Solved

sql query  last six months

Posted on 2013-06-21
5
1,249 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

821 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