sql for date query

[mdarsot]

i have sql query where i specify data to be selected between 2 dates for example.

where
date between '23-FEB-09' and '01-MAR-09'


In above example i have to keep changing date every time i want numbers for another week. I want the query to automatically give me numbers for each week. where week starts from Monday ends on Sunday. We could put a start or end date for number of occurance.

Any help is appreciated.

thanks

 

[ZenRaven]

Would something like this work?

SELECT DATEPART(wk,blahdate) AS Week, blah, blah, blah
FROM MyTable
WHERE blahdate BETWEEN @startdate AND @enddate
GROUP BY DATEPART(wk,blahdate), blah, blah, blah
ORDER BY DATEPART(wk,blahdate)

--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[ZenRaven]

that didn't make much sense, did it (group by?) ... maybe this? What's the result you're looking for before I keep guessing myself to death

SELECT blah, blah, blah
FROM MyTable
WHERE DATEPART(wk,blahdate) BETWEEN @startweek AND @endweek

or

SELECT blah, blah, blah
FROM MyTable
WHERE DATEPART(wk,blahdate) BETWEEN DATEPART(wk,@startdate) AND DATEPART(wk,@enddate)

--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[mdarsot]

Thanks

SELECT blah, blah, blah
FROM MyTable
WHERE DATEPART(wk,blahdate) what criteria goes here



BETWEEN DATEPART(wk,@startdate) // january 2009

AND DATEPART(wk,@enddate) // Dec 2009

 

[ZenRaven]

Before I go any further, what is the end result you're looking for? Do you want to specify start/end dates, weeks, or months? Do you want the result to be individual records or aggregated by week? I'm going to need more info so I can stop running myself in circles.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[mdarsot]

table
date, quantity


i want to select all the quantity and sum it on weekly basis. Week will be starting Monday ending Sunday. We can provide query begin & end date which will limit how many weeks of data it shows. So query will start adding quanity on weekly basis(mon-sun) starting at begin date till the end date.

 

[ZenRaven]

I guess my 1st post was closer than I thought. I had originally ignored the Monday through Sunday requirement but I think this should handle that.

DECLARE @startdate datetime, @enddate datetime, @datefirst int

SET @startdate = '2009-01-01' --replace with whatever date you want
SET @enddate = '2009-12-13'  --replace with whatever date you want
SET @datefirst = @@datefirst

SET DATEFIRST 1 --Makes Monday the 1st day of the week

SELECT DATEPART(wk,blahdate) AS WeekNo, sum(SomeQuantity)
FROM MyTable
WHERE blahdate BETWEEN @startdate AND @enddate
GROUP BY DATEPART(wk,blahdate)
ORDER BY DATEPART(wk,blahdate)

SET DATEFIRST @datefirst --Set 1st day of week back to original setting

--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[mdarsot]

i get following error

ERROR at line 1:
ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

 

[ZenRaven]

I don't know what that is but you're definitely not using Microsoft SQL Server. I believe you may be in the wrong forum. Maybe Oracle?


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson