Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü�meyer" wrote:
> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> > We have a data extract prcess that runs daily. In SQL Script, there is a
> > variable that equals to 1 (meaning 1 day worth of data). We run this
> > monday
> > through friday at 9:00 PM. Now the client wants, if there is any
> > processing
> > on the weekend (Saturday/Sunday) it should be added to the monday's data.
> >
> > This is an automated process and we need to keep it like that. The
> > question
> > is, how can I modify the script so that it picks up the daily data Tuesday
> > through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> > ?
> >
> > Thanks for any help.
>
>|||Sure that you got your results in mind you want to have, we don´t.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Süßmeyer" wrote:
>> Do you want a sum or the rows on their own, only presenting those on the
>> weekdays ?
>> You have to specify your expected results a bit.
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>> > We have a data extract prcess that runs daily. In SQL Script, there is
>> > a
>> > variable that equals to 1 (meaning 1 day worth of data). We run this
>> > monday
>> > through friday at 9:00 PM. Now the client wants, if there is any
>> > processing
>> > on the weekend (Saturday/Sunday) it should be added to the monday's
>> > data.
>> >
>> > This is an automated process and we need to keep it like that. The
>> > question
>> > is, how can I modify the script so that it picks up the daily data
>> > Tuesday
>> > through friday and 3 days of data (Saturday, Sunday and Monday) on
>> > mondays
>> > ?
>> >
>> > Thanks for any help.
>>

Adding Parameters from .ASPX file

HI,
I want to show results in a report based on a variable send from an .aspx file. Is it possible to do this in de report designer. I have checked the possibilities for adding parameters but as far as I know the only way add parameters to queries in the report designer is, a prompt, a text-box or dropdown. Can I also add code like <% Request.Form(variable) %>.
This would really help me alot so I hope somebody knows the answer. Mucho gracias.
capsync

Hi,

It is quite possible to do. I've posted this code in one of my previous post. This should help you quite a bit, place this code in an aspx file's button click code behind event.

I created a 'objReport' object which was a SRS report object. You should focus on the response.write part.
[ReportServer] = SRS reportserver URL
[Path] = Report Location eg. '/folder/myReport'
[ReportParameters] = Well anything
string strFullParameters = string.Format(objReport.Parameters, strParams);
strFullParameters = strFullParameters.Replace("/", "%2f");
strFullParameters = strFullParameters.Replace(":", "%3a");
strFullParameters = strFullParameters.Replace(" ", "+");

Response.Write("<DIV><IFRAME ID=SQLReportsFrame SRC='" + this.ReportServer + "?" + objReport.Path.Replace("/", "%2f") + strFullParameters + "&rc:ReplacementRoot=" + this.Request.Url.AbsoluteUri + "?ServerUrl=' HEIGHT=100% WIDTH=100%></IFRAME></DIV>");

|||Thanks for your answer. Unfortunately, I still didn't manage to fix it. Can you give me some more explanation please? Thanks in advance.
cheers,
capsync|||

All the parameters needs to exist on the SRS report first( Create all possible parameters), but allow blanks. Unless you would like to add paramters dynamically which is a whole diffrent ball game. Then you could use only the ones required by sending the from the aspx page.

Adding a variable to a stored procedure

Hi,

I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.

Help!!!

I want to add @.OrderBy at the end of :

------
SELECT *
FROM Annonces
WHERE CategoryID = @.CategoryID AND DateOnCreate >= @.DateFinale
------

My declaration :

Declare @.OrderBy nvarchar(50)

If @.Sort = 'date_de'
set @.OrderBy = ' ORDER BY DateOnCreate DESC'
Else
set @.OrderBy = ' ORDER BY DateOnCreate ASC'

You want your code to be built dynamically within in the SQL it sounds like.

I don't think you can do that. Variables can not have inline code in SQL.

|||

You can use a CASE in the ORDER BY with limited flexibility. Do a quick google search or search for my previous post. I think I posted a similar sample a while back.

|||

Dynamic SQL is the classic solution to this, if you need complete flexibility, as Kelsey said. CASE is way less flexible.

You can also write the query in SQLCLR code, if you're using SQL Server 2005 and need lots of flexibility.

Or you can wait until SQL Server 2008, which reportedly will have this capability.

Don

|||

Ok thanks,

Now, how can i add this line

--> AND dbo.fn_GetDistance(@.CodePostal, UserNumber) <= @.Dist <--

In this code :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + '''' + @.OrderBy

I tried many things but all failed.

Thanks|||

In fact, if the dataset is small, your front end is the best place to do this dynamic sorting.

|||

This should give you an idea.:

Declare @.stmtvarchar(1000), @.CodePostalvarchar(10), @.CategoryIDint, @.DateFinaledatetime, @.orderbyvarchar(10)Select @.CodePostal ='567', @.CategoryID = 10, @.DateFinale =getdate(),@.orderby =' name asc'select @.stmt ='SELECT * , dbo.fn_GetDistance(''' + @.CodePostal +''', UserNumber) AS DistanceFROM AnnoncesWHERE CategoryID =' +convert(Varchar,@.CategoryID) +' AND DateOnCreate >= ''' +convert(varchar,@.DateFinale) +'''' + @.OrderBy

|||

Hi,

I tried :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + ''' AND dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) <=' + @.Dist + '' + @.OrderBy

But i get :

Conversion failed when converting the varchar value 'SELECT *, .........blablabla....... <=' to data type int.

I dont understand?

|||

ok i got it!!!

Finally...

My error was i tried to use an integer in a varchar statement.

So i use:

Set@.Distance =convert(Varchar, @.Dist)

And now i can do:

select@.stmt ='SELECT *, dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID ='+ @.CategoryID +' AND DateOnCreate >='''+ @.DateFinale +''' AND dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) <='+ @.Distance +''+ @.OrderBy