We help IT Professionals succeed at work.
Private
Troubleshooting Question

Get data based on a week from a SQL table

58 Views
Last Modified: 2020-09-25
Hello,
Any suggestions on a Stored Procedure to get data for a week , starting from Monday to Sunday.
Also a parameter to set the date starting from. 

The field is a table are :
Tablename: Table1
Columns: Date, Information.

Thanks



Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
To get the day of week, look at datepart:
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15

Probably the 'dw' option:  select datepart(dw,getdate());

Author

Commented:
Can you give an example with the following information:
Tablename: Table1
Columns: Date, Information.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
select datepart(dw,date_column), information from table1;

Author

Commented:
Thanks, but this is not quite I am looking for.
I need the data which falls between a week starting from Monday to Sunday when I pass on the date as a parameter. 


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Well a week to me is the week number of the year so, 1 to 52. Monday to Sunday is the weekday or dayofweek.

datepart(dw,date_value) returns the weed day.  Depending on your locality the number returned by the start of the week can mean different things.  The link you povided mentions DATEFIRST.

Your requirements still aren't all that clear but there seems to be several examples in the learnsql.com link you posted.

What piece is causing you problems?

Sounds like you might be looking for a simple BETWEEN type query but you haven't provided much about your actual requirements.

Author

Commented:

Sounds like you might be looking for a simple BETWEEN type query but you haven't provided much about your actual requirements.  --Exactly a between query just to check the dates between Monday till Sunday. 

Author

Commented:
Example:

I want to check all data which was entered between last 2 weeks. Week- Moday to Sunday.
Here the '2' should be a flexible parameter. I might get a request to find data entered 5 weeks a ago.

Thanks


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>i want to check all data which was entered between last 2 weeks. Week- Moday to Sunday.

Your requirements still aren't clear.  All days of the week are between Monday and Sunday.

Between two dates examples are in the link you posted.  Still not sure what parts you are finding confusing.

Please post sample data and expected results from that sample data.

Author

Commented:
Ok, please find the excel sheet as you requested
DaterangeQuery.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Still not clear where day of week matters?

From the data you posted, it is a simple between:
select * 
from table1 
where mydate between cast('14-Sep-2020' as date) and cast('27-Sep-2020' as date);

Open in new window


Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ce76c1c6a79fc18e1af795ed583ab028

Author

Commented:
Hi,
Thanks.
There is line  
I get a requirement to have data for past 2 weeks ( * Monday -14/Sep/2020 till Sunday 27/September/2020)





So '2' is a parameter for the Stored procedure.
When it is past 2 weeks, the first day is Monday and the week ends on Sunday.

Also you are hard coding the dates. I will get the requirement as past 2 weeks and the dates should be a sql function. Any suggestions? Thanks again 
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Create the parameter as a varchar then use dateadd?

This example uses a local variable so just change it to a procdure parameter.
DECLARE @date_var varchar(20);

set @date_var='27-Sep-2020'

select * from table1 where mydate between DATEADD(wk,-2,cast(@date_var as date)) and cast(@date_var as date);

Open in new window


Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0000626ff8e059dad25bf0d6b6f15648

Author

Commented:
Thanks a lot !
Just a quick one, Is there any option to set week earlier instead of date
**** set @date_var='27-Sep-2020'  

Can it be like Set@Week_Earlier = 4
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
You can use whatever parameters you want in your procedure.

You will need a date somewhere eventually.  If you want it based off "today", that is the getdate() function I used previously.

So, if the procedure only accepts a weeks-before as a parameter, then use getdate in the procedure and go back that number of weeks.

Author

Commented:
Great, thanks a lot!

Author

Commented:
Can you help me in the other sql question?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Probably not.  I'm OK with straight SQL when it comes to SQL Server.  Your other question will probably require a lot more work than simple SQL.

Author

Commented:
ok, thanks!

Author

Commented:
Hi,
Can you help me with this :
You can use whatever parameters you want in your procedure.

You will need a date somewhere eventually.  If you want it based off "today", that is the getdate() function I used previously.

So, if the procedure only accepts a weeks-before as a parameter, then use getdate in the procedure and go back that number of weeks.

Author

Commented:
Apologies , as really tight on deadline and struggling

Author

Commented:
Starting from the Monday. So we would pass in -6 to go back 6 weeks

The weeks start on Mondays !
Thanks


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
You have to understand that I don't know your specific requirements.

A procedure has output parameters and/or performs some logic.  I don't know what you want this procedure to do.  You also mentioned a function.  Those return a value.

Are you saying you want a procedure that only accepts a single parameter for weeks-before and use the current date to base the query off of?

Your original question mentioned passing in the start date and number of weeks before that date so, two parameters.

You should have all the pieces on getting rows between dates and subtracting a number of weeks from a date.

What pieces of this task is causing you problems?

>>Starting from the Monday. So we would pass in -6 to go back 6 weeks

Starting from what Monday?

Author

Commented:

Are you saying you want a procedure that only accepts a single parameter for weeks-before and use the current date to base the query off of? - Exactly this is the question.


Starting from what Monday?  Suppose we have to go 2 weeks back , the Monday 7/Septeber/2020
If it is 4 weeks back it will be 24/August/2020

Hope it answers your queries.

Author

Commented:
use the current date to base the query off of? - Exactly this is the question.

So basically - 1 parameter ( how many weeks back)
This gives us 1 date till end of this week (Sunday).
Hope it answers your query
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
You understand what you need this to do but you aren't really giving me enough to provide a copy/paste solution to meet your complete requirements.

for example:
What does this procedure need to do?  Once you select the data, it has to do something with it. I have no idea what that is.

Then you have what seems to be different requirements:
You said you want the query to be based off of the current date then you mention starting on Monday again.

If the select runs today, it will go back 2 weeks from thursday.

If you want the query to ALWAYS start from a Monday, if you run it today, what is the actual date  range for the query you are wanting to pull records for?

Author

Commented:
What does this procedure need to do?  The procedure with 1 parameter i.e. Weeks to go back so its an number.


If the select runs today, it will go back 2 weeks from thursday. ---
If Select runs today and the parmeter is 2 then the dates will be start date will be 7-Sept-2020 and the end date will be 27-Sept-2020. 

Author

Commented:
Apologies as the earlier spec were confusing but, will try to answer your queries to make it clear as possible. 
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>What does this procedure need to do?  The procedure with 1 parameter i.e. Weeks to go back so its an number.

Once it queries the rows from the table, what do you need to do with that data?

Author

Commented:
we have 1 parameter - weeks to go back. example 2
then select query to return the rows in between the two dates.

Author

Commented:
If Select runs today and the parmeter is 2 then the dates will be start date will be 7-Sept-2020 and the end date will be 27-Sept-2020
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
So you just want a procedure that only returns a result set, no other business logic at all?

A while back you mentioned passing in negative numbers:
So we would pass in -6 to go back 6 weeks

Are you wanting positive or negative parameters?

Author

Commented:
A while back you mentioned passing in negative numbers:
So we would pass in -6 to go back 6 weeks

Are you wanting positive or negative parameters?
Its positive number declared as varchar
It was just to explain


Author

Commented:
Thanks a lot !

Author

Commented:
So you just want a procedure that only returns a result set, no other business logic at all?
Yes a result set based on the two dates. 

Author

Commented:
Example : Get an input parameter : 2

The procedure should return a result set based on the dates:
example1 :
If Select runs today and the parmeter is 2 then the dates will be start date will be 7-Sept-2020 (Monday)  and the end date will be 27-Sept-2020(Sunday) .  

example2 :
If Select runs today and the parmeter is 3 then the dates will be start date will be 31-August-2020 (Monday)  and the end date will be 27-Sept-2020(Sunday) .  

example3 :
If Select runs today and the parmeter is 4 then the dates will be start date will be 24-August-2020 (Monday)  and the end date will be 27-Sept-2020(Sunday) .  


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Isn't that what Scott's procedure does?  I didn't test it but he knows SQL Server better than I do so I would trust his code.

Author

Commented:
Oh Dear,
It didn't load . Will check now.

Author

Commented:
Thanks Scott,
It worked perfectly!
Can't thank you enough!

Author

Commented:
slightwv (䄆 Netminder) ,
You are a star, helped me a lot!
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
CORRECTION.  Remove the -1 from the ending date calc, since I am (purposely) using < not <=:

WHERE t1.date >= csd.starting_date AND t1.date < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date)

So the query will be "before Sep 28" rather than "on or before Sep 27".  This is better because then the specific data type of the date column doesn't matter.  

Author

Commented:
Thanks Scott, will do so.

Author

Commented:
Hi Scott,
Just a little bit of extention to the result, will appreciate if you help me there:
https://www.experts-exchange.com/questions/29195749/Get-count-of-the-query-result.html#questionAdd
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.