SQLTeam.com | Weblogs | Forums

How to solve these two problems?

Customers(Customer_ID, Customer_Name)

Orders(OrderID, Customer_ID, Amount, Order_Date).

--Write Query was to find Names of the top 5 customers
by Amount with total amount more than 1000 USD for the last 30 days.

Order(Oid,OCity,OAmount,ODate,Cid,CName)

Give me TotalOrder Count for each City for each Month for the City only starting with C and Total Order Count is moer than 4.

hi

i have created sample data and the first query

sample data script

drop Table #Customers
create table #Customers(Customer_ID int, Customer_Name varchar(100))
insert into #Customers select 1,'Annie'
insert into #Customers select 2,'Beth'
insert into #Customers select 3,'Kevin'
insert into #Customers select 4,'Tom'
insert into #Customers select 5,'Harry'
insert into #Customers select 6,'Axel'
insert into #Customers select 7,'Foley'
insert into #Customers select 8,'Tim'
insert into #Customers select 9,'Susan'
insert into #Customers select 10,'Rhonda'

select * from #Customers

drop Table #Orders
create table #Orders(OrderID int, Customer_ID int, Amount decimal(10,2), Order_Date Date)

insert into #Orders select 123,1,234.50,'2022-10-25'
insert into #Orders select 124,1,500.67,'2022-10-22'
insert into #Orders select 125,1,800.67,'2022-10-21'

insert into #Orders select 300,2,234.50,'2022-10-25'
insert into #Orders select 301,2,500.67,'2022-10-22'
insert into #Orders select 302,2,800.67,'2022-09-21'

insert into #Orders select 300,3,678.50,'2022-10-25'
insert into #Orders select 301,3,500.67,'2022-10-22'
insert into #Orders select 303,3,800.67,'2022-09-21'

insert into #Orders select 300,4,234.50,'2022-10-25'
insert into #Orders select 301,4,500.67,'2022-10-22'
insert into #Orders select 304,4,1000.67,'2022-09-21'

insert into #Orders select 300,5,234.50,'2022-10-25'
insert into #Orders select 301,5,500.67,'2022-10-22'
insert into #Orders select 305,5,800.67,'2022-09-21'

insert into #Orders select 300,6,234.50,'2022-10-25'
insert into #Orders select 301,6,500.67,'2022-10-22'
insert into #Orders select 306,6,800.67,'2022-10-21'

insert into #Orders select 300,7,234.50,'2022-10-25'
insert into #Orders select 301,7,500.67,'2022-10-22'
insert into #Orders select 307,7,800.67,'2022-09-21'

insert into #Orders select 300,8,678.50,'2022-10-25'
insert into #Orders select 301,8,500.67,'2022-10-22'
insert into #Orders select 308,8,800.67,'2022-09-21'

insert into #Orders select 300,9,234.50,'2022-10-25'
insert into #Orders select 301,9,500.67,'2022-10-22'
insert into #Orders select 309,9,800.67,'2022-09-21'

insert into #Orders select 300,10,234.50,'2022-10-25'
insert into #Orders select 301,10,890.67,'2022-10-22'
insert into #Orders select 3010,10,800.67,'2022-09-21'

select * from #Orders

select 
   top 5 Customer_Name,sum(amount) 
from 
  ( select  
       a.Customer_ID 
	   , a.Customer_Name 
	   , b.OrderID 
	   , b.Amount 
	   , b.Order_Date 
    from  
	   #Customers a join #Orders b 
	        on a.Customer_ID = b.Customer_ID 
 	where 
	   datediff(dd,order_date,getdate()) <= 30 ) a
group by 
   Customer_Name
having 
   sum(amount) > 1000

image