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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-29 : 12:02:07

Hey guys

i need some help

i run a basic query with the following table

SELECT TOP 1000 [hst_merchnum]
,[hst_date_processed]
,[Net_Sales]
FROM [FDMS].[dbo].[Fact_Financial_History]
where hst_merchnum = '878000039'

Which returns the following

hst_merchnum hst_date_processed Net_Sales
878000039 2009-07-01 31.95

and i return all the net sales from 2009 to july 2012

I built an other query which is

SELECT [hst_merchnum],
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
FROM [FDMS].[dbo].[Fact_Financial_History]
where year(hst_date_processed) > 2010
group by hst_merchnum

But it only brings in data up to may 2012 , does any one have any ideas ?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-29 : 12:10:27

[code]SELECT [hst_merchnum],
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
FROM [FDMS].[dbo].[Fact_Financial_History]
where year(hst_date_processed) > '2010'
group by hst_merchnum[/code]
First, look a the comparison criteria you have on red. I think it should be within single Quotes.
second, if you desire to see July data...you probably need to include the month criteria.

--------------------------
Joins are what RDBMS's do for a living

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-29 : 12:20:47

hi

Well i was hoping that hst_date_processed, includes year and month , i could sumarize by the year only

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-29 : 12:30:16

[code]Yes, you can.
How about this '01/01/2010'. Logically : Isn't this similar to '2010'?[/code]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-29 : 12:32:08

well i would of thought so, so the quwery which i wrote

SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],

should identify what is 2012 from the hst_date_processed field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 12:35:17

quote:
Originally posted by masond

well i would of thought so, so the quwery which i wrote

SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],

should identify what is 2012 from the hst_date_processed field

yep. provided you dont have any further filters added based on hst_date_processed in WHERE clause

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources