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 |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2002-12-02 : 16:03:41
|
| Greetings all. Been a bit since I have visited. Here is a question that I am wrangling with.I have a table that has an entry for each period (which is really defined as a month). Each entry has statistical information related to an individual. One field is to determine if they placed an order in the current period.Question: If the customer has not placed an order in the current period I want to count how many consecutive periods they have not had an order.Table structure would be as the following:Customer ID,Period ID,...,Order Placed*Data would look like this: 1, 5, 11, 4, 11, 3, 01, 2, 12, 5, 02, 4, 12, 3, 12, 2, 0Results would look like this:Customer, Periods Consecutive Orders1, 22, 0* Order placed field is a Bit field with 1 = yes and 0 = NoIs that clear? I appreciate any suggestions. I could do it up real nice with a fat, juicy cursor but then my mother would speak with me anymore. Wait, she doesn't anyway. Thank you so much in advance for any assistance.Rev. James Tow |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-12-02 : 17:19:48
|
| try this:declare @temp table (CustomerID int, PeriodID int, OrderPlaced int)declare @currentmonth intset @currentmonth = 7insert into @temp select 1, 5, 1 insert into @temp select 1, 4, 1 insert into @temp select 1, 3, 0 insert into @temp select 1, 2, 1 insert into @temp select 2, 5, 0 insert into @temp select 2, 4, 1 insert into @temp select 2, 3, 1 insert into @temp select 2, 2, 0 select CustomerID, @currentmonth - max(PeriodID) as ConsecutivePeriodsfrom @tempwhere OrderPlaced = 1group by CustomeriDam I missing something?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-12-03 : 11:17:49
|
| I came up with a solution that I have below. It is different from the actual SQL query that works but I had to change it to protect the innocent and keep myself from getting canned due to the NDA's that I signed:declare @customer_iden_fk numeric (12)set @customer_iden_fk = 1010000089SELECT BACKUP_ORDER_COUNT = CASE WHEN dbo.Get_Period_Iden(CUST.LAST_BKUP_ORDR_DATE) = dbo.GetCurrPeriodIden() THEN (SELECT datediff(mm, First_Non_BO_Date, dbo.GetPeriodBeginDate(dbo.GetCurrPeriodIden())) FROM (SELECT First_Non_BO_Date = ( select top 1 tp.begin_date from tbl_cust_period_summary tps1 inner join tbl_periods tp on tps1.period_iden_fk = tp.iden where tps1.customer_iden_fk = @customer_iden_fk and tps1.backup_order_flag = 0 and tp.begin_date < dbo.GetPeriodBeginDate(dbo.GetCurrPeriodIden()) order by 1 desc)) As DateRange)ELSE 0 ENDFROM USRDSS.DBO.TBL_CUSTOMER CUST WHERE CUST.IDEN = @customer_iden_fkIt is ugly but then so am I (or so my mother told me). I appreciate the help however. Thanks!Rev. James Tow |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-12-03 : 18:32:29
|
| Sorry James - couldn't quite pick up from your tone whether that means problem solved - or whether you're asking further questions?Ciao--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-12-03 : 18:54:58
|
| Sorry, it means that I solved my problem. I did encounter some performance issues and strange events while developing it however. Performance with the IsNull in my where clause was taking over 5 minutes for a simple select statement. Remove it and it was under a second. I know that IsNull is evil and should be avoided now. Also, when using Str(@variable) I was getting some strange results, well not results but errors, however when changed to cast(@variable, as varchar) I did not get the errors. It was very strange. Non the less, I do appreciate the help.Rev. James Tow |
|
|
|
|
|
|
|
|
Consecutive instances...