Sql Query Help: Urgent

[riyaz1984]

two tables lo common ga column edhaina undaa?

database name is common bhayya....

[150bryant]

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

where TodayDate.[Database] = Yestdate.[Database]

 

try cheyi..

 

[riyaz1984]

 

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

where TodayDate.[Database] = Yestdate.[Database]

 

try cheyi..

 

Msg 8117, Level 16, State 1, Line 28

Operand data type date is invalid for subtract operator.

[150bryant]

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 datediff(day, TODAYDATE.DATE1,YESTDATE.DATE2)

 

from TODAYDATE, YESTDATE

where TodayDate.[Database] = Yestdate.[Database]

 

Try this

[riyaz1984]

 

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 datediff(day, TODAYDATE.DATE1,YESTDATE.DATE2)

 

from TODAYDATE, YESTDATE

where TodayDate.[Database] = Yestdate.[Database]

 

Try this

 

thanks bayya but output default ga -1 vastundi.

 

My expection is DB growth between these two dates is 10 MB.

[150bryant]

Add

 

select TODAYDATE.[Database], YESTDATE.[Database], datediff(day, TODAYDATE.DATE1,YESTDATE.DATE2) as Date_Diff,  TODAYDATE.Db_size-YESTDATE.Db_Size as DB_SizeDiff

from TODAYDATE, YESTDATE

where TodayDate.[Database] = Yestdate.[Database]

[riyaz1984]

 

Add

 

select TODAYDATE.[Database], YESTDATE.[Database], datediff(day, TODAYDATE.DATE1,YESTDATE.DATE2) as Date_Diff,  TODAYDATE.Db_size-YESTDATE.Db_Size as DB_SizeDiff

from TODAYDATE, YESTDATE

where TodayDate.[Database] = Yestdate.[Database]

 

many thanks bayya...... vachindi output as expected..........

 

One another help........ assala ee SQL ela nerchukovali bayya......... nenu DBA background lo unna.......

 

any tutorials or books??

[riyaz1984]

  (.L@

[150bryant]

many thanks bayya...... vachindi output as expected..........

 

One another help........ assala ee SQL ela nerchukovali bayya......... nenu DBA background lo unna.......

 

any tutorials or books??

pm iloveindia ..

athani website edho undhi...

 

also w3schools .com try cheyi..

 

the key in writing queries to know about the TABLES and DATA present in the tables.

if you know how the tables are and how the data is arranged in them...it becomes easy to use.

i am from mech background...naakey vachindi antey neeku vasthundi easy gaaney..

 

goodluck..try learning daily 1%everyday...end of the year(365days ki u will learn atleast 300% of info)

[Saanvi]

many thanks bayya...... vachindi output as expected..........

 

One another help........ assala ee SQL ela nerchukovali bayya......... nenu DBA background lo unna.......

 

any tutorials or books??

I started from w3schools.com

 

install oracle and practice as much as you can

[Saanvi]

pm iloveindia ..

athani website edho undhi...

 

http://sqlsaga.com/

[roome6]

Nice bayya.,  :) Appreciate ur help to fellow DB ppl.

pm iloveindia ..

athani website edho undhi...

 

also w3schools .com try cheyi..

 

the key in writing queries to know about the TABLES and DATA present in the tables.

if you know how the tables are and how the data is arranged in them...it becomes easy to use.

i am from mech background...naakey vachindi antey neeku vasthundi easy gaaney..

 

goodluck..try learning daily 1%everyday...end of the year(365days ki u will learn atleast 300% of info)

 

[Bairagi From Bapatla]

LiquorLunatic Nuvvu super bhayya.... kev keka anthe... CITI_y@R  CITI_$D#

 

[Iphone6]

gp

[ramsinn]

GP