newbie27
asked on
SQL syntax - need some more help
Hello,
I am still struggling to get the results correct for all time sales. Please can someone still look into my question?
The query is in my previous question
Thanks
I am still struggling to get the results correct for all time sales. Please can someone still look into my question?
The query is in my previous question
Thanks
ASKER
Ok thanks.
I have to remove this condition to get all the order history
-- AND
-- (CAT_Orders.OrderIncludeIn WeeklySumm ary is Null)
Currently, I am getting the attached resutls, which is repeating the product name .. it looks like the sale count is correct.
Please advise if I can group it by product name?
Thanks
I have to remove this condition to get all the order history
-- AND
-- (CAT_Orders.OrderIncludeIn
Currently, I am getting the attached resutls, which is repeating the product name .. it looks like the sale count is correct.
Please advise if I can group it by product name?
Thanks
2010-11-22 11:19:33.050 6002 Cupcake Kit 1 1
2010-11-24 16:54:50.567 6007 Cinderella Theatre 1 6
2010-11-25 21:05:33.067 6008 Astronaut Ice Cream 1 22
2010-11-25 21:05:33.067 6008 Guinness World Records YoYo Set 1 2
2010-11-25 21:05:33.067 6008 Guinness World Records Playing Card Set 1 1
2010-11-25 21:05:33.067 6008 Guinness World Records Paper Plane Set 1 1
2010-11-30 11:11:40.173 6010 Film Quote Babygrow - The Force is Strong in This One 1 11
2010-12-01 09:30:52.060 6020 Wooden Acrobatic Monkey 1 2
2010-12-09 18:09:46.757 6039 Fisher Space Pen Bullet 1 1
2010-12-09 18:09:46.757 6039 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2010-12-09 22:26:37.457 6040 Royal Botanical Candles 1 4
2010-12-09 22:26:37.457 6040 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2010-12-09 22:26:37.457 6040 Football Ground Street Sign 1 2
2010-12-10 21:22:42.493 6044 Retro Fisher Price Toys - Record Player 1 6
2010-12-11 14:35:42.590 6045 Retro Fisher Price Toys - Record Player 2 6
2010-12-12 21:31:26.003 6047 Vineyard Tour Wine Tasting & Lunch for Two 1 2
2010-12-12 22:33:23.670 6048 Angel Chimes 1 2
2011-01-04 10:48:46.000 6050 Football Ground Street Sign 1 2
2010-12-14 20:39:41.597 6051 Retro Weather Fridge Magnets 1 6
2010-12-14 22:33:09.113 6052 Terracotta Piggy Bank 1 2
2010-12-14 23:37:27.000 6053 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2010-12-15 12:25:25.763 6054 Snakes & Ladders and Ludo Retro Board Game 1 1
2010-12-16 12:49:29.057 6058 Flying Hats Retro Board Game 1 2
2010-12-18 13:01:08.460 6062 Retro Fisher Price Toys - Record Player 1 6
2010-12-18 14:59:50.023 6063 The Most Amazing Places on Britain's Coast Book 1 1
2010-12-19 00:21:25.870 6065 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2010-12-20 22:46:54.660 6070 Retro Fisher Price Toys - Record Player 1 6
2010-12-21 16:54:26.257 6073 Retro Fisher Price Toys - Record Player 1 6
2010-12-22 21:07:34.747 6074 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2010-12-22 22:40:46.447 6075 Film Quote Babygrow - Nobody Puts Baby in the Corner 2 30
2011-01-04 10:38:43.000 6077 Retro Weather Fridge Magnets 2 6
2010-12-24 14:51:42.897 6079 Terracotta Piggy Bank 1 2
2010-12-26 08:17:31.020 6084 Grow It 1 5
2011-01-02 12:22:09.993 6108 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-01-08 19:04:27.637 6119 Silver Loveheart Necklace 1 2
2011-01-12 11:22:06.270 6121 Blow Football Retro Board Game 1 2
2011-01-16 16:55:30.513 6124 Retro Fisher Price Toys - Two Tune Television 1 1
2011-01-16 16:55:30.513 6124 Retro Fisher Price Toys - Teaching Clock 1 1
2011-01-17 16:15:12.000 6125 Haynes The Dad's Manual Book 1 1
2011-01-17 16:15:12.000 6125 Paper Yachts 1 1
2011-01-17 16:15:12.000 6125 Personalised Ladybird Fairy Tales 1 4
2011-01-20 15:59:08.000 6127 Wooden Acrobatic Monkey 1 2
2011-01-28 09:22:08.313 6130 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-01 09:25:28.463 6132 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-06 16:41:14.133 6138 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-08 22:43:16.520 6140 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-08 22:43:16.520 6140 Film Quote Babygrow - The Force is Strong in This One 1 11
2011-02-08 22:43:16.520 6140 Film Quote Babygrow - The Force is Strong in This One 1 11
2011-02-09 16:42:27.230 6141 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-09 16:42:27.230 6141 Film Quote Babygrow - The Force is Strong in This One 1 11
2011-02-12 19:29:39.470 6143 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-17 17:43:46.370 6144 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-17 17:43:46.370 6144 Film Quote Babygrow - The Force is Strong in This One 1 11
2011-02-18 17:20:18.953 6145 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-02-19 09:48:54.247 6147 Film Quote Babygrow - The Force is Strong in This One 1 11
2011-02-25 22:28:57.773 6149 The Stamp Collection Bone China Mugs 1 1
2011-02-28 22:15:55.627 6150 Film Quote Babygrow - Nobody Puts Baby in the Corner 2 30
2011-03-01 00:13:05.460 6151 Film Quote Babygrow - Nobody Puts Baby in the Corner 1 30
2011-03-08 10:20:46.007 6154 Film Quote Babygrow - The Force is Strong in This One 2 11
2011-03-08 10:20:46.007 6154 Balloon Powered Train 1 2
It is repeating the products because they are on different orders.
ASKER
Oh okay.
Does that mean I cannot get the weekly and cumulative sales for the orders?
Please advise
Thanks
Does that mean I cannot get the weekly and cumulative sales for the orders?
Please advise
Thanks
You cannot get the order detail and the summary in the same report without duplicating the products. I assume you want to see individual orders. If not, yes you can get weekly and cumulative sales grouped by product on the same report. This would be a much different query though.
ASKER
Yes, I was triying to include another column to show cumulative sales for each product
I want to show the all time sale for a given product in the same report as I am currenlty using...
Is there no way we can just add a column and do not duplicate the product name and show only once for each product?
Please advise
Thanks
I want to show the all time sale for a given product in the same report as I am currenlty using...
Is there no way we can just add a column and do not duplicate the product name and show only once for each product?
Please advise
Thanks
ALTER VIEW [dbo].[viewWeeklySalesReport] AS
SELECT TOP (100) PERCENT
Datepart(DW,CAT_Orders.OrderDate) as DayofWeek,
Datepart(D,CAT_Orders.OrderDate) as Day,
Datepart(M,CAT_Orders.OrderDate) as Month,
Year(CAT_Orders.OrderDate) as Year,
CAT_Orders.OrderDate,
CAT_ShipTo.FirstName,
CAT_ShipTo.LastName,
CAT_Orders.OrderID,
CAT_Products.ProductName,
SUBSTRING(CAT_OrderDetails.Options, (LEN(CAT_OrderDetails.Options ) + 2 )- CHARINDEX(':',REVERSE(CAT_OrderDetails.Options )), LEN(CAT_OrderDetails.Options )) as Options,
CAT_OrderDetails.Quantity,
(SELECT SUM(od.Quantity) FROM CAT_OrderDetails od WHERE od.ProductId = CAT_OrderDetails.ProductId) as Cumulative,
CASE CAT_Products.TaxExempt
WHEN 1 Then 'No'
Else 'Yes'
End 'TaxExempt',
CAT_OrderDetails.UnitCost ,
CASE CAT_Products.TaxExempt
WHEN 0 THEN (CAT_OrderDetails.UnitCost/1.175)
ELSE (CAT_OrderDetails.UnitCost )
END as 'NetProductCost' ,
CASE CAT_Products.TaxExempt
WHEN 0 THEN (CAT_OrderDetails.UnitCost -(CAT_OrderDetails.UnitCost/1.175))
ELSE (CAT_OrderDetails.UnitCost -(CAT_OrderDetails.UnitCost))
END as 'VatCost' ,
CAT_Orders.ShipCosts,
(CAT_Orders.ShipCosts/1.175) as 'ShippingNet',
((CAT_Orders.ShipCosts/1.175) * 0.175) as 'ShippingVAt',
((CAT_OrderDetails.UnitCost * CAT_OrderDetails.Quantity) + CAT_Orders.ShipCosts ) as TotalCost
FROM CAT_Orders
INNER JOIN CAT_OrderDetails ON CAT_Orders.OrderID = CAT_OrderDetails.OrderID
INNER JOIN CAT_Products ON CAT_OrderDetails.ProductId = CAT_Products.ProductId
Inner JOIN CAT_ShipTo ON CAT_Orders.CustomerId = CAT_ShipTo.UserId
WHERE ((CAT_Orders.PaymentState='1_paid') or (CAT_Orders.PaymentState='ORDER_CREATED'))
AND
(CAT_Orders.OrderIncludeInWeeklySummary is Null)
ORDER BY CAT_OrderDetails.OrderDetailsId
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
fixed with slightly different conditions
Open in new window