How to get consolidated Data in Yearwise manner..?

• bhushan_juare

  Ten Centuries

  Points: 1292

  More actions

  February 25, 2013 at 5:00 am

  #274540

  [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]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  February 25, 2013 at 7:42 am

  #1590805

  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/