Comparison between multiple sql views join statement
-
Experts,
I am trying to bring the result from multiple sql views .Below is my code.
SELECT q1.RouteCode,SUM(Total) AS Total,SUM(omers) AS omers,SUM(U) AS U,SUM(SPAR) AS SPAR,SUM(STI) AS STI,SUM(OTHERS) AS OTHERS,SUM(Best) AS Best),SUM(Number) AS NumberFROM(SELECT RouteCode,sum(Units) as Total,count(distinct Customercode) as omers,count(distinct productcode) as U,SUM(CASE WHEN productcode IN (90972102) THEN Units ELSE 0 END) AS SPAR, SUM(CASE WHEN productcode IN (82641500) THEN Units ELSE 0 END) AS STI,SUM(CASE WHEN PRODUCTCODE IN (70161900) THEN UNITS ELSE 0 END ) AS OTHERS,FORMAT(max([Date]),'hh\:mm') as [Last Transaction time], Route_Name = case RouteCode when 'NA' then 'Martin' else nullend FROM [INTERFACE_1].[dbo].[v_E40OrdersahQt] where [DeliveryDate] > CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) and (RouteCode LIKE 'NA') GROUP BY [RouteCode])q1JOIN(SELECT RouteCode, count(distinct (case when AccessCode = 0 then CustomerCode end)) as Best FROM [INTERFACE_1].[dbo].[ v_e40ahQt_Log] where cast([date]as Date) LIKE '2018-09-09' AND CustomerCode LIKE '900%' GROUP BY [RouteCode] )q2ON q2.[RouteCode] = q1.[RouteCode]JOIN( SELECT RouteCode, count(CustomerCode) as Number FROM [INTERFACE_1].[dbo].[ v_e40ahQt_Plan] WHERE CONVERT(DATE,VisitDay)=CONVERT(Date,GETDATE()) AND CustomerCode LIKE '900%' GROUP BY [RouteCode] )q3 ON q3.[RouteCode] = q1.[RouteCode] GROUP BY q1.[RouteCode] WITH ROLLUPQuery output:
Requirement: I need another column(results) which should compare between (best vs omers+number). Ex: as you can see below omers has 4 records and number has 2 records with duplication and total(omers+number) record is 4 but best has 3 only in this case result should be 1.
-
Some DDL and sample data would be helpful as well as expected outcome, not what you are currently getting
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Your query, as well as being suitable only for monitors which are >16 feet wide, does not parse.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - ShawnBryan - Monday, September 10, 2018 11:25 AMExperts,
I am trying to bring the result from multiple sql views .Below is my code.
SELECT q1.RouteCode,SUM(Total) AS Total,SUM(omers) AS omers,SUM(U) AS U,SUM(SPAR) AS SPAR,SUM(STI) AS STI,SUM(OTHERS) AS OTHERS,SUM(Best) AS Best),SUM(Number) AS NumberFROM(SELECT RouteCode,sum(Units) as Total,count(distinct Customercode) as omers,count(distinct productcode) as U,SUM(CASE WHEN productcode IN (90972102) THEN Units ELSE 0 END) AS SPAR, SUM(CASE WHEN productcode IN (82641500) THEN Units ELSE 0 END) AS STI,SUM(CASE WHEN PRODUCTCODE IN (70161900) THEN UNITS ELSE 0 END ) AS OTHERS,FORMAT(max([Date]),'hh\:mm') as [Last Transaction time], Route_Name = case RouteCode when 'NA' then 'Martin' else nullend FROM [INTERFACE_1].[dbo].[v_E40OrdersahQt] where [DeliveryDate] > CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) and (RouteCode LIKE 'NA') GROUP BY [RouteCode])q1JOIN(SELECT RouteCode, count(distinct (case when AccessCode = 0 then CustomerCode end)) as Best FROM [INTERFACE_1].[dbo].[ v_e40ahQt_Log] where cast([date]as Date) LIKE '2018-09-09' AND CustomerCode LIKE '900%' GROUP BY [RouteCode] )q2ON q2.[RouteCode] = q1.[RouteCode]JOIN( SELECT RouteCode, count(CustomerCode) as Number FROM [INTERFACE_1].[dbo].[ v_e40ahQt_Plan] WHERE CONVERT(DATE,VisitDay)=CONVERT(Date,GETDATE()) AND CustomerCode LIKE '900%' GROUP BY [RouteCode] )q3 ON q3.[RouteCode] = q1.[RouteCode] GROUP BY q1.[RouteCode] WITH ROLLUPQuery output:
Requirement: I need another column(results) which should compare between (best vs omers+number). Ex: as you can see below omers has 4 records and number has 2 records with duplication and total(omers+number) record is 4 but best has 3 only in this case result should be 1.
Let's start with fixing the query you supplied, as the formatting after you pasted it into a code block was pretty bad:
SELECT
q1.RouteCode,
SUM(Total) AS Total,
SUM(omers) AS omers,
SUM(U) AS U,
SUM(SPAR) AS SPAR,
SUM(STI) AS STI,
SUM(OTHERS) AS OTHERS,
SUM(Best) AS Best,
SUM(Number) AS Number
FROM (
SELECT
RouteCode,
SUM(Units) AS Total,
COUNT(DISTINCT Customercode) AS omers,
COUNT(DISTINCT productcode) AS U,
SUM(CASE WHEN productcode IN (90972102) THEN Units ELSE 0 END) AS SPAR,
SUM(CASE WHEN productcode IN (82641500) THEN Units ELSE 0 END) AS STI,
SUM(CASE WHEN productcode IN (70161900) THEN Units ELSE 0 END ) AS OTHERS,
FORMAT(MAX([Date]), 'hh\:mm') AS [Last Transaction time],
Route_Name = CASE RouteCode
WHEN 'NA' THEN 'Martin'
ELSE NULL
END
FROM INTERFACE_1.dbo.v_E40OrdersahQt
WHERE DeliveryDate > CAST(FLOOR(CAST(GETDATE() AS float))AS datetime)
AND RouteCode LIKE 'NA'
GROUP BY RouteCode
) AS q1
INNER JOIN (
SELECT
RouteCode,
COUNT(DISTINCT (CASE WHEN AccessCode = 0 THEN CustomerCode END)) AS Best
FROM INTERFACE_1.dbo.[ v_e40ahQt_Log]
WHERE CAST([date] AS date) LIKE '2018-09-09'
AND CustomerCode LIKE '900%'
GROUP BY RouteCode
) AS q2
ON q2.RouteCode = q1.RouteCode
INNER JOIN (
SELECT
RouteCode,
COUNT(CustomerCode) AS Number
FROM INTERFACE_1.dbo.[ v_e40ahQt_Plan]
WHERE CONVERT(date, VisitDay) = CONVERT(Date, GETDATE())
AND CustomerCode LIKE '900%'
GROUP BY RouteCode
) AS q3
ON q3.RouteCode = q1.RouteCode
GROUP BY q1.RouteCode
WITH ROLLUP;Note that there appears to be a view with a leading space in the name, unless that was also a formatting problem?
Note also that I've removed square brackets wherever they are not actually needed, and that I removed a ) just after the column alias Best in the very first SELECT.And now let's address your question. First, I can't really understand exactly what you are trying to accomplish. You say you want to compare, but the rules for making such a comparison are not in any way clear. Please be much more detailed and specific as to what you are looking to achieve.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply