| Author |
 Topic  |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
 Posted - 01/08/2013 : 03:35:12
|
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
3328 Posts |
Posted - 01/08/2013 : 03:59:29
|
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. |
 |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/08/2013 : 04:03:47
|
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) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/08/2013 : 04:15:49
|
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/
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 01/08/2013 : 04:25:23
|
Instead of dbo.getdateonly(date), use CONVERT(DATE,date)
Madhivanan
Failing to plan is Planning to fail |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/08/2013 : 04:27:26
|
| something is wrong with the t1.sitecode - it doesn't seem to recognize it in join |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/08/2013 : 04:29:29
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/08/2013 : 04:51:20
|
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/
|
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 05:16:25
|
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. |
|
|
| |
Topic |
|
query help
