Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Use date function to find sales history

Author  Topic 

nyuser
Starting Member

4 Posts
Posted - 2008-01-26 : 21:32:36
How to use a date functions in stored procedure to create a report as shown below: (table with sample data is included)

-----------------------------------------------------------------
             Item Sale History as of 1-30-2008
-----------------------------------------------------------------
Duration      (mm/yy)    Total Quantity       Retail Price 
-----------------------------------------------------------------
Current Week  (Jan/08)         58                 59.98
Last Week     (Jan/08)         48                 49.98
2 Week Ago    (Jan/08)         32                 33.98
3 Week Ago    (Jan/08)         15                 16.98
4 Week Ago    (Jan/08)         04                 05.98
Current Month (Jan 08)        157                166.90
1  Month Ago  (Dec 07)         38                 39.98
2  Month Ago  (Nov 07)         27                 28.98
3  Month Ago  (Oct 07)
4  Month Ago  (Sep 07)
5  Month Ago  (Aug 07)
6  Month Ago  (Jul 07)
7  Month Ago  (Jun 07)
8  Month Ago  (May 07)
9  Month Ago  (Apr 07)
10 Month Ago  (Mar 07)
11 Month Ago  (Feb 07)
12 Month Ago  (Jan 07)

DECLARE @ItemHistory TABLE (
	ItemID INT,
	Quantity INT,
	TotalPrice MONEY,
	DateSold DATETIME )

INSERT INTO @ItemHistory 
SELECT 1, 01, 01.99, '01-01-2007' UNION ALL
SELECT 1, 30, 30.99, '01-30-2007' UNION ALL
SELECT 1, 02, 02.99, '02-02-2007' UNION ALL
SELECT 1, 22, 22.99, '02-22-2007' UNION ALL
SELECT 1, 03, 03.99, '03-03-2007' UNION ALL
SELECT 1, 27, 27.99, '03-27-2007' UNION ALL
SELECT 1, 04, 04.99, '04-04-2007' UNION ALL
SELECT 1, 16, 16.99, '04-16-2007' UNION ALL
SELECT 1, 05, 05.99, '05-05-2007' UNION ALL
SELECT 1, 24, 24.99, '05-24-2007' UNION ALL
SELECT 1, 06, 06.99, '06-06-2007' UNION ALL
SELECT 1, 29, 29.99, '06-29-2007' UNION ALL
SELECT 1, 07, 07.99, '07-07-2007' UNION ALL
SELECT 1, 31, 31.99, '07-31-2007' UNION ALL
SELECT 1, 08, 08.99, '08-08-2007' UNION ALL
SELECT 1, 19, 19.99, '08-19-2007' UNION ALL
SELECT 1, 09, 09.99, '09-09-2007' UNION ALL
SELECT 1, 20, 20.99, '09-20-2007' UNION ALL
SELECT 1, 10, 10.99, '10-10-2007' UNION ALL
SELECT 1, 21, 21.99, '10-21-2007' UNION ALL
SELECT 1, 11, 11.99, '11-11-2007' UNION ALL
SELECT 1, 16, 16.99, '11-16-2007' UNION ALL
SELECT 1, 12, 12.99, '12-12-2007' UNION ALL
SELECT 1, 26, 26.99, '12-26-2007' UNION ALL
SELECT 1, 01, 01.99, '01-01-2008' UNION ALL
SELECT 1, 03, 03.99, '01-03-2008' UNION ALL
SELECT 1, 06, 06.99, '01-06-2008' UNION ALL
SELECT 1, 09, 09.99, '01-09-2008' UNION ALL
SELECT 1, 14, 14.99, '01-14-2008' UNION ALL
SELECT 1, 18, 18.99, '01-18-2008' UNION ALL
SELECT 1, 22, 22.99, '01-22-2008' UNION ALL
SELECT 1, 26, 26.99, '01-26-2008' UNION ALL
SELECT 1, 28, 28.99, '01-28-2008' UNION ALL
SELECT 1, 30, 30.99, '01-30-2008'

Thank you.

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-01-26 : 22:55:48
Here is a possible start for you. You should use the front end, display application to format the values:
Duration, DateSold

declare @asOf datetime
set @asof = '2008-01-30'

select	'week' as duration
	,max(dateSold) dateSold
	,datediff(week, 0, @asof) - datediff(week, 0, DateSold) as Offset
	,sum(quantity) TotalQuantity
	,sum(TotalPrice) RetailPrice
from @ItemHistory
where	datediff(week, 0, @asof) - datediff(week, 0, DateSold) between 0 and 5
group by datediff(week, 0, @asof) - datediff(week, 0, DateSold)
UNION ALL
select	'month' as duration
	,max(dateSold) dateSold
	,datediff(month, 0, @asof) - datediff(month, 0, DateSold) as OffSet
	,sum(quantity) TotalQuantity
	,sum(TotalPrice) RetailPrice
from @ItemHistory
where	datediff(month, 0, @asof) - datediff(month, 0, DateSold) >= 0
group by datediff(month, 0, @asof) - datediff(month, 0, DateSold)


output:
duration dateSold                                               Offset      TotalQuantity RetailPrice           
-------- ------------------------------------------------------ ----------- ------------- --------------------- 
week     2008-01-30 00:00:00.000                                0           58            59.9800
week     2008-01-26 00:00:00.000                                1           48            49.9800
week     2008-01-18 00:00:00.000                                2           32            33.9800
week     2008-01-09 00:00:00.000                                3           15            16.9800
week     2008-01-03 00:00:00.000                                4           4             5.9800
week     2007-12-26 00:00:00.000                                5           26            26.9900
month    2008-01-30 00:00:00.000                                0           157           166.9000
month    2007-12-26 00:00:00.000                                1           38            39.9800
month    2007-11-16 00:00:00.000                                2           27            28.9800
month    2007-10-21 00:00:00.000                                3           31            32.9800
month    2007-09-20 00:00:00.000                                4           29            30.9800
month    2007-08-19 00:00:00.000                                5           27            28.9800
month    2007-07-31 00:00:00.000                                6           38            39.9800
month    2007-06-29 00:00:00.000                                7           35            36.9800
month    2007-05-24 00:00:00.000                                8           29            30.9800
month    2007-04-16 00:00:00.000                                9           20            21.9800
month    2007-03-27 00:00:00.000                                10          30            31.9800
month    2007-02-22 00:00:00.000                                11          24            25.9800
month    2007-01-30 00:00:00.000                                12          31            32.9800


EDIT:
Changed getdate() to @asOf

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-01-26 : 23:02:28
BTW - Thank you for supplying DDL and DML !! I wish others would do that

Be One with the Optimizer
TG
Go to Top of Page

nyuser
Starting Member

4 Posts
Posted - 2008-01-27 : 09:26:05
TG,

Thank you very much for your quick reply and yes I will use duration & offset in my front-end for formatting. Also, is it possible to include offset in duration (as shown below), this will make my front-end code little bit easy.

duration+offset        
week0                    Current Week
week1                    Last Week
week2                    2 Week Ago
week3                    3 Week Ago
week4                    4 Week Ago
month0                   Current Month
month1                   1  Month Ago
month2                   2  Month Ago
month3                   3  Month Ago
month4                   4  Month Ago
month5                   5  Month Ago
month6                   6  Month Ago
month7                   7  Month Ago
month8                   8  Month Ago
month9                   9  Month Ago
month10                 10 Month Ago
month11                 11 Month Ago
month12                 12 Month Ago
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-01-27 : 09:55:17
That defeats the purpose of doing the display formatting at the frontend . This choice should be based on resource performance rather than "ease" of development and the correct choice is to do the formatting on frontend. But if you put query (from above) into a "derived table" you can format the columns anyway you want:


select	duration + convert(varchar, Offset) as [dur+Offset]
	,dateSold
	,TotalQuantity
	,RetailPrice
from	(
	<query from above>
	) d


Be One with the Optimizer
TG
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site