riyaz1984 Posted May 22, 2014 Report Posted May 22, 2014 Hello friends, I need some help regarding sql query. Query 1: for todays' date select [database], db_size, cast(Create_date as DATE) as DATE1 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONEAPP_Insureds' Output: database db_size DATE1 ONEAPP_Insureds 15.5 2014-05-22 Query 2: for Yesterday's date select [database], db_size, cast(Create_date as DATE) as DATE2 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() -1 as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONE_Insureds' Output: database db_size DATE2 ONEAPP_Insureds 10 2014-05-21 My requirement is i need a single query to get the difference(db_size) between 2 dates. Appreciate your help
riyaz1984 Posted May 22, 2014 Author Report Posted May 22, 2014 evaro okallo cheppandi bayya..... maa manager gaadu langa chinchukuntunnadu repatikalla ivamani......
150bryant Posted May 22, 2014 Report Posted May 22, 2014 WITH TODAYDate as ( select [database], db_size, cast(Create_date as DATE) as DATE1 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONEAPP_Insureds' ), YESTDATE as ( select [database], db_size, cast(Create_date as DATE) as DATE2 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() -1 as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONE_Insureds' ) select TODAY.Database, TODAYDATE.DATE1- YESTDATE.DATE2 as DATE_Diff, TODAYDATE.Db_size-YESTDATE.Db_Size as DB_Size from TODAYDATE, YESTDATE try cheyi..
roome6 Posted May 22, 2014 Report Posted May 22, 2014 Nice., Ee concept work avvali., try cheyi bayya., WITH TODAYDate as ( select [database], db_size, cast(Create_date as DATE) as DATE1 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONEAPP_Insureds' ), YESTDATE as ( select [database], db_size, cast(Create_date as DATE) as DATE2 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() -1 as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONE_Insureds' ) select TODAY.Database, TODAYDATE.DATE1- YESTDATE.DATE2 as DATEDiff, TODAYDATE.Db_size-YESTDATE.Db_Size as DB_Size try cheyi..
riyaz1984 Posted May 22, 2014 Author Report Posted May 22, 2014 thanks bayya but error vastundi Msg 422, Level 16, State 4, Line 25 Common table expression defined but not used.
150bryant Posted May 22, 2014 Report Posted May 22, 2014 or USE select datediff(day, TODAYDATE.DATE1,YESTDATE.DATE2)
Nene Thop Posted May 22, 2014 Report Posted May 22, 2014 +1 WITH TODAYDate as ( select [database], db_size, cast(Create_date as DATE) as DATE1 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONEAPP_Insureds' ), YESTDATE as ( select [database], db_size, cast(Create_date as DATE) as DATE2 from dbo.TBL_Database_space_management inner join ( select Snapshot_id, Create_date from TBL_Snapshot where cast(Create_date as DATE) = cast( GETDATE() -1 as DATE)) as snap on snap.Snapshot_id=TBL_Database_space_management.Snapshot_id where Server_id in ( select Server_id from TBL_Servers where Server_name like 'ma-srv-sql08dev') and [database] = 'ONE_Insureds' ) select TODAY.Database, TODAYDATE.DATE1- YESTDATE.DATE2 as DATE_Diff, TODAYDATE.Db_size-YESTDATE.Db_Size as DB_Size from TODAYDATE, YESTDATE try cheyi..
roome6 Posted May 22, 2014 Report Posted May 22, 2014 bayya., see this for explanation. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d0d3cce-a59c-43cd-8cb8-d0a7307999f1/common-table-expression-defined-but-not-used-why?forum=transactsql thanks bayya but error vastundi Msg 422, Level 16, State 4, Line 25 Common table expression defined but not used.
riyaz1984 Posted May 22, 2014 Author Report Posted May 22, 2014 bayya., see this for explanation. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d0d3cce-a59c-43cd-8cb8-d0a7307999f1/common-table-expression-defined-but-not-used-why?forum=transactsql Thanks for the link....okka mukka ardam ayite cheppu teeskoni kottu......... manaki SQL ABCD lu kuda ravu bayya.....
150bryant Posted May 22, 2014 Report Posted May 22, 2014 *<:( :3D_Smiles_216: two tables lo common ga column edhaina undaa?
Recommended Posts