January 31, 2012 at 11:00 am
Hello -
I'm tying to create a SQL View that will allow users to query the data in excel file.
I need help to write a script to pull this weeks data + 13 weeks ahead. I'm at a lost for the logic.
I can't even supply an example of what I have been trying because I know I'm not even close.
Can anyone shed some light on this for me?
Regards,
D-
January 31, 2012 at 11:11 am
Data from an excel file? You mean, SQL query to retreive data directly from excel file?
Or do u have a staging table (which is the loaded with data extracted from the excel file) from where the view should pick the data?
January 31, 2012 at 11:13 am
a select statement could look like the following:
DECLARE @DateStart DATETIME = CONVERT(CHAR(10),GETDATE(),121);
DECLARE @DateEnd DATETIME = DATEADD(DD, 91, @DATESTART);
SELECT*
FROM
WHERE[DateTime_Column] >= @DateStart
AND[DateTime_Column] < @DateEnd
Hope that helps
//Edit made the dateadd add 91 days instead of 13 days as the requirement was 13 weeks 🙂
January 31, 2012 at 11:14 am
I have a staging table that the users will connect to and pull this data into excel.
Right now it pulls in 52 weeks ahead. So the full year of 2012 and then some
January 31, 2012 at 11:22 am
Then Loundy's post will help you 🙂
January 31, 2012 at 11:37 am
Thank you for fast replies...
With the Declare can I create this as a View? I was getting the error message Incorrect syntax near the keyword 'DECLARE'
DECLARE @DateStart DATETIME = CONVERT(CHAR(10),GETDATE(),121);
DECLARE @DateEnd DATETIME = DATEADD(dd,91, @DATESTART);
SELECT dbo.BacklogLabor.*
FROM dbo.BacklogLabor
WHERE[Schedule Week] >= @DateStart
AND[Schedule Week] < @DateEnd
January 31, 2012 at 11:40 am
what version of sql server are you using? The way i've declared the variables are specific to sql 2008 +
January 31, 2012 at 11:41 am
You cannot create parameterized views. You may either create a Stored Procedure or Table-valued function to do the task.
January 31, 2012 at 11:42 am
I'm using SQL server 2008 R2
January 31, 2012 at 11:43 am
for a view instead of declaring variables change the where clause to the following;
WHERE[DateTime_Column] >= CONVERT(CHAR(10),GETDATE(),121)
AND[DateTime_Column] < CONVERT(CHAR(10),DATEADD(DD,91,GETDATE()),121)
January 31, 2012 at 11:52 am
That ROCKS!!! Thank you
One quick question I just found out they want me to pull one week back so for example it I was running this today they want from the 29th of January + 13 weeks ahead.
Can that be done?
January 31, 2012 at 11:55 am
I don't quite understand, 1 week back would be 24th jan 🙂
January 31, 2012 at 12:13 pm
My apologies for not explain it more. This is to pull a TimeCard log that shows what hours have been booked 1 week past and 13 week ahead. Our time cards go from Sunday to Saturday
When I ran the script the Schedule week which is my Date column I see 2012-02-05 00:00:00.000
I didn’t know if there was a way to view back further to include the 29th?
I hope that makes sense...
Thanks,
D
January 31, 2012 at 12:21 pm
It certainly is possible 🙂
WHERE[DateTime_Column] >= CONVERT(CHAR(10),DATEADD(DD,-7,GETDATE()),121)
AND[DateTime_Column] < CONVERT(CHAR(10),DATEADD(DD,91,GETDATE()),121)
the dateadd function is very flexible. You can + or - days, months, years, minutes, hours etc. play around with it to get the date range you require 🙂
January 31, 2012 at 12:39 pm
You have helped me out so much Thank you... I can see how it all works now.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply