Cross Tab Query!!! 1

[Cpreston]

Hi

I have been trying to create a view where I and list out OTI reason in one column and then a count of account managers that have had these OTIFS against there orders

So trying to get this look from the query as below

I currently have this query and the output thus far.

SELECT (dbrderHeader.udfSalesOrderOTIF) AS Count, dbrderHeader.OrderStatus, dbo.SalesRep.Name,
dbrderHeader.udfSalesOrderOTIF
FROM dbrderHeader INNER JOIN
dbo.SalesRep ON dbrderHeader.SalesRepID = dbo.SalesRep.SalesRepID
GROUP BY dbrderHeader.OrderStatus, dbo.SalesRep.Name, dbrderHeader.udfSalesOrderOTIF
HAVING (dbrderHeader.OrderStatus = 8) AND (NOT (COUNT(dbrderHeader.udfSalesOrderOTIF) IS NULL)) AND (NOT (dbrderHeader.udfSalesOrderOTIF IS NULL))
ORDER BY COUNT(dbrderHeader.udfSalesOrderOTIF)

Could someone point me in the right direction please

Thanks

 

[gmmastros]

This is something that is usually better handled on the front end. I say this because there is a way to create cross tab queries, but when the "columns" are variable, things get real messy real quick.

For example, in 6 months, there may be another name added to the list that you want to include in the cross tab query. You could either change the "simple" query to hard code the new name (and include another column in your output), or you could create super messy code that handles it automatically.

Even if you write a real messy query that handles any number of columns, you still have the difficult task of making sure your front end application can accommodate the new column.

For the reasons listed above, it's better to perform your cross tab in the front end.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

I have tried this using the Pivot method but cant quite get the syntax right

select * from "148-vwOtifAnlaysiscp"
pivot (count (Type) for Name in ([Name1],[manager1],[manager2]))
FROM "148-vwOtifAnlaysiscp" as OT
INNER JOIN dbo.salesrep as sr
ON OT.ProductID = sr.name
WHERE OT.OrderStatus = 8 AND OT.udfSalesOrderOTIF IS NOT NULL

It is giving
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.

But cant see why the FROM is not working.

Any ideas please, thanks

 

[SkipVought]

Here's what I have done in the past when working with SQL Server or Oracle. Use Excel to query a subset in one sheet via MS Query and the use the Pivot Table feature to report the result structure you need.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

Hi

Normally i would have used a front end or excel as suggested. However this query is for a smartview that does not have such functionality so i need to get the result within SQL if possible.

Thanks

 

[gmmastros]

If you must...

; With Data As
(
  SELECT (dbo.OrderHeader.udfSalesOrderOTIF) AS Count, dbo.OrderHeader.OrderStatus, dbo.SalesRep.Name, 
  dbo.OrderHeader.udfSalesOrderOTIF
  FROM dbo.OrderHeader INNER JOIN
  dbo.SalesRep ON dbo.OrderHeader.SalesRepID = dbo.SalesRep.SalesRepID
  GROUP BY dbo.OrderHeader.OrderStatus, dbo.SalesRep.Name, dbo.OrderHeader.udfSalesOrderOTIF
  HAVING (dbo.OrderHeader.OrderStatus = 8) AND (NOT (COUNT(dbo.OrderHeader.udfSalesOrderOTIF) IS NULL)) AND (NOT (dbo.OrderHeader.udfSalesOrderOTIF IS NULL))
  ORDER BY COUNT(dbo.OrderHeader.udfSalesOrderOTIF)
)
Select udfSalesOrderOTIF,
       Coalesce(Min(Case When Name = 'Bob Banks' Then [Count] End), 0) As [Bob],
       Coalesce(Min(Case When Name = 'Scott Laxton' Then [Count] End), 0) As [Scott],
       Coalesce(Min(Case When Name = 'Eddie O''Riordan' Then [Count] End), 0) As [Eddie],
       Coalesce(Min(Case When Name = 'Lawrie Gingle' Then [Count] End), 0) As [Lawrie],
       Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group],
       Sum([Count]) As [Total]
From   Data
Group By udfSalesOrderOTIF

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Thanks for the code. I am trying to apply it, but cant quite the sysntax right. if I run it exactly like you have it I get
Msg 1033, Level 15, State 1, Line 10
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Also you have From Data in the second part of the query, what should go in here please???

Sorry my coding is not brialliant. I have tried to manipulate the code but cant get it right.

many thanks

 

[gmmastros]

Remove the Order By line, but leave everything else exactly as is. IF this works, I'll explain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Yes I tried that but then I believe it complained about the FROM DATA.

I dont have access to the data until Monday so will confirm then. Many thanks

 

[Cpreston]

Hi

OK I ran the code as below and took the row out and got this message, I knew I got a message but was not sure, please advise

Msg 8117, Level 16, State 1, Line 17
Operand data type varchar is invalid for sum operator.


; With Data As
(
SELECT (dbrderHeader.udfSalesOrderOTIF) AS Count, dbrderHeader.OrderStatus, dbo.SalesRep.Name,
dbrderHeader.udfSalesOrderOTIF
FROM dbrderHeader INNER JOIN
dbo.SalesRep ON dbrderHeader.SalesRepID = dbo.SalesRep.SalesRepID
GROUP BY dbrderHeader.OrderStatus, dbo.SalesRep.Name, dbrderHeader.udfSalesOrderOTIF
HAVING (dbrderHeader.OrderStatus = 8) AND (NOT (COUNT(dbrderHeader.udfSalesOrderOTIF) IS NULL)) AND (NOT (dbrderHeader.udfSalesOrderOTIF IS NULL))

)
Select udfSalesOrderOTIF,
Coalesce(Min(Case When Name = 'Bob Banks' Then [Count] End), 0) As [Bob],
Coalesce(Min(Case When Name = 'Scott Laxton' Then [Count] End), 0) As [Scott],
Coalesce(Min(Case When Name = 'Eddie O''Riordan' Then [Count] End), 0) As [Eddie],
Coalesce(Min(Case When Name = 'Lawrie Gingle' Then [Count] End), 0) As [Lawrie],
Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group],
Sum([Count]) As [Total]
From Data
Group By udfSalesOrderOTIF

 

[philhege]

What data type is dbrderHeader.udfSalesOrderOTIF? You're aliasing it as COUNT in the CTE, but that doesn't make it a number.
In the main query, you're using it in a SUM() function. Do you understand why that doesn't work?

-----------
With business clients like mine, you'd be better off herding cats.

 

[Cpreston]

I believe it may be int but not certain. I wll need to check tomorrow. I dont fully understand to be homest.

Thanks

 

[fredericofonseca]

missing the aggregation operator, which taking in consideration the alias given I am assuming it should be Count

; With Data As
(
SELECT [highlight #CC0000][b]count[/b][/highlight](oh.udfSalesOrderOTIF) AS Count
     , oh.OrderStatus
     , sr.Name
     , oh.udfSalesOrderOTIF
FROM dbo.OrderHeader oh
INNER JOIN dbo.SalesRep sr
   ON oh.SalesRepID = sr.SalesRepID
GROUP BY oh.OrderStatus
       , sr.Name
       , oh.udfSalesOrderOTIF
HAVING (oh.OrderStatus = 8)
   AND (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
   AND (NOT (oh.udfSalesOrderOTIF IS NULL))

)
Select udfSalesOrderOTIF
     , Coalesce(Min(Case When Name = 'Bob Banks' Then [Count] End), 0) As [Bob]
     , Coalesce(Min(Case When Name = 'Scott Laxton' Then [Count] End), 0) As [Scott]
     , Coalesce(Min(Case When Name = 'Eddie O''Riordan' Then [Count] End), 0) As [Eddie]
     , Coalesce(Min(Case When Name = 'Lawrie Gingle' Then [Count] End), 0) As [Lawrie]
     , Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group]
     , Sum([Count]) As [Total]
From Data
Group By udfSalesOrderOTIF

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Ok great that code now works and gives the expected results. I may need to add in invoice date fields at a future time but for now this is great.

Thanks for the exceptional help.