March 11, 2015 at 7:06 am
Hi Folks,
I need help on how to solve this in SQL.
I have incorrect result when I have my period set at '201501' or any period that starts with 01.
The @GroupSize is currently set at 3 meaning quarter. I would like to return data for the period 201411, 201412 and 201501. Running the SQL shows a From of 201499 instead of 201411. Likewise, when I set @Groupsize to be 6 meaning half year and period set at 201501. I would like my from to show data from 201408.
Thanks.
EO
declare @open_period_name varchar (20)
set @open_period_name = '201501 May 2014'
declare @grp varchar (50)
set @grp = 'Birmingham'
declare @GroupSize int
set @GroupSize = 3
SELECTpe.period,c.prof_ctr_desc,p.employee_name, m.matter_name,o.offc_desc, left(@open_period_name, 6) - @GroupSize + 1 as From_, left(@open_period_name, 6)as To_, *
FROMbo_live2.dbo.hbm_matter m
LEFT JOIN bo_live2.dbo.glm_period_end pe ON DATEADD(dd,0,DATEDIFF(dd,0,m.open_date)) BETWEEN pe.per_begin_date AND pe.per_end_date
LEFT JOIN bo_live2.dbo.HBL_Office o ON m.offc = o.offc_code
LEFT JOIN bo_live2.dbo.HBM_Persnl p ON m.resp_empl_uno = p.empl_uno
LEFT JOIN bo_live2.dbo.hbl_prof_ctr c ON c.prof_ctr_code = m.prof
WHEREpe.period between left(@open_period_name, 6) - @GroupSize + 1 and left(@open_period_name, 6)
and (
@grp = o.offc_desc
OR @grp = c.prof_ctr_desc
OR @grp = p.employee_name
)
AND m.matter_code like '%[^0-9]%'
AND m.inactive = 'N'
ORDER BY 2
March 11, 2015 at 11:16 am
To get help faster include some DDL (note the link in my signature).
In the meantime I would suggest that you avoid using ORDER BY <number> and use ORDER BY <column name> instead. E.g. in your posted query you should do:
ORDER BY c.prof
-- Itzik Ben-Gan 2001
March 11, 2015 at 6:56 pm
You can get the months you need using a Tally or Calendar table.
declare @open_period_name varchar (20)
set @open_period_name = '201501 May 2014';
declare @GroupSize int
set @GroupSize = 3;
WITH Tally (n) AS
(
SELECT TOP (@GroupSize) 1-ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n)
)
SELECT n, DATEADD(month, n, LEFT(@open_period_name, 6)+'01')
,CONVERT(VARCHAR(6), DATEADD(month, n, LEFT(@open_period_name, 6)+'01'), 112)
FROM Tally;
The above will work for @GroupSize up to 12.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2015 at 11:43 am
Hi All,
Thanks for your responses. I figured it out when I was playing with it yesterday. This is the code that gave me exactly what I was looking for.
declare @open_period_name varchar(20)
set @open_period_name = 201510
declare @GroupSize int
set @Groupsize = 6
declare @grp varchar (10)
set @grp = 'London'
declare @Date varchar(2)
set @Date = 'oD'
declare @Matter_code nvarchar (14)
set @Matter_code = 'bky'
SELECTpe.period,c.prof_ctr_desc,p.employee_name, substring(m.matter_name,6, 20)as matter_name,z.client_name,m.matter_uno,m.clnt_matt_code, m.matter_code, m.open_date, m._appointment_date, o.offc_desc,
case when right(left(@open_period_name, 6) - @GroupSize + 1, 2) NOT BETWEEN 1 AND 12
THEN left(@open_period_name, 6) - @GroupSize + 1 - 88
ELSE left(@open_period_name, 6) - @GroupSize + 1
END as From_, left(@open_period_name, 6)as To_
FROMbo_live2.dbo.hbm_matter m
LEFT JOIN bo_live2.dbo.glm_period_end pe ON CASE @Date WHEN 'AD' THEN DATEADD(dd,0,DATEDIFF(dd,0,m._appointment_date))
WHEN 'OD' THEN DATEADD(dd,0,DATEDIFF(dd,0,m.open_date)) END
BETWEEN pe.per_begin_date AND per_end_date
LEFT JOIN bo_live2.dbo.HBL_Office o ON m.offc = o.offc_code
LEFT JOIN bo_live2.dbo.HBM_Persnl p ON m.resp_empl_uno = p.empl_uno
LEFT JOIN bo_live2.dbo.hbl_prof_ctr c ON c.prof_ctr_code = m.prof
Left join hbm_client z on z.client_uno = m.client_uno
WHEREpe.period between case when right(left(@open_period_name, 6) - @GroupSize + 1, 2) NOT BETWEEN 1 AND 12
THEN left(@open_period_name, 6) - @GroupSize + 1 - 88
ELSE left(@open_period_name, 6) - @GroupSize + 1
END
and left(@open_period_name, 6)
and (
@grp = o.offc_desc
OR @grp = c.prof_ctr_desc
OR @grp = p.employee_name
)
AND m.matter_code like '%[^0-9]%'
AND m.inactive = 'N'
AND (m.matter_code)IN (@Matter_code)
ORDER BY 4
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply