Scripting/logic help

• Atyeo9

  Valued Member

  Points: 54

  More actions

  March 20, 2018 at 10:14 am

  #327984

  My head is well and truly mashed and I could do with some help please...
  I need to write a script to count the number of records opened and closed in certain dates.  This will require a pivot eventually but essentially we're looking at two tables - one has the open date, the other the closed date.  Both have a matching id.  So far so good.  Where I'm struggling is that I need to do a monthly breakdown that needs to be dynamic and increase each month to show the number of records closed in a given month number for a given month.   I'm having difficulty even explaining it let alone coding it!

  Example: 
  10 cases are opened in January
  12 in Feb
  3 in March

  Of the 10 opened in January, 2 are closed in January (Month 1), 3 are closed in February (Month 2) and 5 are closed in March (Month 3)
  Of the 12 opened in February, 7 are closed in February (Month 1), 1 in March (Month 2)
  Of the 3 opened in March, 1 is closed in March (Month 1)

  This would therefore be reflected something like:
  
  Each month of the report another month is added to row and column.
  Just to make things simple the figure actually needs to be percentage rather than number, but baby steps!

  Thanks for any pointers....

• x

  SSC-Insane

  Points: 23660

  More actions

  March 20, 2018 at 11:52 am

  #1984011

  Atyeo9 - Tuesday, March 20, 2018 10:14 AM

  My head is well and truly mashed and I could do with some help please...
  I need to write a script to count the number of records opened and closed in certain dates.  This will require a pivot eventually but essentially we're looking at two tables - one has the open date, the other the closed date.  Both have a matching id.  So far so good.  Where I'm struggling is that I need to do a monthly breakdown that needs to be dynamic and increase each month to show the number of records closed in a given month number for a given month.   I'm having difficulty even explaining it let alone coding it!

  Example: 
  10 cases are opened in January
  12 in Feb
  3 in March

  Of the 10 opened in January, 2 are closed in January (Month 1), 3 are closed in February (Month 2) and 5 are closed in March (Month 3)
  Of the 12 opened in February, 7 are closed in February (Month 1), 1 in March (Month 2)
  Of the 3 opened in March, 1 is closed in March (Month 1)

  This would therefore be reflected something like:
  
  Each month of the report another month is added to row and column.
  Just to make things simple the figure actually needs to be percentage rather than number, but baby steps!

  Thanks for any pointers....

  I'd probably generate criteria for the entire year and then just leave some months invisible in the actual report. When generating the criteria, I'd include the rows and columns they apply to.

• x

  SSC-Insane

  Points: 23660

  More actions

  March 20, 2018 at 2:17 pm

  #1984041

  patrickmcginnis59 10839 - Tuesday, March 20, 2018 11:52 AM

  Atyeo9 - Tuesday, March 20, 2018 10:14 AM

  My head is well and truly mashed and I could do with some help please...
  I need to write a script to count the number of records opened and closed in certain dates.  This will require a pivot eventually but essentially we're looking at two tables - one has the open date, the other the closed date.  Both have a matching id.  So far so good.  Where I'm struggling is that I need to do a monthly breakdown that needs to be dynamic and increase each month to show the number of records closed in a given month number for a given month.   I'm having difficulty even explaining it let alone coding it!

  Example: 
  10 cases are opened in January
  12 in Feb
  3 in March

  Of the 10 opened in January, 2 are closed in January (Month 1), 3 are closed in February (Month 2) and 5 are closed in March (Month 3)
  Of the 12 opened in February, 7 are closed in February (Month 1), 1 in March (Month 2)
  Of the 3 opened in March, 1 is closed in March (Month 1)

  This would therefore be reflected something like:
  
  Each month of the report another month is added to row and column.
  Just to make things simple the figure actually needs to be percentage rather than number, but baby steps!

  Thanks for any pointers....

  I'd probably generate criteria for the entire year and then just leave some months invisible in the actual report. When generating the criteria, I'd include the rows and columns they apply to.

  Example of creating criteria for instance, first create an index for the rows and columns, then attach attributes to them that could be parameters for gathering the actual totals to match up with it:

  
WITH MonthPtr (OFFST) AS (
SELECT 0 OFFST UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
)
,
MonthMatrix (MonthLine, MonthCol) AS
(
SELECT C.OFFST MonthCol, R.OFFST MonthRow FROM MonthPtr C CROSS JOIN MonthPtr R
)
,
DateStartDat AS (SELECT CONVERT(DATETIME,'2018-01-01') DateStart)
,
MonthActualStart (MonthLine, MonthCol, OpenStart, CloseStart)
AS
(
SELECT MonthLine, MonthCol, DATEADD(MM,MonthLine,DateStart) OpenStart, DATEADD(MM,MonthLine+MonthCol,DateStart) CloseStart FROM MonthMatrix CROSS JOIN DateStartDat
)
,
MonthActualSelectCalc (MonthLine, MonthCol, OpenStart, OpenEnd, CloseStart, CloseEnd)
AS
(
SELECT MonthLine, MonthCol,OpenStart, DATEADD(DD,-1,DATEADD(MM,1,OpenStart)) OpenEnd, CloseStart, DATEADD(DD,-1,DATEADD(MM,1,CloseStart)) CloseEnd
FROM MonthActualStart
)
,
MonthActualSelect (MonthLine, LabelLine, MonthCol, LabelCol, OpenStart, OpenEnd, CloseStart, CloseEnd)
AS
(
SELECT MonthLine, DATENAME(MM,OpenStart), MonthCol, DATENAME(MM,CloseStart), OpenStart, OpenEnd, CloseStart, CloseEnd
FROM MonthActualSelectCalc
-- USE THIS CRITERIA TO KEEP IT ALL WITHIN A YEAR IF NEEDED
WHERE DATEPART(YY,OpenStart) = DATEPART(YY,CloseStart)
)
-- THEN USE THESE COLUMNS TO MATCH THE LINES AND COLUMNS TO ACTUAL CRITERIA TO SELECT YOUR DATA
-- OBVIOUSLY LABEL COL MIGHT NOT BE NEEDED I GUESS
-- ALSO I ADMIT TO USING ZERO'S ORIGIN TO SPECIFY LINES AND COLUMNS LOL
SELECT * FROM MonthActualSelect
ORDER BY MonthLine, MonthCol


• Jeff Moden

  SSC Guru

  Points: 1004372

  More actions

  March 20, 2018 at 3:28 pm

  #1984068

  Atyeo9 - Tuesday, March 20, 2018 10:14 AM

  My head is well and truly mashed and I could do with some help please...
  I need to write a script to count the number of records opened and closed in certain dates.  This will require a pivot eventually but essentially we're looking at two tables - one has the open date, the other the closed date.  Both have a matching id.  So far so good.  Where I'm struggling is that I need to do a monthly breakdown that needs to be dynamic and increase each month to show the number of records closed in a given month number for a given month.   I'm having difficulty even explaining it let alone coding it!

  Example: 
  10 cases are opened in January
  12 in Feb
  3 in March

  Of the 10 opened in January, 2 are closed in January (Month 1), 3 are closed in February (Month 2) and 5 are closed in March (Month 3)
  Of the 12 opened in February, 7 are closed in February (Month 1), 1 in March (Month 2)
  Of the 3 opened in March, 1 is closed in March (Month 1)

  This would therefore be reflected something like:
  
  Each month of the report another month is added to row and column.
  Just to make things simple the figure actually needs to be percentage rather than number, but baby steps!

  Thanks for any pointers....

  It's pretty simple.  Calculate your stuff vertically, as normal.  Then do a "Dynamic Crosstab".  Please see the following article for details. 
  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)