Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql query  last six months

Posted on 2013-06-21
5
Medium Priority
?
1,739 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 600 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 49

Accepted Solution

by:
PortletPaul earned 1400 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 49

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 49

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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