Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to count the total sales prior's 4 weekend if user give the weekend date. Expand / Collapse
Author
Message
Posted Sunday, August 5, 2012 2:50 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, January 13, 2013 11:35 AM
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
Post #1340294
Posted Sunday, August 5, 2012 4:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
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
Post #1340310
Posted Sunday, August 5, 2012 4:40 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, January 13, 2013 11:35 AM
Points: 289, Visits: 539
Thanks bitbucket-25253
I appreciate your help.
Post #1340311
Posted Sunday, August 5, 2012 5:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, January 13, 2013 11:35 AM
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.
Post #1340313
Posted Sunday, August 5, 2012 8:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
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
Post #1340326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse