help building query - 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

help building query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-10 : 14:37:30

Aim –

to get the last three months sales figures, from the date the report has been run, and put the the figures in separate columns associated with months.then provide a flag to give me indiciation if the sales amount has decreased by 10% within those 3months

The table which i am using is

SELECT TOP 1000 [hst_merchnum]
,[hst_date_processed]
,[hst_sales_amt]
FROM [FDMS].[dbo].[Fact_Financial_History]

[hst_merchnum] =
[hst_date_processed] =date of transactions, date is listed as 2120101
hst_sales_amt] = sales amount

I would need columns created so i can show last 3months hst_sales_amt from point of report

For eg – ran report in march

layout

hst_Merchantnum, Jan , Feb, march,
001 £1000 , £2000, £5000

eg 2) – ran report in august
hst_Merchantnum, June, july, August,
002 £1000 , £2000, £5000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 15:15:59

you need to do a dynamic pivot on months
see

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

is hst_date_processed a datetime field? what date does value of 2120101 represent?

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-10 : 15:20:51

hi visakh16

sorry i mean that
[hst_date_processed] =the date for eg be listed as

20120101
20120601
20120801

so it be year, month, day

thanks for posting about dynamic pivot on months, i am new to sql , and i had a quick browse of that link, however its above my knowledge , is there anything simpler i could apply to my query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 15:47:20

Nope...unfortunately thats easiest way in which you can get data in format you want. are you using this for report? is your front end reporting tool like ssrs?

for date column is datatype datetime?

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-10 : 16:47:04

I am going to run the query in sql, once query has been executed, I'll export the data into excel. The report is going to done on an adoc basis, so there isn't a front end system in place. I don't get what you mean by date column is datatype datetime

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-10 : 16:51:43

How would you do a dymanic pivot in months as a query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 16:57:37

quote:
Originally posted by masond

How would you do a dymanic pivot in months as a query ?

exactly same as logic used in link

get comma separated list of months on a variable

use a dynamic sql query including PIVOT with above variable with month list and execute it to get pivotted result

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources