Need to add date field in 1

[Cpreston]

Hi

Recently (and from help from you guys) I had a query that I now need to add in a filed called oh.deliverydate
This is so our front end system as a date field from which we can select between dates. I have tried to add it in but it then takes out the count levels for OTIF for each sales manager, it totals correctly but does not show the correct figured per sales manager. Could someone advise where I need to add it in with syntax please. Thanks

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


FROM dbrderHeader 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))


), GrandTotal As
(
Select Sum([Count]) As GrandTotal
From Data
)
Select udfSalesOrderOTIF
, Coalesce(Min(Case When Name = 'Bob Banks' Then [Count] End), 0) As [Bob]
, Coalesce(Min(Case When Name = 'Antony Considine' Then [Count] End), 0) As [Antony]
, Coalesce(Min(Case When Name = 'Eddie O''Riordan' Then [Count] End), 0) As [Eddie]
, Coalesce(Min(Case When Name = 'Lawrie Gingel' Then [Count] End), 0) As [Lawrie]
, Coalesce(Min(Case When Name = 'Office' Then [Count] End), 0) As [Office]
, Coalesce(Min(Case When Name = 'Scott Laughton' Then [Count] End), 0) As [Scott]
, Coalesce(Min(Case When Name = 'Ray Cowburn' Then [Count] End), 0) As [Ray]
, Coalesce(Min(Case When Name = 'Lee Heitzman' Then [Count] End), 0) As [Lee]
, Coalesce(Min(Case When Name = 'Administration' Then [Count] End), 0) As [Admin]
, Coalesce(Min(Case When Name = 'Paul Lowery' Then [Count] End), 0) As [Paul]
, Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group]
, Sum([Count]) As [Total]
, 100.0 * Sum([count]) / GrandTotal.GrandTotal AS Percentage
From Data
Cross Join GrandTotal
Group By udfSalesOrderOTIF,GrandTotal.GrandTotal

 

[TamarGranor]

If you're trying to total only the records between two dates, you don't need to put the date field into the field list. You just need to add the comparison to the WHERE clause. I'm guessing that what you're looking for is:

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


FROM dbo.OrderHeader oh
INNER JOIN dbo.SalesRep sr
ON oh.SalesRepID = sr.SalesRepID
WHERE DeliveryDate BETWEEN @StartDate AND @EndDate <====== Here's the new condition. 
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))


), GrandTotal As
(
Select Sum([Count]) As GrandTotal
From Data
)
Select udfSalesOrderOTIF
, Coalesce(Min(Case When Name = 'Bob Banks' Then [Count] End), 0) As [Bob]
, Coalesce(Min(Case When Name = 'Antony Considine' Then [Count] End), 0) As [Antony]
, Coalesce(Min(Case When Name = 'Eddie O''Riordan' Then [Count] End), 0) As [Eddie]
, Coalesce(Min(Case When Name = 'Lawrie Gingel' Then [Count] End), 0) As [Lawrie]
, Coalesce(Min(Case When Name = 'Office' Then [Count] End), 0) As [Office]
, Coalesce(Min(Case When Name = 'Scott Laughton' Then [Count] End), 0) As [Scott]
, Coalesce(Min(Case When Name = 'Ray Cowburn' Then [Count] End), 0) As [Ray]
, Coalesce(Min(Case When Name = 'Lee Heitzman' Then [Count] End), 0) As [Lee]
, Coalesce(Min(Case When Name = 'Administration' Then [Count] End), 0) As [Admin]
, Coalesce(Min(Case When Name = 'Paul Lowery' Then [Count] End), 0) As [Paul]
, Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group]
, Sum([Count]) As [Total]
, 100.0 * Sum([count]) / GrandTotal.GrandTotal AS Percentage
From Data
Cross Join GrandTotal
Group By udfSalesOrderOTIF,GrandTotal.GrandTotal

You'll need to populate the @StartDate and @EndDate variables before running this, of course.

Tamar

 

[Olaf Doschke]

I followed some of your threads and overall you're doing very many statistical analytics on your data in the dimensions of employees, time, etc.
That rather is the job of a datawarehouse and you force BI analysis on your OLTP (online transactional processing) database while there are other data representations for OLAP (online analytical processing).

So maybe take a step back and look into totally different tools MSSQL offers, too, eg OLAP cudes and MDX.

I'm not saying I'm an expert in this, but the direction of the answers you want surely is BI analytics.

Bye, Olaf.

 

[Cpreston]

Hi

Yes Tamar i just need to say between 2 dates.
however i need the dates dynamic so a user can choose the datess they require.

The front end system for a smart view as a parameter tool to do this but of course it needs the deliverydate in the sql code so it can ne used for the parameter.

Thanks

 

[Cpreston]

Hi

I have managed to get it working now thanks TamarGranor

I adapted the front end code with the WHERE clause but sued their date parameter tool. All working now. In this case I think it was just knowing whereabouts to place the Where clause.

many thanks