Calculating Dates - SQL Server Forums

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

SQL Server Development (2000)

Calculating Dates

Author

Topic

Husman
Starting Member

13 Posts

Posted - 2007-09-21 : 09:44:07

Hi,

Can someone help me with the below query

I want to count all orders, start date < 28 days of last end Date.

For example:
orderid 1 have 4 orders( 2 were within 28 days)
orderid 2 have 1 order(1 was within 28 days)

CREATE TABLE Orders
(Order_ID int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime DEFAULT NULL
)

INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-01-01 00:00:00.000','2007-01-28 00:00:00.000')
INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-02-02 00:00:00.000','2007-03-03 00:00:00.000')
INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-04-01 00:00:00.000','2007-04-25 00:00:00.000')
INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-06-03 00:00:00.000','2007-06-25 00:00:00.000')
INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(2,'2007-04-01 00:00:00.000','2007-04-25 00:00:00.000')
INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(2,'2007-05-28 00:00:00.000','2007-06-25 00:00:00.000')
INSERT INTO [Orders]([Order_ID], [StartDate], [EndDate])
VALUES(2,'2007-06-29 00:00:00.000','2007-08-25 00:00:00.000')

Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:56:33

Try

select order_id,count(*) from orders
where startdate<(select dateadd(day,datediff(day,0,max(enddate)),-28) from orders)
group by order_id

Madhivanan

Failing to plan is Planning to fail

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-21 : 10:01:58

Hi,

Try this

SELECT [Order_ID], COUNT([EndDate])
FROM Orders
WHERE DATEDIFF(DAY, [StartDate], [EndDate]) <= 28
GROUP BY [Order_ID]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 10:04:35

quote:
Originally posted by PeterNeo

Hi,

Try this

SELECT [Order_ID], COUNT([EndDate])
FROM Orders
WHERE DATEDIFF(DAY, [StartDate], [EndDate]) <= 28
GROUP BY [Order_ID]

OP specified

I want to count all orders, start date < 28 days of last end Date.

Madhivanan

Failing to plan is Planning to fail

Husman
Starting Member

13 Posts

Posted - 2007-09-21 : 10:10:31

None of the above querys worked

Madhivanan query answer was 8 for order 1 and 6 for order 2

PeterNeo query answer was 4 for oder 1 and 2 for order 2

Answer I was looking 2 for order 1 and 1 for order 2

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-21 : 23:24:50

Husman,

You really need to check your data... the last EndDate is 2007-08-25 00:00:00.000

28 days less than that is 2007-07-28 00:00:00.000

Since all of the StartDates in the data you provided are less than that, the expected row counts you posted are wrong... all the rows qualify for the criteria you asked for.

--Jeff Moden

Husman
Starting Member

13 Posts

Posted - 2007-09-22 : 03:03:31

Jeff,

Above data is right

I want to count days between Last End Date and Next Start Date.
Example
Orderid StartDate EndDate
1 2007-01-01 2007-01-28
1 2007-02-02 2007-03-03(Last Enddate 28/01/2007 next startdate 02/02/2007)within 28 days
1 2007-06-03 2007-06-25 (Last Enddate 03/03/2007 next startdate 02/06/2007)not within 28 days

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 03:39:36

"orderid 1 have 4 orders( 2 were within 28 days)
orderid 2 have 1 order(1 was within 28 days)"

I can't get that result


Item        Order_ID    Start      End        Prev End   Diff        
----------- ----------- ---------- ---------- ---------- ----------- 
1           1           2007-01-01 2007-01-28 NULL       NULL
2           1           2007-02-02 2007-03-03 2007-01-28 5
3           1           2007-04-01 2007-04-25 2007-03-03 29
4           1           2007-06-03 2007-06-25 2007-04-25 39
5           2           2007-04-01 2007-04-25 NULL       NULL
6           2           2007-05-28 2007-06-25 2007-04-25 33
7           2           2007-06-29 2007-08-25 2007-06-25 4

You: orderid 1 have 4 orders( 2 were within 28 days)
Me: orderid 1 have 4 orders( 1 were within 28 days) Item 2

You: orderid 2 have 1 order(1 was within 28 days)
Me: orderid 2 have 3 order(1 was within 28 days) Item 7

perhaps you can have another go at explaining what result you want so we all get it!

Kristen

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-22 : 10:43:58

Heck... you should have said "last End Date PER CUSTOMER PER ROW" in the first place!

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-22 : 10:53:23

Heh... I see 3 orders for Order_ID = 2

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-22 : 16:33:44

I agree with Kristen... you have a strange idea of how many days there are between dates...

Order_ID	PrevEndDate	StartDate	        EndDate	                DaysAfter
1	2007-01-28 00:00:00	2007-02-02 00:00:00	2007-03-03 00:00:00	5
1	2007-03-03 00:00:00	2007-04-01 00:00:00	2007-04-25 00:00:00	29
1	2007-04-25 00:00:00	2007-06-03 00:00:00	2007-06-25 00:00:00	39
2	2007-04-25 00:00:00	2007-05-28 00:00:00	2007-06-25 00:00:00	33
2	2007-06-25 00:00:00	2007-06-29 00:00:00	2007-08-25 00:00:00	4

Please tell us what YOUR formula for calculating days between dates is, hmmmm?

--Jeff Moden

Husman
Starting Member

13 Posts

Posted - 2007-09-22 : 16:57:23

Kristen,

Your result is right.
orderid 1 have 4 orders( 1 were within 28 days) Item 2
orderid 2 have 2 order(1 was within 28 days) Item 7

I really like the structure of the answer above. Can you please forward the formula for this structure.

Kind Regards

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:32:45

"Heh... I see 3 orders for Order_ID = 2"

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:37:09

I didn't create a formula, I just tabulated your data, but if it helps you are welcome to it:


CREATE TABLE #OrdersOrig
(Order_ID int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime DEFAULT NULL
)

CREATE TABLE #Orders
(
	Seq	int identity(1,1) NOT NULL,
	Order_ID int NOT NULL,
	StartDate datetime NOT NULL,
	EndDate datetime DEFAULT NULL
)

INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-01-01 00:00:00.000','2007-01-28 00:00:00.000')
INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-02-02 00:00:00.000','2007-03-03 00:00:00.000')
INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-04-01 00:00:00.000','2007-04-25 00:00:00.000')
INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(1,'2007-06-03 00:00:00.000','2007-06-25 00:00:00.000')
INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(2,'2007-04-01 00:00:00.000','2007-04-25 00:00:00.000')
INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(2,'2007-05-28 00:00:00.000','2007-06-25 00:00:00.000')
INSERT INTO [#OrdersOrig]([Order_ID], [StartDate], [EndDate])
VALUES(2,'2007-06-29 00:00:00.000','2007-08-25 00:00:00.000') 

SELECT	Order_ID, StartDate, EndDate
FROM	#OrdersOrig
ORDER BY Order_ID, EndDate, StartDate

INSERT INTO #Orders(Order_ID, StartDate, EndDate)
SELECT	Order_ID, StartDate, EndDate
FROM	#OrdersOrig
ORDER BY EndDate, Order_ID, StartDate

SELECT	[Item] = O1.Seq, O1.Order_ID, 
	[Start] = CONVERT(varchar(10), O1.StartDate, 121), 
	[End] = CONVERT(varchar(10), O1.EndDate, 121), 
	[Prev End] = CONVERT(varchar(10), O3.EndDate, 121), 
	[Diff] = DATEDIFF(Day, O3.EndDate, O1.StartDate)
FROM	#Orders AS O1
	LEFT OUTER JOIN #Orders AS O3	-- Previous EndDate
		 ON O3.Seq = O1.Seq - 1
ORDER BY O1.Order_ID, O1.Seq

DROP TABLE #OrdersOrig
GO
DROP TABLE #Orders
GO

Please put some effort into a clear description of your problem, data, and expected results next time. Otherwise folk here, who give up their time for free, waste time wehn they could be helping others.

Kristen

Husman
Starting Member

13 Posts

Posted - 2007-10-02 : 12:24:06

thanx Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources