How to rewrite that query using a pivot - And optimizie the query

Question

How to rewrite that query using a pivot - And optimizie the query

Stanley Pagenel

Ten Centuries

Points: 1283
More actions
October 9, 2014 at 1:21 pm

#297315

Hi I would like to rewrite that query using pivot instead of Union. And also can it be optimized.
Table structure :
CREATE TABLE [dbo].[EXT_062$](
[Clt Code] [varchar](255) NULL,
[Part No#] [varchar](255) NULL,
[Cont Year] [real] NULL,
[Plan] [real] NULL,
[Period End Date] [datetime] NULL,
[F/T Earn-Jan] [real] NULL,
[F/T Earn-Feb] [real] NULL,
[F/T Earn-Mar] [real] NULL,
[F/T Earn-Apr] [real] NULL,
[F/T Earn-May] [real] NULL,
[F/T Earn-Jun] [real] NULL,
[F/T Earn-Jul] [real] NULL,
[F/T Earn-Aug] [real] NULL,
[F/T Earn-Sep] [real] NULL,
[F/T Earn-Oct] [real] NULL,
[F/T Earn-Nov] [real] NULL,
[F/T Earn-Dec] [real] NULL,
[Contribs-Jan] [real] NULL,
[Contribs-Feb] [real] NULL,
[Contribs-Mar] [real] NULL,
[Contribs-Apr] [real] NULL,
[Contribs-May] [real] NULL,
[Contribs-Jun] [real] NULL,
[Contribs-Jul] [real] NULL,
[Contribs-Aug] [real] NULL,
[Contribs-Sep] [real] NULL,
[Contribs-Oct] [real] NULL,
[Contribs-Nov] [real] NULL,
[Contribs-Dec] [real] NULL,
[CWI Sep] [real] NULL,
[YTD F/T Earn] [real] NULL,
[Not Used] [real] NULL,
[YTD Act earn] [real] NULL,
[YTD Cont] [real] NULL,
[Cred Serv] [real] NULL,
[Scheduled Wks] [real] NULL,
[Full Time Wks] [real] NULL,
[F38] [varchar](255) NULL,
[pk_id] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[pk_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Query to rewrite and optimize :
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Jan] as EarnAmount
,E.[Contribs-Jan] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-01-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-01-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
INTO dbo.Tab_PayRollEvents_ALL_PENEARN_temp1
--select *
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Jan]>0
and isdate(E.[CONT Year] )=1
Insert INTO
dbo.Tab_PayRollEvents_ALL_PENEARN_temp1
--February
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Feb] as EarnAmount
,E.[Contribs-Feb] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-02-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-02-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Feb]>0
and isdate(E.[CONT Year] )=1
Union All
--March
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Mar] as EarnAmount
,E.[Contribs-Mar] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-03-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-03-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Mar]>0
and isdate(E.[CONT Year] )=1
Union All
--April
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Apr] as EarnAmount
,E.[Contribs-Apr] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-04-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-04-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Apr]>0
and isdate(E.[CONT Year] )=1
Union All
--May
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-May] as EarnAmount
,E.[Contribs-May] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-05-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-05-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-May]>0
and isdate(E.[CONT Year] )=1
Union All
--June
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Jun] as EarnAmount
,E.[Contribs-Jun] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-06-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-06-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Jun]>0
and isdate(E.[CONT Year] )=1
Union All
--July
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Jul] as EarnAmount
,E.[Contribs-Jul] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-07-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-07-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Jul]>0
and isdate(E.[CONT Year] )=1
Union All
--August
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Aug] as EarnAmount
,E.[Contribs-Aug] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-08-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-08-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Aug]>0
and isdate(E.[CONT Year] )=1
Union All
--Sept
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Sep] as EarnAmount
,E.[Contribs-Sep] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-09-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-09-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Sep]>0
and isdate(E.[CONT Year] )=1
Union All
--Oct
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Oct] as EarnAmount
,E.[Contribs-Oct] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-10-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-10-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Oct]>0
and isdate(E.[CONT Year] )=1
Union All
--Nov
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Nov] as EarnAmount
,E.[Contribs-Nov] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-11-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-11-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Nov]>0
and isdate(E.[CONT Year] )=1
Union All
--Dec
SELECT
E.[Part No#]
,P.PersonID
,E.[F/T Earn-Dec] as EarnAmount
,E.[Contribs-Dec] as ContribAmount
,Case when isdate(E.[CONT Year] )=1 then
cast(cast(E.[CONT Year] as CHAR(4)) as Datetime) else
'1900-01-01' end as F_YearofStartPeriod
,Cast(E.[CONT Year] as varchar) as Yearofstartperiod
,'-12-01' as monthdayofstartperiod
,cast((Cast(E.[CONT Year] as varchar) + '-12-01') as datetime) as cdate
,1 as Earningstype
,EF.EmploymentID
FROM client.[EXT_062$] E
LEFT JOIN dbo.Tab_Persons_Final P -- only for participants in Ariel.
ON P.[Part No#] = E.[Part No#]
Join Tab_Employments_FINAL EF
on P.PersonID = EF.PersonID
where [F/T Earn-Dec]>0
and isdate(E.[CONT Year] )=1
Thanks
Stan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This isn't tested at all because you didn't post all the tables involved nor sample data. The method used is described in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

SELECT E.[Part No#],

P.Personid,

x.Earnamount,

x.Contribamount,

CASE

WHEN ISDATE( E.[Cont Year] ) = 1 THEN CAST( CAST( E.[Cont Year] AS char( 4 ))AS datetime )

ELSE '1900-01-01'

END AS F_Yearofstartperiod,

CAST( E.[Cont Year] AS varchar )AS Yearofstartperiod,

x.Monthdayofstartperiod,

x.Cdate,

1 AS Earningstype,

Ef.Employmentid

FROM Client.Ext_062$ E

LEFT

JOIN Dbo.Tab_Persons_Final P -- only for participants in Ariel.

ON P.[Part No#] = E.[Part No#]

JOIN Tab_Employments_Final Ef

ON P.Personid = Ef.Personid

CROSS APPLY( VALUES('-01-01',

CAST( CAST( E.[Cont Year] AS char(4)) + '-01-01' AS datetime ),

E.[F/t Earn-jan],

E.[Contribs-jan]),

('-02-01',

CAST( CAST( E.[Cont Year] AS char(4)) + '-02-01' AS datetime ),

E.[F/t Earn-feb],

E.[Contribs-feb]),

('-03-01',

CAST( CAST( E.[Cont Year] AS char(4)) + '-03-01' AS datetime ),

E.[F/t Earn-mar],

E.[Contribs-mar]),

('-04-01',

CAST( CAST( E.[Cont Year] AS char(4)) + '-04-01' AS datetime ),

E.[F/t Earn-apr],

E.[Contribs-apr]),

('-05-01',

CAST( CAST( E.[Cont Year] AS char(4)) + '-05-01' AS datetime ),

E.[F/t Earn-may],

E.[Contribs-may]))x(Monthdayofstartperiod, Cdate, Earnamount, Contribamount)

WHERE Earnamount > 0

AND ISDATE( E.[Cont Year] ) = 1;

Ask any questions that you have and please, use the IFCode tags to include your code in a box to avoid long posts.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Stanley Pagenel Ten Centuries Points: 1283 More actions · Answer 2

Stanley Pagenel

Ten Centuries

Points: 1283

October 10, 2014 at 6:50 am

#1751590

Thanks Louis 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I am glad Luis was able to help you with you query but I have to ask. Why are you using the real datatype for nearly every column? This looks like financial type of stuff and you would be far better using an exact numeric type instead of an approximate one.

_______________________________________________________________

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/

Stanley Pagenel Ten Centuries Points: 1283 More actions · Answer 4

Yes it financial stuff. But thanks for your advice. I will put that in practice 🙂