Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

COUNT Expand / Collapse
Author
Message
Posted Monday, August 17, 2009 7:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
Below is the SQL that works but not 100%. I need to count Titles so that I may chart my results. The Defect Titles are in a lookup table and the IDs are stored by INSERT in tbl_Assembly_Hold_Defects. Any help is greatly appreciated.


SELECT tlkp_Defects.Defect_Title, COUNT(tbl_Assembly_Hold_Defects.Defect_ID) AS Defect_Count, tbl_Assembly_Holds.Record_Date FROM tbl_Assembly_Hold_Defects, tlkp_Defects, tbl_Assembly_Holds

WHERE tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID AND tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009' AND tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber

GROUP BY tlkp_Defects.Defect_Title, tbl_Assembly_Holds.Record_Date, tbl_Assembly_Hold_Defects.Defect_ID, tlkp_Defects.Defect_ID, tbl_Assembly_Holds.TagNumber, tbl_Assembly_Hold_Defects.TagNumber
Post #771882
Posted Monday, August 17, 2009 8:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 AM
Points: 2,716, Visits: 2,471
I think I understand your requirements, try this..

SELECT
count(tlkp_Defects.Defect_Title)
tbl_Assembly_Holds.Record_Date
FROM
tbl_Assembly_Hold_Defects
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber
WHERE
tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'
GROUP BY
tbl_Assembly_Holds.Record_Date


I have COUNTed the titles by date.
Also changed your join syntax to the more acceptable SQL92 standard.


Kev
Post #771968
Posted Monday, August 17, 2009 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
I just tried your script and this is my result:

"Error in list of function arguments: '.' not recognized.
Unable to parse query text."

Post #771986
Posted Monday, August 17, 2009 9:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 AM
Points: 2,716, Visits: 2,471
I've just noticed I missed a comma out so try this

SELECT
count(Defect_Title),
tbl_Assembly_Holds.Record_Date
FROM
tbl_Assembly_Hold_Defects
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber
WHERE
tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'
GROUP BY
tbl_Assembly_Holds.Record_Date

Post #772002
Posted Monday, August 17, 2009 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
SSCrazy,

That worked but what I am looking for is:

1. Count the defects by title through a date range.
2. Chart the range
3. Show how many occurred throughtout the range and not per day.
For example:

Defect Record_Date

01 Broken Lid 8/12/2009
01 Broken Lid 8/13/2009
01 Broken Lid 8/13/2009
99 Dropped on Floor 8/12/2009

Broken Lid = 3
Dropped on Floor = 1
Post #772016
Posted Monday, August 17, 2009 9:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 AM
Points: 2,716, Visits: 2,471
OK then

SELECT
tlkp_Defects.Defect_Title,
count(*)
FROM
tbl_Assembly_Hold_Defects
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber
WHERE
tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'
GROUP BY
tlkp_Defects.Defect_Title

Post #772021
Posted Monday, August 17, 2009 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
Thanks X 1000.

I made a couple of changes to your solution and I got the results I am looking for.


SELECT
tlkp_Defects.Defect_Title,
count(*)
FROM
tbl_Assembly_Hold_Defects
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber
WHERE
tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'
GROUP BY
tlkp_Defects.Defect_Title
Post #772041
Posted Monday, August 17, 2009 10:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:14 PM
Points: 5,364, Visits: 8,952
Just want to point out that you are using the BETWEEN operator with a date field... if your date field can have time values in it, then you probably are not getting what you want.

Each of the dates in the between clause will default to a time of "00:00:00.000". If you're looking for all dates on 8/14, then you need to either:
1. Record_date between '8/12/2009' and '8/14/2009 23:59:59.997'
OR
2. Record_date >= '8/12/2009' and Record_date < '8/15/2009'


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #772056
Posted Monday, August 17, 2009 10:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
You make a good point. The results I was looking for was 8/12 thru 8/13 that's why I set it to those parameters. However, users may want to include "today" in their results.
Post #772104
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse