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.
Author |
Topic |
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-11-16 : 16:08:39
|
[code]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: smasterId Sid name status validated BatchID createddt created_by --- ------- ----------- ------- -------- ------- --------------------- -------------931 100.2.4 Siteipname New 0 NULL 2012-11-16 14:00:40.800 Test932 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 TestExpected outputTable:smasterId Sid name status validated BatchID createddt created_by --- ------- ----------- ------- -------- ------- --------------------- -------------931 100.2.4 Siteipname New 1 1 2012-11-16 14:00:40.800 Test932 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 TestTable:SDetailBatchID passedcount failedcount createddt created_by ------- ----------- ----------- ----------------------- ----------1 2 2 2012-11-16 14:00:40.800 TestTable:SlogErrorID SID Msg createddt created_by Batchid -------- --- --------------------------------------- -------------------- ------------ --------1 0 invalid Sid is required field 2012-11-16 14:00:40.800 Test 12 100.2.5 Sitename is required and cannot be null 2012-11-16 14:00:40.800 Test 1 thanks for your help [/code] |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-16 : 17:07:05
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-16 : 22:50:51
|
[code]DECLARE @BatchID intSELECT @BatchID= COALESCE(MaxBatchID,0) + 1FROM (SELECT MAX(BatchID) AS MaxBatchID FROM SDetail)tUPDATE smasterSET BatchID = @BatchIDWHERE BatchID IS NULLINSERT 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,BatchIDFROM smasterWHERE BatchID = @BatchIDINSERT SDetailSELECT s.BatchID,(SELECT COUNT(1) FROM SmasterWHERE BatchID = s.BatchID) - COUNT(1) ,COUNT(1),MIN(createddt),MIN(created_by)FROM Slog sWHERE BatchID = @BatchID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-11-17 : 12:50:04
|
Thanks a lot visakh how do i call the call the validation functions instead of case statement in the query |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-11-17 : 12:52:26
|
Thanks a lot visakh how do i call the call the validation functions instead of case statement in the query |
|
|
|
|
|
|
|