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.
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 mannerFor 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.PRODHThanks & 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 containerif 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|