How do I customize the application to format the following code.

SELECT TbChangeRequest.RequestedDate, Sum(IIf([FkStatus]<>2 And RequestedDate=Date()-1,1,0)) AS [Opened yesterday], Sum(IIf([FkStatus]=2 And RequestedDate=Date()-1,1,0)) AS [Close yesterday], Sum(IIf([FkStatus]<>2 And RequestedDate=Date(),1,0)) AS [Opened today], Sum(IIf([FkStatus]=4,1,0)) AS [Need More Information], Sum(IIf([FkStatus]=1,1,0)) AS [Pending ECN], Sum(IIf([FkStatus]=3,1,0)) AS [Pending-Parts Split]
FROM TbChangeRequest
GROUP BY TbChangeRequest.RequestedDate;


Here the best I can do:

SELECT TbChangeRequest.RequestedDate,
SUM(
IIF([FkStatus]<>2
AND RequestedDate=DATE()-1,1,0)) AS [Opened yesterday],
SUM(
IIF([FkStatus]=2
AND RequestedDate=DATE()-1,1,0)) AS [Close yesterday],
SUM(
IIF([FkStatus]<>2
AND RequestedDate=DATE(),1,0)) AS [Opened today],
SUM(
IIF([FkStatus]=4,1,0)) AS [Need More Information],
SUM(
IIF([FkStatus]=1,1,0)) AS [Pending ECN],
SUM(
IIF([FkStatus]=3,1,0)) AS [Pending-Parts Split]
FROM TbChangeRequest
GROUP BY TbChangeRequest.RequestedDate;

Here what I would like:

SELECT TbChangeRequest.RequestedDate,
SUM(IIF([FkStatus] <> 2 AND RequestedDate = DATE()-1, 1, 0)) AS [Opened yesterday],
SUM(IIF([FkStatus] = 2 AND RequestedDate = DATE()-1, 1, 0)) AS [Close yesterday],
SUM(IIF([FkStatus] <> 2 AND RequestedDate = DATE(), 1, 0)) AS [Opened today],
SUM(IIF([FkStatus] = 4, 1, 0)) AS [Need More Information],
SUM(IIF([FkStatus] = 1, 1, 0)) AS [Pending ECN],
SUM(IIF([FkStatus] = 3, 1, 0)) AS [Pending-Parts Split]
FROM TbChangeRequest
GROUP BY TbChangeRequest.RequestedDate;

Thanks



Edited 3 time(s). Last edit at 06/23/2011 05:32PM by guidomarcel.

Hi TazQuebec,
thanks for your feedback.
Here is the best I can do by switching off the linebreaks for conditions:

SELECT   TbChangeRequest.RequestedDate,
         SUM(
         IIF([FkStatus]<>2 AND RequestedDate=DATE()-1,1,0)) AS [Opened yesterday],
         SUM(
         IIF([FkStatus]=2 AND RequestedDate=DATE()-1,1,0)) AS [Close yesterday],
         SUM(
         IIF([FkStatus]<>2 AND RequestedDate=DATE(),1,0)) AS [Opened today],
         SUM(
         IIF([FkStatus]=4,1,0)) AS [Need More Information],
         SUM(
         IIF([FkStatus]=1,1,0)) AS [Pending ECN],
         SUM(
         IIF([FkStatus]=3,1,0)) AS [Pending-Parts Split]
FROM     TbChangeRequest
GROUP BY TbChangeRequest.RequestedDate;

I still need to improve this, i.e. to avoid a linvtreak before "IIF" inside scalar functions. Do you agree?

Regards
Guido

Yes that be great.

Thanks

P.S. I like your utility.

Hi TazQuebec,
I will try to include this feature in the 2011 February Release
Regards
Guido

Hi, with current formatting options and switching-off linebreaks before AND keyword, the following format can be achieved:

SELECT   TbChangeRequest.RequestedDate                                                     ,
         SUM( IIF([FkStatus]<>2 AND RequestedDate=DATE()-1,1,0)) AS [Opened yesterday]     ,
         SUM( IIF([FkStatus] =2 AND RequestedDate=DATE()-1,1,0)) AS [Close yesterday]      ,
         SUM( IIF([FkStatus]<>2 AND RequestedDate=DATE(),1,0))   AS [Opened today]         ,
         SUM( IIF([FkStatus] =4,1,0))                            AS [Need More Information],
         SUM( IIF([FkStatus] =1,1,0))                            AS [Pending ECN]          ,
         SUM( IIF([FkStatus] =3,1,0))                            AS [Pending-Parts Split]
FROM     TbChangeRequest
GROUP BY TbChangeRequest.RequestedDate;

Best Regards
Guido

What is "current formatting"?

Here what I got now.

SELECT   TbChangeRequest.RequestedDate,
         SUM(
         IIF([FkStatus] <> 2 AND RequestedDate = DATE()-1, 1, 0)) AS [Opened yesterday],
         SUM(
         IIF([FkStatus] = 2 AND RequestedDate = DATE()-1, 1, 0)) AS [Close yesterday],
         SUM(
         IIF([FkStatus] <> 2 AND RequestedDate = DATE(), 1, 0)) AS [Opened today],
         SUM(
         IIF([FkStatus] = 4, 1, 0)) AS [Need More Information],
         SUM(
         IIF([FkStatus] = 1, 1, 0)) AS [Pending ECN],
         SUM(
         IIF([FkStatus] = 3, 1, 0)) AS [Pending-Parts Split]
FROM     TbChangeRequest
WHERE    TbChangeRequest.RequestedDate >= #2011-08 - 01# AND TbChangeRequest.RequestedDate <= #2011-08 - 31#
GROUP BY TbChangeRequest.RequestedDate;

The AND behave the same inside the IIF and in the WHERE

Here what I would like.

SELECT   TbChangeRequest.RequestedDate,
         SUM(IIF([FkStatus] <> 2 AND RequestedDate = DATE()-1, 1, 0)) AS [Opened yesterday]     ,
         SUM(IIF([FkStatus] =  2 AND RequestedDate = DATE()-1, 1, 0)) AS [Close yesterday]      ,
         SUM(IIF([FkStatus] <> 2 AND RequestedDate = DATE()  , 1, 0)) AS [Opened today]         ,
         SUM(IIF([FkStatus] =  4, 1, 0))                              AS [Need More Information],
         SUM(IIF([FkStatus] =  1, 1, 0))                              AS [Pending ECN]          ,
         SUM(IIF([FkStatus] =  3, 1, 0))                              AS [Pending-Parts Split]
FROM     TbChangeRequest
WHERE    TbChangeRequest.RequestedDate >= #2011-08-01#
AND      TbChangeRequest.RequestedDate <= #2011-08-31#
GROUP BY TbChangeRequest.RequestedDate;

Thanks all for your help

Hi,
did you use the most recent release "June 2011"?
With "current formatting options" I meant the default options.
you are right, the AND in the WHERE conditions behave exactly in the same way as the AND in the IIF clause. To separate this, I would need to add options specifically for the IIF-statement. Will put this to TODO-list.

Best Regards
Guido

When I format my SQL string with the June version here the result

SELECT
           IIF(TbTransit.FkCategory = 2,
                      IIF([COUNTRY_CODE] IN('CA'                     ,
                                            'US'                     ,
                                            'MX'), 5, 6), 7) AS Ligne,
           SUM(TbTransit.BO_Qty * TbTransit.[UnitPrice])     AS [Value]
FROM       TbTransit
INNER JOIN MD_SRC_LFA1
ON         TbTransit.Vendor = MD_SRC_LFA1.VENDOR_NUMBER
WHERE      TbTransit.PkTransit IN(SELECT    TbTransitDtl.FkTransit
           FROM       TbSemaine
           INNER JOIN TbTransitDtl
           ON         TbSemaine.PkSemaine = TbTransitDtl.FkSemaine
           WHERE      TbSemaine.Annee     = 2011
           AND        TbSemaine.Mois      = 5)
GROUP BY
           IIF(TbTransit.FkCategory = 2,
                      IIF([COUNTRY_CODE] IN('CA',
                                            'US',
                                            'MX'), 5, 6), 7)
ORDER BY
           IIF(TbTransit.FkCategory = 2,
                      IIF([COUNTRY_CODE] IN('CA',
                                            'US',
                                            'MX'), 5, 6), 7) ;

Here what I would like

SELECT     IIF(TbTransit.FkCategory = 2, IIF([COUNTRY_CODE] IN('CA', 'US', 'MX'), 5, 6), 7) AS Ligne,
           SUM(TbTransit.BO_Qty * TbTransit.[UnitPrice])     AS [Value]
FROM       TbTransit
INNER JOIN MD_SRC_LFA1
ON         TbTransit.Vendor = MD_SRC_LFA1.VENDOR_NUMBER
WHERE      TbTransit.PkTransit IN(SELECT    TbTransitDtl.FkTransit
           FROM       TbSemaine
           INNER JOIN TbTransitDtl
           ON         TbSemaine.PkSemaine = TbTransitDtl.FkSemaine
           WHERE      TbSemaine.Annee     = 2011
           AND        TbSemaine.Mois      = 5)
GROUP BY   IIF(TbTransit.FkCategory = 2, IIF([COUNTRY_CODE] IN('CA', 'US', 'MX'), 5, 6), 7)
ORDER BY   IIF(TbTransit.FkCategory = 2, IIF([COUNTRY_CODE] IN('CA', 'US', 'MX'), 5, 6), 7) ;

Can it be done or come close to that?

Thanks

Hi TazQuebec,
I merged your 2 threads in one thread as they are about the same subject. Hope it is OK for you.
Regards
Guido