Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

get data only for current month

Posted on 2012-03-22
7
Medium Priority
?
349 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

564 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