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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 stored procedure Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 11/16/2012 :  16:08:39  Show Profile  Reply with Quote

I have a staging table which has unprocessed records per batch..

I need a query which should validate the unprocess records and log the records into another table which is not valid records and also need to log the passed and failed count.


staging Table: smaster

Id      Sid         name         status   validated  BatchID   createddt		  created_by 
---     -------   -----------   -------   --------   -------   ---------------------       -------------
931     100.2.4    Siteipname     New       0         NULL     2012-11-16 14:00:40.800       Test
932	           Siteppname     New       0         NULL     2012-11-16 14:00:40.800       Test 
933     100.2.3    Siteipname     Modify    0         NULL     2012-11-16 14:00:40.800       Test 
934     100.2.5			  Modify    0         NULL     2012-11-16 14:00:40.800       Test


Expected output



Table:smaster

Id      Sid         name         status   validated  BatchID   createddt		   created_by 
---     -------   -----------   -------   --------   -------   ---------------------       -------------
931     100.2.4    Siteipname     New       1         1	       2012-11-16 14:00:40.800       Test
932		   Siteppname     New       1         1        2012-11-16 14:00:40.800       Test 
933     100.2.3    Siteipname     Modify    1         1        2012-11-16 14:00:40.800       Test 
934     100.2.5			  Modify    1         1        2012-11-16 14:00:40.800       Test



Table:SDetail

BatchID   passedcount  failedcount   createddt			created_by 
-------   -----------  -----------  -----------------------   	----------
1        2             2           2012-11-16 14:00:40.800       Test


Table:Slog

ErrorID       SID       Msg									createddt                  created_by    Batchid  
--------      ---      ---------------------------------------					--------------------       ------------   --------
1             0         invalid  Sid is required field						2012-11-16 14:00:40.800     Test           1
2	     100.2.5    Sitename is required and cannot be null 				2012-11-16 14:00:40.800     Test           1 



thanks for your help

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/16/2012 :  17:07:05  Show Profile  Reply with Quote
Are there any common attributes between the tables? As in, what columns would you join the talbes on?

It looks like SID for SMaster and Slog and BatchID for Slog and SDetail. But, for SID, I don't see how it relates to more than just the one row. Can you explain?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/16/2012 :  22:50:51  Show Profile  Reply with Quote

DECLARE @BatchID int
SELECT @BatchID= COALESCE(MaxBatchID,0) + 1
FROM (SELECT MAX(BatchID) AS MaxBatchID FROM SDetail)t


UPDATE smaster
SET BatchID = @BatchID
WHERE BatchID IS NULL

INSERT Slog
(SID,
Msg,
createddt,
created_by,
BatchId
)
SELECT COALESCE(SID,0),
CASE WHEN Sid IS NULL THEN 'invalid  Sid is required field'
     WHEN name IS NULL THEN 'Sitename is required and cannot be null'
...
END,
createddt,
created_by,
BatchID
FROM smaster
WHERE BatchID = @BatchID

INSERT SDetail
SELECT s.BatchID,
(SELECT COUNT(1) 
FROM Smaster
WHERE BatchID = s.BatchID) - COUNT(1) ,
COUNT(1),
MIN(createddt),
MIN(created_by)
FROM Slog s
WHERE BatchID = @BatchID



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 11/17/2012 :  12:50:04  Show Profile  Reply with Quote
Thanks a lot visakh how do i call the call the validation functions instead of case statement in the query




Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 11/17/2012 :  12:52:26  Show Profile  Reply with Quote
Thanks a lot visakh how do i call the call the validation functions instead of case statement in the query




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