Date values, start and ending for a month

[bobpv]

I need to get the 1st and last day of the last full 6 months ie, Feb,1,2012 and Feb 28,2012 (or 29 if leap year) Jan 1, 2012 - Jan 31,2012 - Dec 1, 2011 - Dec 31,2011 - Nov 1,2011 - Nov 30,2011 and so on. I need that series in separate fields in order to use them in a query to gather the prior data from the table.

The table I am querying is storing material use, and each month use needs to be summed and added to the sum of all prior months. So the above vales would be use in the select as (this example uses November, I will need to use the same thing in the query to grab 6 months of data)
SELECT SUM(CASE WHEN movedate <= '2010-11-01'
THEN (booked+adjusts)-(used+spoiled) ELSE 0 END) as qBegin,
SUM(CASE WHEN movedate between '2010-11-01' and '2010-11-30'
THEN (booked+adjusts)-(used+spoiled) ELSE 0 END) as qEnd

That will give me the beginning value and current month value, which I need to do for 6 months. The query could be run anytime during a month, but would only want to return the past 6 full months - if run on March 15th, it would exclude March and only show Feb of the current year back to September of the prior year. Any suggestions?

 

[drlex]

Look at grouping on DATEPART(mm,[movedate]), assuming you'll not be looking at months in two distinct years.


soi là, soi carré

 

[UnsolvedCoding]

Why not just do between getdate() and getdate()-180?

 

[bobpv]

Getdate() -180 won't work, due to the months values (Sept to Feb would be 181, or 182 if leap year). I am not sur eif there is a way to make that adjustment?

driex, yes, I will need the month values in distinct years, to expand what I need to do:

SELECT SUM(CASE WHEN movedate <= '2010-09-01'
THEN (booked+adjusts)-(used+spoiled) ELSE 0 END) as qBegin1, (qbegin reads all the table data prior to the date)

SUM(CASE WHEN movedate between '2010-09-01' and '2010-09-30'
THEN (booked+adjusts)-(used+spoiled) ELSE 0 END) as qEnd1
(qEnd reads data for the specified month)
SUM(CASE WHEN movedate <= '2010-10-01'
THEN (booked+adjusts)-(used+spoiled) ELSE 0 END) as qBegin2,
SUM(CASE WHEN movedate between '2010-10-01' and '2010-10-31'
THEN (booked+adjusts)-(used+spoiled) ELSE 0 END) as qEnd2

and so on until I get to the last full month (6 month period from Sept 2010 -- Feb 2011) each month values must be calculated separately.

Make better sense?

 

[RTag]

bobpv what is the difference between this question and your other question:

http://www.tek-tips.com/viewthread.cfm?qid=1669994

?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[bobpv]

RTag,
2 different issues, one to pull 6 full months from the table, the other to extract the start and end dates of those 6 months.

I wound up creating a couple of functions to give me the dates I wanted, one for start date which I can then use to back out 6 months using the MONTH - 6, the other to give me end dates of the month which I can apply ot the query.

Many thanks to all for the suggestions and the explanations. They helped me figure it out.