August 28, 2014 at 3:49 pm
This is what I originally had, to get two column counts and displayed in one table. It displayed fine but the counts are the same for both tables data.
Table SYNCHMESSAGE:
ABC
ABC
DEF
DEF
DEF
GHI
GHI
GHI
GHI
Table IMPORTMESSAGE:
CBA
CBA
CBA
CBA
CBA
FED
FED
IHG
IHG
IHG
IHG
IHG
Output:
SYNCHMESSAGE COUNT IMPORTMESSAGE COUNT
--------------------------------------------------------------
ABC 2 CBA 2
DEF 3 FED 3
GHI 4 IHG 4
Query:
SELECT
B.SYNCHMESSAGE as 'td', '',
COUNT(B.SYNCHMESSAGE) as 'td', '',
A.IMPORTMESSAGE as 'td', '',
COUNT(A.IMPORTMESSAGE) as 'td', ''
FROM
DataDEV.dbo.SYNCHTERMS AS B
LEFT JOIN
Data.dbo.Data_Errors AS A ON A.Key = B.KEY
WHERE
DAY(GETDATE()) = DAY(B.DATEADDED)
AND MONTH(GETDATE()) = MONTH(B.dateadded)
AND DAY(GETDATE()) = DAY(A.IMPORTDATE)
AND MONTH(GETDATE()) = MONTH(A.IMPORTDATE)
GROUP BY
B.SYNCHMESSAGE, A.IMPORTMESSAGE
ORDER BY
B.SYNCHMESSAGE DESC
Expected results:
SYNCHMESSAGE COUNT IMPORTMESSAGE COUNT
-----------------------------------------------------
ABC 2 CBA 5
DEF 3 FED 2
GHI 4 IHG 5
August 28, 2014 at 4:57 pm
Something like below. I don't have test data, so haven't tested it yet.
Edit: The WHERE conditions on your original query caused the LEFT JOIN to become a de factor INNER JOIN, which threw off the totals. I did more of a query re-write than that to perhaps improve the query overall.
SELECT
B.SYNCHMESSAGE as 'td', '',
MAX(B.row_count) AS [COUNT],
A.IMPORTMESSAGE as 'td', '',
MAX(A.row_count) AS [COUNT]
FROM (
SELECT
, SYNCHMESSAGE, COUNT(*) AS row_count
FROM DataDEV.dbo.SYNCHTERMS
WHERE
DATEADDED >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
DATEADDED < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
GROUP BY
, SYNCHMESSAGE
) AS B
LEFT OUTER JOIN (
SELECT
, IMPORTMESSAGE, COUNT(*) AS row_count
FROM Data.dbo.Data_Errors
WHERE
IMPORTDATE >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
IMPORTDATE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
GROUP BY
, IMPORTMESSAGE
) AS A ON
A. = B.
GROUP BY
B.SYNCHMESSAGE, A.IMPORTMESSAGE
ORDER BY
B.SYNCHMESSAGE DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 28, 2014 at 11:05 pm
Quick suggestion, count each table content in a separate CTE and join them for the combined results
😎
USE tempdb;
GO
/* Test data */
WITH SYNCHMESSAGE (M_KEY,M_MSG) AS
(
SELECT
M_KEY
,M_MSG
FROM
(VALUES
(1,'ABC')
,(1,'ABC')
,(2,'DEF')
,(2,'DEF')
,(2,'DEF')
,(3,'GHI')
,(3,'GHI')
,(3,'GHI')
,(3,'GHI')
) AS X(M_KEY,M_MSG)
)
,IMPORTMESSAGE(M_KEY,M_MSG) AS
(
SELECT
M_KEY
,M_MSG
FROM
(VALUES
(1,'CBA')
,(1,'CBA')
,(1,'CBA')
,(1,'CBA')
,(1,'CBA')
,(2,'FED')
,(2,'FED')
,(3,'IHG')
,(3,'IHG')
,(3,'IHG')
,(3,'IHG')
,(3,'IHG')
) AS X(M_KEY,M_MSG)
)
/* Use CTEs to do the counting */
,SYNC_BASE AS
(
SELECT
SM.M_KEY
,MAX(SM.M_MSG) AS M_MSG
,COUNT(SM.M_MSG) AS MSG_COUNT
FROM SYNCHMESSAGE SM
GROUP BY SM.M_KEY
)
,IMP_BASE AS
(
SELECT
SM.M_KEY
,MAX(SM.M_MSG) AS M_MSG
,COUNT(SM.M_MSG) AS MSG_COUNT
FROM IMPORTMESSAGE SM
GROUP BY SM.M_KEY
)
SELECT
SB.M_MSG AS SB_M_MSG
,SB.MSG_COUNT AS SB_MSG_COUNT
,IB.M_MSG AS IB_M_MSG
,IB.MSG_COUNT AS IB_MSG_COUNT
FROM SYNC_BASE SB
LEFT OUTER JOIN IMP_BASE IB
ON SB.M_KEY = IB.M_KEY;
Results
SB_M_MSG SB_MSG_COUNT IB_M_MSG IB_MSG_COUNT
-------- ------------ -------- ------------
ABC 2 CBA 5
DEF 3 FED 2
GHI 4 IHG 5
August 29, 2014 at 6:30 am
thanks much for your response. Testing it now but not sure about the variable. What does this represent? ?
Msg 207, Level 16, State 1, Line 8
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'KEY'.
August 29, 2014 at 6:44 am
USE tempdb;
GO
/* Test data */
WITH SYNCHMESSAGE (M_KEY,M_MSG) AS
(
SELECT
M_KEY
,M_MSG
FROM
(VALUES
(1,'ABC')
,(1,'ABC')
,(2,'DEF')
,(2,'DEF')
,(2,'DEF')
,(3,'GHI')
,(3,'GHI')
,(3,'GHI')
,(3,'GHI')
) AS X(M_KEY,M_MSG)
)
,IMPORTMESSAGE(M_KEY,M_MSG) AS
(
SELECT
M_KEY
,M_MSG
FROM
(VALUES
(1,'CBA')
,(1,'CBA')
,(1,'CBA')
,(1,'CBA')
,(1,'CBA')
,(2,'FED')
,(2,'FED')
,(3,'IHG')
,(3,'IHG')
,(3,'IHG')
,(3,'IHG')
,(3,'IHG')
) AS X(M_KEY,M_MSG)
)
/* Use CTEs to do the counting */
,SYNC_BASE AS
(
SELECT
SM.M_KEY
,MAX(SM.M_MSG) AS M_MSG
,COUNT(SM.M_MSG) AS MSG_COUNT
FROM SYNCHMESSAGE SM
GROUP BY SM.M_KEY
)
,IMP_BASE AS
(
SELECT
SM.M_KEY
,MAX(SM.M_MSG) AS M_MSG
,COUNT(SM.M_MSG) AS MSG_COUNT
FROM IMPORTMESSAGE SM
GROUP BY SM.M_KEY
)
SELECT
SB.M_MSG AS SB_M_MSG
,SB.MSG_COUNT AS SB_MSG_COUNT
,IB.M_MSG AS IB_M_MSG
,IB.MSG_COUNT AS IB_MSG_COUNT
FROM SYNC_BASE SB
LEFT OUTER JOIN IMP_BASE IB
ON SB.M_KEY = IB.M_KEY;
This is actually quite confusing but let me give it a try. Thanks. Appreciate the response. Will follow up.
August 29, 2014 at 8:12 am
ScottPletcher (8/28/2014)
Something like below. I don't have test data, so haven't tested it yet.Edit: The WHERE conditions on your original query caused the LEFT JOIN to become a de factor INNER JOIN, which threw off the totals. I did more of a query re-write than that to perhaps improve the query overall.
SELECT
B.SYNCHMESSAGE as 'td', '',
MAX(B.row_count) AS [COUNT],
A.IMPORTMESSAGE as 'td', '',
MAX(A.row_count) AS [COUNT]
FROM (
SELECT
, SYNCHMESSAGE, COUNT(*) AS row_count
FROM DataDEV.dbo.SYNCHTERMS
WHERE
DATEADDED >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
DATEADDED < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
GROUP BY
, SYNCHMESSAGE
) AS B
LEFT OUTER JOIN (
SELECT
, IMPORTMESSAGE, COUNT(*) AS row_count
FROM Data.dbo.Data_Errors
WHERE
IMPORTDATE >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
IMPORTDATE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
GROUP BY
, IMPORTMESSAGE
) AS A ON
A. = B.
GROUP BY
B.SYNCHMESSAGE, A.IMPORTMESSAGE
ORDER BY
B.SYNCHMESSAGE DESC
I am not getting the desired results here:
Client key missing or not complete36extraneous input ')' expecting EOF (at position 136)1
Client key missing or not complete36no viable alternative at character '“' (at position 11)1
Client key missing or not complete36no viable alternative at input 'OR' (at position 23)1
Client key missing or not complete36no viable alternative at input 'OR' (at position 30)1
Client key missing or not complete36no viable alternative at input 'OR' (at position 37)1
Client key missing or not complete36no viable alternative at input 'OR' (at position 450)1
Client key missing or not complete36no viable alternative at input 'OR' (at position 518)1
Client key missing or not complete36no viable alternative at input 'OR' (at position 659)1
Client key missing or not complete36Phrases cannot be empty (at position 158)1
would expect unique values to appear.
August 29, 2014 at 8:50 am
j_depp_99 (8/29/2014)
thanks much for your response. Testing it now but not sure about the variable. What does this represent? ?Msg 207, Level 16, State 1, Line 8
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'KEY'.
I copied it from your very first post:
LEFT JOIN
Data.dbo.Data_Errors AS A ON A.Key = B.KEY
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2014 at 9:42 am
ScottPletcher (8/29/2014)
j_depp_99 (8/29/2014)
thanks much for your response. Testing it now but not sure about the variable. What does this represent? ?Msg 207, Level 16, State 1, Line 8
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'KEY'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'KEY'.
I copied it from your very first post:
LEFT JOIN
Data.dbo.Data_Errors AS A ON A.Key = B.KEY
Sorry about that; my slip up. I put the join on the two tables that really only have a single common column (projectKey) but one table can have a clientkey entry while the other would not have the same key. So I think I need to change the left join, right?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply