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 2000 Forums
 Transact-SQL (2000)
 Help with TSQL, getting StartDate and EndDate !!!

Author  Topic 

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-20 : 11:32:23
Hi All,

I have a table called PromoHistory with the following fields,types, and Null/NotNull attributes.

WeekDate (datetime) NOT NULL
OutletId (nvarchar) NOT NULL
ProductId (nvarchar) NOT NULL
Price (decimal) NOT NULL
cost (decimal) NOT NULL
PromotionType (nvarchar) NOT NULL
AdType (nvarchar)
DisplayType (nvarchar)
[X Value] (nvarchar)
[Y Value] (nvarchar)

This table has 721651 records.
Following is an example of records for one of the ProductID in one of the OutletID (Although this table have thousands of Products and hundreds of Outlet)
(please note that records are order by WeekDate asc).
(each coulmn is seperated by comma)

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]

2006-10-21, 00370, 000-38000-31841, 3.02, 2.53, Price Promotion, NULL, NULL, 3, 2
2006-10-28, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 2
2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2007-03-24, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-03-31, 00370, 000-38000-31841, 2.5, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-07, 00370, 000-38000-31841, 2.5, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-14, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-21, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-28, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-05, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-12, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL

Now, based on PromoHistory table I want have a SELECT statement excluding WeekDate but want to include the following two columns:

StartDate (datetime)
EndDate (datetime)


In each set of same OutletId; same ProductId; same Price; same cost; same PromotionType; same AdType; same DisplayType; same [X Value]; and same [Y Value], I
want to populate those new columns. i want to populate [Startdate] with the earliest WeekDate in the set and [EndDate] with the largest WeekDate in the set.

So there are two things that must be considerd; a) It's a same set and b) the differnce between the WeeklyDate is 7 (be sure they are sort based on
WeekDate). As long as those 2 criteria are met there should always be 1 record generated per set in the SELECT statement.


In the above example note the following three records.

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Now lets check the 2 criteria. A)Its a same set; meaning it has same OutletId; same ProductId; same Price; same cost; same PromotionType; same AdType; same
DisplayType; same [X Value]; and same [Y Value]. B) For the first two rows only the difference between the Weekdate is 7. So the SELECT statement should
return the following 2 records:

StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

In the 2nd row of the SELECT statement please note that since one of the criteria was not matched then the EndDate will be calculated as follows
Enddate=StartDate+7, so it will be Endate=2006-11-25 + 7, which is 2006-12-02.

Also not that in the SELECT the Enddate and StartDate cannot be NULL.

So for the above example the SELECT should return the following results.

2006-10-21, 2006-10-28, 00370, 000-38000-31841, 3.02, 2.53, Price Promotion, NULL, NULL, 3, 2
2006-10-28, 2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 2
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2007-03-24, 2007-03-31, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-03-31, 2007-04-07, 00370, 000-38000-31841, 2.5, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-07, 2007-04-14, 00370, 000-38000-31841, 2.5, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-14, 2007-04-28, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-05, 2007-05-12, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-12, 2007-05-19, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL


Can someone please help on this to create a script to accomplish this problem?

Thanks a million in advance.

Zee

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-20 : 14:37:01

CAN ANYONE PLEASE HELP?????
IF ANYONE NEEDS THE CREATE TABLE AND INSERT SCRIPT for this example please let me know...

Desperately waiting for help... :(
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-20 : 16:08:31
Your post is overly confusing.
Back up a bit...
...explain why you cannot just use GROUP BY with MIN and MAX to get the StartDate and EndDate by group?

e4 d5 xd5 Nf6
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 16:15:54
quote:
Originally posted by zeeshan13


CAN ANYONE PLEASE HELP?????
IF ANYONE NEEDS THE CREATE TABLE AND INSERT SCRIPT for this example please let me know...

Desperately waiting for help... :(



You should provide this without us having to ask for it. It'll help get your problems answered much faster as it will get people interested in your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 16:26:02
My initial guess is you will have to loop through each record.. and do the processing..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 10:42:36

blindman,

I am not sure how I can use group by to get the min & max date. If I can use it can you please guide me?. First of all I am not trying to get the min & max date per row, rather its per set. And there are 2 criteria that need to be followed which I have explain earlier. Can you please read again the whole problem....

Using examples I tried to explain the problem as clealry as possible. But I guess because of the more number of columns or may be the size of the problem people are not taking intrest.

Can you guys please read the problem from top to bottom once. If you still think you are lost please get back to me.

Thanks a million in advance.

Zee
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-21 : 10:52:31
quote:
Originally posted by zeeshan13


CAN ANYONE PLEASE HELP?????
IF ANYONE NEEDS THE CREATE TABLE AND INSERT SCRIPT for this example please let me know...

Desperately waiting for help... :(



1. Please provide the script
2. Indicate, in plain english, what you want returned. For example, "For each widget, I need to return the min and max price, for a given month". Your explanation it very, very confusing. Be brief and precise and accurate, and you'll get much more help.


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 11:16:11
I think what he is wanting is to get the min and max dates per set and return that as Startdate and Enddate. Blindman mentioned using group by with min and max which will do the trick...kinda like this:

create table #test (sets int, dt datetime)

insert into #test values (1, '2/3/07')
insert into #test values (1, '2/7/07')
insert into #test values (1, '2/15/07')
insert into #test values (2, '2/19/07')
insert into #test values (2, '3/5/07')
insert into #test values (2, '2/9/07')
insert into #test values (2, '2/6/07')


select sets, min(dt) as StartDate, max(dt) as EndDate from #test group by sets
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 11:32:06
jsmith8858,

I dont know how to explain it in one line since its hard to explain beacuse of the complexity of the problem. Below I have explain it based on 2 criteria.

Look at the following PromoHistory table again (with 13 rows). Here they are sort by WeekDate

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-10-21, 00370, 000-38000-31841, 3.02, 2.53, Price Promotion, NULL, NULL, 3, 2
2006-10-28, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 2
2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2007-03-24, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-03-31, 00370, 000-38000-31841, 2.5, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-07, 00370, 000-38000-31841, 2.5, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-14, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-21, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-28, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-05, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-12, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL

WHAT I AM LOOKING FOR:
In my select query I want to get the Startdate, and Enddate along with OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value] based on the following 2 criterias:

CRITERIA 1: (Just to discuss, If its met I will say it a SET)
Check if its it same OutletId; same ProductId; same Price; same cost; same PromotionType; same adType; same DisplayType; same [X Value]; and same [Y Value].

CRITERIA 2: (Just to discuss, if its met along with CRITERIA1 I will call it FINAL SET)
Once the CRITERIA1 is met, now also check if the differnce between the WeeklyDate is 7 (be sure they were sort based on Weekdate)

To narrow down look the following 3 records in the PromoHistoryTable.

In the above example note the following three records.

Lets call it FIGURE1:
WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Now lets check the both Criteria against these 3 records.

CHECKING CRITERIA 1:
The CRITERIA 1 is met for all the 3 rows since in these 3 rows they are same OutletId; same ProductId; same Price; same cost; same PromotionType; same adType; same DisplayType; same [X Value]; and same [Y Value].

CHECKING CRITERIA 2:
The CRITERIA 2 is meeting for the first two rows only since the Difference in WeekDate for the first two rows (2006-11-04 & 2006-11-11) is 7. But the Differnce of Weekdate between row 2nd and 3rd (2006-11-11 & 2006-11-25) is not 7 but it is 14.

So for the first two rows in FIGURE1 the SELECT will generate the following one record.
StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

For the third row since there's nothing for it (no other record to compare for this SET) , the StartDate will be its own WeekDate (2006-11-25) & the EndDate will be its Weekdate+7 (2006-12-02).

So for the third row in FIGURE1 the SELECT should return the following.
StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

So for those 3 rows in the FIGURE1 it should return the following 2 rows only
StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Does it make sense?
I want to do this for all records in the table.

Please let me know how can you help me on this.






Thanks a million...

Zee
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 11:34:54
jsmith8858,

Below is a script that will create a temperoray table PromoHistory with those 13 rows inserted. Please run it, and then you can test & play around with your script.

Here is the CREATE table & Insert script for you.
--Start of Script
Create table #PromoHistory
(
WeekDate datetime NOT NULL, OutletId nvarchar(50) NOT NULL, ProductId nvarchar(50) NOT NULL,Price decimal(18,2) NOT NULL,Cost decimal(18,2) NOT NULL,PromotionType nvarchar(50) NOT NULL,
AdType nvarchar(50),DisplayType nvarchar(50),[X Value] nvarchar(50),[Y Value] nvarchar(50))
GO

INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2006-10-21','00370','000-38000-31841',3.02,2.53,'Price Promotion',NULL,NULL,'3','2')
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2006-10-28','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,2,2)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2006-11-04','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2006-11-11','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2006-11-25','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-03-24','00370','000-38000-31841',2.51,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-03-31','00370','000-38000-31841',2.50,1.97,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-04-07','00370','000-38000-31841',2.50,2.39,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-04-14','00370','000-38000-31841',2.50,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-04-21','00370','000-38000-31841',2.50,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-04-28','00370','000-38000-31841',2.50,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-05-05','00370','000-38000-31841',2.51,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES ('2007-05-12','00370','000-38000-31841',2.53,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
--End of Script

Finaly the script that I am looking for should return the following records only.

StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-10-21, 2006-10-28, 00370, 000-38000-31841, 3.02, 2.53, Price Promotion, NULL, NULL, 3, 2
2006-10-28, 2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 2
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2007-03-24, 2007-03-31, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKE, IN AISLE, NULL, NULL
2007-03-31, 2007-04-07, 00370, 000-38000-31841, 2.50, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-07, 2007-04-14, 00370, 000-38000-31841, 2.50, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-14, 2007-04-28, 00370, 000-38000-31841, 2.50, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-05, 2007-05-05, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-12, 2007-05-19, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL

CAN YOU PLEASE HELP....
THANKS A MILLION IN ADVANCE.....

Zee
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 11:39:19
What fields make up a set? It appears you don't have a unique combination of fields to distiquish a set.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 11:49:44
I do have a unique combination.
The OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value] will is a unique combination & then will make a SET.

So a) the script needs to identify each SET. (I have explained the definition of "A SET" in my last reply). Then it need to compare all the rows in each set (make sure the rows for each row is ordered by WeekDate), and then comeup with Start and Enddate, and so on.

Also note that a SET may also have one row.

Does it make sense?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 12:01:20
You have these listed as what you want for output:

2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Those are the same set correct? Why are they in 2 different lines of output?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 12:26:17
According to what you have defined as being a set (all fields except WeekDate) this will get you the StartDate and EndDate columns. (It's what Blindman was saying to do)


select min(WeekDate) as StartDate, max(WeekDate) as EndDate, OutletId, ProductId, Price, Cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
from #PromoHistory
group by OutletId, ProductId, Price, Cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
order by OutletId, ProductId, Price, Cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 12:27:28
Look at those 3 rows in the PromoHistory table.

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Yes, they are same set, that is CRITERA1 has met, but the CRITERIA2 that is the differnce in Weekdate between last two rows is not 7 but 14.

So for those 3 rows the SELECT should generate the following 2 rows.

StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Does it make sense?
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 12:31:55

Van,

No your script is not right. The SELECT should not have same StartDate and EndDate.

Please read my last reply....
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 12:33:12
I forgot about the second condition. You will probably have to loop through the records in order to compare the dates.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 12:35:41
I wish I was that good in SQL :( ....still learning.

Here is a high level neat example.

So for same SET (CRITERIA 1 is met) let say we have 7 records sort by weekdate asc.

So if the differnce between Weekdate 2 & WeekDate 1 is 7 then its good and then check if the differnce betweek WeekDate 3 & WeekDate 2 is 7, if it is 7 then good check the differnce between WeekDate 3 & WeekDate 4 which in the following example is not 7, then this WeekDate 3 (2006-01-15) will become the EndDate and the weekday 1 (2006-01-01) will be the startDate.

Then for the remaining records from Week 4 you need to start the above process again unless and comeup with a EndDate.

Example:
2006-01-01
2006-01-08
2006-01-15
2006-01-29
2006-02-05
2006-02-12
2006-02-19

So in this example (it is a same SET) two records will be generated

SatrDate,Enddate
2006-01-01, 2006-01-15
2006-01-29, 2006-02-19

Does this make sense?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 12:39:42
One last thing, what if it's less than 7, still counts like 7 and keep going right?
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 12:44:59
Van,

It has to be 7.
It cannot be less than or greater than 7. Less than 7 will be processed similarly how the greater than 7 would be..which I already explained.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-21 : 13:56:02
Can anyone help me please?

Thanks in advance..
Go to Top of Page
    Next Page

- Advertisement -