current week - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

current week

Author

Topic

anth_halliday
Starting Member

4 Posts

Posted - 2011-04-18 : 05:09:23

hi, im making a program for my parents company that managers orders. the company is a bakery that takes orders on a monday. they will make entred into the program on either a monday or tuesday with each order date and week number recorded in the database. for example shop a places an order the database currently records the date it was placed "18-04-2011" and the week number "16".

i have used table adapters in VB.net to create a few test sql statements to filter out each product e.g. for the current week i need to show the number of product A's ordered by all customers, total number of product B's ordered...

i was hoping there is a way i could include in each of the statements that only returns the rows with the week number the same as the current.

i have used sql very very little before so any help will be greatly appreciated. if i explained it badly i will gladly elaborate.

anth_halliday
Starting Member

4 Posts

Posted - 2011-04-18 : 05:52:32

iv found something i think is what i want but like i said i know nothing about sql and dont know how to add it into the current query.
currently:
SELECT SUM(Product_A) AS Expr1
FROM Odrers

this returns a single number which is exactly what i want but i would also like to filter the results by the week number as well.
i have columns in the table for the order date and another for week number.

this is what i think i need bu just dont know how to incorporate it in the query.
DATEPART( wk, GETDATE() )

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-18 : 06:26:59

It's really safer to filter orders by a date range instead of week number. There is a week #27 every year


SELECT SUM(Product_A) AS Expr1
FROM Orders
Where OrderDate >= @RangeBegin
and OrderDate < @RangeEnd

Corey

I Has Returned!!

anth_halliday
Starting Member

4 Posts

Posted - 2011-04-18 : 06:55:31

thanks for the reply. the only thing is i need the total of each product ordered so i will need over 70 queries (1 for each product), so to avoid then making 52 (1 for each week of the year) queries for each product i was hoping to get something what when the query was run it found the current week/date.

the reason i need the current week is that the ordered will get printed off throughout the week not just on the monday so if i had orderdate=today if the ordered were printed on a thursday nothing would appear as the orderers would have been entered on a monday or turesday.

anth_halliday
Starting Member

4 Posts

Posted - 2011-04-18 : 06:55:36

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-18 : 07:34:18

Why don't you post your table structure and same sample data. I'm pretty confident that you won't need 70 queries... you could always calculate the Date Range of the current week from getdate()...


Select 
	getdate(),
	Today = dateadd(dy,datediff(dy,0,getdate()),0),
	Monday = dateadd(dy,datediff(dy,0,getdate())-(datepart(dw,getdate()+0)+5)%7,0),
	NextMonday = dateadd(dy,datediff(dy,0,getdate())-(datepart(dw,getdate()+0)+5)%7,0)+7

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources