Solved

get data only for current month

Posted on 2012-03-22
7
309 Views
Last Modified: 2012-06-27
I have a table with orders data that has the next 3 columns:

sched_start_date
sched_finish_date
desired_ship_date


sample data (same order as above):

2012-04-25 00:00:00      2012-04-26 00:00:00      2012-04-24 00:00:00.000
2012-04-26 00:00:00      2012-04-30 00:00:00      2012-04-27 00:00:00.000
2012-04-30 00:00:00      2012-04-30 00:00:00      2012-04-27 00:00:00.000
2012-05-03 00:00:00      2012-05-03 00:00:00      2012-05-01 00:00:00.000
2012-05-04 00:00:00      2012-05-04 00:00:00      2012-05-01 00:00:00.000
2012-05-07 00:00:00      2012-05-08 00:00:00      2012-05-01 00:00:00.000
2012-05-08 00:00:00      2012-05-09 00:00:00      2012-05-08 00:00:00.000
2012-05-09 00:00:00      2012-05-09 00:00:00      2012-05-04 00:00:00.000


that determines whether an order will ship late.
I would like to retrieve only the orders that are going to be late for the current month since as right now, all months are being retrieved. Just need to retrieve the late orders for the current month

Is this something I could get help with? I am using MS SQL Server 2000

Thank you much.
0
Comment
Question by:metropia
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
add this to your where clause:

-- current month
datepart(mm, desired_ship_date) = datepart(mm,getdate())
and

-- is late?
sched_finish_date > desired_ship_date
0
 

Author Comment

by:metropia
Comment Utility
actually i take that back, can it look to the last 30 days, using current date as stating point?

thanks
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
-- last 30 days.
desired_ship_date > dateadd(d, getdate(), -30)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:metropia
Comment Utility
I have it like this:

 
SELECT * FROM RG_ORDERS_THAT_MAY_GO_LATE_REPORT_VW
WHERE DATEPART(mm, DESIRED_SHIP_DATE) = DATEPART(mm,GETDATE())
and desired_ship_date > dateadd(d, getdate(), -30)


but I get an error:

Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 2 of dateadd function.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
sorry, I inversed:

-- 30 days ago, at midnight.
desired_ship_date >= dateadd(dd,datediff(dd,0,Getdate())-30,0)
and
--one second before midnight for shipments later today
desired_ship_date  <= (dateadd(dd, datediff(dd,0,Getdate()),0)- 1)+ '23:59:59'
0
 

Author Comment

by:metropia
Comment Utility
would you be able to explain to me what the new clauses are doing so that i can better understand?

Thanks a lot.
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
there was actually a slight error:

use these:


-- 30 days ago, at midnight.
desired_ship_date >= dateadd(dd,datediff(dd,0,Getdate())-30,0)
and

--one second before midnight for shipments later today
desired_ship_date  <= (dateadd(dd, datediff(dd,0,Getdate()),0))+ '23:59:59'


Run this to get back dates:

SELECT     DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 30, 0) AS minDate, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + '23:59:59' AS maxDate



The reason for these are because of time stamps.  

If you run the query at 15:00 and there is a later shipdate today (say 17:00) you won't pull that in your query.

to get around that we set the time to midnight of the minDate and and 23:59:59 of the max date to pull back everything in those dates.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now