Fetch Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Fetch Query

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-04-09 : 02:11:00

Hi

I have table sales. In sales table columns contains
Auto_Numer
Order_number
Product_Name
Order_amount
Order_date

One product is having more than one orders (order_numer )

I will pass three parameters

@Threshold_Amount = 1000
@Start_Date = 01/01/2009
@End_Date = 31/12/2009

I need the query to fetch the Auto_Number and products, the order_amount should be more than 1000(Threshold_Amount) and the order_date between 01/01/2009 and 31/12/2009.

Please help on this…

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 02:17:21

[code]
select Auto_Number, Product_Name
from sales
where Order_date >= @Start_Date
and Order_Date <= @End_Date
and Order_amount > @Threshold_Amount
[/code]

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 02:19:57

Try this:

Declare @Threshold_Amount int,
@Start_Date datetime,
@End_Date datetime

set @Threshold_Amount = 1000
set @Start_Date = 01/01/2009
set @End_Date = 31/12/2009

Select Auto_number, Product_name, Order_amount from
SalesTable
where Order_amount > @Threshold_Amount
and order_date between @Start_Date and @End_Date

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-04-09 : 02:25:18

Hi khtan

Is possible the product having more than one orders
So i need to sum(Amount) >= 1000 group by product

Example


CREATE TABLE #TEMP_TEST(
ID INT IDENTITY(1,1),
ORDER_NUMBER INT,
PRODUCT VARCHAR(50),
AMOUNT FLOAT,
ORDER_DATE DATETIME
)

INSERT INTO #TEMP_TEST
SELECT 240135, 'TEST',500,GETDATE()			UNION ALL
SELECT 240136, 'TEST',500,GETDATE()-1		UNION ALL
SELECT 240167, 'ORIGIN',950,GETDATE()+1	UNION ALL
SELECT 140268, 'LESS',900,GETDATE()-5		UNION ALL		
SELECT 213212, 'GREATER',300,GETDATE()+6	UNION ALL
SELECT 213123, 'GREATER',700,GETDATE()+10

set @Threshold_Amount = 1000
set @Start_Date = 2010-04-01
set @End_Date = 2010-04-31

Expected Output is

1	240135	test	500	2010-04-09 11:47:35.480
2	240136	test	500	2010-04-08 11:47:35.480
5	213212	greater	300	2010-04-15 11:47:35.480
6	213123	greater	700	2010-04-19 11:47:35.480

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 02:30:02

Try this:

Select Order_number, Product, Amount from #TEMP_TEST
Where Product in (
Select Product from #TEMP_TEST
group by product
having sum(amount) >= 1000)

Regards,
Bohra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 02:30:44

[code]
select t.ID, t.ORDER_NUMBER, t.PRODUCT, t.AMOUNT, t.ORDER_DATE
from #TEMP_TEST t
inner join
(
select PRODUCT
from #TEMP_TEST
group by PRODUCT
having sum(AMOUNT) >= @Threshold_Amount
) p on t.PRODUCT = p.PRODUCT
where ORDER_DATE >= @Start_Date
and ORDER_DATE <= @End_Date
[/code]

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

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-04-09 : 02:34:24

Thanks a Lot Khtan & pk_bohra

pk_bohra I did the same thing what u have done. But am looking different method.

Thanks

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-04-09 : 02:38:42

Hi Khtan

If i use your method i will get error like this
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If i use below one its working fine..


select	t.ID, t.ORDER_NUMBER, t.PRODUCT, t.AMOUNT, t.ORDER_DATE
from	#TEMP_TEST t
	inner join
	(
		select	PRODUCT
		from	#TEMP_TEST
		group by PRODUCT
		having	sum(AMOUNT) >= 1000
	) p	on	t.PRODUCT	= p.PRODUCT
where	ORDER_DATE	 BETWEEN '2010-04-01' AND '2010-04-30'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 02:53:17

quote:
If i use your method i will get error like this
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

quote:
Originally posted by WoodHouse
set @Threshold_Amount = 1000
set @Start_Date = '2010-04-01'
set @End_Date = '2010-04-31 30'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources