|
bhushan_juare
Starting Member
44 Posts |
 Posted - 02/25/2013 : 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 |
Edited by - bhushan_juare on 02/25/2013 07:03:11
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/26/2013 : 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/
|
 |
|
How to get consolidated Data in Yearwise manner..?
