Dynamically growing/shrinking code in an SP

Question

Dynamically growing/shrinking code in an SP

etl2016

Default port

Points: 1459
More actions
May 10, 2016 at 4:59 am

#310477

Hi,
I have a requirement where a portion of code either grows or shrinks depending on situation, every time it runs.
For example, on week-1 of a month, it has to take a constraint on only 1 week. Next time, it might be two weeks worth of checkings and next 3, 4 (may be 5) and then back to 1 week.
Which feature of T-SQL may I use here? Dynamic SQL or any simpler option?
thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

etl2016 (5/10/2016)
Hi,
I have a requirement where a portion of code either grows or shrinks depending on situation, every time it runs.
For example, on week-1 of a month, it has to take a constraint on only 1 week. Next time, it might be two weeks worth of checkings and next 3, 4 (may be 5) and then back to 1 week.
Which feature of T-SQL may I use here? Dynamic SQL or any simpler option?
thank you.

It depends on what data and schema you have, can you please post the DDL scripts (create table), sample data as an insert statement and the expected results?

😎

Lowell SSC Guru Points: 323518 More actions · Answer 2

it really sounds like it's just plain old parameterized query;

you could be using something like date math to add days or weeks on demand to expand or shrink your calculations.

a simple example i just put together. i can pass 7 days 14 days, even 365 if it made reporting sense.

you could change the parameter to add weeks, or you could just have it find the first day of he current month, and get all data from that.

datemath is a powerful tool that should be in your SQL toolbox.

CREATE PROCEDURE ExampleRange(@DaysBack int=7)

AS

BEGIN

SET NOCOUNT ON

SET @DaysBack = (ABS(@DaysBack) * -1)

SELECT *

FROM MyTable e

WHERE e.DateOfService BETWEEN Dateadd(dd, @DaysBack, Dateadd(dd, Datediff(dd, 0, Getdate()), 0))

AND Dateadd(dd, Datediff(dd, 0, Getdate()), 0)

END --PROC

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

etl2016 Default port Points: 1459 More actions · Answer 3

many thanks.

Every sunday evening 5pm is a cut-off mark and certain aggregations are to be done for that week, with Sunday 5pm as cut-off. As we know, whether there 4 sundays in a given month or 5 sundays is a variant.

So, I am working out how to induce flexibility in the code which should know, "yes, for this month its only 4 sundays, not 5 or 3"

Could you please advise.

thank you

Lowell SSC Guru Points: 323518 More actions · Answer 4

etl2016 (5/10/2016)
many thanks.
Every sunday evening 5pm is a cut-off mark and certain aggregations are to be done for that week, with Sunday 5pm as cut-off. As we know, whether there 4 sundays in a given month or 5 sundays is a variant.
So, I am working out how to induce flexibility in the code which should know, "yes, for this month its only 4 sundays, not 5 or 3"
Could you please advise.
thank you

a sample query you've tried would go a long way; otherwise all of us would have to guess at what you are after.

do you want to filter data via a WHERE statement from the start of the month (ie 05/01/2016 00:00:00) to the nearest sunday 5pm in this month? without going into the future?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

etl2016 (5/10/2016)
many thanks.
Every sunday evening 5pm is a cut-off mark and certain aggregations are to be done for that week, with Sunday 5pm as cut-off. As we know, whether there 4 sundays in a given month or 5 sundays is a variant.
So, I am working out how to induce flexibility in the code which should know, "yes, for this month its only 4 sundays, not 5 or 3"
Could you please advise.
thank you

If you have to do your aggregations for the week from Sunday at 5:00 to Sunday at 5:00, how does the number of Sundays in any given month influence anything? Wouldn't you just use date math to determine the datetime of the previous Sunday at 5:00 and then use:

AND some_datetime_column >= @start_datetime

AND some_datetime_column < @end_datetime

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323518 More actions · Answer 6

here's what i came up with to get the first date of the month, to the "latest" sunday 5pm

there might be a shorter datemath to do the same work, it's just the rabbit hole i fell down

select.

--beginning of current month

DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) As StartOfMonth,

--the end of week(Sunday) plust a time component.

DATEADD(dd, DATEDIFF(dd,0,DATEADD(dd, DATEPART(DW,GETDATE())*-1 + 1, GETDATE())), 0)+ convert(time,'17:00:00') As TheSundayDate --last sunday

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Lowell (5/10/2016)
here's what i came up with to get the first date of the month, to the "latest" sunday 5pm
there might be a shorter datemath to do the same work, it's just the rabbit hole i fell down
select.
--beginning of current month
DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) As StartOfMonth,
--the end of week(Sunday) plust a time component.
DATEADD(dd, DATEDIFF(dd,0,DATEADD(dd, DATEPART(DW,GETDATE())*-1 + 1, GETDATE())), 0)+ convert(time,'17:00:00') As TheSundayDate --last sunday

The arithmetic operation on the date data type does not work, your code will error. Here is an alternative

😎

SELECT

DATEADD(DAY,1,EOMONTH(GETDATE(),-1)) AS StartOfMonth

,DATEADD(HOUR,17,CONVERT(DATETIME,CONVERT(DATE,DATEADD(DAY,-(DATEPART(WEEKDAY, GETDATE())-1),GETDATE()),0),0)) AS TheSundayDate;