Help with the count function
-
CREATE TABLE [dbo].[quality](
[qualityID] [int] NULL,
[qualitydesc] [nvarchar](100) NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
GO
data for the table qualityCheck is as follows
2010-11-30 00:00:00.000 4 XXXX2010-12-15 00:00:00.000 4 YYYYY
2010-12-15 00:00:00.000 4 ZZZZZ
2013-07-11 00:00:00.000 1 Watever
2013-03-12 00:00:00.000 2 This
2012-12-03 00:00:00.000 1 that
2013-02-20 00:00:00.000 1 nothing
2011-10-14 00:00:00.000 1 To worry about
2013-03-28 00:00:00.000 1 this
2013-03-28 00:00:00.000 1 is
2011-11-15 00:00:00.000 1 a
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2011-11-21 00:00:00.000 1 To worry about
2013-03-06 00:00:00.000 3 To worry about
2012-03-15 00:00:00.000 1 To worry about
2013-03-12 00:00:00.000 1 To worry about
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[quality](
[qualityID] [int] NULL,
[qualitydesc] [nvarchar](100) NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
GO
data for the table quality table is as follows
1 IO True2 Min True
3 Max True
4 Reg True
5 Other True
then i run a query as follows for a count of the qualityDescriptions according to dueDate
SELECT COUNT(*) AS TotalCount,Quality.Qualitydesc,QualityCheck.DueDAte
FROM QualityCheck INNER JOIN Quality
ON QualityCheck.QualityID = Quality.qualityID
GROUP BY quality.qualityID,QualityCheck.DueDate,quality.qualitydesc
having
DATEDIFF(d,DueDate,GETDATE()) >= 90
and the result set of the query is as shown below
1 IO 2011-10-14 00:00:00.0001 IO 2011-11-15 00:00:00.000
11 IO 2011-11-21 00:00:00.000
1 IO 2012-03-15 00:00:00.000
1 IO 2012-12-03 00:00:00.000
1 IO 2013-02-20 00:00:00.000
2 IO 2013-03-12 00:00:00.000
2 IO 2013-03-28 00:00:00.000
1 IO 2013-07-11 00:00:00.000
1 Min 2013-03-12 00:00:00.000
1 Max 2013-03-06 00:00:00.000
1 Reg 2010-11-30 00:00:00.000
2 Reg 2010-12-15 00:00:00.000
1 Other 2013-08-19 00:00:00.000
however i want the result to coalesce the count of IO into one row, Min's into one row i.e. the aggregate results to be displayed. something like below, is this possible ?
22 IO 90 Days elapsed1 Min 90 Days elapsed
1 Max 90 Days elapsed
3 Reg 90 Days elapsed
1 Other 90 Days elapsed
-
Try this:
SELECT COUNT(*) AS TotalCount,Quality.Qualitydesc
FROM QualityCheck INNER JOIN Quality
ON QualityCheck.QualityID = Quality.qualityID
WHERE DATEDIFF(d,DueDate,GETDATE()) >= 90
GROUP BY quality.qualityDesc
The code section you posted as the table definitions were the same so just guessed the table definition of quality check.
Fitz
-
Thanks that was what i was exactly looking for
thanks again
-
You've received what you wanted, but here's some additional help. 😉
In the future, review the DDL and sample data that you post. You posted the same table twice and missed one table. You should post your sample data as insert statements to make easier to test the code before posting a solution. Something like this:
CREATE TABLE #qualitycheck(
[DueDate] [datetime] NULL,
[qualityID] [int] NULL,
[Sometext] [nvarchar](100) NULL
)
INSERT INTO #qualitycheck VALUES(
'2010-11-30 00:00:00.000', 4, 'XXXX'),(
'2010-12-15 00:00:00.000', 4, 'YYYYY'),(
'2010-12-15 00:00:00.000', 4, 'ZZZZZ'),(
'2013-07-11 00:00:00.000', 1, 'Watever'),(
'2013-03-12 00:00:00.000', 2, 'This'),(
'2012-12-03 00:00:00.000', 1, 'that '),(
'2013-02-20 00:00:00.000', 1, 'nothing'),(
'2011-10-14 00:00:00.000', 1, 'To worry about'),(
'2013-03-28 00:00:00.000', 1, 'this '),(
'2013-03-28 00:00:00.000', 1, 'is '),(
'2011-11-15 00:00:00.000', 1, 'a'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2011-11-21 00:00:00.000', 1, 'To worry about'),(
'2013-03-06 00:00:00.000', 3, 'To worry about'),(
'2012-03-15 00:00:00.000', 1, 'To worry about'),(
'2013-03-12 00:00:00.000', 1, 'To worry about')
CREATE TABLE #quality(
[qualityID] [int] NULL,
[qualitydesc] [nvarchar](100) NULL,
[IsActive] [bit] NULL
)
INSERT INTO #quality VALUES(
1, 'IO',1 ),(
2, 'Min',1 ),(
3, 'Max',1 ),(
4, 'Reg',1 ),(
5, 'Other',1)
Knowing that, let's review the code. There's a non-SARGable clause that you might want to change. If you have functions in your columns, you won't be able to use indexes correctly. The first solution that comes to mind is to change it like this:
WHERE DueDate <= DATEADD(d,-90,GETDATE())But that date will include time, so we might want to remove time. A more complex function can help with that.
WHERE DueDate <= DATEADD(d,DATEDIFF( DD, 90, GETDATE()), 0)If you want to check the differences, run the following code.
SELECT DATEADD(d,-90,GETDATE()) UNION ALL
SELECT DATEADD(d,DATEDIFF( DD, 90, GETDATE()), 0)
Try to understand how that works and it will help you with many date/time calculations. If you have questions, feel free to ask. 😉
-
I thought i was done but didnot account for values with a count of '0'
So how would i return '0' for descriptions i.e. any of the results that do not have a value. So in my example if the quality "reg" had no values in the quality check column instead of not showing the "Reg" is there a way to return a 0 value ? . As a result the data would look something like this
22 IO 90 Days elapsed
1 Min 90 Days elapsed
1 Max 90 Days elapsed
0 Reg 90 Days elapsed
1 Other 90 Days elapsed
-
I couldn't get the results with the sample data that you posted, but this should be what you're looking for.
SELECT COUNT(qc.QualityID) AS TotalCount,
q.Qualitydesc
FROM Quality q
LEFT
JOIN QualityCheck qc ON qc.QualityID = q.qualityID
AND qc.DueDate >= DATEADD(d,DATEDIFF( DD, 90, GETDATE()), 0)
GROUP BY q.qualityDesc
-
Sweeeeeeeeeetttttt
-
SQLTestUser (6/5/2014)
SweeeeeeeeeettttttThe question is, do you understand the concept of "SARGability" the Luis explained? I ask because it's probably the single greatest obstacle to high performance code there is... well, except maybe for ORMs assigning the wrong datatype to constants. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply