SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to get consolidated Data in Yearwise manner..?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bhushan_juare
Starting Member

45 Posts

Posted - 02/25/2013 :  06:58:20  Show Profile  Reply with Quote
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

Edited by - bhushan_juare on 02/25/2013 07:03:11

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/25/2013 :  13:24:10  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:07:18  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000