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

General SQL Server Forums

New to SQL Server Programming

Query merge

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 10:09:55

Hi all,

I've got the following query,

SELECT     SUM(dbo.tblFile.ByteSize)/1024/1024/1024
FROM         dbo.tblCOMImage 
	INNER JOIN dbo.tblCOMSeries ON dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2 
	INNER JOIN dbo.tblCOMStudy ON dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1 
	INNER JOIN dbo.tblFile ON dbo.tblCOMImage._idFile = dbo.tblFile.idFile

where dbo.tblCOMStudy.FirstArchivalDate > '2012-07-24' and dbo.tblCOMStudy.FirstArchivalDate < '2012-07-25'

--but I'd like to add the following to it...

AND datepart(hour,Datestart) >= 8
AND datepart(hour,Datestart) <= 20
group by daystart
order by daystart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 10:55:54

[code]
SELECT (((SUM(dbo.tblFile.ByteSize)/1024)/1024)/1024)
FROM dbo.tblCOMImage
INNER JOIN dbo.tblCOMSeries ON dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2
INNER JOIN dbo.tblCOMStudy ON dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1
INNER JOIN dbo.tblFile ON dbo.tblCOMImage._idFile = dbo.tblFile.idFile

where dbo.tblCOMStudy.FirstArchivalDate >= '2012-07-24 08:00' and dbo.tblCOMStudy.FirstArchivalDate <= '2012-07-24 20:00'
group by daystart
order by daystart
[/code]

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

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 16:30:24

Thank you Visakh!! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 17:03:22

welcome

suggest you to understand how date values are stored and date manipulations happen in sql server

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-25 : 06:00:21

One more thing on this Visakh, can I group the results by day by going back to using Datepart?? Will take a look at your blog later this weekend.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:15:11

quote:
Originally posted by JaybeeSQL

One more thing on this Visakh, can I group the results by day by going back to using Datepart?? Will take a look at your blog later this weekend.

yep you can

just make it like


SELECT    DATEADD(dd,DATEDIFF(dd,0,dbo.tblCOMStudy.FirstArchivalDate),0) AS DateVal,
SUM(dbo.tblFile.ByteSize)/1024/1024/1024
FROM         dbo.tblCOMImage 
	INNER JOIN dbo.tblCOMSeries ON dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2 
	INNER JOIN dbo.tblCOMStudy ON dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1 
	INNER JOIN dbo.tblFile ON dbo.tblCOMImage._idFile = dbo.tblFile.idFile

where dbo.tblCOMStudy.FirstArchivalDate >=  your start date value and dbo.tblCOMStudy.FirstArchivalDate < your end date value  +1
GROUP BY DATEADD(dd,DATEDIFF(dd,0,dbo.tblCOMStudy.FirstArchivalDate),0)

the blog explains it too

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

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-30 : 10:52:39

One last question on this - instead of returning 'Null' for days where there are no values, how would I return "zero" instead? (don't ask me why, I've no idea!)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 12:49:29

quote:
Originally posted by JaybeeSQL

One last question on this - instead of returning 'Null' for days where there are no values, how would I return "zero" instead? (don't ask me why, I've no idea!)

Thanks

use COALESCE or ISNULL to convert NULLs to 0

see

http://msdn.microsoft.com/en-us/library/ms190349.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources