Solved

SQL syntax - need some more help

Posted on 2011-03-11
9
192 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:newbie27
  • 5
  • 4
9 Comments
 
LVL 6

Expert Comment

by:zane_o
ID: 35109366
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

0
 
LVL 8

Author Comment

by:newbie27
ID: 35109925
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

0
 
LVL 6

Expert Comment

by:zane_o
ID: 35110057
It is repeating the products because they are on different orders.  
0
 
LVL 8

Author Comment

by:newbie27
ID: 35110194
Oh okay.

Does that mean I cannot get the weekly and cumulative sales for the orders?

Please advise

Thanks
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:zane_o
ID: 35110356
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.
0
 
LVL 8

Author Comment

by:newbie27
ID: 35110593
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

0
 
LVL 6

Assisted Solution

by:zane_o
zane_o earned 100 total points
ID: 35110691
When you say in the same report, that means you want the order detail on the report. If you have order detail, you will have duplicate products as each order is unique. Grouping the products together would not show you what products were on individual orders.
0
 
LVL 8

Accepted Solution

by:
newbie27 earned 0 total points
ID: 35123183
Ok I see your point.

Please advise what minimum data we need to include in this report?

If we do not include the order detail then do you think we can fetch correct data?

Please advise

Thanks
0
 
LVL 8

Author Closing Comment

by:newbie27
ID: 35187236
fixed with slightly different conditions
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now