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

x
?
Solved

get data only for current month

Posted on 2012-03-22
7
Medium Priority
?
342 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
[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
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37754776
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
ID: 37754786
actually i take that back, can it look to the last 30 days, using current date as stating point?

thanks
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37754828
-- last 30 days.
desired_ship_date > dateadd(d, getdate(), -30)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:metropia
ID: 37754837
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 41

Expert Comment

by:Kyle Abrahams
ID: 37754847
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
ID: 37754885
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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 37757701
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

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 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