|
sqldev6363
Yak Posting Veteran
54 Posts |
 Posted - 2012-08-22 : 17:11:58
|
| CREATE TABLE #Table( [TableId] [INT] NOT NULL, [TableName] [VARCHAR](255) NOT NULL, [DataCheckFlag] [CHAR](1) NULL, [Monday] [Char](1) NULL, [Tuesday] [Char](1) NULL, [Wednesday] [Char](1) NULL, [Thursday] [Char](1) NULL, [Friday] [Char](1) NULL, [Saturday] [Char](1) NULL, [Sunday] [Char](1) NULL, [DataComplete] [bit] NULL ) Insert into #Table values (1,'Data1','Y','N','Y','Y','Y','Y','N','Y',0),(2,'Data2','Y','N','Y','Y','Y','Y','N','Y',0),(3,'Data3','Y','N','Y','Y','Y','Y','N','Y',0),(4,'Data4','Y','N','Y','Y','Y','Y','N','Y',0),(5,'Data5','Y','Y','Y','Y','Y','Y','Y','N',0)CREATE TABLE #Table1( [TableName] [VARCHAR](255) NOT NULL, [DataLoadTime] Datetime NULL, ) Insert into #Table1 values ('Data1','08-21-2012'),('Data2','08-22-2012'),('Data3','08-22-2012'),('Data4','08-20-2012'),('Data5','08-22-2012') SELECT t.TableName , Max(t1.DataLoadTime) FROM #Table1 (NOLOCK) t1 INNER JOIN #Table t ON t1.TableName = t.TableName WHERE t.DataCheckFlag = 'Y' AND (DateName(WEEKDAY,GETDATE()) = 'Monday' AND (t.Monday)= 'Y') OR (DateName(WEEKDAY,GETDATE()) = 'Tuesday' AND (t.Tuesday)= 'Y') OR (DateName(WEEKDAY,GETDATE()) = 'Wednesday' AND (t.Wednesday)= 'Y') OR (DateName(WEEKDAY,GETDATE()) = 'Thursday' AND (t.Thursday)= 'Y') OR (DateName(WEEKDAY,GETDATE()) = 'Friday' AND (t.Friday)= 'Y') OR (DateName(WEEKDAY,GETDATE()) = 'Saturday' AND (t.Saturday)= 'Y') OR (DateName(WEEKDAY,GETDATE()) = 'Sunday' AND (t.Sunday)= 'Y') GROUP BY t.TableName HAVING CONVERT(INT, CONVERT(VARCHAR, MAX(DataLoadTime), 112)) < CONVERT(INT, CONVERT(VARCHAR, GETDATE(), 112)) Let me explain what i am doing...I have #table and #table1 in that TableName is common in both the tables.Based on that we need to check by combiningthe 2 tables whether we got the DataLoadTime is loaded with today's Date. And we don't need to check all the TableNames everyday.For Example we have Data1 in #Table. we will check whether we have "DataCheckFlag" = 'Y' and if it is Tuesday today then check whether "Tuesday" field has 'Y' for that. Those combination only we have to check.What i need is when we run the above query it has to check the data is loaded with today's date and update the "DataComplete" column with "1"for that row.If it is loaded with today's date and the conditions met then i need to execute 1 SP.Example: "Data1" is loaded with today's date in "#Table1' in the column "DataLoadTime" then update the "DataComplete" column in #table with "1" AndExec dbo.StoreProc.Thenif suppose "Data2" is not loaded with today's date check back after sometime, say like 1 hour.If it is loaded with today's date thenupdate the "DataComplete" column with "1" in "#table"for that row. Then Exec dbo.storeProc2....Could anyone please help me to get query on this.dev |
|
On the Fly we need to update a column and then..