How to group all dates into one row 1

[Cpreston]

Hi

This is a kind of follow onto to a previous post but the problem is slightly different. I have adapted the code form the previous post to try and get a list od dates in the left hand column and then across the top a list of reasons why delivery's may have failed. The code appears to be working okay other than putting rows for each reason with the same date.

So for example if on the 2016-04-01 I have counts in OTIF (10), Other (5) and Admin (5)

It is listing the count separately with the headings across the top.

2016-04-01 10
2016-04-01 5
2016-04-01 5

What I am trying to get is
DeliveryDate Otif Other Admin Total
2016-04-01 10 5 5 20

I have tried to alter the code but cant seem to get the result I need. Could someone advise how and where I need to change the code please. Thanks in advance

; With Data As
(
SELECT count(oh.udfSalesOrderOTIF) AS Count
, oh.OrderStatus
, oh.Deliverydate
, oh.udfSalesOrderOTIF

FROM dbrderHeader oh
--INNER JOIN dbo.SalesRep sr
--ON oh.SalesRepID = sr.SalesRepID
WHERE DeliveryDate >= '2016-04-01 00:00:00'
GROUP BY oh.deliverydate
, oh.OrderStatus
, oh.udfSalesOrderOTIF


HAVING (oh.OrderStatus = 8)
AND (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
AND (NOT (oh.udfSalesOrderOTIF IS NULL))


), GrandTotal As
(
Select Sum([Count]) As GrandTotal
From Data
)
Select DeliveryDate
, Coalesce(Min(Case When udfSalesOrderOTIF = 'OTIF' Then [Count] End), 0) As [OTIF]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Early Delivery' Then [Count] End), 0) As [Early Delivery]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Make-up' Then [Count] End), 0) As [Make-up]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Not on system' Then [Count] End), 0) As [Not on system]
, Coalesce(Min(Case When udfSalesOrderOTIF= 'Other' Then [Count] End), 0) As [Other]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Production - not processed' Then [Count] End), 0) As [Production - not processed]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Production - other' Then [Count] End), 0) As [Production - other]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Sales - cancelled order' Then [Count] End), 0) As [Sales - cancelled order]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Administration' Then [Count] End), 0) As [Admin]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Sales - other' Then [Count] End), 0) As [Sales - other]
, Coalesce(MIN(Case When udfSalesOrderOTIF = 'Transport - planned to fail' Then [Count] End), 0) As [Transport - planned to fail]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Transport - insufficient room on trailer' Then [Count] End), 0) As [Transport - insufficient room on trailer]
, Coalesce(Min(Case When udfSalesOrderOTIF= 'Transport - delayed' Then [Count] End), 0) As [Transport - delayed]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Transport - other' Then [Count] End), 0) As [Transport - other]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Transport - vehicle broke down' Then [Count] End), 0) As [Production - other]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Transport - weong product delivered' Then [Count] End), 0) As [Transport - weong product delivered]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Transport - lost' Then [Count] End), 0) As [Transport - lost]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Yard - no stock' Then [Count] End), 0) As [Yard - no stock]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Yard - other' Then [Count] End), 0) As [Yard - other]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Yard - product missing /lost' Then [Count] End), 0) As [Yard - product missing /lost]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Yard - rolled / damaged' Then [Count] End), 0) As [Yard - rolled / damaged]
, Coalesce(Min(Case When udfSalesOrderOTIF = 'Yard - wrong product delivered' Then [Count] End), 0) As [Yard - wrong product delivered]
, Sum([Count]) As [Total]
, 100.0 * Sum([count]) / GrandTotal.GrandTotal AS Percentage
From Data
Cross Join GrandTotal
Group By Deliverydate, udfSalesOrderOTIF,GrandTotal.GrandTotal
Order By Deliverydate

 

[dhookom]

I think you would be much better off using PIVOT. I expect someone who has more time can provide the exact syntax but I suggest you do a little searching.

Also, please consider using TGML to format your posting for greater readability. It's fun and easy!

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Hi

Thanks for the reply and beleive me I have searched and tried various fomula to try and achieve what i want, I wont post on here unless I am totally stumped and exhausted other avenues. i looked at Pivot but could not get it working with ths syntax above.

I have no idea what TGMI is may be fun but I would need to know what you mean before I join the fun on that one.

Hopefully someone may ahve a little time to assist

 

[Andrzejek]

TGML tags - look just above the window where you post/reply, just above Preview and Submit Post buttons. See all those little icons for text formatting, colors, etc.?

Try some of them and do the 'Preview" to see what they do.



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[TamarGranor]

This is untested, but should give you the idea:

; With Data As
(
SELECT oh.Deliverydate
, oh.udfSalesOrderOTIF
, COUNT(oh.udfSalesOrderOTIF) AS OTIFCount
FROM dbo.OrderHeader oh
--INNER JOIN dbo.SalesRep sr
--ON oh.SalesRepID = sr.SalesRepID
WHERE DeliveryDate >= '2016-04-01 00:00:00'
  AND (oh.OrderStatus = 8)
GROUP BY oh.deliverydate, oh.udfSalesOrderOTIF
HAVING (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
AND (NOT (oh.udfSalesOrderOTIF IS NULL))

SELECT *
  FROM Data
   PIVOT (SUM(OTIFCount))
     FOR udfSalesOrderOTIF
      IN ([list all the possible values of oh.udfSalesOrderOTIF here])) AS OTIFTotal

If this doesn't give you enough to figure it out, post some code to create sample data I can play with to tweak it.

Tamar

 

[Cpreston]

Hi

This is how my code is at the moment but I am getting a message as below, tried various things but cannot get the syntax right as yet. Also attached is some data that the original code I produced is giving, but as you will see the dates are duplicated per OTIF reason. Many thanks

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'OTIF'.



; With Data As
(
SELECT oh.Deliverydate
, oh.udfSalesOrderOTIF
, COUNT(oh.udfSalesOrderOTIF) AS OTIFCount
FROM dbrderHeader oh
--INNER JOIN dbo.SalesRep sr
--ON oh.SalesRepID = sr.SalesRepID
WHERE DeliveryDate >= '2016-04-01 00:00:00'
AND (oh.OrderStatus = 8)
GROUP BY oh.deliverydate, oh.udfSalesOrderOTIF
HAVING (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
AND (NOT (oh.udfSalesOrderOTIF IS NULL)))

SELECT *
FROM Data
PIVOT (SUM(OTIFCount)
FOR udfSalesOrderOTIF
IN ('OTIF','Early Delivery','Make-up','Not on system','Other','Production - not processed','Production - other','Sales - cancelled order',
'Administration','Sales - other','Transport - planned to fail','Transport - insufficient room on trailer','Transport - delayed' ,'Transport - other',
'Transport - vehicle broke down','Transport - wrong product delivered','Transport - lost','Yard - no stock','Yard - other','Yard - product missing /lost',
'Yard - rolled / damaged','Yard - wrong product delivered') as OTIFTotal

, Sum([Count]) As [Total]
, 100.0 * Sum([count]) / GrandTotal.GrandTotal AS Percentage
From Data
Cross Join GrandTotal
Group By Deliverydate, udfSalesOrderOTIF,GrandTotal.GrandTotal
Order By Deliverydate

 

[fredericofonseca]

kindly format the code and use the TGML tags to present it in a way we can look at it easily. you been here long enough and have been asked to do it several times.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[dhookom]

The PIVOT IN (...) should specify column heading titles in []s rather than single quotes.

IN ([OTIF],[Early Delivery],[Make-up],[Not on system],...,[Yard - wrong product delivered]) as OTIFTotal

I typically use the PIVOT last with the only statement after is ORDER BY.

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Hi

Apologies for the formatting I will try and use the TGML from now on. I did try what you suggested dhookhom earlier today before my last post for all the types of udfSalesOrderOTIF but I beleive it then complained about the Select * and the OTIF.

I beleive I also tried without these lines in below and also just including just the order by. However I have not tried you other suggestion doing it just before the Orderby which I can try tomorrow, but it appears to be the Select * and the OTIF etc.. that causes the issue. I will keep trying.

, Sum([Count]) As [Total]
, 100.0 * Sum([count]) / GrandTotal.GrandTotal AS Percentage
From Data
Cross Join GrandTotal
Group By Deliverydate, udfSalesOrderOTIF,GrandTotal.GrandTotal
Order By Deliverydate
​

Also just tried an indent and I must agree looks nicer and easier to read. Thanks all for your replys.

 

[Cpreston]

Hi

TamarGranor (Programmer)
Andrzejek (Programmer)

I had email alerts last night at 23.12 saying you guys had responded to the post but when I have come in I cannot see anything. Not sure why, if you did try to send a reply could you re-post.
I will be trying to solve it again today so fingers crossed.

Thanks

 

[Cpreston]

A little success at last. It now gives me the Dates all in order and the columns with the OTIF reasons going across the top with the count for that day.

I have tried to add a Total column to show at the end of OTIF reasons (so it shows the daily totals) called GrandTotal but this is giving the entire total down the whole of that column instead of the daily total for the OTIFs.
I will keep trying and update the post if I manage to solve it, otherwise if anyone as any ideas please advise. Thanks all (oh also tried tidying up full code so it is more readable on here)


; With Data As

​

(

SELECT oh.Deliverydate

,oh.udfSalesOrderOTIF
, COUNT(oh.udfSalesOrderOTIF) AS OTIFCount​

FROM dbrderHeader oh

​

WHERE DeliveryDate >= '2016-04-01 00:00:00'
AND (oh.OrderStatus = 8)
GROUP BY oh.deliverydate, oh.udfSalesOrderOTIF​

HAVING (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
AND (NOT (oh.udfSalesOrderOTIF IS NULL)))

, GrandTotal As
(
Select Sum([Otifcount]) As GrandTotal
From Data
)
​

SELECT *

FROM Data​

PIVOT (SUM(OTIFcount)
FOR udfSalesOrderOTIF​

IN ([OTIF],[Early Delivery],[Make-up],[Not on system],[Other],[Production - not processed],[Production - other],
[Sales - cancelled order], [Administration],[Sales - other],[Transport - planned to fail],[Transport - insufficient room on trailer],[Transport - delayed] ,
[Transport - other],[Transport - vehicle broke down],[Transport - wrong product delivered],[Transport - lost],[Yard - no stock],[Yard - other],[Yard - product missing /lost],
[Yard - rolled / damaged],[Yard - wrong product delivered])) AS OTIFTotal, GrandTotal

​

ORDER BY deliverydate

 

[dhookom]

I thought you were going to try using TGML?

This might work although it's air-code and I'm not nearly as knowledgeable as others in this forum.

; With Data As
(
SELECT oh.Deliverydate
,oh.udfSalesOrderOTIF
, COUNT(oh.udfSalesOrderOTIF) AS OTIFCount
FROM dbo.OrderHeader oh
WHERE DeliveryDate >= '2016-04-01 00:00:00'
AND (oh.OrderStatus = 8)
GROUP BY oh.deliverydate, oh.udfSalesOrderOTIF
HAVING (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
AND (NOT (oh.udfSalesOrderOTIF IS NULL)))

, cteGrandTotal As
(
Select Deliverydate, Sum([Otifcount]) As GrandTotal
From Data
GROUP BY DeliveryDate
)

, ctePiv as
(
SELECT *
FROM Data
PIVOT (SUM(OTIFcount)
FOR udfSalesOrderOTIF
IN ([OTIF],[Early Delivery],[Make-up],[Not on system],[Other],[Production - not processed],
[Production - other],  [Sales - cancelled order], [Administration],[Sales - other],
[Transport - planned to fail],[Transport - insufficient room on trailer],[Transport - delayed] ,
[Transport - other],[Transport - vehicle broke down],[Transport - wrong product delivered],
[Transport - lost],[Yard - no stock],[Yard - other],[Yard - product missing /lost],
[Yard - rolled / damaged],[Yard - wrong product delivered])) AS OTIFTotal
)
SELECT P.*, GT.GrandTotal
FROM ctePiv P JOIN cteGrandTotal GT ON P.DeliveryDate = GT.DeliveryDate
ORDER BY P.deliverydate

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Hi

I thought I had used TGML, I used the indent which made it a lot tidier . Are there some instructions anywhere?

Ok I just ran your code and it appears to have worked ok. I will examine the code and take a good note how it is coded. Many thanks for your help

 

[dhookom]

TGML couldn't be much easier. It's very much like using a word processor.

The icons for formatting your posts are above the input/post area. For instance if you want to format your SQL, just select your SQL statement and click the code icon. Enter "SQL" into the "Language" dialog box and click OK.

You can easily create bullet lists, bold, underline, colors, and other formats. Pre is great for displaying columns of data.

Always review your posts using the Preview button prior to submitting.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

instructions" - use Preview to see your posts before you Submit.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Hi

Oh got you, like I have done now, that's great. If you don't know it is there I doubt it would get used, but certainly will from now on, thanks again all.

; With Data As
(
SELECT oh.Deliverydate
,oh.udfSalesOrderOTIF
, COUNT(oh.udfSalesOrderOTIF) AS OTIFCount
FROM dbo.OrderHeader oh
WHERE DeliveryDate >= '2016-04-01 00:00:00'
AND (oh.OrderStatus = 8)
GROUP BY oh.deliverydate, oh.udfSalesOrderOTIF
HAVING (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
AND (NOT (oh.udfSalesOrderOTIF IS NULL)))

, cteGrandTotal As
(
Select Deliverydate, Sum([Otifcount]) As GrandTotal
From Data
GROUP BY DeliveryDate
)

, ctePiv as
(
SELECT *
FROM Data
PIVOT (SUM(OTIFcount)
FOR udfSalesOrderOTIF
IN ([OTIF],[Early Delivery],[Make-up],[Not on system],[Other],[Production - not processed],
[Production - other],  [Sales - cancelled order], [Administration],[Sales - other],
[Transport - planned to fail],[Transport - insufficient room on trailer],[Transport - delayed] ,
[Transport - other],[Transport - vehicle broke down],[Transport - wrong product delivered],
[Transport - lost],[Yard - no stock],[Yard - other],[Yard - product missing /lost],
[Yard - rolled / damaged],[Yard - wrong product delivered])) AS OTIFTotal
)
SELECT P.*, GT.GrandTotal
FROM ctePiv P JOIN cteGrandTotal GT ON P.DeliveryDate = GT.DeliveryDate
ORDER BY P.deliverydate

 

[dhookom]

Thanks CPreston. Much better.

Duane
Hook'D on Access
MS Access MVP