Help:Need to remove parameters in SP and modify SP

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 2008 Forums

Transact-SQL (2008)

Help:Need to remove parameters in SP and modify SP

Author

Topic

Nav522
Starting Member

27 Posts

Posted - 2010-02-11 : 14:46:38

Hello Folks,
I have this procedure which takes @startdate and @Enddate as parameters.My requirement is to get rid of this parameters and include the logic to look for previous month. Am not quite sure how to start this on writing this logic. Can anyone please throw some idea on this.Thanks a Million

ALTER PROCEDURE [dbo].[abc] (@StartDate datetime,@EndDate datetime,@Payer numeric,@Product numeric,@ActionCode numeric )
AS

Select @Product as productID into #ppp

If @Product = -1
Begin
Delete from #ppp
Insert into #ppp Select productID from tblProducts
End
Else If @Product = 1
Begin
Insert into #ppp (productID) Values (8)
End
/**********************************************END******************************************************/

Select @ActionCode as actioncode
into #ccc

If @ActionCode = -1
Begin
Delete from #ccc

Insert into #ccc
Select actioncode
from tblactioncodes
where newclaimstatus = 'C'
and actioncode NOT IN (26, 324)
End
/*******************************************************************************************************/

SELECT
c.claimid,
c.claimpatientlastname,
c.claimpatientfirstname,
c.claimadmitdate,
c.claimdischdate,
a.actioncode,
ac.actionname,
a.actiondate,
c.clientid,
cl.clientname,
cp.productid,
p.productname,
(SELECT TOP 1 actiondate
FROM table
WHERE claimid = c.claimid
and productid = cp.productid
and actioncode = 129
and actionoverriddenflag = 0
and actiondeletedflag = 0
ORDER BY dateinserted DESC) AS AuditDate,
(SELECT TOP 1 CASE actioncode
WHEN 86 THEN 'Y'
ELSE 'N'
END
FROM table
WHERE claimid = c.claimid
and productid = cp.productid
and actionoverriddenflag = 0
and actiondeletedflag = 0
ORDER BY actiondate DESC) AS Prep,
claimclientbusunit,
claimclientlobdesc,
claimclientlob
From
table innerjoin table
Where
a.actioncode IN (Select actioncode from #ccc)
and a.actionoverriddenflag = 0
and a.actiondeletedflag = 0
and a.actiondate between @StartDate and @EndDate

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 14:51:17

DATEADD(mm,-1,GETDATE()) will be 1 month backwards from today

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 14:51:26

If you want to select a months old data from current date, you can use

a.actiondate > dateadd(month,-1,dateadd(d, datediff(d, 0, getdate()), 0))

dateadd(month,-1,dateadd(d, datediff(d, 0, getdate()), 0)) would give current date - 1 month (after removing the 'time' portion)

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 14:52:21

SELECT
c.claimid,
c.claimpatientlastname,
c.claimpatientfirstname,
c.claimadmitdate,
c.claimdischdate,
a.actioncode,
ac.actionname,
a.actiondate,
c.clientid,
cl.clientname,
cp.productid,
p.productname,
(SELECT TOP 1 actiondate
FROM table
WHERE claimid = c.claimid
and productid = cp.productid
and actioncode = 129
and actionoverriddenflag = 0
and actiondeletedflag = 0
ORDER BY dateinserted DESC) AS AuditDate,
(SELECT TOP 1 CASE actioncode
WHEN 86 THEN 'Y'
ELSE 'N'
END
FROM table
WHERE claimid = c.claimid
and productid = cp.productid
and actionoverriddenflag = 0
and actiondeletedflag = 0
ORDER BY actiondate DESC) AS Prep,
claimclientbusunit,
claimclientlobdesc,
claimclientlob
From
table innerjoin table
Where
a.actioncode IN (Select actioncode from #ccc)
and a.actionoverriddenflag = 0
and a.actiondeletedflag = 0
and a.actiondate between getdate() and DATEADD(mm,-1,GETDATE())

Nav522
Starting Member

27 Posts

Posted - 2010-02-11 : 15:01:04

Thanks for getting back everyone.Well here i need to get the data for entire previous month(month end) i.e. from 2010-01-01 until 2010-01-31

DATEADD(mm,-1,GETDATE()) is getting back with the following
2010-01-11 14:57:30.460. Any ideas about this?

Thanks a lot

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 15:02:16

look at what vijayisonly posted...

and a.actiondate between getdate() AND dateadd(month,-1,dateadd(d, datediff(d, 0, getdate()), 0))

Nav522
Starting Member

27 Posts

Posted - 2010-02-11 : 15:07:47

Hi Harry,
Even dateadd(month,-1,dateadd(d, datediff(d, 0, getdate()), 0)) is coming back with the following
2010-01-11 00:00:00.000

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 15:15:56

Do you want the 1st of THIS month?

SELECT dateadd(Month, datediff(Month, 0, getdate()), 0)

1st of LAST month?

SELECT dateadd(Month, datediff(Month, 0, getdate())-1, 0)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 15:18:57

and last date of previous month

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

so..u wud need...

a.actiondate between dateadd(Month, datediff(Month, 0, getdate())-1, 0) and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Nav522
Starting Member

27 Posts

Posted - 2010-02-11 : 15:32:59

Awesome guys Thankyou verymuch.Just a final question what if i want to get the starting day of the year(First Day of year)..How would we write that?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 15:50:54

Just replace 'month' with 'year' in the query Kristen has provided.

SELECT dateadd(year, datediff(year, 0, getdate()), 0)

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 03:20:40

Its unfortunate that this particular formular is the one to use. It looks daunting, and most newbies use some sort of string manipulation to get 1st of Month or similar.

DATEADD and DATEDIFF just perform some integer maths on the dates. There are no datatype conversions at all. And as such this is the most CPU efficient way.

datediff(year, 0, getdate()) = "How many years from start of time to Today?"

dateadd(year, XXXX, 0) = "Add XXXX years to start of time (zero - but that is treated as a datetime)"

Works for Year, Quarter, Month, Day ...

... however once you get to Seconds (I think, it might be Minutes or even Hours) the precision runs out and you get arithmetic overflow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources