Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to count the total sales prior's 4 weekend if user give the weekend date.


How to count the total sales prior's 4 weekend if user give the weekend date.

Author
Message
Jay Pete
Jay Pete
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 539
Hi All,

I need to count the total sales prior's 4 weekend when user give the weekend date.
I have sample data and table structure.
Please help me out to build the query.

Create table Sales(
Weekenddate date,
Salesweek money
)


Insert into Sales Values('2011-12-24',1000)
Insert into Sales Values('2011-12-17',2000)
Insert into Sales Values('2011-12-10',2500)
Insert into Sales Values('2011-12-03',4000)
Insert into Sales Values('2011-11-26',1000)
Insert into Sales Values('2011-11-19',5000)
Insert into Sales Values('2011-11-12',10000)


Thanks in advance,
Jay
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5679 Visits: 25280
Is this what you are looking for:

 
DECLARE @D DATETIME
SET @D = '2011-12-24' -- user passed value
SELECT SUM(Salesweek) FROM Sales WHERE Weekenddate BETWEEN DATEADD(ww,-3,@D) AND @D

Result: 9500




Now if the user passes in an improper date, for example
 
SET @D = '2011-12-23'
SELECT SUM(Salesweek) FROM Sales WHERE Weekenddate BETWEEN DATEADD(ww,-3,@D) AND @D

Result: 8500



Your T-SQL that executes the code, should check that the user passed value is a valid weekenddate. You could use an IF EXISTS statement to check that and return a message if the date is not valid.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Jay Pete
Jay Pete
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 539
Thanks bitbucket-25253
I appreciate your help.
Jay Pete
Jay Pete
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 539
bitbucket-25253 (8/5/2012)
Is this what you are looking for:

 
DECLARE @D DATETIME
SET @D = '2011-12-24' -- user passed value
SELECT SUM(Salesweek) FROM Sales WHERE Weekenddate BETWEEN DATEADD(ww,-3,@D) AND @D

Result: 9500




Now if the user passes in an improper date, for example
 
SET @D = '2011-12-23'
SELECT SUM(Salesweek) FROM Sales WHERE Weekenddate BETWEEN DATEADD(ww,-3,@D) AND @D

Result: 8500



Your T-SQL that executes the code, should check that the user passed value is a valid weekenddate. You could use an IF EXISTS statement to check that and return a message if the date is not valid.


Is there any way to get the rid for invalid weekend date.
Let's say that if user will give the date '2011-12-23' and it should take the closer weekend date for the given date.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5679 Visits: 25280

2011-12-18   2011-12-19   2011-12-20   2011-12-21   2011-12-22   2011-12-23   2011-12-24
Sunday    Monday Tuesday    Wednesday   Thursday   Friday    Saturday



Now let me ask you this .. assume the user inputs the date of 2011-12-21 which is a Wednesday... and you asked
it should take the closer weekend date for the given date.


Which do you think is the closer weekend the 24th or the 17th ?

Now if the user inputs an incorrect date and does not realize the incorrect input, and you correct it, will the user really be returned the data that they thought they asked for ?

If they are not informed, incorrect decisions could be made. Now I know this is taking the answer out of the realm of T-SQL, but isn't it our job as DBAs / Developers to provide the best support we can ?

Let me put it another way, the user gets the data, deduces that the sales volume is not sufficient enough to give you the DBA a pay raise, all because the user input an incorrect date and was not informed of making that error.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search