Click here to Skip to main content
14,669,437 members
Home » Discussions » Database
   

Database

 
GeneralRe: Database suddenly slow Pin
Mycroft Holmes19-Sep-19 13:14
professionalMycroft Holmes19-Sep-19 13:14 
AnswerRe: Database suddenly slow Pin
Member 1240381729-Dec-19 23:35
MemberMember 1240381729-Dec-19 23:35 
QuestionI need this windows odbc driver Pin
Member 1458760611-Sep-19 8:00
MemberMember 1458760611-Sep-19 8:00 
AnswerRe: I need this windows odbc driver Pin
OriginalGriff11-Sep-19 8:01
mveOriginalGriff11-Sep-19 8:01 
GeneralRe: I need this windows odbc driver Pin
Member 1458760611-Sep-19 8:16
MemberMember 1458760611-Sep-19 8:16 
AnswerRe: I need this windows odbc driver Pin
Maciej Los11-Sep-19 8:47
mveMaciej Los11-Sep-19 8:47 
QuestionLooking for some assistance with a query Pin
FrankLepkowski10-Sep-19 9:50
MemberFrankLepkowski10-Sep-19 9:50 
AnswerRe: Looking for some assistance with a query Pin
MadMyche10-Sep-19 11:12
mveMadMyche10-Sep-19 11:12 
A couple of tweaks is all you are going to need:

Using an INNER JOIN requires matching record to be in both tables.
To get all records from one table regardless if it matches or not you would use either a LEFTRIGHT JOIN. As you want all the records from the second table in the ON connector it would be a RIGHT JOIN

The second thing is dealing with returning 0 if no records match. Relatively easy, just slap an ISNULL (or COALESCE for ANSI SQL) with 0 as the null replacement.

My first attempt at this came up with
SELECT m.LoopCode
,       m.Chopper
,       Total_BRKS  = IsNull(Sum(s.BRKS), 0)
,       Total_BBOH  = IsNull(Sum(s.BBOH), 0)
,       Avg_DTAB        = IsNull(Avg(s.DTAB), 0)

FROM        @ShiftData          s
RIGHT JOIN @MapBushingData  m ON s.[Position] = m.LoopCode

WHERE ( s.ShiftDate >= cast('02/03/2011' as date)
    AND     s.ShiftDate <= cast('03/04/2013' as date)
)
OR  s.ShiftDate IS NULL

GROUP BY m.Chopper,m.LoopCode
And in testing I found out that IF there is a matched record BUT the date was out of range it would not return the record.
If this is desired; great.
If not, we are going to tweak how the data is JOINed together by relocating the WHERE clauses to also be part of the JOIN connections. And this is what I came up with
DECLARE @ShiftData TABLE (ndx int identity(1,1) not null, Position int, ShiftDate Date, BRKS int, BBOH int, DTAB int)
INSERT @ShiftData
VALUES (1, '10/11/2012', 1, 2, 3)
,  (2, '12/11/2013', 3, 4, 5)
,  (3, '10/11/2011', 1, 2, 3)

DECLARE @MapBushingData TABLE (ndx int identity(1,1) not null, LoopCode int, Chopper varchar(10))
INSERT @MapBushingData
VALUES (1, 'Chopper 1')
, (2, 'Chopper 2')
, (3, 'Chopper 3')
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional

GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 1:56
mveMadMyche11-Sep-19 1:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 4:15
MemberFrankLepkowski11-Sep-19 4:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 6:28
mveMadMyche11-Sep-19 6:28 
QuestionNeed help mixing two queries in one... Pin
Joan M1-Sep-19 0:40
professionalJoan M1-Sep-19 0:40 
AnswerRe: Need help mixing two queries in one... Pin
Richard Deeming2-Sep-19 1:02
mveRichard Deeming2-Sep-19 1:02 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 1:39
professionalJörgen Andersson2-Sep-19 1:39 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 10:24
professionalJoan M2-Sep-19 10:24 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:27
professionalJörgen Andersson2-Sep-19 19:27 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 19:29
professionalJoan M2-Sep-19 19:29 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:35
professionalJörgen Andersson2-Sep-19 19:35 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 10:21
professionalJoan M2-Sep-19 10:21 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 10:43
mvephil.o2-Sep-19 10:43 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 11:24
professionalJoan M2-Sep-19 11:24 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 12:25
mvephil.o2-Sep-19 12:25 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 18:52
professionalJoan M2-Sep-19 18:52 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes3-Sep-19 12:40
professionalMycroft Holmes3-Sep-19 12:40 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 9:24
professionalJoan M4-Sep-19 9:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.