query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-01-08 : 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
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.

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
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 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

52326 Posts

Posted - 2013-01-08 : 04:15:49

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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)

Madhivanan

Failing 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 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

52326 Posts

Posted - 2013-01-08 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources