First of Month, End of Month - 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 2000 Forums

Transact-SQL (2000)

First of Month, End of Month

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-08 : 11:37:28

Based on the Current Date, I want to check a date in the where clause of my query to return only dates between the first of the month, and the last day of the month. (between 12-01-2003 00:00:00 and 12-31-2003 23:59:59)

Example today is 12/8

select ReceiptDate, PONumber, Amount

from PO

where ReceiptDate between x(first day of current month) and y (last day of current month.)

What is the most efficient way to do this?

Thanks,

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-12-08 : 11:42:41

How about

select ReceiptDate, PONumber, Amount
from PO
where DATEPART(mm, ReceiptDate) = DATEPART(mm, GETDATE())
AND DATEPART(yy, ReceiptDate) = DATEPART(yy, GETDATE())

?

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 11:46:40

This will return you the last day of the month...

You already know the first day....

DECLARE @dt datetime
SET @dt = GETDATE()

SELECT DAY(DATEADD(m, DATEDIFF(m, 0, @dt)+1, 0)-1)

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 12:08:35

To expand...


DECLARE @dt datetime, @s_dt datetime, @e_dt datetime, @x int
    SET @dt = GETDATE()

 SELECT    @x = DAY(DATEADD(m, DATEDIFF(m, 0, @dt)+1, 0)-1)
 SELECT    @s_dt = DATEADD(dd,((DATEPART(dd,GetDate())-1)* -1),GetDate())
	 , @e_dt = DATEADD(dd,@x - DATEPART(dd,GetDate()),GetDate())
 SELECT    @s_dt, @e_dt

DECLARE @tmp TABLE (s_dt datetime, e_dt datetime)

INSERT INTO @tmp SELECT @s_dt, @e_dt

 SELECT    *
   FROM	   Orders
  WHERE    OrderDate > = @s_dt AND OrderDate < = @e_dt 

 SELECT    *
   FROM	   Orders
  WHERE    OrderDate > = '12/1/2003' AND OrderDate < = '12/31/2003' 

 SELECT    *
   FROM	   Orders 
  WHERE    DATEPART(mm, OrderDate) = DATEPART(mm, GETDATE()) 
    AND    DATEPART(yy, OrderDate) = DATEPART(yy, GETDATE())

 SELECT    *
   FROM	   Orders
   JOIN	   @tmp
     ON    OrderDate > = s_dt AND OrderDate < = e_dt

I thought query 1 and 2 would both be index seeks, but the first one is a scan (the second, a seek)...as are the others except the last one, which is an index seek with a bookmark lookup

I know we have done a lot of these, but I thought the first one would have been a seek...

Have to search the site for some old posts...

Brett

8-)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-08 : 12:18:07

Thank you, using the variables sped it up. I was already using the datepart matching, but thought there might be a better way. I hope Yukon has some period type functions.

Thanks,

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 12:27:30

Did you compare the performance between query 1 and query 4?

Brett

8-)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-08 : 13:16:45

No I haven't, but I can. I will use only the columns neccesary, rather than selecting *. I bet using the first method will be faster, because this SQL Server has Raid Level 5 on the drives.

Will have a chance to compare tonight.

Thanks,

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 15:13:20

quote:
Originally posted by TSQLMan

No I haven't, but I can. I will use only the columns neccesary, rather than selecting *. I bet using the first method will be faster, because this SQL Server has Raid Level 5 on the drives.

Will have a chance to compare tonight.

Thanks,

Do a show plan when you do...

Query 1 will do a scan, and 4 will do a seek...

Well, as long as you have the date column indexed..

Good Luck...

Brett

8-)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-08 : 17:45:05

Q1 WAS FASTER. HERE IS THE SP THE QUESTION WAS DERIVED FROM. IF YOU LOOK AT THE LINE BEGINNING ---Begin Get Receipts of Prior Months Committements--, THIS IS THE ORIGINAL QUERY I STARTED WITH.

IF YOU WANT PICK APART THE REMAINDER OF THE QUERY, ANY COMMENTS ARE WELCOME. THANKFULLY THIS MONSTER ONLY RUNS ONCE A DAY.

ALTER PROCEDURE GetMineCost_061

AS

DECLARE @ExpenseType int
DECLARE @Account varchar(12)
DECLARE @SUPPLY MONEY
DECLARE @CAPITOL MONEY
DECLARE @EXPENSE MONEY
DECLARE @OVERHEAD MONEY
DECLARE @INVENTORY MONEY

--Truncate Table for Update
DELETE MineCostTotal

WHERE Company = 'TACC'

--Insert Company as Row Key
INSERT INTO MineCostTotal
(COMPANY)

SELECT 'TACC' AS COMPANY

SET @ExpenseType = 0

----------------GET COMMITTED DOLLARS TOTAL MONTH TO DATE FROM TACC "COMMITTED_COST" TABLE------------
--Go To Label to Increase @EXPENSE, AND @ACCOUNT by 1 for Case Statement which Determines Column
--for Expense Type
COM_TACC:

SET @ExpenseType = @ExpenseType + 1

SET @Account = (SELECT ExpenseType =

CASE

WHEN @ExpenseType = 1 THEN '7102%'
WHEN @ExpenseType = 2 THEN '25%'
WHEN @ExpenseType = 3 THEN '2801%'
WHEN @ExpenseType = 4 THEN '7102-00-9%'
WHEN @ExpenseType = 5 THEN '1601%'

END )

IF @ExpenseType = 1 SET @SUPPLY = (SELECT SUM(COMMITTED_MTD) as Amount
FROM Data_061.dbo._COMMITTED_COST
WHERE ORD_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account)
ELSE

IF @ExpenseType = 2 SET @CAPITOL = (SELECT SUM(COMMITTED_MTD) as Amount
FROM Data_061.dbo._COMMITTED_COST
WHERE ORD_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account)
ELSE

IF @ExpenseType = 3 SET @EXPENSE = (SELECT SUM(COMMITTED_MTD) as Amount
FROM Data_061.dbo._COMMITTED_COST
WHERE ORD_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account)
ELSE

IF @ExpenseType = 4 SET @OVERHEAD = (SELECT SUM(COMMITTED_MTD) as Amount
FROM Data_061.dbo._COMMITTED_COST
WHERE ORD_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account)

IF @ExpenseType = 5 SET @INVENTORY = (SELECT SUM(COMMITTED_MTD) as Amount
FROM Data_061.dbo._COMMITTED_COST
WHERE ORD_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account)

IF @ExpenseType < 5 GOTO COM_TACC

UPDATE MineCostTotal

set MCCapitol = @CAPITOL, MCExpense= @EXPENSE, MCSupply = @SUPPLY - @OVERHEAD, MCOverhead = @OVERHEAD, MCInventory = @INVENTORY

WHERE Company = 'TACC'

SET @SUPPLY = 0
SET @CAPITOL = 0
SET @EXPENSE = 0
SET @OVERHEAD = 0

-------------------Get Received Dollar Total From Received Cost Table from TACC "RECEIVED_COST" Table.-------------

SET @ExpenseType = 0

--Go To Label to Increase @EXPENSE, AND @ACCOUNT by 1 for Case Statement which Determines Column
--for Expense Type
REC_TACC:

SET @ExpenseType = @ExpenseType + 1

SET @Account = (SELECT ExpenseType =

CASE

WHEN @ExpenseType = 1 THEN '7102%'
WHEN @ExpenseType = 2 THEN '25%'
WHEN @ExpenseType = 3 THEN '2801%'
WHEN @ExpenseType = 4 THEN '7102-00-9%'
WHEN @ExpenseType = 5 THEN '1601%'

END )

IF @ExpenseType = 1 SET @SUPPLY = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE is null)

ELSE

IF @ExpenseType = 2 SET @CAPITOL = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE is null)
ELSE

IF @ExpenseType = 3 SET @EXPENSE = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE is null)
ELSE

IF @ExpenseType = 4 SET @OVERHEAD = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE is null)

IF @ExpenseType = 5 SET @Inventory = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE is null)

IF @ExpenseType < 5 GOTO REC_TACC

UPDATE MineCostTotal

set MRCapitol = @CAPITOL, MRExpense= @EXPENSE, MRSupply = @SUPPLY - @OVERHEAD, MROverhead = @OVERHEAD ,MRInventory = @INVENTORY

WHERE Company = 'TACC'

--PRINT convert(char(20),@Capitol)

SET @SUPPLY = 0
SET @CAPITOL = 0
SET @EXPENSE = 0
SET @OVERHEAD = 0

-------------------Get Inventory Issued Dollar Total From Received Cost Table from TACC "RECEIVED_COST" Table.-------------

SET @ExpenseType = 0

--Go To Label to Increase @EXPENSE, AND @ACCOUNT by 1 for Case Statement which Determines Column
--for Expense Type
ISS_TACC:

SET @ExpenseType = @ExpenseType + 1

SET @Account = (SELECT ExpenseType =

CASE

WHEN @ExpenseType = 1 THEN '7102%'
WHEN @ExpenseType = 2 THEN '2801%'

END )

IF @ExpenseType = 1 SET @SUPPLY = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE = 'I')
ELSE

IF @ExpenseType = 2 SET @EXPENSE = (SELECT SUM(RECEIVED_MTD) as Amount
FROM Data_061.dbo._RECEIVED_COST
WHERE SYSTEM_DT_MTD > 1 AND GL_DESCRIPTOR LIKE @Account
AND TRX_TYPE = 'I')
ELSE

IF @ExpenseType < 2 GOTO ISS_TACC

UPDATE MineCostTotal

set IssuesSupply = @SUPPLY, IssuesExpense = @EXPENSE

WHERE Company = 'TACC'

SET @SUPPLY = 0
SET @CAPITOL = 0
SET @EXPENSE = 0
SET @OVERHEAD = 0

-----------------Begin Get Receipts of Prior Months Committements----------------------------------------
CREATE TABLE #RPMTemp
(
RPMTEMP_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
GL_DESCRIPTOR char(12),
AMOUNT MONEY
)

INSERT INTO #RPMTemp
(GL_DESCRIPTOR, AMOUNT)

SELECT SUBSTRING(pol.mn_no,1,4)+ SUBSTRING(pol.dp_no,1,4),IM.Actual_Cost * IM.QTY_Received AS AMOUNT

FROM DATA_061.DBO.POORDHDR_SQL PO

INNER JOIN DATA_061.DBO.POORDLIN_SQL POL ON PO.ORD_NO = POL.ORD_NO
INNER JOIN DATA_061.DBO.IMRECHST_SQL IM ON POL.ORD_NO = IM.ORD_NO AND POL.LINE_NO = IM.LINE_NO

WHERE DATEPART(MM,DBO.UDFMACDATE(IM.REC_HST_DT)) = DATEPART(MM,GETDATE())

AND DATEPART(DD,DBO.UDFMACDATE(IM.REC_HST_DT)) < DATEPART(DD,GETDATE())

AND DBO.UDFMACDATE(PO.ORD_DT) < GETDATE() - DATEPART(DAY,GETDATE()) + 1

set @ExpenseType = 0

RPM_TACC:

SET @ExpenseType = @ExpenseType + 1

SET @Account = (SELECT ExpenseType =

CASE

WHEN @ExpenseType = 1 THEN '7102%'
WHEN @ExpenseType = 2 THEN '25%'
WHEN @ExpenseType = 3 THEN '2801%'
WHEN @ExpenseType = 4 THEN '71029%'
WHEN @ExpenseType = 5 THEN '1601%'

END)

IF @ExpenseType = 1 SET @SUPPLY = (SELECT SUM(AMOUNT) FROM #RPMTemp
WHERE GL_DESCRIPTOR like @Account)

ELSE

IF @ExpenseType = 2 SET @CAPITOL = (SELECT SUM(AMOUNT) FROM #RPMTemp
WHERE GL_DESCRIPTOR like @Account)

ELSE

IF @ExpenseType = 3 SET @EXPENSE = (SELECT SUM(AMOUNT) FROM #RPMTemp
WHERE GL_DESCRIPTOR like @Account)

ELSE

IF @ExpenseType = 4 SET @OVERHEAD = (SELECT SUM(AMOUNT) FROM #RPMTemp
WHERE GL_DESCRIPTOR like @Account)

ELSE

IF @ExpenseType = 5 SET @INVENTORY = (SELECT SUM(AMOUNT) FROM #RPMTemp
WHERE GL_DESCRIPTOR like @Account)

IF @ExpenseType < 5 GOTO RPM_TACC

UPDATE MINECOSTTOTAL

SET RPMSupply = @SUPPLY - @OVERHEAD, RPMCapitol = @CAPITOL, RPMExpense = @EXPENSE, RPMInventory = @INVENTORY, RPMOverhead = @OVERHEAD

WHERE COMPANY = 'TACC'

--Begin Get Committments to be received in Future Months.

UPDATE MINECOSTTOTAL

SET CTBRTotal = (SELECT SUM(pol.exp_unit_cost * pol.qty_ordered) AS Amount

FROM Data_061.dbo.poordhdr_sql po

INNER JOIN data_061.dbo.poordlin_sql pol ON po.ord_no = pol.ord_no

WHERE DBO.UDFMACDATE(po.ord_dt) > GETDATE() - DATEPART(DAY,GETDATE()) + 1
AND pol.request_dt <> 0
AND DATEPART(MONTH,DBO.UDFMACDATE(pol.request_dt)) > DATEPART(MONTH,GETDATE())
AND POL.MN_NO = '71020000' AND POL.DP_NO NOT LIKE '9%')

WHERE COMPANY = 'TACC'

DROP TABLE #RPMTemp

SET @SUPPLY = 0
SET @CAPITOL = 0
SET @EXPENSE = 0
SET @OVERHEAD = 0

bigbelly
Starting Member

39 Posts

Posted - 2003-12-08 : 21:56:34

Why not the simple script

select ReceiptDate, PONumber, Amount
from PO
where DATEDIFF(mm, ReceiptDate, GETDATE()) = 0

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-08 : 22:09:59

You could, but the idea is to construct a WHERE clause that can take advantage of an index on the column(s) you're searching. By embedding the column within a function, the query optimizer can't use the index. Changing the expression slightly gives the same results:

SELECT ReceiptDate, PONumber, Amount from PO
where ReceiptDate BETWEEN DateAdd(mm, DATEDIFF(mm, ReceiptDate, GETDATE()), 0) AND
DateAdd(mm, DATEDIFF(mm, ReceiptDate, GETDATE())+1, 0)

...and gives the optimizer a better chance at using any index on Receiptdate, and the query will return faster.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-09 : 08:48:57

Gentleman, thank you.

robvolk, your query shaved about 14 seconds of a minute on the query. There was no index on the date field, but I was able to add one without problem. The real performance issue with this query is that the date is actually and integer, and I convert it with a custom function. eg.. the date is '12/15/03' it is in the system as 20031215.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources