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
Solved

get data only for current month

Posted on 2012-03-22
7
319 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 40

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 40

Expert Comment

by:Kyle Abrahams
ID: 37754828
-- last 30 days.
desired_ship_date > dateadd(d, getdate(), -30)
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 40

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 40

Accepted Solution

by:
Kyle Abrahams earned 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

829 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