January 30, 2017 at 1:08 am
Hello friends
hopefully you can assist me
In my query, I need to frame a condition like
Count of activities of activity category 'EAS-1F' having no value for field 'Country’,
I wrote the query like:
;WITH CountryNull
AS
(
SELECT ACo.activityIncId, ACo.activitySqlId,
COUNT(CASE WHEN countryName IS NULL THEN activityCode ELSE NULL END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId AND ACo.isDeleted=0x0 AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS ON CS.countryIncId = ACT.countryIncId AND CS.countrySqlId = ACT.countrySqlId AND CS.isDeleted = 0x0
ON ACT.activityIncId = ACo.activityIncId AND ACT.activitySqlId = ACo.activitySqlId AND ACT.isDeleted = 0x0
WHERE countryName IS NULL AND SUBSTRING(ACo.activityCode, 2, 2) >= '15' AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
)
The query is returning results.
But having a doubt like this part of the code below is really required as I am checking whether the countryname is null and getting the count.
WHERE countryName IS NULL
Help please to rewrite it if it is correct or not.
January 30, 2017 at 1:46 am
VSSGeorge - Monday, January 30, 2017 1:08 AMHello friendshopefully you can assist me
In my query, I need to frame a condition like
Count of activities of activity category 'EAS-1F' having no value for field 'Country’,
I wrote the query like:;WITH CountryNull
AS
(
SELECT ACo.activityIncId, ACo.activitySqlId,
COUNT(CASE WHEN countryName IS NULL THEN activityCode ELSE NULL END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId AND ACo.isDeleted=0x0 AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS ON CS.countryIncId = ACT.countryIncId AND CS.countrySqlId = ACT.countrySqlId AND CS.isDeleted = 0x0
ON ACT.activityIncId = ACo.activityIncId AND ACT.activitySqlId = ACo.activitySqlId AND ACT.isDeleted = 0x0
WHERE countryName IS NULL AND SUBSTRING(ACo.activityCode, 2, 2) >= '15' AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
)The query is returning results.
But having a doubt like this part of the code below is really required as I am checking whether the countryname is null and getting the count.WHERE countryName IS NULL
Help please to rewrite it if it is correct or not.
Can we get some facts clear first:
There's no column called "country"
This isn't the entire query
Which table contains column "countryName"?
Which table contains column "activityCode"?
Here's a reformatted version of your CTE, can you please test that it works:
;WITH CountryNull AS (
SELECT
ACo.activityIncId,
ACo.activitySqlId,
COUNT(CASE WHEN countryName IS NULL THEN activityCode ELSE NULL END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo
ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId
AND ACo.isDeleted=0x0
AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN (dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS
ON CS.countryIncId = ACT.countryIncId
AND CS.countrySqlId = ACT.countrySqlId
AND CS.isDeleted = 0x0)
ON ACT.activityIncId = ACo.activityIncId
AND ACT.activitySqlId = ACo.activitySqlId
AND ACT.isDeleted = 0x0
WHERE countryName IS NULL
AND SUBSTRING(ACo.activityCode, 2, 2) >= '15'
AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
)
SELECT ...
[/code]
Edit: code formatting appears to have some issues this morning.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 30, 2017 at 2:25 am
ChrisM@Work - Monday, January 30, 2017 1:46 AMVSSGeorge - Monday, January 30, 2017 1:08 AMHello friendshopefully you can assist me
In my query, I need to frame a condition like
Count of activities of activity category 'EAS-1F' having no value for field 'Country’,
I wrote the query like:;WITH CountryNull
AS
(
SELECT ACo.activityIncId, ACo.activitySqlId,
COUNT(CASE WHEN countryName IS NULL THEN activityCode ELSE NULL END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId AND ACo.isDeleted=0x0 AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS ON CS.countryIncId = ACT.countryIncId AND CS.countrySqlId = ACT.countrySqlId AND CS.isDeleted = 0x0
ON ACT.activityIncId = ACo.activityIncId AND ACT.activitySqlId = ACo.activitySqlId AND ACT.isDeleted = 0x0
WHERE countryName IS NULL AND SUBSTRING(ACo.activityCode, 2, 2) >= '15' AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
)The query is returning results.
But having a doubt like this part of the code below is really required as I am checking whether the countryname is null and getting the count.WHERE countryName IS NULL
Help please to rewrite it if it is correct or not.
Can we get some facts clear first:
There's no column called "country"
This isn't the entire query
Which table contains column "countryName"?
Which table contains column "activityCode"?
Here's a reformatted version of your CTE, can you please test that it works:;WITH CountryNull AS (
SELECT
ACo.activityIncId,
ACo.activitySqlId,
COUNT(CASE WHEN countryName IS NULL THEN activityCode ELSE NULL END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo
ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId
AND ACo.isDeleted=0x0
AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN (dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS
ON CS.countryIncId = ACT.countryIncId
AND CS.countrySqlId = ACT.countrySqlId
AND CS.isDeleted = 0x0)
ON ACT.activityIncId = ACo.activityIncId
AND ACT.activitySqlId = ACo.activitySqlId
AND ACT.isDeleted = 0x0
WHERE countryName IS NULL
AND SUBSTRING(ACo.activityCode, 2, 2) >= '15'
AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
)
SELECT ...[/code]
Edit: code formatting appears to have some issues this morning.
The field 'Country' is shown based on the column name ‘countryname’ from the table 'Countries'.
The activitycode is the column of the table Activities ACo.
Since we need the count of activities, activitycode is used.
Since there is no direct relation between the tables Activities and Countries, it is joined through ActivitiesCountries table.
Hope you are clear now.
January 30, 2017 at 3:41 am
-- without the GROUP BY provides a good idea of the distribution of values
-- check the results by eye
SELECT
ACo.activityIncId,
ACo.activitySqlId,
cs.countryName
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo
ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId
AND ACo.isDeleted=0x0
AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN (dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS
ON CS.countryIncId = ACT.countryIncId
AND CS.countrySqlId = ACT.countrySqlId
AND CS.isDeleted = 0x0)
ON ACT.activityIncId = ACo.activityIncId
AND ACT.activitySqlId = ACo.activitySqlId
AND ACT.isDeleted = 0x0
WHERE 1 = 1 --countryName IS NULL
AND SUBSTRING(ACo.activityCode, 2, 2) >= '15'
AND ACo.activityCode like 'S[1-9]%'
-- with GROUP BY should yield expected results
SELECT
ACo.activityIncId,
ACo.activitySqlId,
SUM(CASE WHEN cs.countryName IS NULL THEN 1 ELSE 0 END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo
ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId
AND ACo.isDeleted=0x0
AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN (dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS
ON CS.countryIncId = ACT.countryIncId
AND CS.countrySqlId = ACT.countrySqlId
AND CS.isDeleted = 0x0)
ON ACT.activityIncId = ACo.activityIncId
AND ACT.activitySqlId = ACo.activitySqlId
AND ACT.isDeleted = 0x0
WHERE 1 = 1 --countryName IS NULL
AND SUBSTRING(ACo.activityCode, 2, 2) >= '15'
AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 30, 2017 at 5:47 am
Thanks for the reply, ChrisM@Work. I liked the way you have shown the working of the query.
I have a doubt now.
Will the condition check in the WHERE clause i.e. 1=1 work for multiple condition checks like the one below.
Here I am checking Subunitname and Internalname NULL count.
SUM(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN 1 ELSE 0 END) AS SU_MC_NullCount
January 30, 2017 at 7:27 am
VSSGeorge - Monday, January 30, 2017 5:47 AMThanks for the reply, ChrisM@Work. I liked the way you have shown the working of the query.
I have a doubt now.
Will the condition check in the WHERE clause i.e. 1=1 work for multiple condition checks like the one below.
Here I am checking Subunitname and Internalname NULL count.
SUM(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN 1 ELSE 0 END) AS SU_MC_NullCount
The 1=1 is just a placeholder - it doesn't do anything.
I'm not sure what you are asking. Your expression looks valid.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 30, 2017 at 8:26 am
ChrisM@Work - Monday, January 30, 2017 7:27 AMVSSGeorge - Monday, January 30, 2017 5:47 AMThanks for the reply, ChrisM@Work. I liked the way you have shown the working of the query.
I have a doubt now.
Will the condition check in the WHERE clause i.e. 1=1 work for multiple condition checks like the one below.
Here I am checking Subunitname and Internalname NULL count.
SUM(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN 1 ELSE 0 END) AS SU_MC_NullCountThe 1=1 is just a placeholder - it doesn't do anything.
I'm not sure what you are asking. Your expression looks valid.
I will explain what I was asking for:
I have another query like the one below, which check the condition
Count of activities of activity category 'EAS-1F' having no value for subunit, and InternalName.
This is a multiple column condition check as you can see where the columns subunit, and InternalName has to be checked.
I wrote the query like:
SELECT ASu.activityIncId, ASu.activitySqlId,
COUNT(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN activityCode ELSE NULL END) AS SU_MC_NullCount
FROM Activities ASu
INNER JOIN ActivitiesCategories ACTSu ON ACTSu.activityCategoryIncId = ASu.activityCategoryIncId
AND ACTSu.activityCategorySqlId = ASu.activityCategorySqlId AND ASu.isDeleted=0x0 AND ACTSu.isDeleted=0x0
AND ACTSu.activityCategoryCode = 'EAS-1F'
LEFT JOIN [dbo].SubUnits SU ON SU.subUnitIncId = ASu.subUnitWhoDoIncId
AND SU.subUnitSqlId = ASu.subUnitWhoDoSqlId
AND SU.isDeleted = 0x0
LEFT join [dbo].Activitiespositions ASP
INNER join [dbo].positions PO
ON PO.positionIncId=ASP.positionIncId and PO.positionSqlId=ASP.positionSqlId and PO.isDeleted=0x0
ON ASu.activityIncId=ASP.activityIncId and ASu.activitysqlId=ASP.activitysqlId
and ASu.isdeleted=0x0 and ASP.isdeleted=0x0
LEFT join [dbo].Contacts CT
on CT.contactIncId=ASP.contactIncId and CT.contactSqlId=ASP.contactSqlId and CT.isDeleted=0x0
LEFT join [dbo].Clients CL1
on CL1.clientIncId=CT.clientIncId and CL1.clientSqlId=CT.clientSqlId and CL1.isDeleted=0x0
and PO.positionCode IN ('EAS001','EAS002')
WHERE SUBSTRING(ASu.activityCode, 2, 2) >= '15' AND ASu.activityCode like 'S[1-9]%'
GROUP BY ASu.activityIncId, ASu.activitySqlId
HAVING ( COUNT(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN activityCode ELSE NULL END) = 0 )
After seeing you reply I changed the query like:
SELECT ASu.activityIncId, ASu.activitySqlId,
SUM(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN 1 ELSE 0 END) AS SU_MC_NullCount
FROM Activities ASu
INNER JOIN ActivitiesCategories ACTSu ON ACTSu.activityCategoryIncId = ASu.activityCategoryIncId
AND ACTSu.activityCategorySqlId = ASu.activityCategorySqlId AND ASu.isDeleted=0x0 AND ACTSu.isDeleted=0x0
AND ACTSu.activityCategoryCode = 'EAS-1F'
LEFT JOIN [dbo].SubUnits SU ON SU.subUnitIncId = ASu.subUnitWhoDoIncId
AND SU.subUnitSqlId = ASu.subUnitWhoDoSqlId
AND SU.isDeleted = 0x0
LEFT join [dbo].Activitiespositions ASP
INNER join [dbo].positions PO
ON PO.positionIncId=ASP.positionIncId and PO.positionSqlId=ASP.positionSqlId and PO.isDeleted=0x0
ON ASu.activityIncId=ASP.activityIncId and ASu.activitysqlId=ASP.activitysqlId
and ASu.isdeleted=0x0 and ASP.isdeleted=0x0
LEFT join [dbo].Contacts CT
on CT.contactIncId=ASP.contactIncId and CT.contactSqlId=ASP.contactSqlId and CT.isDeleted=0x0
LEFT join [dbo].Clients CL1
on CL1.clientIncId=CT.clientIncId and CL1.clientSqlId=CT.clientSqlId and CL1.isDeleted=0x0
and PO.positionCode IN ('EAS001','EAS002')
WHERE 1 = 1 AND SUBSTRING(ASu.activityCode, 2, 2) >= '15' AND ASu.activityCode like 'S[1-9]%'
GROUP BY ASu.activityIncId, ASu.activitySqlId
I am doubting will this query get the correct results.
January 31, 2017 at 3:49 am
Other than a join predicate in the wrong place it looks fine.
What "doubts" do you have?
If you have "doubts", have these been confirmed by your unit testing? You have tested it, right?
SELECT
ASu.activityIncId, ASu.activitySqlId,
SUM(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN 1 ELSE 0 END) AS SU_MC_NullCount
FROM Activities ASu
INNER JOIN ActivitiesCategories ACTSu
ON ACTSu.activityCategoryIncId = ASu.activityCategoryIncId
AND ACTSu.activityCategorySqlId = ASu.activityCategorySqlId
AND ASu.isDeleted=0x0 AND ACTSu.isDeleted=0x0
AND ACTSu.activityCategoryCode = 'EAS-1F'
LEFT JOIN [dbo].SubUnits SU
ON SU.subUnitIncId = ASu.subUnitWhoDoIncId
AND SU.subUnitSqlId = ASu.subUnitWhoDoSqlId
AND SU.isDeleted = 0x0
LEFT join [dbo].Activitiespositions ASP
INNER join [dbo].positions PO
ON PO.positionIncId=ASP.positionIncId
and PO.positionSqlId=ASP.positionSqlId
and PO.isDeleted=0x0
and PO.positionCode IN ('EAS001','EAS002') -- moved
ON ASu.activityIncId=ASP.activityIncId
and ASu.activitysqlId=ASP.activitysqlId
and ASu.isdeleted=0x0
and ASP.isdeleted=0x0
LEFT join [dbo].Contacts CT
on CT.contactIncId=ASP.contactIncId
and CT.contactSqlId=ASP.contactSqlId
and CT.isDeleted=0x0
LEFT join [dbo].Clients CL1
on CL1.clientIncId=CT.clientIncId
and CL1.clientSqlId=CT.clientSqlId
and CL1.isDeleted=0x0
-- and PO.positionCode IN ('EAS001','EAS002')
WHERE SUBSTRING(ASu.activityCode, 2, 2) >= '15'
AND ASu.activityCode like 'S[1-9]%'
GROUP BY ASu.activityIncId, ASu.activitySqlId
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 31, 2017 at 8:59 am
Dear ChrisM@Work ,Thank You for your patience and kindness. Your guidance helped me a lot.
January 31, 2017 at 9:33 am
VSSGeorge - Monday, January 30, 2017 1:08 AMHello friendshopefully you can assist me
In my query, I need to frame a condition like
Count of activities of activity category 'EAS-1F' having no value for field 'Country’,
I wrote the query like:
;WITH CountryNull
AS
(
SELECT ACo.activityIncId, ACo.activitySqlId,
COUNT(CASE WHEN countryName IS NULL THEN activityCode ELSE NULL END) AS CountryNullCount
FROM Activities ACo
INNER JOIN ActivitiesCategories ACTCo ON ACTCo.activityCategoryIncId = ACo.activityCategoryIncId
AND ACTCo.activityCategorySqlId = ACo.activityCategorySqlId AND ACo.isDeleted=0x0 AND ACTCo.isDeleted=0x0
AND ACTCo.activityCategoryCode = 'EAS-1F'
LEFT JOIN dbo.ActivitiesCountries AS ACT
INNER JOIN dbo.Countries AS CS ON CS.countryIncId = ACT.countryIncId AND CS.countrySqlId = ACT.countrySqlId AND CS.isDeleted = 0x0
ON ACT.activityIncId = ACo.activityIncId AND ACT.activitySqlId = ACo.activitySqlId AND ACT.isDeleted = 0x0
WHERE countryName IS NULL AND SUBSTRING(ACo.activityCode, 2, 2) >= '15' AND ACo.activityCode like 'S[1-9]%'
GROUP BY ACo.activityIncId, ACo.activitySqlId
)The query is returning results.
But having a doubt like this part of the code below is really required as I am checking whether the countryname is null and getting the count.
WHERE countryName IS NULLHelp please to rewrite it if it is correct or not.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 31, 2017 at 9:36 am
Only if you can first help us. Where is the DDL? What you did post has some serious design errors. We do not white with flags in SQL (thatwas assembly language), and many of your data element names do not conform to ISO 11179 naming rules. Things like“activity_category_code†make no sense; is the attribute property “_code†a “_category�
I see you are also still using the old camel case formatting; it reallydoes not work. Thanks to the way people process Latin alphabet words,your eye twitches to the Uppercase letters.
“Activities_Categoriesâ€should not be in a table in the query. The categories are an attribute of something, not an entity or a relationship. There should be a REFERENCES clause in the DDL, and not a join.
>>Help please to rewrite it if it is correct or not. <<
I think you need to start over and do it right. How do you write SQL without any idea as to what the tables look like? How are we supposed to do it? 80 to 90% of the work in SQL is done in the DDL, not in the DML. In particular, using REFERENCES assures the data integrity in your schema. You also need to take at least one course or read at least one book on basic data modeling.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 31, 2017 at 9:54 am
VSSGeorge - Tuesday, January 31, 2017 8:59 AMDear ChrisM@Work ,Thank You for your patience and kindness. Your guidance helped me a lot.
You're welcome, and thanks for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2017 at 11:21 pm
VSSGeorge - Monday, January 30, 2017 1:08 AMH
Only if you can first help us. Where is the DDL? What you did post has some serious design errors. We do not white with flags in SQL (thatwas assembly language), and many of your data element names do not conform to ISO 11179 naming rules. Things like“activity_category_code†make no sense; is the attribute property “_code†a “_category�
I see you are also still using the old camel case formatting; it reallydoes not work. Thanks to the way people process Latin alphabet words,your eye twitches to the Uppercase letters.
“Activities_Categoriesâ€should not be in a table in the query. The categories are an attribute of something, not an entity or a relationship. There should be a REFERENCES clause in the DDL, and not a join.
>>Help please to rewrite it if it is correct or not. <<
I think you need to start over and do it right. How do you write SQL without any idea as to what the tables look like? How are we supposed to do it? 80 to 90% of the work in SQL is done in the DDL, not in the DML. In particular, using REFERENCES assures the data integrity in your schema. You also need to take at least one course or read at least one book on basic data modeling.
Joe, Thanks for the feedback.
I am a beginner, as far as data modelling is concerned. If you can suggest a book which suits my level, it will be good. Thank You.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply