Query help

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  July 17, 2012 at 12:30 pm

  #276059

  Hello all,

  CREATE TABLE #pyft001

  (

  py_fl BIT ,

  fy INT ,

  p_nm VARCHAR (25),

  pr INT ,

  adfl BIT

  );

  INSERT INTO #pyft001

  SELECT 0,2010,'Opening Period',0,1 UNION ALL

  SELECT 0,2010,'July',1,0 UNION ALL

  SELECT 0,2010,'August',2,0UNION ALL

  SELECT 0,2010,'September',3,0UNION ALL

  SELECT 0,2010,'October',4,0UNION ALL

  SELECT 0,2010,'November',5,0UNION ALL

  SELECT 0,2010,'December',6,0UNION ALL

  SELECT 0,2010,'January',7,0UNION ALL

  SELECT 0,2010,'ebruary',8,0UNION ALL

  SELECT 0,2010,'March',9,0UNION ALL

  SELECT 0,2010,'April',10,0UNION ALL

  SELECT 0,2010,'May',11,0UNION ALL

  SELECT 0,2010,'June',12,0UNION ALL

  SELECT 0,2010,'Adjustment',13,1UNION ALL

  SELECT 0,2010,'Adjustment',14,1UNION ALL

  SELECT 0,2010,'Adjustment',15,1UNION ALL

  SELECT 0,2010,'Closing Period',99,1UNION ALL

  SELECT 0,2011,'Opening Period',0,1UNION ALL

  SELECT 0,2011,'July',1,0UNION ALL

  SELECT 0,2011,'August',2,0UNION ALL

  SELECT 0,2011,'September',3,0UNION ALL

  SELECT 0,2011,'October',4,0UNION ALL

  SELECT 0,2011,'November',5,0UNION ALL

  SELECT 0,2011,'December',6,0UNION ALL

  SELECT 0,2011,'January',7,0UNION ALL

  SELECT 0,2011,'February',8,0UNION ALL

  SELECT 0,2011,'March',9,0UNION ALL

  SELECT 0,2011,'April',10,0UNION ALL

  SELECT 0,2011,'May',11,0UNION ALL

  SELECT 0,2011,'June',12,0UNION ALL

  SELECT 0,2011,'Adjustment',13,1UNION ALL

  SELECT 0,2011,'Adjustment',14,1UNION ALL

  SELECT 0,2011,'Adjustment',15,1UNION ALL

  SELECT 0,2011,'Closing Period',99,1UNION ALL

  SELECT 0,2012,'Opening Period',0,1UNION ALL

  SELECT 0,2012,'July',1,0UNION ALL

  SELECT 0,2012,'August',2,0UNION ALL

  SELECT 0,2012,'September',3,0UNION ALL

  SELECT 0,2012,'October',4,0UNION ALL

  SELECT 0,2012,'November',5,0UNION ALL

  SELECT 0,2012,'December',6,0UNION ALL

  SELECT 0,2012,'January',7,0UNION ALL

  SELECT 0,2012,'February',8,0UNION ALL

  SELECT 0,2012,'March',9,0UNION ALL

  SELECT 0,2012,'April',10,0UNION ALL

  SELECT 0,2012,'May',11,0UNION ALL

  SELECT 0,2012,'June',12,0UNION ALL

  SELECT 0,2012,'Adjustment',13,1UNION ALL

  SELECT 0,2012,'Adjustment',14,1UNION ALL

  SELECT 0,2012,'Adjustment',15,1UNION ALL

  SELECT 1,2012,'Full Accrual Adjustments',16,1UNION ALL

  SELECT 0,2012,'Closing Period',99,1

  SELECT * FROM #pyft001

  --Clean up

  DROP TABLE #pyft001

  I am trying to write a query where I have to get the p_nm with in the fiscal year. If the py_fl falls within the adfl (true) then the p_nm should be max (pr) where adf = 0 for that fiscal year.

  If the py_fl falls within the (June – July) fiscal year, it has to be associated p_nm, pr

  Result:

  2012-June-12

  Thanks in advance!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  July 17, 2012 at 12:41 pm

  #1513807

  Nice job posting ddl and sample data. I am however complete confused with what you want this query to do. Can you try explaining this again more clearly?

  _______________________________________________________________

  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/

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  July 17, 2012 at 12:55 pm

  #1513814

  Thanks for the reply Sean Lange.

  Sure, the py_fl (prior period flag) is set to true for pr (period) 16 (Fiscal year 2012), and the adfl (adjustment flag) is true for the pr (period)’s (0, 99, 13, 14, 15, 16) for all the fiscal years.

  Now, If the py_fl is true for the pr’s (0, 99, 13, 14, 15, 16) then pr 12 (the max period where adfl is false (0)) has to be displayed else whatever the associated period to the py_fl has to be displayed.

  Thanks again!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  July 17, 2012 at 1:40 pm

  #1513845

  This, probably?

  DECLARE

  @py_fl BIT ,

  @fy INT ,

  @p_nm VARCHAR (25),

  @pr INT ,

  @adfl BIT

  SELECT

  @py_fl = py_fl

  ,@fy = fy

  ,@p_nm = p_nm

  ,@pr = pr

  ,@adfl = adfl

  FROM #pyft001 py

  WHERE py.py_fl = 1

  IF @pr IN (0, 99, 13, 14, 15, 16)

  BEGIN

  WITH CTE AS

  (

  SELECT py.p_nm , py.fy

  , RN = ROW_NUMBER() OVER (ORDER BY py.pr DESC)

  FROM #pyft001 py

  WHERE py.fy = @fy

  AND py.adfl = 0

  )

  SELECT *

  FROM CTE

  WHERE RN = 1

  END

  ELSE

  BEGIN

  SELECT py.p_nm , py.fy

  FROM #pyft001 py

  WHERE py.fy = @fy

  AND py.p_nm = @p_nm

  END

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  July 17, 2012 at 1:49 pm

  #1513855

  Thanks for the query Coldcoffee.

  We have a issue here, they may add other adjustment periods in the future, so it is not a fixed set of periods.

  Any other method to retrieve the results dynamically.

  Thanks again!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  July 17, 2012 at 1:51 pm

  #1513857

  ssc_san (7/17/2012)

  ---

  We have a issue here, they may add other adjustment periods in the future, so it is not a fixed set of periods.

  Meaning, 0,9,13,14,16 will have other entries added it them?

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  July 17, 2012 at 1:59 pm

  #1513868

  ColdCoffee (7/17/2012)

  ---

  ssc_san (7/17/2012)

  ---

  We have a issue here, they may add other adjustment periods in the future, so it is not a fixed set of periods.

  Meaning, 0,9,13,14,16 will have other entries added it them?

  Yes ColdCoffee, they may add other adjustment periods in future.

  Thanks.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  July 17, 2012 at 2:08 pm

  #1513873

  ssc_san (7/17/2012)

  ---

  ColdCoffee (7/17/2012)

  ---

  ssc_san (7/17/2012)

  ---

  We have a issue here, they may add other adjustment periods in the future, so it is not a fixed set of periods.

  Meaning, 0,9,13,14,16 will have other entries added it them?

  Yes ColdCoffee, they may add other adjustment periods in future.

  Thanks.

  What distinguishes Adjustments and Fiscal MOnths ? Just the names?

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  July 17, 2012 at 2:17 pm

  #1513879

  ColdCoffee (7/17/2012)

  ---

  ssc_san (7/17/2012)

  ---

  ColdCoffee (7/17/2012)

  ---

  ssc_san (7/17/2012)

  ---

  We have a issue here, they may add other adjustment periods in the future, so it is not a fixed set of periods.

  Meaning, 0,9,13,14,16 will have other entries added it them?

  Yes ColdCoffee, they may add other adjustment periods in future.

  Thanks.

  What distinguishes Adjustments and Fiscal MOnths ? Just the names?

  adfl (Adjustment flag) is set to true for the adjustment periods.

  Thanks.