How to get consolidated Data in Yearwise manner..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

How to get consolidated Data in Yearwise manner..?

Author

Topic

bhushan_juare
Starting Member

45 Posts

Posted - 2013-02-25 : 06:58:20

Hi All,

Below T-Sql Query collect consolidated data between two dates which is used as a parameters. Now My Concern is i want to reperesent output data in Yearwise manner

For EX:- If FromDate = '2012-01-01 00:00:00.000' & EndDate = '2013-02-13 00:00:00.000', Here I want All 2012 Numeric data(i.e. QUANTITY, VALUE_IN_FC, WEIGHT in my case) in seperate columns and All 2013 data in next seperate columns based on parameters value user passed(i.e Dynamically Yearwise)...

Declare @FromDate DATETIME
Declare @EndDate DATETIME

Set @FromDate = '2013-01-01 00:00:00.000'
Set @EndDate = '2013-02-13 00:00:00.000'

Select Distinct so.vkbur As MARKET,
so.bezei As NAME,
sd.kunrg As PARTY,
cm.NAME1 As PARTY_NAME,
sd.PRODH As SEGMENT,
sl.VTEXT As MATERIAL_DESCRIPTION,
za.FGCODE As ITEM,
za.FGDESC As ITEM_DESCRIPTION,
za.EANNUM As CODE,
sd.FKIMG As QUANTITY,
sd.NETWR As VALUE_IN_FC,
sd.NTGEW As WEIGHT
FROM
sales_office so WITH(NOLOCK)
LEFT JOIN
SALES_DATA sd WITH(NOLOCK)
On
so.VKBUR = sd.VKBUR
INNER JOIN
ZBARARCHIVE za WITH(NOLOCK)
On
sd.MATNR = za.FGCODE
INNER JOIN
Cust_Mas cm WITH(NOLOCK)
On
sd.KUNRG = cm.KUNNR
INNER JOIN
Segment_line04 sl WITH(NOLOCK)
On
sd.prodh_level_4 = sl.PRODH_LEVEL_4
WHERE
sd.FKDAT >= @FromDate
AND
sd.FKDAT <= @EndDate
AND
sl.VTEXT not in ('', 'Blank')
AND
za.EANNUM != ''
Order By
sd.kunrg, sd.PRODH

Thanks & Regards,
Bhushan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-25 : 13:24:10

Can you show expected output to help show what you want?

The way I'm ready your question I think you'll have to use dynamic SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 01:07:18

its very easy to buld this dynamically in reporting tools like SSRS. In SSRS, you can make use of year as column group to get this inside matrix container

if you want to do this in sql you might need dynamic sql or your year window should be fixed atleast ie always 2yrs,5yrs etc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources