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

x
?
Solved

Sql Query to return results for current month

Posted on 2013-05-17
5
Medium Priority
?
573 Views
Last Modified: 2013-05-17
Hi All I have the following query

SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, 
                      dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime) 
                      END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm, 
                      dbo.itran.it_exdate) AS Month_No, dbo.ihead.ih_credit AS Credit
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal LIKE '%LINITSER%') AND 
                      (dbo.ihead.ih_credit NOT LIKE '%CRE%')
ORDER BY our_date

Open in new window


What I would like to do is only return results where Our_Date  = Current month

Can anybody help.

John
0
Comment
Question by:pepps11976
[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
5 Comments
 
LVL 6

Expert Comment

by:BurundiLapp
ID: 39174419
These any use to you, these are the SQL reference codes I use in various queries relating to dates:

SELECT 
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-3,0)) AS LD_PQ, -- last second of last day of previous quarter
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0)) AS LD_THREEM, -- last second of last day of 3 months ago
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)) AS LD_TWOM, -- last second of last day of 2 months ago
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LD_LASTM, -- last second of last day of last month
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS LD_THISM, -- last second of last day of this month
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) AS LD_NEXTM, -- last second of last day of next month
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+3,0)) AS LD_TWOMH -- last second of last day of 2 months hence

SELECT
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-3,0)) AS FD_LASTQ, --FirstDayof Last Quarter
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-2,0)) AS FD_TWOMA, --FirstDay of two months ago
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) AS FD_LASTM, --FirstDay of previous month
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))   AS FD_THISM, --FirstDay of current Month
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS FD_NEXTM, --FirstDay of Next Month
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) AS FD_TWOMH, --FirstDay in two months time
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+3,0)) AS FD_THREEMH --FirstDay in three months time

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39174421
try this
SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, 
                      dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime) 
                      END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm, 
                      dbo.itran.it_exdate) AS Month_No, dbo.ihead.ih_credit AS Credit
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal LIKE '%LINITSER%') AND 
                      (dbo.ihead.ih_credit NOT LIKE '%CRE%')
AND     month(CAST(dbo.itran.it_due AS datetime)) = month(getdate()) 
and     year(CAST(dbo.itran.it_due AS datetime)) = year(getdate()) 
ORDER BY our_date

Open in new window

0
 

Author Comment

by:pepps11976
ID: 39174428
Neo_jarvis

I get an error with the code you provided saying The conversion of a date data type to a datetime data type resulted in an out of range value
0
 
LVL 2

Expert Comment

by:mensmaximus
ID: 39174436
Did you try to expand your where clause by adding the date criteria for our_date? I would try adding something like:

and datepart(yy,our_date) = datepart(yy,dateadd(m,0,getdate()))
and datepart(m,our_date) = datepart(m,dateadd(m,0,getdate()))

Open in new window

0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39174440
Ok,  I got it the data in your table for the column itran.it_due  might have some values which cannot be converted to datetime, no worries, the below code might handle it.

;WITH CTE AS
(
SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, 
                      dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime) 
                      END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm, 
                      dbo.itran.it_exdate) AS Month_No, dbo.ihead.ih_credit AS Credit
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal LIKE '%LINITSER%') AND 
                      (dbo.ihead.ih_credit NOT LIKE '%CRE%')
ORDER BY our_date
)
SELECT * FROM CTE 
WHERE   month(CAST(our_date AS datetime)) = month(getdate()) 
and     year(CAST(our_date AS datetime)) = year(getdate()) 

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 setup several different housekeeping processes for a SQL Server.

609 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