Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

Babaksql
Starting Member

32 Posts

Posted - 2008-11-06 : 00:55:24

Hi,
I have a table that consists the sales information of different items. Could you please tell me how can I use the SELECT statement to retrieve the first and the last sale records of particular items such as X and Y during a given month.

Thanks for your attention.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-06 : 01:41:59

something like


select t1.* from your_table as t1 inner join
(
	select item,min(date) as min_date, max(date) as max_date from your_table
	group by item
) as t2 on t1.item=t2.item and (t1.date=t2.min_date or t1.date=t2.max_date)
where item='some value' and t1.date='some date'

Madhivanan

Failing to plan is Planning to fail

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-06 : 01:46:39

try this

SELECT Item, MIN(ItemId) AS 'MinId', MAX(ItemId) AS 'maxId'
FROM SalesItems 
WHERE Item IN ('X', 'Y')
GROUP BY Item, MONTH(Date), YEAR(Date)

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 02:37:01

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Item ORDER BY date) AS Seq1,
ROW_NUMBER() OVER (PARTITION BY Item ORDER BY date DESC) AS Seq2,*
FROM Table
)t
WHERE Seq1=1 OR Seq2=1[/code]

Babaksql
Starting Member

32 Posts

Posted - 2008-11-06 : 02:56:52

I understand Madhivana comment and I tried it. But it didn't gave me any row. Please let me clarify the issue by the following example. Suppose I have this sales table

ItemID Date(dd/mm/yyyy) SalePrice
------ --------------- ---------
A 01/01/2001 $100
A 05/01/2001 $101
A 27/01/2001 $120
A 04/02/2001 $100
A 10/02/2001 $90
A 24/02/2001 $90
A 28/02/2001 $100
B 01/01/2001 $50
B 03/01/2001 $40
B 05/02/2001 $50
.
.
.

I want to have the first and last sales of each item for each month during the period of 01/01/2001 to 01/yy/2001. So the result should be as follows:

ItemID Date SalePrice
------ ---- ---------
A 01/01/2001 $100
A 27/01/2001 $120
A 04/02/2001 $100
A 28/02/2001 $100
B 01/01/2001 $50
B 03/01/2001 $40
B 05/02/2001 $50

Could you please help me to find the solution. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 03:04:06

use this then

declare @test table
(
ItemID char(1),
 Date datetime,--(dd/mm/yyyy) 
SalePrice money
)
------ --------------- ---------

insert into @test
select 'A', '01/01/2001', '$100' union all
select 'A', '05/01/2001', '$101' union all
select 'A' ,'27/01/2001', '$120' union all
select 'A', '04/02/2001', '$100' union all
select 'A', '10/02/2001', '$90' union all
select 'A', '24/02/2001', '$90' union all
select 'A', '28/02/2001', '$100' union all
select 'B', '01/01/2001', '$50' union all
select 'B', '03/01/2001', '$40' union all
select 'B', '05/02/2001', '$50' 


select ItemID,Date,SalePrice
from (select row_number() over (partition by ItemID,month(Date) order by date) as seq1,
row_number() over (partition by ItemID,month(Date) order by date desc) as seq2,*
from @test
)t
where seq1=1 or seq2=1
order by ItemID,Date

output
----------------------------------------

ItemID	Date	                SalePrice
A	2001-01-01 00:00:00.000	100.00
A	2001-01-27 00:00:00.000	120.00
A	2001-02-04 00:00:00.000	100.00
A	2001-02-28 00:00:00.000	100.00
B	2001-01-01 00:00:00.000	50.00
B	2001-01-03 00:00:00.000	40.00
B	2001-02-05 00:00:00.000	50.00

Babaksql
Starting Member

32 Posts

Posted - 2008-11-06 : 03:32:58

sorry visakh16, but it doesn't give me the correct result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 04:08:40

why? whats the problem? isnt it giving output what you posted?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 04:54:40

Just in case the original data spans several years...

-- Prepare sample data
DECLARE @Sample TABLE
	(
		ItemID CHAR(1),
		Date DATETIME,
		SalePrice MONEY
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	'A', '01/01/2001', '$100' UNION ALL
SELECT	'A', '05/01/2001', '$101' UNION ALL
SELECT	'A' ,'27/01/2001', '$120' UNION ALL
SELECT	'A', '04/02/2001', '$100' UNION ALL
SELECT	'A', '10/02/2001',  '$90' UNION ALL
SELECT	'A', '24/02/2001',  '$90' UNION ALL
SELECT	'A', '28/02/2001', '$100' UNION ALL
SELECT	'B', '01/01/2001',  '$50' UNION ALL
SELECT	'B', '03/01/2001',  '$40' UNION ALL
SELECT	'B', '05/02/2001',  '$50' 

-- Here is the solution
SELECT		ItemID,
		Date,
		SalePrice
FROM		(
			SELECT	ItemID,
				Date,
				SalePrice,
				ROW_NUMBER() OVER (PARTITION BY ItemID, DATEDIFF(MONTH, 0, Date) ORDER BY Date) AS recFirst,
				ROW_NUMBER() OVER (PARTITION BY ItemID, DATEDIFF(MONTH, 0, Date) ORDER BY Date DESC) AS recLast
			FROM	@Sample
		) AS d
WHERE		1 IN (recFirst, recLast)
ORDER BY	ItemID,
		Date

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 04:57:43

Or this, if you are not using proper datatype to store your dates

DECLARE @Sample TABLE
	(
		ItemID CHAR(1),
		Date CHAR(10),
		SalePrice MONEY
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	'A', '01/01/2001', '$100' UNION ALL
SELECT	'A', '05/01/2001', '$101' UNION ALL
SELECT	'A' ,'27/01/2001', '$120' UNION ALL
SELECT	'A', '04/02/2001', '$100' UNION ALL
SELECT	'A', '10/02/2001',  '$90' UNION ALL
SELECT	'A', '24/02/2001',  '$90' UNION ALL
SELECT	'A', '28/02/2001', '$100' UNION ALL
SELECT	'B', '01/01/2001',  '$50' UNION ALL
SELECT	'B', '03/01/2001',  '$40' UNION ALL
SELECT	'B', '05/02/2001',  '$50' 

SELECT		ItemID,
		Date,
		SalePrice
FROM		(
			SELECT	ItemID,
				Date,
				SalePrice,
				ROW_NUMBER() OVER (PARTITION BY ItemID, DATEDIFF(MONTH, 0, CAST(Date AS DATETIME)) ORDER BY Date) AS recFirst,
				ROW_NUMBER() OVER (PARTITION BY ItemID, DATEDIFF(MONTH, 0, CAST(Date AS DATETIME)) ORDER BY Date DESC) AS recLast
			FROM	@Sample
		) AS d
WHERE		1 IN (recFirst, recLast)
ORDER BY	ItemID,
		CAST(Date AS DATETIME)

E 12°55'05.63"
N 56°04'39.26"

Babaksql
Starting Member

32 Posts

Posted - 2008-11-06 : 20:05:52

Thanks a lot Peso, your solution works great.

Babaksql
Starting Member

32 Posts

Posted - 2008-11-09 : 21:17:15

Hi, I have another question.

Is it possible to have an arithmatic operation in this process to calculate the multiplication of all the sales prices in a month. I mean multiplication of all the sales prices of each itemID between the month that is presented by the recFirst and recLast in Peso's solution. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 23:13:17

GROUP BY MONTH(Date) and apply reqd aggregation over SalePrice column. are you sure you need muliplication and not SUM?

Babaksql
Starting Member

32 Posts

Posted - 2008-11-09 : 23:27:22

Yes Visakh16, I want multiplication. I know that multiplication does not make sense for sale price. In fact, the real table is another financial table. I just made this example for simplicity.

Could you please make your solution clearer? What is the function for aggregate multiplication? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 02:09:38

SUM(ItemsInStock * SalesPrice)

E 12°55'05.63"
N 56°04'39.26"

Babaksql
Starting Member

32 Posts

Posted - 2008-11-10 : 03:07:14

Hi Peso, the real data field for aggregate multiplication is something that is not related to any other fields.
Your solution works great and it gives me the first record (recFirst) and the last record (recLast) of each month. I want to add another calculation to give me the the aggregate multiplication of a particular field such as FieldXYZ during each month that starts by a recFirst and ends by a recLast.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 03:41:14

Please provide sample data to illustrate your scenario.

E 12°55'05.63"
N 56°04'39.26"

Babaksql
Starting Member

32 Posts

Posted - 2008-11-10 : 04:08:28

Suppose we have this table

ItemID Date SalePrice SaleFacor
------ ------- --------- ---------
A 01/01/2001 $100 4
A 05/01/2001 $101 5
A 27/01/2001 $120 2
A 04/02/2001 $100 3
A 10/02/2001 $90 2
A 24/02/2001 $90 2
A 28/02/2001 $100 4
B 01/01/2001 $50 1
B 03/01/2001 $40 5
B 05/02/2001 $50 3

I want to have the results as follows:

output
----------------------------------------

ItemID Date SalePrice SaleFactor
------ ----------- --------- -----------
A 01/01/2001 100 null
A 27/01/2001 120 4*5*2 = 40
A 04/02/2001 100 null
A 28/02/2001 100 3*2*2*4 = 48
B 01/01/2001 50   null
B 03/01/2001 40   1*5 = 5
B 05/02/2001 50   3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 09:51:28

[code]DECLARE @Sample TABLE
(
ItemID CHAR(1),
Date CHAR(10),
SalePrice MONEY,
SaleFactor INT
)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 'A', '01/01/2001', '$100', 4 UNION ALL
SELECT 'A', '05/01/2001', '$101', 5 UNION ALL
SELECT 'A' ,'27/01/2001', '$120', 2 UNION ALL
SELECT 'A', '04/02/2001', '$100', 3 UNION ALL
SELECT 'A', '10/02/2001', '$90', 2 UNION ALL
SELECT 'A', '24/02/2001', '$90', 2 UNION ALL
SELECT 'A', '28/02/2001', '$100', 4 UNION ALL
SELECT 'B', '01/01/2001', '$50', 1 UNION ALL
SELECT 'B', '03/01/2001', '$40', 5 UNION ALL
SELECT 'B', '05/02/2001', '$50', 3

SELECT d.ItemID,
d.Date,
d.SalePrice,
z.SaleFactor
FROM (
SELECT ItemID,
Date,
SalePrice,
ROW_NUMBER() OVER (PARTITION BY ItemID, DATEDIFF(MONTH, 0, CAST(Date AS DATETIME)) ORDER BY Date) AS recFirst,
ROW_NUMBER() OVER (PARTITION BY ItemID, DATEDIFF(MONTH, 0, CAST(Date AS DATETIME)) ORDER BY Date DESC) AS recLast
FROM @Sample
) AS d
LEFT JOIN (
SELECT ItemID,
MAX(Date) AS Date,
EXP(SUM(LOG(SaleFactor))) AS SaleFactor
FROM @Sample
GROUP BY ItemID,
DATEDIFF(MONTH, 0, Date)
) AS z ON z.ItemID = d.ItemID
AND z.Date = d.Date
WHERE 1 IN (d.recFirst, d.recLast)
ORDER BY d.ItemID,
CAST(d.Date AS DATETIME)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources