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: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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 6:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 1,945, Visits: 3,065
The weekend is two days in the Christians and only Friday for Muslims. Some reporting systems start the weekend on Friday night. Some reporting systems define the weekend as longer when it is a holiday weekend; this is better form sales data. About the only things people agree on is that each week has one and only “weekend” and that the weekend is a contiguous interval.

Another problem is that proprietary MONEY data types do not do correct math; they round too soon and do not carry enough decimal places for GAAP and EU standards. Use DECIMAL(s, 5) instead.

Can you tell I spent a lot of time on Standards Committees and analytics

Here is another approach. Since SQL is a database language, we prefer to do look ups and not calculations. I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This fomat is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.

You input any calendar day, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'
or for four weeks,
WHERE sale_day LIKE '2012W2[6543]-[67]'
This great for reporting done within only one year, but tricky when the reporting window has to go back to the previous year.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1340317
Posted Sunday, August 5, 2012 8:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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