query the first day of each month

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

query the first day of each month

Author

Topic

rekon32
Starting Member

16 Posts

Posted - 2013-08-08 : 00:22:29

Is my query below the most efficient way to query the first day of each month? Thank you in advance.



SELECT base_rate, doc_pt, chgdate 
FROM producthistory
	WHERE chgdate IN
		(
		SELECT MIN(chgdate)
		FROM ProductHistory
		WHERE prod_id =1199812
		and base_rate > 0
		GROUP BY  MONTH(chgdate), YEAR(chgdate)
		)
AND prod_id = 1199812
ORDER BY chgdate DESC

Data Analyst

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 00:57:21

if you're sure that table producthistory will have a record for beginning date of every month you can do this



SELECT base_rate, doc_pt, chgdate 
FROM producthistory
WHERE chgdate = DATEADD(mm,DATEDIFF(mm,0,chgdate),0)
AND prod_id = 1199812
ORDER BY chgdate DESC

If your attempt is get data for product at the earliest available date in month then use like


SELECT base_rate, doc_pt, chgdate
FROM
(
SELECT base_rate, doc_pt, chgdate ,MIN(chgdate) OVER (PARTITION BY  DATEADD(mm,DATEDIFF(mm,0,chgdate),0)) AS MinMonthDate
FROM producthistory
WHERE prod_id = 1199812
)t
WHERE chgdate = MinMonthDate
ORDER BY chgdate DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rekon32
Starting Member

16 Posts

Posted - 2013-08-08 : 01:16:00

Perfect, thank you!

Data Analyst

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 01:18:40

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-08 : 07:25:27

Visakh, wouldn't this be enough?

~~MIN(chgdate) OVER (PARTITION BY DATEADD(mm,DATEDIFF(mm,0,chgdate),0))~~

MIN(ChgDate) OVER (PARTITION BY DATEDIFF(MONTH, '19000101', ChgDate))

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 07:52:13

yep should be enough

I modified it from select query hence missed taking out DATEADD part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources