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.
| Author |
Topic |
|
Rheinhardt
Yak Posting Veteran
66 Posts |
 Posted - 2012-06-25 : 11:08:11
|
| Hi,I have Sales History data. On a Item level:Item,Date/Week,qtyI have another table with all the available weeks in it:Weekstartdate,Weekenddate,weeknr/BucketIDI want to be able to count all the available weeks/buckets the Item had from the first week it sold. Not just the weeks it actually sold but count the available buckets since that date to getdate().Examples:Item A might have only started selling in Jan 2012 and even though it only sold in 5 weeks/buckets I want to see how many bucket/weeks was actually avaible to the procuct A in total up to getdate()....Therefore start coutning all avaliable buckets/date/weeks for each item from first sold date.Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-25 : 12:04:53
|
| Where do you want to do this? SSAS, SSRS, SQL, ...And what data do you have available?datediff(dd,min(solddate),getdate())/7==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Rheinhardt
Yak Posting Veteran
66 Posts |
Posted - 2012-06-25 : 13:23:37
|
Hi,In SQl script/procedure please. The data is in SQl database/tables.ThanksRheinhardtquote:
Originally posted by nigelrivett
Where do you want to do this? SSAS, SSRS, SQL, ...And what data do you have available?datediff(dd,min(solddate),getdate())/7==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
Rheinhardt
Yak Posting Veteran
66 Posts |
Posted - 2012-06-25 : 13:42:01
|
| datediff(dd,min(solddate),getdate())/7 Does unfortunately not solve my problem of getting all available buckets/weeks that that producthad to sell, other than JUST the buckets it sold in. So if Prodcust started selling 4 weeks ago and only sold intermittendly 2 of the 4 weeks to current date/getdate() I want to calculate the total available of 4 weeks and not the 2 it only sold in.I have two tables:Item,week/bucket, qtyand...Bucket/week startdate,Bucket/week enddate, week/bucketid |
|
|
|
|
|
|
|
|
Cursor/loop/count???