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 2012 Forums
 Transact-SQL (2012)
 query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 01/08/2013 :  03:35:12  Show Profile  Reply with Quote
update t1
set t1.online = t2.d
from #sites t1 inner join
(
select count(dbo.getdateonly(date) )as d,site
from images where date>getdate()-365
group by site,dbo.getdateonly(date)
) as t2
on t1.sitecode = t2.site


Above is my code -- the number I really want is the how many records are returned when I do
select count(dbo.getdateonly(date) )as d,site
from images where date>getdate()-365
group by site,dbo.getdateonly(date)


How do I get that?

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  03:59:29  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Not sure what you want. What is getdateonly(date)?
Maybe you need to add the date to the join - or maybe remove it from the group by?

Suspect getdateonly(date) might be dateadd(dd,datediff(dd,0,date),0) - if so I would get rid of the function.

Maybe
update t1
set t1.online = t2.d
from #sites t1 inner join
(
select count(*) as c, site
from images where date>getdate()-365
group by site) as t2
on t1.sitecode = t2.site



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 01/08/2013 :  04:03:47  Show Profile  Reply with Quote
yes for getdateonly

but I want this to return how many records there were not total but how many days of records (and i'm looking at date not time) - so waht you did doesn't help at all

I want the results of how many rows show up when I do

select count(*) as c, site
from images where date>getdate()-365 and site='415'
group by site,dbo.getdateonly(date)) (this is takigna sample site)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/08/2013 :  04:15:49  Show Profile  Reply with Quote

update t1
set	t1.online = t2.cnt
from	#sites t1 inner join
(
select count(*) as cnt
from
(
select count(dbo.getdateonly(date) )as d,site 
from	images where date>getdate()-365
where 	t1.sitecode	= site
group by site,dbo.getdateonly(date)
)t
) as t2


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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 01/08/2013 :  04:25:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Instead of dbo.getdateonly(date), use CONVERT(DATE,date)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 01/08/2013 :  04:27:26  Show Profile  Reply with Quote
something is wrong with the t1.sitecode - it doesn't seem to recognize it in join
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 01/08/2013 :  04:29:29  Show Profile  Reply with Quote
even this query alone


select count(*) as cnt
from
(
select count(dbo.getdateonly(date))as d,site
from images where date>getdate()-365
and site='415'
group by site,dbo.getdateonly(date)
)

doesn't work

it gives Incorrect syntax near ')'.

but this is what i'm trying to do
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/08/2013 :  04:51:20  Show Profile  Reply with Quote
quote:
Originally posted by esthera

even this query alone


select count(*) as cnt
from
(
select count(dbo.getdateonly(date))as d,site
from images where date>getdate()-365
and site='415'
group by site,dbo.getdateonly(date)
)t

doesn't work

it gives Incorrect syntax near ')'.

but this is what i'm trying to do


its missing an alias

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

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  05:16:25  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
How about
update t1
set t1.online = t2.d
from #sites t1 inner join
(
select count(distinct dateadd(dd,datediff(dd,0,date),0))as d,site
from images where date>getdate()-365
group by site
) as t2
on t1.sitecode = t2.site


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
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.08 seconds. Powered By: Snitz Forums 2000