Very special request (according to me...)

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)

Very special request (according to me...)

Author

Topic

jeanfre777
Starting Member

2 Posts

Posted - 2010-05-02 : 10:13:41

Hi everyone, I'm quite new to sql and I have a special case that I can't resolve, I'll try to explain it in my own english, since I'm french Canadian.

Let's say I have a table which contains records of every special opening and closing hours for a store, for the next year to come. Some events are based on a one-day schedule, then other are 2, 3, on even more consecutive days (for example, hollydays will contain 3 records, one for december 23, one for december 24 and one for dec. 25). Every single row represents a special schedule for a day of the year.

I want to be able to show two weeks in advance (when the current date is two week before the first day of the event), the consecutive days or individual days representing an event. The problem comes when for exemple the current date is 2010-12-09(two weeks before the 2010-12-23), but i wan't the result to show the consecutive days of the 2010-12-23 which are not in the "two-weeks-before" range...

Let's see the content of the table:

storeID dayDate openingHour closingHour
1 2010-12-23 10:00 16:00
1 2010-12-24 11:00 14:00
1 2010-12-25 11:00 14:00
1 2010-06-24 9:00 16:00
1 2010-08-10 10:00 17:00

I want the result set to contain when the current date is for example 2010-12-09 (2 weeks before the 2010-12-23). I want to be able to get dec 24 and dec 25 even if they are not two weeks before the current date. :

storeID dayDate openingHour closingHour
1 2010-12-23 10:00 16:00
1 2010-12-24 11:00 14:00
1 2010-12-25 11:00 14:00

Please let me know if you have a solution.

THANKS

Jean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 10:30:49

do you mean this?


;With CTE AS
(
SELECT storeID, dayDate, openingHour, closingHour
FROM Table 
WHERE dayDate <= DATEADD(wk,2,GETDATE())

UNION ALL

SELECT t.storeID,t.dayDate,t.openingHour,t.closingHour
FROM Table t
OUTER APPLY (SELECT MAX(t.dayDate) AS Prv
             FROM CTE 
             WHERE storeID = t.storeID
             AND dayDate < t.dayDate
             )t1
WHERE t.dayDate < DATEADD(dd,1,t1.Prv)
)

SELECT storeID, dayDate, openingHour, closingHour
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jeanfre777
Starting Member

2 Posts

Posted - 2010-05-03 : 07:02:59

I tried this code and I got an error message :

Msg 4101, Level 15, State 1, Line 11
Aggregates on the right side of an APPLY cannot reference columns from the left side.

;With CTE AS
(
SELECT storeID, dayDate, openingHour, closingHour
FROM dbo.JourneeFeriee
WHERE dayDate <= DATEADD(wk,2,GETDATE())

UNION ALL

SELECT t.storeID,t.dayDate,t.openingHour,t.closingHour
FROM dbo.JourneeFeriee t
OUTER APPLY (SELECT MAX(t.dayDate) AS Prv
FROM CTE
WHERE storeID = t.storeID
AND dayDate < t.dayDate
)t1
WHERE t.dayDate < DATEADD(dd,1,t1.Prv)
)

SELECT storeID, dayDate, openingHour, closingHour
FROM CTE

OPTION (MAXRECURSION 0)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 09:12:11

Try this:

DECLARE @BaseDate DATETIME
SET @BaseDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
--SET @BaseDate = '2010-12-09' 

;With CTE AS
(
SELECT storeID, dayDate, openingHour, closingHour
FROM tableName
WHERE dayDate BETWEEN @BaseDate AND DATEADD(wk,2,@BaseDate)

UNION ALL

SELECT t.storeID, t.dayDate, t.openingHour, t.closingHour
FROM tableName t
INNER JOIN CTE
	ON t.storeID = CTE.storeID
	AND t.dayDate = CTE.dayDate + 1
)
SELECT storeID, dayDate, openingHour, closingHour
FROM CTE
GROUP BY storeID, dayDate, openingHour, closingHour
ORDER BY storeID, dayDate

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 09:49:38

quote:
Originally posted by jeanfre777

I tried this code and I got an error message :

Msg 4101, Level 15, State 1, Line 11
Aggregates on the right side of an APPLY cannot reference columns from the left side.

;With CTE AS
(
SELECT storeID, dayDate, openingHour, closingHour
FROM dbo.JourneeFeriee
WHERE dayDate <= DATEADD(wk,2,GETDATE())

UNION ALL

SELECT t.storeID,t.dayDate,t.openingHour,t.closingHour
FROM dbo.JourneeFeriee t
OUTER APPLY (SELECT MAX(t.dayDate) AS Prv
FROM CTE
WHERE storeID = t.storeID
AND dayDate < t.dayDate
)t1
WHERE t.dayDate < DATEADD(dd,1,t1.Prv)
)

SELECT storeID, dayDate, openingHour, closingHour
FROM CTE

OPTION (MAXRECURSION 0)

remove the t and then try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 11:05:31

quote:
Originally posted by visakh16
SELECT MAX(dayDate) AS Prv
FROM CTE

I don't think you can do that. Anyway, you shouldn't need aggregates, the solution I posted earlier seemed to work when I tested it.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 11:11:51

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16
SELECT MAX(dayDate) AS Prv
FROM CTE

I don't think you can do that. Anyway, you shouldn't need aggregates, the solution I posted earlier seemed to work when I tested it.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

you can do what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 11:31:18

quote:
Originally posted by visakh16
you can do what?

Combine aggregates and with recursive CTEs like that. I just tested your code, and it returned the following error:

Msg 467, Level 16, State 1, Line 28
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'CTE'.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 12:37:46

[code]
;With CTE AS
(
SELECT storeID, dayDate, openingHour, closingHour
FROM Table
WHERE dayDate <= DATEADD(wk,2,GETDATE())

UNION ALL

SELECT t.storeID,t.dayDate,t.openingHour,t.closingHour
FROM Table t
OUTER APPLY (SELECT TOP 1 t.dayDate AS Prv
FROM CTE
WHERE storeID = t.storeID
AND dayDate < t.dayDate
ORDER BY t.dayDate DESC
)t1
WHERE t.dayDate < DATEADD(dd,1,t1.Prv)
)

SELECT storeID, dayDate, openingHour, closingHour
FROM CTE

OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 17:32:21

quote:

Originally posted by visakh16


;With CTE AS
(
SELECT storeID, dayDate, openingHour, closingHour
FROM Table 
WHERE dayDate <= DATEADD(wk,2,GETDATE())

UNION ALL

SELECT t.storeID,t.dayDate,t.openingHour,t.closingHour
FROM Table t
OUTER APPLY (SELECT TOP 1 t.dayDate AS Prv
             FROM CTE 
             WHERE storeID = t.storeID
             AND dayDate < t.dayDate
             ORDER BY t.dayDate DESC
             )t1
WHERE t.dayDate < DATEADD(dd,1,t1.Prv)
)

SELECT storeID, dayDate, openingHour, closingHour
FROM CTE

OPTION (MAXRECURSION 0)

You can't do that either.

Msg 461, Level 16, State 1, Line 28
TOP operator is not allowed in the recursive part of a recursive common table expression 'CTE'.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources