sql - 我需要提取最近 30 天的信息

Question

我需要一点帮助我有一个代码可以在最后几天拉出，但我需要它来拉出最后 30 个我的代码如下。

    SELECT
                 c."Id" as 'Customer Id',
                 
                    case
                         when m."T&C Sent"  = 'Yes' then 1
                         else 0
                     end as 'TC Sent',
                 concat('Z', m."Id") as 'Deal Id',
                 c."Customer Name" as 'Contracting Party',
                 c."Company Reg. Number" as 'Company reg number',
                 c."First Name" + ' ' + c."Last Name" as 'Contact Name',
                 c."Email" as 'Contact Email',
                 si."Supplier External Name" AS 'Contracted Supplier',
                 si."Supplier Internal Name" AS 'Supplier Internal Name',
                 
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN cast(m."contract end date elec" + 1 as date)
                         WHEN m."which meter"  = 'Gas' THEN cast(m."contract end date gas" + 1 as date)
                         ELSE NULL
                     END AS 'Contract Start Date',
                 
                    case
                         WHEN m."which meter"  = 'Electricity' THEN cast(m."new contract end date elec" as date)
                         WHEN m."which meter"  = 'Gas' THEN cast(m."new ced gas" as date)
                         ELSE NULL
                     END AS 'Contract End Date',
                 
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN m."mpan bottom line"
                         WHEN m."which meter"  = 'Gas' THEN m."mprn"
                         ELSE NULL
                     END AS 'MPAN/MPR',
                 
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN m."New Day Rate E1"
                         WHEN m."which meter"  = 'Gas' THEN m."New Unit Price/p Gas"
                         ELSE NULL
                     END AS 'Day Unit Rate (p/kwh)',
                 
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN ifnull(m."New Night Rate E1", 'n/a')
                         WHEN m."which meter"  = 'Gas' THEN 'n/a'
                         ELSE NULL
                     END AS 'Night Unit Rate (p/kwh)',
                 
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN ifnull(m."New Eve &Weekend/p", 'n/a')
                         WHEN m."which meter"  = 'Gas' THEN 'n/a'
                         ELSE NULL
                     END AS 'Evening & Weekend Unit Rate (p/kwh)',
                 
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN m."New SC E1 (ppd)"
                         WHEN m."which meter"  = 'Gas' THEN m."New SC Gas (ppd)"
                         ELSE NULL
                     END AS 'Standing Charge',
                 round((
                    CASE
                         WHEN m."which meter"  = 'Electricity' THEN isnull(m."New KVA Charge", 'n/a')
                         WHEN m."which meter"  = 'Gas' THEN 'n/a'
                         ELSE NULL
                     END), 4) AS 'Capacity Charge',
                 isnull(m."KVA Charge Frequency", '') as 'Capacity Charge Freq',
                 
                    case
                         when m."Passthrough"  = 'Yes' then 'Full Pass-through'
                         when m."Passthrough"  = 'No' then 'Fully Fixed'
                         when m."Passthrough"  is null then 'Fully Fixed'
                         else m."Passthrough"
                     end as 'Contract Type',
                 isnull(c."Account Manager", 'the team') as 'Account Manager Name',
                 isnull(u."Phone", '0191 303 7750') as 'Account Manager Phone',
                 
                    case
                         when (left(m."Mpan Top Line", 2))  = '00' then 'HH'
                         when (left(m."Mpan Top Line", 2))  = '01' then 'Multi Rate'
                         when (left(m."Mpan Top Line", 2))  = '02' then 'Domestic'
                         when (left(m."Mpan Top Line", 2))  = '03' then 'Single Rate'
                         when (left(m."Mpan Top Line", 2))  = '04' then 'Multi Rate'
                         when (left(m."Mpan Top Line", 2))  = '05' then 'Max Demand'
                         when (left(m."Mpan Top Line", 2))  = '06' then 'Max Demand'
                         when (left(m."Mpan Top Line", 2))  = '07' then 'Max Demand'
                         when (left(m."Mpan Top Line", 2))  = '08' then 'Max Demand'
                         else null
                     end as 'Elec Profile Summary',
                 tcs."File Name 1" as 'URL 1 Name',
                 tcs."URL 1" as 'URL 1',
                 tcs."File Name 2" as 'URL 2 Name',
                 tcs."URL 2" as 'URL 2',
                 tcs."File Name 3" as 'URL 3 Name',
                 tcs."URL 3" as 'URL 3'
        FROM  meters m
        LEFT JOIN Customers c ON m."Customer Name"  = c."Id" 
        LEFT JOIN contacts ctsm ON m."Contact Name"  = ctsm."Id" 
        LEFT JOIN contacts ctsc ON c."Id"  = ctsc."Customer Name" 
        LEFT JOIN users u ON c."Account Manager"  = u."Full Name" 
        LEFT JOIN "Working Days" wd0 ON wd0."Date"  = business_days(m."Meter Sold Date", Today(), '1,7') 
        LEFT JOIN "Supplier Ts&Cs Code" code ON m."Id"  = code."m.Id" 
        LEFT JOIN "Supplier Info" si ON code."Supplier Id"  = si."SupplierId" 
        LEFT JOIN "Supplier TCs Links" tcs ON tcs."Unique Code"  = code."Code"  
        WHERE    ((cast(m."Meter Sold Date" as date)  = (
            case
                 when wd0."Working Day?"  = 1 then business_completion_day(m."meter Sold Date", 60, '1,7')
                 else Today() -1
             end)
         AND    m."Which Meter"  in ( 'Gas'  , 'Electricity'  )
         AND    m."T&C Sent"  = 'No'
         AND    c."Email"  IS NOT NULL
         AND    c."Customer Name"  IS NOT NULL
         AND    c."First Name"  IS NOT NULL
         AND    c."Last Name"  IS NOT NULL
         AND    si."Supplier External Name"  IS NOT NULL
         AND    si."Supplier Internal Name"  IS NOT NULL))

如果有人可以帮助我，我开始放弃它。这每天都会自动运行，但如果错过了异常，它将不会返回并取消。我需要这个才能回去。

标签： sqlzoho