G2A Many GEOs
SHARE
TWEET

Untitled

a guest Apr 9th, 2020 162 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE  @order_date datetime, @number_of_days INT, @order_id INT, @ratio FLOAT, @min_sales int=5, @min_days int=14, @category_id int=1
  2.  
  3. DECLARE by_category cursor fast_forward FOR
  4. SELECT OrderID, OrderDate FROM Orders
  5.                      ORDER BY OrderDate ASC
  6. OPEN by_category
  7. fetch NEXT FROM by_category INTO @order_id, @order_date
  8. while @@fetch_status = 0
  9. BEGIN
  10.   DECLARE @is_true tinyint, @actual_date datetime
  11.   SET @is_true = 1
  12.   SET @number_of_days = 14
  13.   SET @ratio = 0.7
  14.  
  15.   while @is_true = 1
  16.   BEGIN
  17.     set @actual_date = DATEADD(DAY, @number_of_days, @order_date)
  18.     declare @all_items INT = 0, @beverage_items INT = 0
  19.     SELECT @all_items = COUNT(*) FROM Orders WHERE OrderDate >= @order_date AND OrderDate <= @actual_date
  20.     SELECT  @beverage_items = COUNT(Orders.OrderID) FROM Orders
  21.                                                   WHERE OrderDate >= @order_date AND
  22.                                                         OrderDate <= @actual_date AND
  23.                                                         Orders.OrderID IN (SELECT [ORDER Details].OrderID FROM [ORDER Details]
  24.                                                                                                                  JOIN Products P2 ON [ORDER Details].ProductID = P2.ProductID
  25.                                                                                                                  JOIN Categories C2 ON P2.CategoryID = C2.CategoryID
  26.                                                                           WHERE C2.CategoryID=@category_id)
  27.     IF @all_items >= @min_sales  AND CAST(@beverage_items AS FLOAT) / CAST(@all_items AS FLOAT) >= @ratio
  28.       BEGIN
  29.         SET @number_of_days += 1
  30.       END
  31.     ELSE
  32.       BEGIN
  33.         SET @is_true = 0
  34.         IF @number_of_days > @min_days
  35.           print('Időszak eleje: ' + CAST(@order_date AS VARCHAR(255)) + ', vége: ' +
  36.                                     CAST(@actual_date AS VARCHAR(255)) + ' napok száma: ' +
  37.                                     CAST(DATEDIFF(DAY, @order_date, @actual_date) AS VARCHAR(255)) + ', rendelések száma: ' +
  38.                                     CAST(@all_items AS VARCHAR(255)))
  39.       END
  40.   END
  41.   fetch NEXT FROM by_category INTO @order_id, @order_date
  42. END
  43. close by_category
  44. deallocate by_category
  45. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top