Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

Julie19
Starting Member

32 Posts

Posted - 2012-05-23 : 10:21:35

Hello

I am getting the Incorrect syntax near the keyword 'Pivot for the following Query.Please advise

SELECT X,Y, Z, A, B, C,
D, E, Agent, Daily1Day, DAILYATTN, '5/21/2012', '05/22/2012', '05/23/12'
(SELECT ReportDate, RptFunction, Center, AMATTUID, AM, TMATTUID,
TM, ATTUID, Agent, Daily1Day, [1DayDailyAttain] AS DAILYATTN,
(SELECT MAX([F])
FROM dbo.t_Month) AS OnedayRes
FROM dbo.q_Agent
WHERE (INT_EXT = 'i'))
PIVOT ( MAX(OnedayRes) FOR OnedayRes IN ('5/21/2012', '05/22/2012',
'05/23/12') ) AS pvt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-23 : 10:41:25

[code](SELECT a.ReportDate AS X,
a.RptFunction AS Y,
a.Center AS Z,
a.AMATTUID AS A,
a.AM AS B,
a.TMATTUID AS C,
a.TM AS D,
a.ATTUID AS E,
a.Agent,
a.Daily1Day,
a.[1DayDailyAttain] AS DailyAttn,
f.a AS [5/21/2012],
f.b AS [05/22/2012],
f.c AS [05/23/12]
FROM dbo.q_Agent AS a
OUTER APPLY (
SELECT MAX(CASE WHEN F = '20120521' THEN CAST('20120521' AS DATE) ELSE NULL END) AS a,
MAX(CASE WHEN F = '20120522' THEN CAST('20120522' AS DATE) ELSE NULL END) AS b,
MAX(CASE WHEN F = '20120523' THEN CAST('20120523' AS DATE) ELSE NULL END) AS c
FROM dbo.t_Month
WHERE F >= '20120521'
AND F < '20120524'
) AS f(a, b, c)
WHERE a.INT_EXT = 'i'[/code]

N 56°04'39.26"
E 12°55'05.63"

Julie19
Starting Member

32 Posts

Posted - 2012-05-23 : 11:10:45

Thanks for the above query

This is my actual query

SELECT ReportDate, RptFunction, Center, Agent, Daily1Day, [1DayDailyAttain],
(SELECT [24 Hr Res]
FROM dbo.t_Month) AS OnedayRes,
(SELECT ReportDate
FROM dbo.t_Month
WHERE (ReportDate = '2012-05-22')) AS RptDate

FROM dbo.q_Agent
WHERE (INT_EXT = 'i') AND (ReportDate = '2012-05-22')

which currently pulls the data for the report date mentioned and everyday the data gets appended to the last of the row

But my requirement is that the data output should have the dates coming from [24 Hour res] and the results for each day from Daily1 day and[1DayDailyAttain] should be under the corresponding the [24 hour res]date Column
the output data in 1daydaily attain should be conditionally formatted like if (1DayDailyAttain0> 1+ the data should be bold green,0.98-0.99 should be bold yellow and <0.95 should be bold red

But My users wants to see the data daily so they can compare to previous day as the data changes everyday which helps them calculate their performance

Right now
it is

Rptfunction..........Agent Daily1day 24Hour Res
ABC 1.1 5/22
...
...
....
XYZ 1.2 5/23
some thing like

Rpt function ....... agent 5/22 5/23 5/24
......... .... XYZ 1.1 1.2 1.6
....................... ABC 0.99 0.98 1.00
Above dates come from the [24 Hr Res] column name and 1.1,1.2... come from daily1 day column

I hope this makes sense
I used the report date in my query just to make sure I am getting the correct data from both the tables.

Your help is greatly appreciated.

Thanks
J

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:27:22

Why not do this pivoting in reports? If you're using sql reporting services,you can very easily achieve it using matrix container in ssrs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Julie19
Starting Member

32 Posts

Posted - 2012-05-23 : 15:41:24

I am using SQL server as we do not have access to reporting services
Can you help me with the query using pivot?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:44:00

so are you asking to pivot lutiple columns based on condition?

like
Daily1Day, 1DayDailyAttain], etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Julie19
Starting Member

32 Posts

Posted - 2012-05-23 : 15:53:19

to make it simler to understand
I would need my data like this:

RptFunction, Center, Agent 5/22 5/23 5/24
Data data data 1.1/1.45 1.2/0.99 1.3/0.98

Since he data changes everyday ,agents need to compare their data to previous date and so on...

5/22 comes from [24 Hour res] in the t_Month table and 1.1/1.45 ,1.2...... comes from Daily1day and 1 day daily attain in the agents table

The common column between these two tables is teh reportdate which is set to get the sysdate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 16:11:37

sorry i cant make out which column belongs to which table from your explanation. please post data in below format and show us output you need

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Julie19
Starting Member

32 Posts

Posted - 2012-05-23 : 16:33:36

Agent Table
CREATE TABLE [dbo].[q_Agent](
[RptFunction] [nvarchar](255) NULL,
[Center] [nvarchar](255) NULL,
[AMATTUID] [nvarchar](255) NULL,
[AM] [nvarchar](255) NULL,
[TMATTUID] [nvarchar](255) NULL,
[TM] [nvarchar](255) NULL,
[ATTUID] [nvarchar](255) NULL,
[Agent] [nvarchar](255) NULL,
[ReportDate] [date] NOT NULL

Month table

CREATE TABLE [dbo].[t_Month](
[Month] [datetime] NULL,
[Daily] [datetime] NULL,
[24 Hour Res] [datetime] NULL,
[ReportDate] [date] NOT NULL

This is what i have so far
SELECT ReportDate, RptFunction, Center, AMATTUID, AM, TMATTUID,
TM, ATTUID, Agent, Daily1Day, DAILYATTN, '5/21/2012', '05/22/2012', '05/23/12',
(SELECT ReportDate, RptFunction, Center, AMATTUID, AM, TMATTUID,
TM, ATTUID, Agent, Daily1Day, [1DayDailyAttain] AS DAILYATTN,
(SELECT [24 Hour Res]
FROM dbo.t_Month) AS OnedayRes
FROM dbo.q_Agent
WHERE (INT_EXT = 'i'))
PIVOT ((Daily1Day) FOR Daily1Day IN OnedayRes(
Case when Reportdate='5/22/2012 then [24 Hr Res] ='5/20/2012'
Case when Reportdate='5/23/2012 then [24 Hr Res]= '05/21/2012'
) )AS pvt

With this query I am getting an incorrect syntax at Pivot

My out put should be

Function Center Rep ID Representative. ------ 5/20 5/21 5/22 5/23
XYZ ABC ag194u GILE ------ 0.98 0.98 0.98 0.98
XYZ ABC as263s SWANSON ------ 1.01 1.01 1.01 1.01
XYZ ABC cj680t JOHNSON0 ------- .97 0.97 0.97 0.97

The dates comes from the [24 hr res] column in Month table and the results for the dates comes from daily1day and 1 daydailyattain in agent table

Julie19
Starting Member

32 Posts

Posted - 2012-05-24 : 08:47:22

Please help with the above query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:35:35

You're still not giving us required information. I cant even see columns daily1day and 1 daydailyattain in posted info so dont know how you want it to be pivoted.

Anyways it should be something like below (i'm just showing for one of columns)


SELECT *
FROM
(
SELECT a.RptFunction,a.Center,a.[Rep ID],a.Representative,m.[24 Hour Res],a.daily1day
FROM [dbo].[q_Agent] a
INNER JOIN [dbo].[t_Month] m
ON m.ReportDate = a.ReportDate
)m
PIVOT (MAX(daily1day) FOR [24 Hour Res] IN ([5/20],[5/21],...,[5/23]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Julie19
Starting Member

32 Posts

Posted - 2012-05-24 : 16:59:05

Sorry about that.Here it is from the agent table
CREATE TABLE [dbo].[q_Agent](
[RptFunction] [nvarchar](255) NULL,
[Center] [nvarchar](255) NULL,
[AMATTUID] [nvarchar](255) NULL,
[AM] [nvarchar](255) NULL,
[TMATTUID] [nvarchar](255) NULL,
[TM] [nvarchar](255) NULL,
[ATTUID] [nvarchar](255) NULL,
[Agent] [nvarchar](255) NULL,
[Daily1Day] [float] NULL,
[1DayDailyAttain] [float] NULL,

and the month table

[dbo].[t_Month](
[24 Hour Res] [datetime] NULL,
[ReportDate] [date] NOT NULL

Julie19
Starting Member

32 Posts

Posted - 2012-05-24 : 23:47:15

Please help with this requirement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 09:52:33

quote:
Originally posted by Julie19

Please help with this requirement

extend the last suggestion to include the extra columns also. Dont wait for someone to give you spoonfed solution!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Julie19
Starting Member

32 Posts

Posted - 2012-05-25 : 11:53:57

This query worked
But I also want to include the 1DaydailyAttain similar to Daily1day in the pivot for the same dates
and also filter the query to use where Int_Ext='i' from the agents table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 15:29:45

quote:
Originally posted by Julie19

This query worked
But I also want to include the 1DaydailyAttain similar to Daily1day in the pivot for the same dates
and also filter the query to use where Int_Ext='i' from the agents table

so how would output be?
for each date you want both columns side by side?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Julie19
Starting Member

32 Posts

Posted - 2012-05-25 : 16:34:07

Yes,for each date I want both columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 16:45:14

then you've follow this method

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources