Link to home
Start Free TrialLog in
Avatar of newbie27
newbie27Flag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of zane_o
zane_o
Flag of United States of America image

I'm attaching the code here so people don't have to dig for it. This was the code you accepted previously. What results are you getting with this new query?
SELECT CAT_Orders.OrderDate,      
        CAT_Orders.OrderID,     CAT_Products.ProductName,                                CAT_OrderDetails.Quantity,                      
(SELECT SUM(od.Quantity) FROM CAT_OrderDetails od WHERE od.ProductId = CAT_OrderDetails.ProductId) as Cumulative 
 
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 
          
 
WHERE     (CAT_Orders.PaymentState='1_paid')  
                        AND 
          (CAT_Orders.OrderIncludeInWeeklySummary is Null)

Open in new window

Avatar of newbie27

ASKER

Ok thanks.

I have to remove this condition to get all the order history

--  AND
--  (CAT_Orders.OrderIncludeInWeeklySummary 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
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

Open in new window

It is repeating the products because they are on different orders.  
Oh okay.

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.
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
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

Open in new window

SOLUTION
Avatar of zane_o
zane_o
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
fixed with slightly different conditions