Author |
Topic |
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2013-01-08 : 03:35:12
|
update t1 set t1.online = t2.dfrom #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.siteAbove 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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 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.Maybeupdate t1set t1.online = t2.dfrom #sites t1 inner join(select count(*) as c, site from images where date>getdate()-365group by site) as t2on 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
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2013-01-08 : 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 allI want the results of how many rows show up when I do select count(*) as c, sitefrom 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
52326 Posts |
Posted - 2013-01-08 : 04:15:49
|
[code]update t1set t1.online = t2.cntfrom #sites t1 inner join(select count(*) as cntfrom(select count(dbo.getdateonly(date) )as d,site from images where date>getdate()-365where t1.sitecode = sitegroup by site,dbo.getdateonly(date))t) as t2[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-01-08 : 04:25:23
|
Instead of dbo.getdateonly(date), use CONVERT(DATE,date)MadhivananFailing to plan is Planning to fail |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2013-01-08 : 04:27:26
|
something is wrong with the t1.sitecode - it doesn't seem to recognize it in join |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2013-01-08 : 04:29:29
|
even this query alone select count(*) as cntfrom(select count(dbo.getdateonly(date))as d,site from images where date>getdate()-365and 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
52326 Posts |
Posted - 2013-01-08 : 04:51:20
|
quote: Originally posted by esthera even this query alone select count(*) as cntfrom(select count(dbo.getdateonly(date))as d,site from images where date>getdate()-365and site='415'group by site,dbo.getdateonly(date))tdoesn't work it gives Incorrect syntax near ')'.but this is what i'm trying to do
its missing an alias------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 05:16:25
|
How aboutupdate t1set t1.online = t2.dfrom #sites t1 inner join(select count(distinct dateadd(dd,datediff(dd,0,date),0))as d,site from images where date>getdate()-365group by site) as t2on 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. |
|
|
|