Customers with the total sales, who placed an order last year but not this year.

Question

Customers with the total sales, who placed an order last year but not this year.

nexus2vijay

SSC-Addicted

Points: 422
More actions
November 12, 2015 at 6:42 am

#309568

Hi Friends,
Attached the Schema of a DB with 4 Tables.
1.Query to get the customers with the total sales, who placed an order last year but not this year.
•Column List:CustomerName, [Total Sales]
•Sorting Order:[Total Sales] (desc)
2.Query to get the last year’s 10 best-selling products and 10 least-selling products with the total sales.
•Column List:ProductName, ProductNumber, [Total Sales]
•Sorting Order:[Total Sales] (desc)
3.Reduce Product’s ListPrice by 20% if it did not receive any order for the last two years and the reduced price would be still above StandardCost.
-Update ListPrice in Product.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ChrisM@Work (11/12/2015)
nexus2vijay (11/12/2015)
Hi Friends,
I have a table with 2 columns Version and CurrentDateTime
For every insert the Records should be Autoincrement for Version Column and Record Insertdatetime for that Version
1.0 | 2015-11-12 10:01.23..
2.0 | 2015-11-12 10.02.11..
............................
Is this another test or is it homework?
What have you tried so far?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246964 More actions · Answer 2

nexus2vijay (11/12/2015)
Hi Friends,
Attached the Schema of a DB with 4 Tables.
1.Query to get the customers with the total sales, who placed an order last year but not this year.
•Column List:CustomerName, [Total Sales]
•Sorting Order:[Total Sales] (desc)
2.Query to get the last year’s 10 best-selling products and 10 least-selling products with the total sales.
•Column List:ProductName, ProductNumber, [Total Sales]
•Sorting Order:[Total Sales] (desc)
3.Reduce Product’s ListPrice by 20% if it did not receive any order for the last two years and the reduced price would be still above StandardCost.
-Update ListPrice in Product.

Why do you keep posting questions which ask others to do everything for you?

We'll help with specific questions, as long as you keep your side of the bargain and demonstrate that you are trying and learning along the way.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MadAdmin SSChampion Points: 11261 More actions · Answer 3

Why do you keep posting questions which ask others to do everything for you?
We'll help with specific questions, as long as you keep your side of the bargain and demonstrate that you are trying and learning along the way.

Agreed!!!

A good way to ask a question is to say that you have a problem, and this is what you have tried.

Even better if you tried 2 different way and failed, showing us your initial draught, with test data and expected results, then I am sure many here will spend maximum time from their limited life helping with communicating a concept that may have been misunderstood.

Here is an excellent description of what you should do.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]