0

I am looking for the best table structure to create a dynamic SQL statement like this below (which is not dynamic yet). I have to choose between

  1. joined tables
  2. a single row with all columns with the content comma-delimted which I then will parse
  3. one large table with multiple rows per Cost Centre Activity Code
  4. or anything else

In this example the key which all link to is: 'NSEA8102' which is a Cost Centre Activity code

SELECT
    @pDate,
    @pDate,
    'NSEA8102',   --Cost Centre Activity Code  
    ccg.tCCGroup, 
    SUM(logs.tTripHours) AS tTriphours,
    'Actual EMV Hours Worked - ' + DATENAME(MONTH,@pDATE) + ' ' + CAST(YEAR(@pDate) AS CHAR(4))
FROM dbo.tblEMV_Logsheet AS logs INNER JOIN
dbo.tblLookup_EMVEquipment AS ccg ON logs.tEquipmentKey = ccg.tEquipmentKey
WHERE tDate BETWEEN @BMonth and @EMonth
    AND (logs.tAreaCode in ('MINEE', 'SERVICE'))
    AND (logs.tEventCode LIKE 'RASSTEEPS')
    AND logs.tSourceLocationCode = 'STEEPS'
    AND logs.tDestinationLocationCode = 'ERASSTSP'
    AND (ccg.tCCGroup IN ('FADT', 'FPC800', 'FWA800'))  
    AND ccg.tValid = 1  
GROUP BY ccg.tCCGroup

Any suggestion would be welcome. Thanks

Pierre Cornelissen
  • 11
  • 5
  • what is the expected result? i.e. not the query above, but the goal? why does it have to be dynamic sql? what alternatives to dynamic sql have you considered? – Paul Maxwell Jul 08 '14 at 08:41
  • Thanks. My question was premature. See this [link](http://stackoverflow.com/questions/24693119/where-field1-in-null) – Pierre Cornelissen Jul 11 '14 at 08:32

0 Answers0