Solved

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

Posted on 2014-02-07
18
1,167 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
  • 11
  • 6
18 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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
 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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:ScottPletcher
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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:ScottPletcher
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:ScottPletcher
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now