• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1233
  • Last Modified:

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

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
metropia
Asked:
metropia
  • 11
  • 6
1 Solution
 
Scott PletcherSenior DBACommented:
DocumentDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

--"DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)" = first day of current month
0
 
Surendra NathCommented:
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
 
metropiaAuthor Commented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Scott PletcherSenior DBACommented:
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
 
metropiaAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
metropiaAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
metropiaAuthor Commented:
the query is part of a reports sub-reports suite.
the user enters the quote number and this is the only parameter provided.
0
 
metropiaAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
metropiaAuthor Commented:
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
 
metropiaAuthor Commented:
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
 
metropiaAuthor Commented:
I let it run for 6 minutes and has not returned any rows.
0
 
Scott PletcherSenior DBACommented:
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
 
metropiaAuthor Commented:
will this change be in the select or and clause?
0
 
metropiaAuthor Commented:
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
 
metropiaAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now