need help writing queries for the urgent requireme

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

need help writing queries for the urgent requireme

Author

Topic

thalli.143
Starting Member

3 Posts

Posted - 2014-04-30 : 06:07:38

can you quickly help getting me queries. appreciated quick response. thank u

1. What are the total number of sales by customer in the last week of December 2013?

2. What are the total number of product sales returned in cases where it was not our fault?

3. Which are the top 4 customers based on 2013 sales, excluding returns?

Note: Do not use TOP or ORDER BY.

4. Show all transactions where a customer purchased less than in their prior order.

5. What are total sales broken down by customer type and year, with the year showing on

columns? Note: Do not use CASE statements.

==============================================

Customer

CustomerID CustomerName Customer Type
123 A Bike Store Value Added Reseller
456 Progressive Sports Specialty Bike Shop
789 Advanced Bike Components Warehouse
312 Modular Cycle Systems Value Added Reseller
654 Metropolitan Sports Supply Specialty Bike Shop
987 Aerobic Exercise Company Warehouse
111 Associated Bikes Value Added Reseller
222 Exemplary Cycles Specialty Bike Shop
333 Tandem Bicycle Store Warehouse
444 Rural Cycle Emporium Value Added Reseller

SalesReturnReason:
-------------------
TransactionID ReturnReason OurFault

109209 Customer Changed Mind N

165555 Wrong Color Sent Y

172138 Wrong Color Sent Y

172138 Defective Product Y

172138 Wrong Size Sent Y

109209 Wrong Color Sent Y

122522 Customer Misordered N

--------------------------------------------------------------

Sales:
-------------

TransactionID CustomerID ProductID TransacationTimestamp SaleAmount Returned

100299 456 9929 2013-04-28 06:59:42 PM $ 13,481.73 N

109209 789 3233 2013-05-13 02:34:53 AM $ 11,526.15 Y

110771 312 1232 2013-05-27 04:05:36 AM $ 14,101.58 N

119950 333 3009 2013-06-12 05:06:48 AM $ 17,050.27 N

122522 456 4393 2013-06-26 02:37:20 AM $ 8,805.04 N

126970 789 9929 2013-07-04 09:19:31 AM $ 1,715.56 N

128088 312 3233 2013-07-16 09:38:49 PM $ 8,756.72 N

135747 654 1232 2013-07-25 09:15:29 AM $ 7,077.05 N

139669 456 3009 2013-08-17 02:44:06 PM $ 18,460.22 N

148145 789 4393 2013-09-23 08:50:52 AM $ 18,291.02 N

154434 312 9929 2013-10-28 12:42:25 PM $ 3,894.81 N

160445 718 3233 2013-11-01 06:12:36 PM $ 11,150.80 N

165555 456 1232 2013-11-08 10:58:01 PM $ 13,887.52 Y

172138 789 3009 2013-12-05 07:29:13 AM $ 19,246.45 Y

174955 333 4393 2013-12-31 06:35:39 AM $ 16,663.11 N

180335 654 3009 2014-01-27 04:00:08 PM $ 4,326.34 N

184337 789 4393 2014-01-30 09:15:55 AM $ 4,695.96 N

pavitra

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-30 : 06:35:01

Homework?

sabinWeb MCP

thalli.143
Starting Member

3 Posts

Posted - 2014-04-30 : 06:43:55

no these are the queries which i am using for dashboard development...

fyi : i have shorten the details of fields here

pavitra

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-30 : 07:35:41

1.total No of Sales


SELECT 
			CustomerName
			,Count(S.CustomerID) as NoSales
	FROM
			Sales AS S
			INNER JOIN Customer AS C
			ON S.CustomerID=C.CustomerID
	WHERE 
			TransacationTimestamp>='20131229'
			AND TransacationTimestamp<'20140101'
	GROUP BY 
			S.CustomerID
			,CustomerName

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-30 : 07:42:18

--2. What are the total number of product sales returned in cases where it was not our fault?
Question : all the products or just distinct products


SELECT Count(ProductID) as NoProduct
	FROM
		SalesReturnReason SRR
		INNER JOIN Sales S
		ON SRR.TransactionID=S.TransactionID
	WHERE 
		SRR.OurFault='N'

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-30 : 07:54:34

--Which are the top 4 customers based on 2013 sales, excluding returns?
--Note: Do not use TOP or ORDER BY.

Question: Why not to use Top, Order By ? Fishy


	SELECT
		C.CustomerName
		FROM
		(
			SELECT 		
				S.CustomerID
				,ROW_NUMBER() OVER(Order by COUNT(S.CustomerID) DESC) as Rn
			FROM	
				Sales as S
				INNER JOIN Customer as C
				ON S.CustomerID=C.CustomerID
			WHERE
				S.Returned ='N'
				AND TransacationTimestamp >='20130101'
				AND TransacationTimestamp <'20140101'
			GROUP BY S.CustomerID
		)A
			INNER JOIN Customer C
			On A.CustomerID=C.CustomerID
	WHERE RN<=4

sabinWeb MCP

thalli.143
Starting Member

3 Posts

Posted - 2014-04-30 : 10:13:31

appreciated if some one responds with 4 and 5 queries

pavitra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-30 : 10:19:24

quote:
4. Show all transactions where a customer purchased less than in their prior order.

Show me what you have tried so far

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-30 : 10:19:27

quote:
5. What are total sales broken down by customer type and year, with the year showing on

columns? Note: Do not use CASE statements.

Show me what you have tried so far

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-04-30 : 10:26:34

quote:
Originally posted by stepson

--Which are the top 4 customers based on 2013 sales, excluding returns?
--Note: Do not use TOP or ORDER BY.

Question: Why not to use Top, Order By ? Fishy

Not fishy, just proof of homework.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-01 : 00:52:10

quote:
Originally posted by DonAtWork

quote:
Originally posted by stepson

--Which are the top 4 customers based on 2013 sales, excluding returns?
--Note: Do not use TOP or ORDER BY.

Question: Why not to use Top, Order By ? Fishy

Not fishy, just proof of homework.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Yes, at homeworks I was thinking about and I give up

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources