April 19, 2006 at 8:31 am
I need some help on how to convert the date functions in my script to SQL.
Thanks a bunch.
Mac
SELECT sbh_process_date,
(CASE
WHEN sbh_sys_prin_id = '82231300' THEN '01 Hillsborough'
WHEN sbh_sys_prin_id = '82231400' THEN '02 Manatee'
WHEN sbh_sys_prin_id = '82231500' THEN '03 Tri-County'
WHEN sbh_sys_prin_id IN ('82231700',
'82231800') THEN '04 Pinellas'
ELSE ''
END) AS region,
(CASE
WHEN hse_dwelling_code IN ('AA',
'AB',
'AC',
'AD',
'AE') THEN '01 Residential SFU'
WHEN hse_dwelling_code IN ('BA',
'BB',
'BC',
'BD',
'BE',
'BF',
'BG',
'BH',
'BI',
'BJ',
'BK',
'BL',
'BM',
'BN',
'BO',
'BP',
'BQ') THEN '02 Residential MDU'
WHEN hse_dwelling_code IN ('YB',
'YC',
'YD',
'YI',
'YR',
'YS') THEN '03 Bulk Subservient'
ELSE hse_dwelling_code
END) AS dwellcode,
(CASE
WHEN sbh_basic_service = 1
AND sbh_tier_service = 0
AND sbh_digital_service = 0
AND sbh_rdrnnr_service + sbh_alt_isp_service + sbh_aol_service + sbh_elk_service + sbh_inj_service = 0
AND sbh_digital_phone_service = 0 THEN '01 Basic Customers Only'
WHEN sbh_basic_service = 1
AND sbh_tier_service = 1
AND sbh_digital_service = 0
AND sbh_rdrnnr_service + sbh_alt_isp_service + sbh_aol_service + sbh_elk_service + sbh_inj_service = 0
AND sbh_digital_phone_service = 0 THEN '02 CPST Customers Only'
WHEN sbh_service_string NOT LIKE '%8A%'
AND sbh_basic_service = 1
AND sbh_tier_service = 1
AND sbh_digital_service = 1
AND sbh_rdrnnr_service + sbh_alt_isp_service + sbh_aol_service + sbh_elk_service + sbh_inj_service = 0
AND sbh_digital_phone_service = 0 THEN '03 Digital Customers Only'
WHEN sbh_service_string LIKE '%8A%'
AND sbh_basic_service = 1
AND sbh_tier_service = 1
AND sbh_digital_service = 1
AND sbh_rdrnnr_service + sbh_alt_isp_service + sbh_aol_service + sbh_elk_service + sbh_inj_service = 0
AND sbh_digital_phone_service = 0 THEN '04 Digital Customers with AO Only'
WHEN sbh_basic_service = 1
AND sbh_tier_service = 1
AND sbh_digital_service = 0
AND sbh_rdrnnr_service + sbh_alt_isp_service + sbh_aol_service + sbh_elk_service + sbh_inj_service > 0
AND sbh_digital_phone_service = 0 THEN '05 CPST and HSD Customers Only'
WHEN sbh_basic_service = 1
AND sbh_tier_service = 1
AND sbh_digital_service = 0
AND sbh_rdrnnr_service + sbh_alt_isp_service + sbh_aol_service + sbh_elk_service + sbh_inj_service > 0
AND sbh_digital_phone_service = 1 THEN '06 CPST and HSD and DP Customers Only'
WHEN sbh_digital_phone_service = 1 THEN '07 Phone Customers Only'
ELSE 'Z_Unidentied'
END) AS subgrouping,
Count(DISTINCT sbh_subscriber_number) AS subcount,
Sum((CASE
WHEN job_job_type = 'VD'
AND job_status = 'C'
AND job_process_date BETWEEN sbh_process_date
AND Dateadd(mm,1,sbh_process_date) THEN 1
ELSE 0
END)) AS vd,
Sum((CASE
WHEN job_job_type = 'NP'
AND job_status = 'C'
AND job_process_date BETWEEN sbh_process_date
AND Dateadd(mm,1,sbh_process_date) THEN 1
ELSE 0
END)) AS np,
vd + np AS discototal
FROM subscriber_history,
house,
job
WHERE sbh_sys_prin_id LIKE '82231%'
AND sbh_process_date IN (Ymd(Year(Getdate()),Month(Getdate()),21),
Dateadd(mm,-1,Ymd(Year(Getdate()),Month(Getdate()),21)),
Dateadd(mm,-2,Ymd(Year(Getdate()),Month(Getdate()),21)),
Dateadd(mm,-3,Ymd(Year(Getdate()),Month(Getdate()),21)))
AND sbh_house_number = hse_house_number
AND sbh_subscriber_number *= job_subscriber_number
AND job_process_date > sbh_process_date
AND hse_dwelling_code IN ('AA',
'AB',
'AC',
'AD',
'AE',
'BA',
'BB',
'BC',
'BD',
'BE',
'BF',
'BG',
'BH',
'BI',
'BJ',
'BK',
'BL',
'BM',
'BN',
'BO',
'BP',
'BQ',
'YB',
'YC',
'YD',
'YI',
'YR',
'YS')
AND subgrouping <> 'Z_Unidentied'
GROUP BY sbh_process_date,
region,
dwellcode,
subgrouping
April 19, 2006 at 8:48 am
Since these will be static when this runs, I'd calcualte them and store the values in variables and then include the variables in this query.
select @a = getdate()
select * from mytable where invoicedate in (@a)
If you are worried about formatting, what are you trying to achieve?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply