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)
 Suming/grouping up data from daily to weekly basis

Author  Topic 

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-03 : 12:58:01
Hi All,

I have a table called ItemHistory with the following fields and types.

SaleDate (smalldatetime)
StoreID (smallint)
ProdID(varchar)
Price (smallmoney)
Cost (smallmoney)
Units (bigint)


Each record in the above ItemHistory table tells the price, cost, and units of each product that’s been sold by each store per saledate. Here the date is on a daily basis, and each product per store per date may be sold more than once per SaleDate.

Now, I want to somehow get the saledate on a weekly basis. That is get the sum (units), Sum (Price), and Sum (cost) for each week, where the week date should always be Wed. So for each set of ProdID & OutletID I want to sum up the numbers where the date is from consecutive Wed to Tues, and then name the date as Wed.

Example:
Alhough I may have thousands of products, and hunderds of Outlets, let consider this example of a ProdID 123-4 which was sold in StoreID 001 from 2007-01-03 (Wed) to 2007-01-09 (Tues). I don’t have the Day field here, put I put it so that it would be clear what I am talking about. [Here each field & record is separated by comma]

Day,SaleDate,StoreID,ProdID,Price,Cost,Units
Wednesday,2007-01-03,001,123-4,8.00,8.00,4
Wednesday,2007-01-03,001,123-4,10.50,10.00,5
Thursday,2007-01-04,001,123-4,12.60,12.00,6
Thursday,2007-01-04,001,123-4,14.70,14.00,7
Friday,2007-01-05,001,123-4,16.80,16.00,8
Saturday,2007-01-06,001,123-4,18.90,18.00,9
Sunday,2007-01-07,001,123-4,21.00,20.00,10
Monday,2007-01-08,001,123-4,23.10,22.00,11
Monday,2007-01-08,001,123-4,25.80,24.00,12
Tuesday,2007-01-09,001,123-4,27.95,26.00,13

Now against the above records my script should return the following one record only.
SaleDate,StoreID,ProdID,Price,Cost,Units
2007-01-03,001,123-4,179.35,170.00,85

Now note that as long as the ProdID & OutletID are same the price, cost, and units are sum up from consecutive Wed to Tues, and then the Starting wed date should always be return as SaleDate.

So In the end whatever my script is going to return the SaleDate should always be Wed, and I should always have one record per SaleDate (always WED), per OutletID, per product.

How can I acomplish this in SQL?

Any quick help would be highly appreciated.

Thanks,

Zee

NeilG
Aged Yak Warrior

530 Posts
Posted - 2007-04-03 : 13:09:49
So to be clear are you trying to get a sum of the product qty and total sales of a certain product and certain store,

and also is there a reason that it has to be a week from wednesday could you not have it a week amount from the date the report is run.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-03 : 13:13:25
The function on the link below will convert the date to the start of the week, Wednesday. Then you can just use a summary query to get your totals, grouped by start of week.

Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

select
	DT,
	WEEK = dbo.F_START_OF_WEEK(a.DT,4)
from
	(
	select DT = convert(datetime,'20070103')	union all
	select DT = convert(datetime,'20070104')	union all
	select DT = convert(datetime,'20070105')	union all
	select DT = convert(datetime,'20070106')	union all
	select DT = convert(datetime,'20070107')	union all
	select DT = convert(datetime,'20070108')	union all
	select DT = convert(datetime,'20070109')	union all
	select DT = convert(datetime,'20070110')
	) a

DT                      WEEK
----------------------- -----------------------
2007-01-03 00:00:00.000 2007-01-03 00:00:00.000
2007-01-04 00:00:00.000 2007-01-03 00:00:00.000
2007-01-05 00:00:00.000 2007-01-03 00:00:00.000
2007-01-06 00:00:00.000 2007-01-03 00:00:00.000
2007-01-07 00:00:00.000 2007-01-03 00:00:00.000
2007-01-08 00:00:00.000 2007-01-03 00:00:00.000
2007-01-09 00:00:00.000 2007-01-03 00:00:00.000
2007-01-10 00:00:00.000 2007-01-10 00:00:00.000

(8 row(s) affected)


CODO ERGO SUM
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-03 : 13:15:12

Yes I am trying to get sum of the product qty and total sales of a certain product and certain store (to have one record per date per outlet per product, where date is WED) but its not for reporting. We want the data on a weekly basis which our client cant provide, and this is how we want to sup them up.

Can you or anyone else help on this please !!!

Thanks a million....
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-03 : 13:18:41
Michael Valentine Jones,

I haven't tried your solution yet. But I have millions of records which for 3 years of history. I dont think I can use your solution to get the date.

If someone can please get me the entire script to help me accopmlish what Ia m trying to get here.

Thanks a million.....
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-03 : 13:27:09
quote:
Originally posted by zeeshan13

Michael Valentine Jones,

I haven't tried your solution yet. But I have millions of records which for 3 years of history. I dont think I can use your solution to get the date.

If someone can please get me the entire script to help me accopmlish what Ia m trying to get here.

Thanks a million.....



You can use my solution to get the dates if you know how to write SQL.

What is it that you don't understand how to do?



CODO ERGO SUM
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-03 : 14:28:51
Michael Valentine Jones,

I did not actually read your solution. Know when I read it it works :). Your function is great :D.

After creating your function this what I did.

--Step 1
--To get the week for each respective Saledate
select saledate,dbo.F_START_OF_WEEK(saledate,4) Week,Price,Cost,Units
into #x
from itemhistory

--Step 2
--Get the SumPrice,SumCost, and SumUnits per Week
select week,sum(Price)SumPrice,sum(cost)Sumcost,sum(Units)SumUnits
from #x
group by week

Here are the exact results which I was looking for:
Week,SumPrice,SumCost,SumUnits
2007-01-03,179.3500,170.0000,85

Thanks a lot !!!!
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-03 : 14:51:48
Michael Valentine Jones,

I am curious to know how your function is working. Can you please explain step by step.

Also, someone was telling me that what I was trying to accomplish can be done without using fuction, and using some case statements instead. Do you or anyone else have any idea on that alternate solution ?

Thanks again !!!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-03 : 16:00:51
quote:
Originally posted by zeeshan13

Michael Valentine Jones,

I am curious to know how your function is working. Can you please explain step by step.

Also, someone was telling me that what I was trying to accomplish can be done without using fuction, and using some case statements instead. Do you or anyone else have any idea on that alternate solution ?

Thanks again !!!

You do not have to use a function; you can use in-line code to do the same thing. Both methods are documented in the link that I gave you.

The code below shows the query with the function, and with in-line code. As you can see, no CASE statement is necessary.

The algorithm is simple: Find the difference in days between saledate and 1753-01-03 (the earliest possible SQL Server datetime Wednesday), divide that number by 7, and then multiply by 7 to give the latest Wednesday on or before the date passed.

As for an alternate solution, you can ask whoever said they have one. I doubt that it will be simpler than the code I posted.

-- Using function
select
	dbo.F_START_OF_WEEK(saledate,4)  as week
	sum(Price) as SumPrice,
	sum(cost) as Sumcost,
	sum(Units) as SumUnits
from
	itemhistory a
group by
	dbo.F_START_OF_WEEK(saledate,4) 
order by
	dbo.F_START_OF_WEEK(saledate,4)


-- Using in-line code
-- Date -53688 = 1753-01-03, the earliest possible Wednesday in SQL Server datetime

select
	dateadd(dd,(datediff(dd,-53688,a.saledate)/7)*7,-53688) as week
	sum(Price) as SumPrice,
	sum(cost) as Sumcost,
	sum(Units) as SumUnits
from
	itemhistory a
group by
	dateadd(dd,(datediff(dd,-53688,a.saledate)/7)*7,-53688)
order by
	dateadd(dd,(datediff(dd,-53688,a.saledate)/7)*7,-53688)







CODO ERGO SUM
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-03 : 17:22:27
Michael,

Thanks again :)
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-04 : 16:40:47
Michael,

You can use the following script with case statement to get the same results.

--Start of script
select case
when datename(weekday, saledate) = 'Thursday' then dateadd(day, -1, saledate)
when datename(weekday, saledate) = 'Friday' then dateadd(day, -2, saledate)
when datename(weekday, saledate) = 'Saturday' then dateadd(day, -3, saledate)
when datename(weekday, saledate) = 'Sunday' then dateadd(day, -4, saledate)
when datename(weekday, saledate) = 'Monday' then dateadd(day, -5, saledate)
when datename(weekday, saledate) = 'Tuesday' then dateadd(day, -6, saledate)
when datename(weekday, saledate) = 'Wednesday' then saledate
end as Week,
sum(Price) as SumPrice,
sum(cost) as Sumcost,
sum(units) as SumUnits
from itemhistory
group by
case
when datename(weekday, saledate) = 'Thursday' then dateadd(day, -1, saledate)
when datename(weekday, saledate) = 'Friday' then dateadd(day, -2, saledate)
when datename(weekday, saledate) = 'Saturday' then dateadd(day, -3, saledate)
when datename(weekday, saledate) = 'Sunday' then dateadd(day, -4, saledate)
when datename(weekday, saledate) = 'Monday' then dateadd(day, -5, saledate)
when datename(weekday, saledate) = 'Tuesday' then dateadd(day, -6, saledate)
when datename(weekday, saledate) = 'Wednesday' then saledate
end
--End of script


Let me know what do you think about this script.

Thanks....
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-04 : 17:18:31
I think my solution is a better way to go, because:
1. It is simpler, less code, and will more than likely run faster.
2. It will work with datetime values that contain times not equal to midnight, for example: 2007-01-03 16:44:55.667
3. It will work with any language setting. The code you posted will only work if the language setting is some version of English.




CODO ERGO SUM
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts
Posted - 2007-04-04 : 17:23:45
I agree with you.

One thing in your following clause:
dateadd(dd,(datediff(dd,-53688,a.saledate)/7)*7,-53688)

How you get this -53688 number, is it equavilent to 1753-01-03 date. If yes how?

can you please explain.

Thanks again....
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-04 : 17:31:58
Just look at what happens when you run this.

select
	Num,
	DT = convert(datetime,Num)
from
	(
	select Num = 0	union all
	select -1	union all
	select 1	union all
	select -53688	union all
	select -53689	union all
	select -53690	union all
	select datediff(dd,0,getdate())
	) a



Also, you should look af various topics on the link below to get a more complete understanding about how to work with date and time in SQL Server.
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762




CODO ERGO SUM
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