Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help with a query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ushavellala
Starting Member

USA
10 Posts

Posted - 11/08/2013 :  01:00:23  Show Profile  Reply with Quote
Hello,

I have this stored procedure and I need to run this for everyday. I have written a query for the same purpose. But I don't want the same records to be inserted when I run the stored proc again on the same day

Here is my query

CREATE PROCEDURE [dbo].[sp_Amt]
AS


declare @Date datetime
select @Date = getdate()

if(@Date = getdate())

BEGIN
INSERT INTO FactTable(PID, Amt, RecordCreatedDate)
select D. PID, SUM(F.Amt),Convert(varchar(11), getdate(),101)
from FactGenTable F
left join DimTable D
on D.CID = F.CID and D.AccIndex = F.AccIndex
where D.AccSeg5 > 1 AND F.Calyear = 2013 AND F.Acc Like '%1200-002%'
group by D.PID
END

Can you please help me with this ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/08/2013 :  01:21:24  Show Profile  Reply with Quote
just add a condition using WHERE NOT EXISTS

CREATE PROCEDURE [dbo].[sp_Amt]
AS


declare @Date datetime
select @Date = getdate()

if(@Date = getdate())

BEGIN 
INSERT INTO FactTable(PID, Amt, RecordCreatedDate) 
select D. PID, SUM(F.Amt),Convert(varchar(11), getdate(),101) 
from FactGenTable F
left join DimTable D
on D.CID = F.CID and D.AccIndex = F.AccIndex
where D.AccSeg5 > 1 AND F.Calyear = 2013 AND F.Acc Like '%1200-002%' 
AND NOT EXISTS (SELECT 1 FROM FactTable WHERE PID = D.PID)
group by D.PID
END

I'm assuming PID is key column in Fact. otherwise use the correct key columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ushavellala
Starting Member

USA
10 Posts

Posted - 11/08/2013 :  01:43:29  Show Profile  Reply with Quote
Thank you very much for your prompt response!! That totally worked. One question, how do you get to know when I post a question, do you get an email alert ? Because I don't get anything as such and was wondering if there is one

Thanks,
US
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/08/2013 :  01:46:02  Show Profile  Reply with Quote
quote:
Originally posted by ushavellala

Thank you very much for your prompt response!! That totally worked. One question, how do you get to know when I post a question, do you get an email alert ? Because I don't get anything as such and was wondering if there is one

Thanks,
US


Nope ...Nothing like that
Its just that I was online and saw the post

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ushavellala
Starting Member

USA
10 Posts

Posted - 11/08/2013 :  01:51:50  Show Profile  Reply with Quote
Oh, Thanks :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000