October 12, 2006 at 11:04 am
I have two tables, tblWasteDischarge & tblManifest, which the identity columns, DischrgSessID & MnfstID are also the primary key for each table, respectively. Both have (int, 1, 1) property. DischrgSessID column in tblManifest table is the foreign key to tblWasteDischarge table.
When I ran the query, I received different results. Here is my two simple select queries (diff. on FROM only):
(1) Ran against tblWasteDischarge table only.
DECLARE @Start datetime, @End datetime
DECLARE @StartDte datetime, @Enddte datetime
DECLARE @12AM char(12), @12PM char(12)
DECLARE @T12AM char(24), @T12PM char(24)
SELECT @Start = '9/1/2006'
SELECT @End = '9/30/2006'
SELECT @12AM = '00:00:00.000'
SELECT @12PM = '23:59:59.000'
-- Extract date data then combine with the time as string
SELECT @T12AM = REPLACE (CONVERT (char(12), @Start, 102), '.', '-') + ' ' + @12AM
-- Convert string to datetime
SELECT @StartDte = CAST (@T12AM AS datetime)
SELECT @T12PM = REPLACE (CONVERT (char(12), @End, 102), '.', '-') + ' ' + @12PM
SELECT @EndDte = CAST (@T12PM AS datetime)
SELECT wd.TruckID,
[Normal] = COUNT (CASE wd.DischrgTmCat WHEN 1 THEN 1 ELSE NULL END),
[After] = COUNT (CASE wd.DischrgTmCat WHEN 2 THEN 1 ELSE NULL END),
[Weekend] = COUNT (CASE wd.DischrgTmCat WHEN 3 THEN 1 ELSE NULL END),
[Holiday] = COUNT (CASE wd.DischrgTmCat WHEN 4 THEN 1 ELSE NULL END),
[ImpAmtFilled] = COUNT (CASE wd.DischrgImpAmtFilled WHEN 1 THEN 1 ELSE NULL END),
[ImpBottleCln] = COUNT (CASE wd.DischrgImpBottleCleaned WHEN 1 THEN 1 ELSE NULL END)
FROM tblWasteDischarge wd
WHERE (wd.DischrgStartDte >= @StartDte AND wd.DischrgStartDte <=@EndDte)
GROUP BY wd.TruckID
ORDER BY wd.TruckID
(2) Ran against two tables with inner join.
DECLARE @Start datetime, @End datetime
DECLARE @StartDte datetime, @Enddte datetime
DECLARE @12AM char(12), @12PM char(12)
DECLARE @T12AM char(24), @T12PM char(24)
SELECT @Start = '9/1/2006'
SELECT @End = '9/30/2006'
SELECT @12AM = '00:00:00.000'
SELECT @12PM = '23:59:59.000'
-- Extract date data then combine with the time as string
SELECT @T12AM = REPLACE (CONVERT (char(12), @Start, 102), '.', '-') + ' ' + @12AM
-- Convert string to datetime
SELECT @StartDte = CAST (@T12AM AS datetime)
SELECT @T12PM = REPLACE (CONVERT (char(12), @End, 102), '.', '-') + ' ' + @12PM
SELECT @EndDte = CAST (@T12PM AS datetime)
SELECT wd.TruckID,
[Normal] = COUNT (CASE wd.DischrgTmCat WHEN 1 THEN 1 ELSE NULL END),
[After] = COUNT (CASE wd.DischrgTmCat WHEN 2 THEN 1 ELSE NULL END),
[Weekend] = COUNT (CASE wd.DischrgTmCat WHEN 3 THEN 1 ELSE NULL END),
[Holiday] = COUNT (CASE wd.DischrgTmCat WHEN 4 THEN 1 ELSE NULL END),
[ImpAmtFilled] = COUNT (CASE wd.DischrgImpAmtFilled WHEN 1 THEN 1 ELSE NULL END),
[ImpBottleCln] = COUNT (CASE wd.DischrgImpBottleCleaned WHEN 1 THEN 1 ELSE NULL END),
[ImpFrmFilled] =COUNT (CASE m.MnfstImpFormFilled WHEN 1 THEN 1 ELSE NULL END)
FROM tblWasteDischarge wd INNER JOIN tblManifest m ON wd.DischrgSessID = m.DischrgSessID
WHERE (wd.DischrgStartDte >= @StartDte AND wd.DischrgStartDte <=@EndDte)
GROUP BY wd.TruckID
ORDER BY wd.TruckID
Here are the return results:
(1) Against tblWasteDischarge table only.
| TruckID | Normal | After | Weekend | Holiday | ImpAmtFilled | ImpBottleCln |
| 1 | 2 | 0 | 0 | 0 | 0 | 0 |
| 10 | 3 | 2 | 1 | 0 | 0 | 0 |
| 11 | 7 | 1 | 0 | 0 | 0 | 0 |
| 12 | 11 | 1 | 0 | 1 | 0 | 0 |
| 13 | 7 | 1 | 2 | 0 | 0 | 0 |
| 14 | 12 | 0 | 1 | 0 | 0 | 0 |
| 16 | 1 | 0 | 0 | 0 | 0 | 0 |
| 17 | 14 | 0 | 0 | 0 | 0 | 0 |
| 2 | 2 | 0 | 0 | 0 | 0 | 0 |
| 3 | 13 | 0 | 0 | 1 | 0 | 0 |
| 4 | 6 | 0 | 0 | 0 | 0 | 0 |
| 5 | 6 | 0 | 0 | 0 | 0 | 0 |
| 6 | 47 | 0 | 0 | 0 | 0 | 0 |
| 7 | 42 | 0 | 1 | 0 | 0 | 0 |
| 8 | 18 | 0 | 0 | 0 | 0 | 0 |
| 9 | 10 | 3 | 0 | 0 | 0 | 0 |
(2) Against tblWasteDischarge & tblManifest tables with inner join.
| TruckID | Normal | After | Weekend | Holiday | ImpAmtFilled | ImpBottleCln | ImpFrmFilled |
| 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10 | 5 | 5 | 1 | 0 | 0 | 0 | 0 |
| 11 | 11 | 3 | 0 | 0 | 0 | 0 | 0 |
| 12 | 11 | 1 | 0 | 1 | 0 | 0 | 0 |
| 13 | 7 | 1 | 2 | 0 | 0 | 0 | 0 |
| 14 | 14 | 0 | 1 | 0 | 0 | 0 | 0 |
| 16 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 17 | 14 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 13 | 0 | 0 | 1 | 0 | 0 | 0 |
| 4 | 6 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5 | 6 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 59 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7 | 42 | 0 | 1 | 0 | 0 | 0 | 0 |
| 8 | 18 | 0 | 0 | 0 | 0 | 0 | 0 |
| 9 | 14 | 3 | 0 | 0 | 0 | 0 | 0 |
The highlighted yellow rows are the different results. The result (1) is the correct. I manually count them for verification. Can anyone assist me why the return result is different when inner joining the tblManifest table? Does it have to do with identity columns for both primary and foreign keys on the same table? Thank you in adv.
October 12, 2006 at 3:40 pm
KC,
It appears that you can have multiple DischrgSessID values for one Waste Discharge, but then you do not include this aspect within your grouping.
Are you not wanting to differentiate between the different Manifests that are associated with a Waste Discharge?
If not, there are a couple of ways to approach this and still keep it within one query.
The easiest way, if you plan on only retrieving counts for one column on the Manifest table is by using a sub-select.
SELECT wd.TruckID,
[Normal] = COUNT (CASE wd.DischrgTmCat WHEN 1 THEN 1 ELSE NULL END),
[After] = COUNT (CASE wd.DischrgTmCat WHEN 2 THEN 1 ELSE NULL END),
[Weekend] = COUNT (CASE wd.DischrgTmCat WHEN 3 THEN 1 ELSE NULL END),
[Holiday] = COUNT (CASE wd.DischrgTmCat WHEN 4 THEN 1 ELSE NULL END),
[ImpAmtFilled] = COUNT (CASE wd.DischrgImpAmtFilled WHEN 1 THEN 1 ELSE NULL END),
[ImpBottleCln] = COUNT (CASE wd.DischrgImpBottleCleaned WHEN 1 THEN 1 ELSE NULL END),
[ImpFrmFilled] =
(SELECT
COUNT (CASE m.MnfstImpFormFilled WHEN 1 THEN 1 ELSE NULL END)
FROM tblManifest m ON wd.DischrgSessID = m.DischrgSessID)
FROM tblWasteDischarge wd
WHERE (wd.DischrgStartDte >= @StartDte AND wd.DischrgStartDte <=@EndDte)
GROUP BY wd.TruckID
ORDER BY wd.TruckID
If you plan on including additional fields within the Manifest table, then moving toward a combination temp table folowed by an aggregate of the other table would be the most maintainable and efficient. Let me know if you need this version, and I'll create it.
Also, you are skipping the 1 second just before midnight within your date range. You may want to change the end time to be:
SELECT @12PM = '23:59:59.999'
-Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply