Solved

how to select records for only completed months based on current date

Posted on 2014-02-07
18
1,209 Views
Last Modified: 2014-02-11
Hi,

easy question, I have a query and I need to select records only for the past completed months.

so if the report let's say is run today 02/07/2014, I  do not want to see records from February, just start date way back in past but end date to last completed month.

SELECT DISTINCT
,	InvoiceQuantity
,	ItemQuantity
FROM 
    CostLine cl LEFT OUTER JOIN
    InvoiceCostLine icl ON
	cl.ItemNumber = icl.ItemNumber
WHERE 
	cl.SalesQuoteNumber = '09320-1'
AND
	icl.InvoiceType = 'STANDARD'
AND 
        DocumentDate << here is the help I need :) >>

Open in new window


Any help would be great.

Thank you!
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
  • 11
  • 6
18 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39842480
DocumentDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

--"DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)" = first day of current month
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39842491
the below sql will get you the last date of the previous month, in this case 31st Jan

select dateadd(dd,datepart(dd,getdate())*-1,GETDATE())

Open in new window


I believe you can add it into your SQL Query and do the rest.
0
 

Author Comment

by:metropia
ID: 39842572
Thank you.

SELECT DISTINCT
	sqcl.ItemNumber
,	sqcl.ItemDescription
,	SUM(sicl.ItemQuantity) [ItemQuantity]
FROM 
	SalesQuoteCostLine sqcl LEFT OUTER JOIN
	SalesInvoiceCostLine sicl ON
	sqcl.ItemNumber = sicl.ItemNumber
WHERE 
	sqcl.SalesQuoteNumber = '10346-0'
AND
	sqcl.DocumentDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY 
	sqcl.ItemNumber
,	sqcl.ItemDescription
ORDER BY 
	sqcl.ItemNumber ASC

Open in new window


I have a question,  what would be the best way to get the LAST 12 completed months with the query above. And then perhaps using a different query, get the Prior 12 months (starting from the LAST 12 months)?

What I need to get in returned is something like this:

Item No      Item Description      Last 12 Months      Prior 12 Months
0
Independent Software Vendors: 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!

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39842648
SELECT DISTINCT
      sqcl.ItemNumber
,      sqcl.ItemDescription
,      SUM(CASE WHEN sqcl.DocumentDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Last 12 Months]
,      SUM(CASE WHEN sqcl.DocumentDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Prior 12 Months]
FROM
      SalesQuoteCostLine sqcl LEFT OUTER JOIN
      SalesInvoiceCostLine sicl ON
      sqcl.ItemNumber = sicl.ItemNumber
WHERE
      sqcl.SalesQuoteNumber = '10346-0'
AND
      sqcl.DocumentDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0) AND
       sqcl.DocumentDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY
      sqcl.ItemNumber
,      sqcl.ItemDescription
ORDER BY
      sqcl.ItemNumber ASC
0
 

Author Comment

by:metropia
ID: 39842685
I am a bit confused with the query Scott.

Your query

--Scott's Query

SELECT DISTINCT
    sqcl.ItemNumber
,	sqcl.DocumentDate
,   SUM(CASE WHEN sqcl.DocumentDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Last 12 Months]
,   SUM(CASE WHEN sqcl.DocumentDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Prior 12 Months]
FROM 
	DW.cost.vStd_SalesQuoteCostLine sqcl LEFT OUTER JOIN
	DW.cost.vStd_SalesInvoiceCostLine sicl ON
	sqcl.ItemNumber = sicl.ItemNumber
WHERE 
	sqcl.SalesQuoteNumber = 'QUO-10346-0'
AND
	sicl.InvoiceType = 'STANDARD'
AND
    sqcl.DocumentDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0) AND
    sqcl.DocumentDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY 
	sqcl.ItemNumber
,	sqcl.DocumentDate
ORDER BY 
    sqcl.ItemNumber ASC

Open in new window


Returns:
scott's results
My query (not having the Prior Column yet added):

-- My Query

SELECT DISTINCT
	sqcl.ItemNumber
,	sqcl.DocumentDate
,	SUM(sicl.ItemQuantity) [LastTwelveMonths_ItemQuantity]
FROM 
	DW.cost.vStd_SalesQuoteCostLine sqcl LEFT OUTER JOIN
	DW.cost.vStd_SalesInvoiceCostLine sicl ON
	sqcl.ItemNumber = sicl.ItemNumber
WHERE 
	sqcl.SalesQuoteNumber = 'QUO-10346-0'
AND
	sicl.InvoiceType = 'STANDARD'
AND
	sqcl.DocumentDate > DATEADD(m, -12, GETDATE()) 
GROUP BY 
	sqcl.ItemNumber
,	sqcl.DocumentDate
ORDER BY 
	sqcl.ItemNumber ASC

Open in new window


Returns:

my results

I am confused because your query returns zero for the last 12 months, and values for the prior to last twelve months.

Whilst mine (I think) returns the same values, but as the Last Twelve months values.

Is there a chance you can explain to me how this works?

Thank you.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39842804
OOPS, I got the date comparisons backward in the SELECT:

,      SUM(CASE WHEN sqcl.DocumentDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Last 12 Months]
,      SUM(CASE WHEN sqcl.DocumentDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Prior 12 Months]


Looks to me like there's no data older than 12 months in your dataset, if the WHERE condition is correct ... maybe the QuoteNumber is different for older data?!
0
 

Author Comment

by:metropia
ID: 39842957
yes there is more to it.

let me explain and see if this is something you can help me with to figure out.

the quote number that is entered by the user may not have records for the prior to the last 12 months, or even possibly for the last twelve months.

the trick is to add the customer number column to the query, then using the combination of customer number and item number list, match previous quote in the last 12, prior 12 months for that customer. seems like i need a stored procedure more than just a simple query.

do you think you can help me with this?

thank you
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39842964
Could you just leave quote number off the WHERE clause?

You could include quote number in the GROUP BY, and thus report separately on all quote numbers for both 12-month periods.
0
 

Author Comment

by:metropia
ID: 39842971
the query is part of a reports sub-reports suite.
the user enters the quote number and this is the only parameter provided.
0
 

Author Comment

by:metropia
ID: 39842983
the way i think about it is:

get customer number from vStd_SalesInvoiceCostLine using the quote number entered by user.

then search for all the combination of quotes and item number for the last 12 months that contain the same item number list as the quote provided by user.

at lest first step to get the last twelve months.

does this sounds right?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39842996
Yes, that could work from a SQL point of view.  

Of course whether or not that is the correct business logic, and will give the user the desired results, depends on your specific environment.
0
 

Author Comment

by:metropia
ID: 39843102
I spoke with user and this is what I got:

I need to show volume history for the last 12 months and the prior 12 months based on the date of the report.  
Include only months that have been completed.
Show volume history for only items listed on current quote.
If there is no volume (quantity) leave blank.
0
 

Author Comment

by:metropia
ID: 39843126
Scott, I think I can use your query because the correct date to use is Invoice Date, not Document Date (my bad)

The problem is that when I change that field on your query, it just hangs forever. Only when I remove this part:

--AND
--      sicl.InvoiceDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0) AND
--      sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

this is the query after modifications:

SELECT DISTINCT
      sqcl.ItemNumber
,   sqcl.ItemDescription
,   SUM(CASE WHEN sicl.InvoiceDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Last 12 Months]
,      SUM(CASE WHEN sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Prior 12 Months]
FROM
      DW.cost.vStd_SalesQuoteCostLine sqcl LEFT OUTER JOIN
      DW.cost.vStd_SalesInvoiceCostLine sicl ON
      sqcl.ItemNumber = sicl.ItemNumber
WHERE
      sqcl.SalesQuoteNumber = 'QUO-10346-0'
AND
      sicl.InvoiceType = 'STANDARD'
--AND
--      sicl.InvoiceDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0) AND
--      sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY
      sqcl.ItemNumber
,      sqcl.ItemDescription
ORDER BY
      sqcl.ItemNumber ASC

But I need the date range still.
0
 

Author Comment

by:metropia
ID: 39843147
I let it run for 6 minutes and has not returned any rows.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39843164
Absolutely no legitimate reason that should cause a performance issue (unless it happened to trigger a "tipping point" (as Kimberly Tripp named it), which causes SQL to switch from using a table scan to using an index).

You could instead check the entire valid date range in the SUM(CASE ...).  SQL would then read all the rows, but only SUM those that applied for that range.


,   SUM(CASE WHEN sicl.InvoiceDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
    AND
        sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
 THEN sicl.ItemQuantity ELSE 0 END) AS [Last 12 Months]
,   SUM(CASE WHEN sicl.InvoiceDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0)
    AND
        sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN sicl.ItemQuantity ELSE 0 END) AS [Prior 12 Months]
0
 

Author Comment

by:metropia
ID: 39843171
will this change be in the select or and clause?
0
 

Author Comment

by:metropia
ID: 39843179
so it would be something like:

SELECT DISTINCT
	sqcl.ItemNumber
,   sqcl.ItemDescription
,	sicl.InvoiceDate
,   SUM(CASE 
			WHEN 
				sicl.InvoiceDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) AND 
				sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) THEN 
				sicl.ItemQuantity 
			ELSE 
				0 
		END) AS [Last 12 Months]
,   SUM(CASE 
			WHEN 
				sicl.InvoiceDate >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0) AND 
				sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) THEN 
				sicl.ItemQuantity 
			ELSE 
				0 
		END) AS [Prior 12 Months]
FROM 
	DW.cost.vStd_SalesQuoteCostLine sqcl LEFT OUTER JOIN
	DW.cost.vStd_SalesInvoiceCostLine sicl ON
	sqcl.ItemNumber = sicl.ItemNumber
WHERE 
	sqcl.SalesQuoteNumber = 'QUO-10346-0'
AND
	sicl.InvoiceType = 'STANDARD'
AND
	sicl.InvoiceDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 24, 0) AND
    sicl.InvoiceDate <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY 
	sqcl.ItemNumber
,   sqcl.ItemDescription
,	sicl.InvoiceDate
ORDER BY 
	sqcl.ItemNumber ASC

Open in new window

0
 

Author Comment

by:metropia
ID: 39843270
Scott, sorry for being a pain. I turned your query into a stored procedure.

And I think I almost got what I need, but when I look at the data. Is there a way to re-arrange, or pivot the results?

I just would like to see the last 12 months and prior 12 months next to each other without having so many rows.

i am attaching both data, and sp

hopefully you can recommend something.

thank you.
data.txt
stored-procedure.txt
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

707 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