SQL Server Forums
Profile | Register | 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
 New Topic  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
52325 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
52325 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000