October 29, 2013 at 4:34 am
Hi,
Since pivot time taking so, Is there any alternate to the pivot function. If there is any then please me on this with example.
below is my query
DECLARE @fiscal_yearstart DATETIME,
@fiscal_yearend DATETIME,
@fiscalyear VARCHAR(50),
@curr_year VARCHAR(20),
@next_Year VARCHAR(10),
@projecttyp VARCHAR(300),
@projecttyp1 VARCHAR(300)
SET @fiscalyear = 'Curr and Next'
SET @projecttyp1= 'LSS Project'
SET @projecttyp = 'JDI Project'
SET @curr_year = YEAR(GETDATE())
SET @next_Year = YEAR(GETDATE()) + 1
IF @fiscalyear = 'Cur'
BEGIN
SET @fiscal_yearstart = CASE WHEN MONTH(GETDATE()) > 9
THEN CONVERT(DATETIME,'01/10/'+ CAST(@curr_year AS VARCHAR(4)),103)
ELSE CONVERT(DATETIME,'01/10/'+ CAST(@curr_year - 1 AS VARCHAR(4)),103)
END
SET @fiscal_yearend = CASE WHEN MONTH(GETDATE()) > 9
THEN CONVERT(DATETIME,'30/09/'+ CAST(@curr_year + 1 AS VARCHAR(4)),103)
ELSE CONVERT(DATETIME,'30/09/'+ CAST(@curr_year AS VARCHAR(4)),103)
END
END
IF @fiscalyear = 'Next'
BEGIN
SET @fiscal_yearstart = CASE WHEN MONTH(GETDATE()) > 9
THEN CONVERT(DATETIME,'01/10/'+ CAST(@next_Year AS VARCHAR(4)),103)
ELSE CONVERT(DATETIME,'01/10/'+ CAST(@next_Year - 1 AS VARCHAR(4)),103)
END
SET @fiscal_yearend = CASE WHEN MONTH(GETDATE()) > 9
THEN CONVERT(DATETIME,'30/09/'+ CAST(@next_Year + 1 AS VARCHAR(4)),103)
ELSE CONVERT(DATETIME,'30/09/'+ CAST(@next_Year AS VARCHAR(4)),103)
END
END
IF @fiscalyear = 'Curr and Next'
BEGIN
SET @fiscal_yearstart = CASE WHEN MONTH(GETDATE()) > 9
THEN CONVERT(DATETIME,'01/10/'+ CAST(@curr_year AS VARCHAR(4)),103)
ELSE CONVERT(DATETIME,'01/10/'+ CAST(@curr_year - 1 AS VARCHAR(4)),103)
END
SET @fiscal_yearend = CASE WHEN MONTH(GETDATE()) > 9
THEN CONVERT(DATETIME,'30/09/'+ CAST(@next_Year + 1 AS VARCHAR(4)),103)
ELSE CONVERT(DATETIME,'30/09/'+ CAST(@next_Year AS VARCHAR(4)),103)
END
END
--SELECT @fiscal_yearstart,@fiscal_yearend
DECLARE @fys DATETIME
SET @fys = @fiscal_yearstart
DECLARE @mon_number INT
SET @mon_number = 1
DECLARE @mon_table TABLE (mon_start DATETIME, mon_end DATETIME, mon_name VARCHAR(20), mon_name_ord VARCHAR(10), mon_ord INT)
WHILE @fys < @fiscal_yearend BEGIN
INSERT INTO @mon_table
SELECT @fys, DATEADD(ms, -2, DATEADD(MONTH, 1, @fys)),
SUBSTRING(CONVERT(VARCHAR(11), @fys, 113), 4, 8),
'month'+CAST(@mon_number as VARCHAR), @mon_number
print @mon_number
SET @fys = DATEADD(MONTH, 1, @fys)
SET @mon_number = @mon_number + 1
END
--select * FROM @mon_table
DECLARE @missing_month TABLE (mon_name VARCHAR(20), mon_name_ord VARCHAR(10), mon_ord INT,
ph_name VARCHAR(50), ph_ord INT, cnt INT)
INSERT INTO @missing_month
SELECT mon_name, mon_name_ord, mon_ord, 'Define', 1, 0 FROM @mon_table
IF CHARINDEX(@projecttyp, 'JDI Project', 1) > 0 BEGIN
INSERT INTO @missing_month
SELECT mon_name, mon_name_ord, mon_ord, 'MAIC', 3, 0 FROM @mon_table
END
IF CHARINDEX(@projecttyp1, 'LSS Project', 1) > 0 BEGIN
INSERT INTO @missing_month
SELECT mon_name, mon_name_ord, mon_ord, 'Measure', 2, 0 FROM @mon_table
UNION ALL
SELECT mon_name, mon_name_ord, mon_ord, 'Analyse', 4, 0 FROM @mon_table
UNION ALL
SELECT mon_name, mon_name_ord, mon_ord, 'Improve', 5, 0 FROM @mon_table
UNION ALL
SELECT mon_name, mon_name_ord, mon_ord, 'Control', 6, 0 FROM @mon_table
END
INSERT INTO @missing_month
SELECT mon_name, mon_name_ord, mon_ord, 'Realisation', 9, 0 FROM @mon_table
------------------*******All Project From the hierarchy------------------------------
DECLARE @projectdetails TABLE (parent_work_id VARCHAR(100),parent_name VARCHAR(500),parent_sequence_id VARCHAR(100),
parent_status_current VARCHAR(100),parent_type_name VARCHAR(100),active_phase VARCHAR(200),
parent_actual_end_date DATETIME,
bus_run VARCHAR(250),segment VARCHAR(250),division VARCHAR(250),sbu VARCHAR(250))
INSERT INTO @projectdetails
SELECTw.child_work_id AS parent_work_id
,w.child_name AS parent_name
,w.child_sequence_id AS parent_sequence_id
,w.child_status_current AS parent_status_current
,w.child_type_name AS parent_type_name
,CASE WHEN w.child_status_current = 'Completed' THEN 'Realisation'
ELSE (SELECT name FROM view_work WHERE work_id = w.child_active_gate) END AS active_phase
,w.child_actual_end_date AS parent_actual_end_date
,t1.tag_name AS bus_run
,t2.tag_name AS segment
,t3.tag_name AS division
,t5.tag_name AS sbu
FROM view_work_hierarchy w
INNER JOIN view_tag t1 ON t1.object_id = w.child_work_id AND t1.tagset_name = 'Business or Function Running the Project'
AND t1.tag_name IN (select distinct tag_name FROM view_tag WHERE tagset_name = 'Business or Function Running the Project')
INNER JOIN view_tag t2 ON t2.object_id = w.child_work_id AND t2.tagset_name = 'Segment Receiving Benefits'
AND t2.tag_name IN (select distinct tag_name FROM view_tag WHERE tagset_name = 'Segment Receiving Benefits')
INNER JOIN view_tag t3 ON t3.object_id = w.child_work_id AND t3.tagset_name = 'Division Receiving Benefits'
AND t3.tag_name IN (select distinct tag_name FROM view_tag WHERE tagset_name = 'Division Receiving Benefits')
INNER JOIN view_tag t5 ON t5.object_id = w.child_work_id AND t5.tagset_name = 'Strategic Business Unit (SBU) Receiving Benefits'
AND t5.tag_name IN (select distinct tag_name FROM view_tag WHERE tagset_name = 'Strategic Business Unit (SBU) Receiving Benefits')
WHEREw.parent_work_id IN ('fs000080000j5er8l54g000000')
ANDw.child_type_name IN ('LSS Project','JDI Project')
ANDw.child_visibility = 'CURR'
ANDw.child_status_current NOT IN ('On Hold', 'Canceled')
---***********Actual project with missing month-----------------------------------
DECLARE @projects TABLE(proj_id VARCHAR(30), proj_name VARCHAR(250), proj_sequence VARCHAR(30), proj_status VARCHAR(20), proj_type VARCHAR(50),
active_phase VARCHAR(130), proj_actual_end DATETIME, business_run VARCHAR(250), segment VARCHAR(250), division VARCHAR(250),
sbu VARCHAR(250), ph_name VARCHAR(100), ph_ord INT, mon_name VARCHAR(50), mon_name_ord VARCHAR(10),
mon_ord INT, mon_start DATETIME, mon_end DATETIME, ph_start DATETIME, ph_end DATETIME)--, rn INT)
INSERT INTO @projects
SELECTparent_work_id,
parent_name,
parent_sequence_id,
parent_status_current,
parent_type_name,
CASE active_phase WHEN 'Measure/Analyze/Improve/Control' THEN 'MAIC' ELSE active_phase END AS active_phase,
parent_actual_end_date,
bus_run, segment, division,sbu,
CASE child_name WHEN 'Measure/Analyze/Improve/Control' THEN 'MAIC' ELSE child_name END AS child_name,
CASEWHEN child_name = 'Define' THEN 1
WHEN child_name = 'Measure' THEN 2
WHEN child_name = 'Measure/Analyze/Improve/Control' THEN 3
WHEN child_name = 'Analyze' THEN 4
WHEN child_name = 'Improve' THEN 5
WHEN child_name = 'Control' THEN 6
WHEN child_name = 'Realisation' THEN 9
ELSE sequence_within_parent END AS sequence_within_parent,
mon_name, mon_name_ord, mon_ord, mon_start, mon_end,
child_planned_start_date, child_planned_end_date
FROM @mon_tablemn
LEFT OUTER JOIN (SELECTw.parent_work_id,
w.parent_name,
w.parent_sequence_id,
w.parent_status_current,
w.parent_type_name,
w.active_phase,
w.parent_actual_end_date,
w1.work_id AS child_work_id,
w1.name AS child_name,
w1.sequence_within_parent,
CASEWHEN w1.name = 'Define' AND w1.planned_start_date IS NULL AND w1.system_start_date < w1.planned_end_date THEN w1.system_start_date
WHEN w1.name = 'Define' AND w1.planned_start_date IS NULL AND w1.system_start_date > w1.planned_end_date THEN w1.planned_end_date
ELSE ISNULL(w1.planned_start_date, w1.planned_end_date) END AS child_planned_start_date,
w1.planned_end_date AS child_planned_end_date,
w.bus_run,
w.segment,
w.division,
w.sbu
FROM @projectdetails w
INNER JOIN view_work w1 ON w1.parent_work_id = w.parent_work_id
WHERE parent_sequence_id IS NOT NULL
AND w1.type_name = 'Gate'
UNION ALL
SELECTparent_work_id,
parent_name,
parent_sequence_id,
parent_status_current,
parent_type_name,
active_phase,
parent_actual_end_date,
parent_work_id AS child_work_id,
'Realisation' AS child_name,
9 AS sequence_within_parent,
(SELECT planned_end_date FROM view_work WHERE work_id = (SELECT TOP 1 gate_id FROM view_gate
WHERE gated_project_id = w.parent_work_id ORDER BY phase_seq DESC))
, DATEADD(ms, -2, DATEADD(YEAR, 1, (SELECT planned_end_date FROM view_work
WHERE work_id = (SELECT TOP 1 gate_id FROM view_gate
WHERE gated_project_id = w.parent_work_id
ORDER BY phase_seq DESC)
)
)
) AS child_planned_end_date,
bus_run,
segment,
division,
sbu
FROM @projectdetails w
) proj ON proj.child_planned_start_date <= mn.mon_end AND proj.child_Planned_end_date >= mn.mon_start
ORDER BY parent_name
--#####################MAIN Query###################################3333
SELECT 'ABC' AS type_data, NULL AS mon_name, NULL AS ph_name, NULL AS mon_ord, NULL AS ph_ord, NULL AS cnt,-- rn,
proj_id, proj_name, proj_sequence, proj_status, proj_type, active_phase, proj_actual_end,
business_run, segment, division, sbu,
ISNULL((SELECT TOP 1 CONVERT(INT,indicator1.value) FROM dbo.fn_MeasureValueMostRecentIndicatorsByProject(proj_id) indicator1
INNER JOIN PSView_Measure vm ON indicator1.measure_id = vm.object_id
AND vm.object_name = N'Project Totals: Net Avoided Costs Best View' ORDER BY date DESC),0) +
ISNULL((SELECT TOP 1 CONVERT(INT,indicator1.value) FROM dbo.fn_MeasureValueMostRecentIndicatorsByProject(proj_id) indicator1
INNER JOIN PSView_Measure vm ON indicator1.measure_id = vm.object_id
AND vm.object_name = N'Project Totals: Net Total P&L Impact Best View' ORDER BY date DESC),0) +
ISNULL((SELECT TOP 1 CONVERT(INT,indicator1.value) FROM dbo.fn_MeasureValueMostRecentIndicatorsByProject(proj_id) indicator1
INNER JOIN PSView_Measure vm ON indicator1.measure_id = vm.object_id
AND vm.object_name = N'Project Totals: Net Soft Best View' ORDER BY date DESC),0) as totalNetBenefits,
----------values of the selected parameters---------------------------------------------------
SUBSTRING((SELECT ', ' +name FROM view_work WHERE work_id IN ('fs000080000j5er8l54g000000')
FOR XML PATH('')),3,1000) AS Descended,
(CASE @fiscalyearWHEN 'Cur' THEN @curr_year
WHEN 'Curr and Next' THEN @curr_year+','+@next_Year
WHEN 'Next' THEN @next_Year
END) AS fiscalYear,
@curr_year AS currentYear,
@next_Year AS nextYear,
SUBSTRING(month1, 3, len(month1)) as month1, SUBSTRING(month2, 3, len(month2)) as month2,
SUBSTRING(month3, 3, len(month3)) as month3, SUBSTRING(month4, 3, len(month4)) as month4,
SUBSTRING(month5, 3, len(month5)) as month5, SUBSTRING(month6, 3, len(month6)) as month6,
SUBSTRING(month7, 3, len(month7)) as month7, SUBSTRING(month8, 3, len(month8)) as month8,
SUBSTRING(month9, 3, len(month9)) as month9, SUBSTRING(month10, 3, len(month10)) as month10,
SUBSTRING(month11, 3, len(month11)) as month11, SUBSTRING(month12, 3, len(month12)) as month12,
SUBSTRING(month13, 3, len(month13)) as month13, SUBSTRING(month14, 3, len(month14)) as month14,
SUBSTRING(month15, 3, len(month15)) as month15, SUBSTRING(month16, 3, len(month16)) as month16,
SUBSTRING(month17, 3, len(month17)) as month17, SUBSTRING(month18, 3, len(month18)) as month18,
SUBSTRING(month19, 3, len(month19)) as month19, SUBSTRING(month20, 3, len(month20)) as month20,
SUBSTRING(month21, 3, len(month21)) as month21, SUBSTRING(month22, 3, len(month22)) as month22,
SUBSTRING(month23, 3, len(month23)) as month23, SUBSTRING(month24, 3, len(month24)) as month24
FROM
(SELECT proj_id, proj_name, proj_sequence, proj_status,proj_type, active_phase, proj_actual_end,
business_run, segment, division, sbu, CAST(ph_ord AS VARCHAR)+' '+ph_name as ph_name, mon_name_ord--, rn
FROM @projects WHERE proj_name IS NOT NULL) abc
PIVOT (
MAX(ph_name) for mon_name_ord IN ([month1], [month2], [month3], [month4], [month5], [month6],
[month7], [month8], [month9], [month10], [month11], [month12],
[month13], [month14], [month15], [month16], [month17], [month18],
[month19], [month20], [month21], [month22], [month23], [month24])
) as xxx
UNION ALL
SELECT 'XYZ' AS type_data,mon_name, ph_name, mon_ord, ph_ord,sum(cnt) as cnt,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL, NULL,NULL,NULL
FROM(
SELECT mon_name,SUBSTRING(ph_name, 3, LEN(ph_name)) as ph_name,
LEFT(ph_name,1) ph_ord,mon_ord,count(proj_id) as cnt
FROM(
SELECT MAX(ph_name) as ph_name, mon_name,mon_ord,proj_id
FROM (
SELECTmon_name,CAST(ph_ord AS VARCHAR)+' '+ph_name as ph_name,mon_ord,proj_id--, ph_ord, proj_id AS cnt
FROM @projects
WHERE proj_id IS NOT NULL
)xyz
GROUP BY mon_name,mon_ord,proj_id
)zyx
GROUP BY mon_name,ph_name,mon_ord
UNION ALL
SELECTmon_name, ph_name, ph_ord,mon_ord,cnt
FROM @missing_month
)chartdata
GROUP BY mon_name, ph_name, mon_ord, ph_ord
ORDER BY mon_ord,ph_ord
October 29, 2013 at 7:33 am
I before Pivot I would use case based pivoting. Here is an article that describes some different options:
http://pratchev.blogspot.com/2007/04/pivoting-data-in-sql-server.html
October 29, 2013 at 7:53 am
I agree with Keith. Crosstabs are generally more efficient than PIVOT. Take a look in my signature at the two articles about cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2013 at 8:56 am
Thanks for the replies..
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply