February 25, 2013 at 5:00 am
[font="Times New Roman"]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
[/font]
February 25, 2013 at 7:42 am
It is pretty unclear what you are trying to do here but I think you want a dynamic cross tab. Please see the articles in my signature about rows to columns. If you need specific coding assistance please take the time to read the first article in my signature for best practices when posting questions.
Also be careful with all those NOLOCK hints. They can produce very difficult to reproduce bugs because it can duplicate or even miss rows.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply