Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Query #2 - MySQL and MS-SQL
Message
From
28/02/2008 01:34:33
Walter Meester
HoogkarspelNetherlands
 
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
01296743
Message ID:
01297282
Views:
29
Kevin,

>
>; WITH DateCTE (WeekEnding)  AS
>     -- Anchor query to get the first date
>     (SELECT @StartDate AS WeekEnding
>       UNION ALL
>          -- Recursive query to grab the rest of the dates
>          SELECT DateAdd(day,7,WeekEnding) AS WeekEnding FROM DateCTE
>             WHERE WeekEnding < @EndDate ),
>
>       -- 2nd CTE to grab the list of Vendors
>       VendorListCTE (VendorID)   AS
>         (SELECT VendorID FROM Purchasing.PurchaseOrderHeader
>             GROUP BY VendorID)
>
Thanks in providing a possible implementation using recursivism.

However it suprises me a bit that you would prefer this over the simple approach of using a temp table. I would not consider this alternative if it were only for the reason that it is it is less readable of what is going on (A common problem with CTE's anyways).

OTOH, it is a good example of how to impress other people not familiar with CTEs and implementing a simple problem in a difficult way. (TEST: give the two solutions to any SQL developer and ask them to figure out what is going on).
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform