Post reply

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

  • August 5, 2012 at 2:50 pm

    #262416

    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

  • August 5, 2012 at 4:36 pm

    #1521793

    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[/url]
    Before posting a performance problem please read[/url]

  • August 5, 2012 at 4:40 pm

    #1521794

    Thanks bitbucket-25253

    I appreciate your help.

  • August 5, 2012 at 5:07 pm

    #1521795

    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.

  • August 5, 2012 at 8:44 pm

    #1521801

    2011-12-182011-12-192011-12-202011-12-212011-12-222011-12-232011-12-24

    Sunday Monday Tuesday WednesdayThursdayFriday 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[/url]
    Before posting a performance problem please read[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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