Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 10th, 2012  |  syntax: None  |  size: 9.03 KB  |  hits: 10  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. T-SQL Case Statement confusion
  2. SELECT CASE
  3.   WHEN bitdelivered = 1 THEN
  4.     '' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)
  5.     + '                '
  6.     + 'þ' + '
  7. ' -- this is a carriage return, do not remove it
  8.   WHEN bitdelivered = 0 AND deliv.dtmdeliverydate < Getdate() THEN
  9.     '' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)
  10.     + '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
  11.     + '&#168;' + '
  12. '
  13.   ELSE
  14.     '' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)
  15.     + '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
  16.     + '&#168;' + '
  17. '
  18. END
  19.        
  20. select
  21. CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
  22. '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' +
  23. CASE
  24.     WHEN bitdelivered = 1 THEN '&#254;'
  25.     ELSE '&#168;'
  26. END + '
  27. '
  28.        
  29. SELECT dbo.tblrequirements.guidrequirementid,
  30.        tblcontracting.strdescription                             AS
  31.        strcontracting,
  32.        dbo.viewawardnumbers.strcontractnumber                    AS
  33.        strawardnumber,
  34.        Ltrim(Rtrim(dbo.viewawardnumbers.strtonumber))            AS strtonumber,
  35.        dbo.tblrequirements.strdescription,
  36.        dbo.tblezquerycontractvalue.curtotalvalue,
  37.        dbo.tblcodescontractvehicles.strdescription               AS
  38.        strcontractvehicle,
  39.        tblcustomer.stracronym                                    AS strcustomer,
  40.        dbo.tblcodesrequirementstatuses.strdescription            AS strstatus,
  41.        Substring(dbo.tblrequirements.strnotes, 0, 512)           AS strnotes,
  42.        Coalesce (dbo.tblrequirements.guidfromid,
  43.        '00000000-0000-0000-0000-000000000000'
  44.        )                                                         AS guidfromid,
  45.        Coalesce (dbo.viewteammembers.guidpersonid,
  46.        '00000000-0000-0000-0000-000000000000')                   AS guidpersonid
  47.        ,
  48.        dbo.viewcurrentoptions.dtmstart,
  49.        dbo.viewcurrentoptions.dtmend,
  50.        Dateadd(d, -60, dbo.viewcurrentoptions.dtmend)            AS
  51.        dtm1stnoticedue,
  52.        Dateadd(d, -30, dbo.viewcurrentoptions.dtmend)            AS
  53.        dtm2ndnoticedue,
  54.        dbo.tblcontractdates.dtmcontractstart,
  55.        dbo.tblcontractdates.dtmcontractend,
  56.        Isnull(dbo.viewteammembers.strshortname, ' Not Assigned') AS strshortname
  57.        ,
  58.        dbo.tblezqueryfunding.curtotalfunded,
  59.        CASE
  60.          WHEN ( dbo.tblcodesrequirementstatuses.strdescription =
  61.                 'Pre-Solicitation'
  62.                  OR dbo.tblcodesrequirementstatuses.strdescription =
  63.                     'Solicitation'
  64.                  OR dbo.tblcodesrequirementstatuses.strdescription =
  65.                     'Source Selection'
  66.               ) THEN 'P'
  67.          WHEN ( Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'IT'
  68.                 AND Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'OPS'
  69.               ) THEN
  70.          'S'
  71.          ELSE 'C'
  72.        END                                                       AS strproctype,
  73.        dbo.tblcodesprocurementtypes.strcode,
  74.        dbo.tblcodesprocurementtypes.strdescription               AS strprocdesc,
  75.        deliveries.dtmdeliverydate,
  76.        deliveries.bitdelivered,
  77.        CASE
  78.          WHEN ( dbo.tblcodesrequirementstatuses.strdescription =
  79.                 'Pre-Solicitation'
  80.                  OR dbo.tblcodesrequirementstatuses.strdescription =
  81.                     'Solicitation'
  82.                  OR dbo.tblcodesrequirementstatuses.strdescription =
  83.                     'Source Selection'
  84.               ) THEN 'Initial Start Date'
  85.          WHEN ( Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'IT'
  86.                 AND Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'OPS'
  87.               ) THEN
  88.          'POP'
  89.          ELSE 'Delivery Date'
  90.        END                                                       AS
  91.        colpopdelivdt,
  92.        CASE
  93.          WHEN ( dbo.tblcodesrequirementstatuses.strdescription =
  94.                 'Pre-Solicitation'
  95.                  OR dbo.tblcodesrequirementstatuses.strdescription =
  96.                     'Solicitation'
  97.                  OR dbo.tblcodesrequirementstatuses.strdescription =
  98.                     'Source Selection'
  99.               ) THEN 'Req Award Date'
  100.          WHEN ( Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'IT'
  101.                 AND Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'OPS'
  102.               ) THEN
  103.          'Option Notice'
  104.          ELSE 'Delivered'
  105.        END                                                       AS
  106.        coloptnotdeliv,
  107.        tblpresolicitations.dtmcontacted,
  108.        tblpresolicitations.dtmrequiredby,
  109.        tblpresolicitations.dtmawardnotice,
  110.        strjobid,
  111.        bitprimary
  112. FROM   dbo.tblrequirements
  113.        INNER JOIN dbo.tblcodesrequirementstatuses
  114.          ON dbo.tblcodesrequirementstatuses.strcode =
  115.             dbo.tblrequirements.strstatusid
  116.        LEFT OUTER JOIN dbo.tblezquerycontractvalue
  117.          ON dbo.tblezquerycontractvalue.guidrequirementid =
  118.             dbo.tblrequirements.guidrequirementid
  119.        LEFT OUTER JOIN dbo.viewawardnumbers
  120.          ON dbo.viewawardnumbers.guidrequirementid =
  121.             dbo.tblrequirements.guidrequirementid
  122.        LEFT OUTER JOIN dbo.tblezqueryfunding
  123.          ON dbo.tblezqueryfunding.strawardnumber =
  124.             dbo.viewawardnumbers.strawardnumber
  125.        LEFT OUTER JOIN dbo.viewcurrentoptions
  126.          ON dbo.tblrequirements.guidrequirementid =
  127.             dbo.viewcurrentoptions.guidrequirementid
  128.        LEFT OUTER JOIN dbo.viewteammembers
  129.          ON dbo.viewteammembers.guidrequirementid =
  130.             dbo.tblrequirements.guidrequirementid
  131.        LEFT OUTER JOIN dbo.tblcontracts
  132.          ON dbo.tblcontracts.guidrequirementid =
  133.             dbo.tblrequirements.guidrequirementid
  134.        LEFT OUTER JOIN dbo.tblcontractdates
  135.          ON dbo.tblcontractdates.guidrequirementid =
  136.             dbo.tblrequirements.guidrequirementid
  137.        LEFT OUTER JOIN dbo.tblcodescontractvehicles
  138.          ON dbo.tblcodescontractvehicles.strcode = dbo.tblcontracts.strvehicleid
  139.        LEFT OUTER JOIN dbo.tblorganizations AS tblcontracting
  140.          ON dbo.tblrequirements.guidfromid = tblcontracting.guidorgid
  141.        LEFT OUTER JOIN dbo.tblorganizations AS tblcustomer
  142.          ON dbo.tblrequirements.guidtoid = tblcustomer.guidorgid
  143.        LEFT OUTER JOIN dbo.tblpresolicitations
  144.          ON dbo.tblpresolicitations.guidrequirementid =
  145.             dbo.tblrequirements.guidrequirementid
  146.        LEFT OUTER JOIN dbo.tblcodesprocurementtypes
  147.          ON dbo.tblcodesprocurementtypes.strcode =
  148.             dbo.tblpresolicitations.strprocurementtypeid
  149.        LEFT OUTER JOIN (SELECT guidrequirementid,
  150.                                REPLACE(REPLACE(REPLACE (
  151. (select
  152. ISNULL(CONVERT(CHAR(10), deliv.dtmdeliverydate, 101), '(no date)') +
  153. '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' +
  154. CASE
  155.     WHEN bitdelivered = 1 THEN '&#254;'
  156.     ELSE '&#168;'
  157. END + '
  158. '
  159.  
  160.  
  161.  
  162.                                        /*(SELECT CASE
  163.                                                  WHEN bitdelivered = 1
  164.                                                THEN
  165.                                                  ''
  166.                                                  +
  167.                                                  CONVERT(CHAR(10),
  168.        deliv.dtmdeliverydate,
  169.        101) +
  170. '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
  171. + '&#254;' + '
  172.  
  173. '
  174. WHEN bitdelivered = 0
  175. AND deliv.dtmdeliverydate < Getdate() THEN
  176. '' +
  177. CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
  178. '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
  179. + '&#168;' + '
  180.  
  181. '
  182. ELSE
  183. '' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
  184. '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
  185. + '&#168;' + '
  186.  
  187. '
  188. END*/
  189. FROM   tblclins clins
  190. INNER JOIN tblcommodities commod
  191. ON commod.guidclinid = clins.guidclinid
  192. INNER JOIN tbldeliveries deliv
  193. ON deliv.guidcommodityid = commod.guidcommodityid
  194. WHERE  clins.guidrequirementid = req.guidrequirementid
  195. AND deliv.bitdelivered = 0
  196. ORDER  BY guidrequirementid,
  197. deliv.dtmdeliverydate
  198. FOR XML PATH('')), '<', '<'), '>', '>'), '&', '&') AS dtmdeliverydate
  199. ,
  200. Stuff ((SELECT '|' + CAST(bitdelivered AS CHAR(1))
  201. FROM   tblclins clins
  202. INNER JOIN tblcommodities commod
  203. ON commod.guidclinid = clins.guidclinid
  204. INNER JOIN tbldeliveries deliv
  205. ON deliv.guidcommodityid = commod.guidcommodityid
  206. WHERE  clins.guidrequirementid = req.guidrequirementid
  207. ORDER  BY guidrequirementid
  208. FOR XML PATH('')), 1, 1, '')                          AS bitdelivered
  209. FROM   tblrequirements req
  210. GROUP  BY guidrequirementid) deliveries
  211.   ON deliveries.guidrequirementid = dbo.tblrequirements.guidrequirementid
  212.        
  213. WHEN bitdelivered = 1
  214.        
  215. ISNULL(CONVERT(CHAR(10), deliv.dtmdeliverydate, 101), '(no date)') +
  216. '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' +
  217. CASE
  218.     WHEN bitdelivered = 1 THEN '&#254;'
  219.     ELSE '&#168;'
  220. END + '
  221. '
create a new version of this paste RAW Paste Data