December 4, 2008 at 5:26 am
Hi,
SQL novice here trying to combine two queries in to one but going nowhere.
This is query 1:
SELECT datepart(mm, pen_process_eqp.pn_eq_end_dm) as Month,
datepart(dd, pen_process_eqp.pn_eq_end_dm) as Date,
datepart(hh, pen_process_eqp.pn_eq_end_dm) As Hour,
Count(pen_link.pn_id) AS FIDS,
equip_dlk.modulelk_nm as Module
FROM equip_dlk, pen_link pen_link,
pen_process_eqp pen_process_eqp with(index(pn_eq_end_dm_idx))
WHERE pen_process_eqp.pn_id = pen_link.pn_id
AND equip_dlk.equiplk_ky = pen_process_eqp.equiplk_ky
AND pen_process_eqp.pn_eq_end_dm Between getdate()-2.04 AND getdate()-0.04
AND equip_dlk.equiplk_nm like '%-THA-SM2'
AND pen_link.fx_id Is Not Null
GROUP BY datepart(dd, pen_process_eqp.pn_eq_end_dm), datepart(hh, pen_process_eqp.pn_eq_end_dm),
datepart(mm, pen_process_eqp.pn_eq_end_dm), equip_dlk.modulelk_nm
order by max(pen_process_eqp.pn_eq_end_dm)
And this is query 2:
select datepart(mm, countsum.start_dm) as Month,
datepart(dd, countsum.start_dm) as Date,
datepart(hh, countsum.start_dm) As Hour,
sum(countsum.starts_sm) as Outs,
equip_dlk.modulelk_nm As Module
from countsum, equip_dlk
where countsum.equiplk_ky = equip_dlk.equiplk_ky
AND equip_dlk.equiplk_nm like '%-Rear_BC'
and countsum.start_dm >= getdate()-2.04
and countsum.end_dm <= getdate()-0.04
group by start_dm, equip_dlk.modulelk_nm
order by max(start_dm)
Can I join these together so that my Outs column is in with the first query? I was thinking it could be done as the month, date and hour columns are common to both but I don't know how to do it.
December 4, 2008 at 6:12 am
ARE the month/date/hour columns common to both? They're coming from two different tables and one says start and the other says end. Are the two different tables/fields going to have the same month/date/hour? You've also got different equiplk_nm filters in each query.
The following *may* work. Heavy emphasis on the may. This makes a lot of assumptions that are probably wrong.
[font="Courier New"]SELECT DATEPART(mm, pen_process_eqp.pn_eq_end_dm) AS [Month],
DATEPART(dd, pen_process_eqp.pn_eq_end_dm) AS [Date],
DATEPART(hh, pen_process_eqp.pn_eq_end_dm) AS [Hour],
COUNT(pen_link.pn_id) AS FIDS,
SUM(countsum.starts_sm) AS Outs,
equip_dlk.modulelk_nm AS Module
FROM equip_dlk
INNER JOIN pen_link pen_link ON pen_process_eqp.pn_id = pen_link.pn_id
INNER JOIN pen_process_eqp pen_process_eqp WITH(INDEX(pn_eq_end_dm_idx)) ON equip_dlk.equiplk_ky = pen_process_eqp.equiplk_ky
INNER JOIN countsum ON countsum.equiplk_ky = equip_dlk.equiplk_ky
WHERE
pen_process_eqp.pn_eq_end_dm BETWEEN GETDATE()-2.04 AND GETDATE()-0.04
AND (equip_dlk.equiplk_nm LIKE '%-THA-SM2' OR equip_dlk.equiplk_nm LIKE '%-Rear_BC')
AND pen_link.fx_id IS NOT NULL
GROUP BY DATEPART(dd, pen_process_eqp.pn_eq_end_dm), DATEPART(hh, pen_process_eqp.pn_eq_end_dm),
DATEPART(mm, pen_process_eqp.pn_eq_end_dm), equip_dlk.modulelk_nm
ORDER BY MAX(pen_process_eqp.pn_eq_end_dm)[/font]
Some reasons this may not work:
pen_link and pen_process_eqp are not linked in the second query, but are in the first.
AND pen_link.fx_id IS NOT NULL is present in the first query, not the second.
Different filters on equip_dlk.equiplk_nm in each query.
Different Grouping Structure in each query
Different date fields in each query
countsum linked in second query not linked in the first query.
December 16, 2008 at 3:16 am
You're right, this will not work, the date fields are different. I've changed my approach and found a solution using table variables. I don't think it's the most efficient but it does what I need.
I'm trying to do one other thing with this query now that will round it off nicely but I just can't find a way. Is it possible to get my start date to go back to the top of the hour?
So when I'm saying this
AND pen_process_eqp.pn_eq_end_dm Between getdate()-2.04 AND getdate()-0.04
I'd like it to go back 2 days and then however many minutes to get to the start of the hour. E.G it's 10:14 on the 16th so I would want my start date to be '2008-12-14 10:00:00.000'
December 16, 2008 at 7:41 am
I think I've answered my own question here. Writing out the question here actually helped me to find a way of doing it.
This is what I've got now:
DECLARE @start_date varchar(21),
@temp_date datetime
SELECT @temp_date = getdate()-2
SELECT @start_date = CAST(datepart(yy, @temp_date) AS varchar(4)) +
CAST(datepart(mm, @temp_date) AS varchar(2)) +
CAST(datepart(dd, @temp_date) AS varchar(2)) + ' ' +
CAST(datepart(hh, @temp_date) AS varchar(2)) +
':00:00.000'
It works fine but if anyone has any other suggestions please let me know.
December 16, 2008 at 7:54 am
Can you post the solution that you've come up with?
This is another way to get the beginning of the hour from 2 days ago:
SELECT DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0))
December 16, 2008 at 8:07 am
Garadin (12/16/2008)
Can you post the solution that you've come up with?This is another way to get the beginning of the hour from 2 days ago:
SELECT DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0))
That's much tidier, thanks.
I read a bunch a stuff on SQL last week and this is the best I could come up (adding in your function above)
DECLARE @FIDS TABLE(iMonth int,
iDate int,
iHour int,
FIDS int,
Module varchar(20))
INSERT INTO @FIDS(iMonth,
iDate,
iHour,
FIDS,
Module)
SELECT datepart(mm, pen_process_eqp.pn_eq_end_dm),
datepart(dd, pen_process_eqp.pn_eq_end_dm),
datepart(hh, pen_process_eqp.pn_eq_end_dm),
Count(pen_link.pn_id),
equip_dlk.modulelk_nm
FROM equip_dlk, pen_link pen_link,
pen_process_eqp pen_process_eqp with(index(pn_eq_end_dm_idx))
WHERE pen_process_eqp.pn_id = pen_link.pn_id
AND equip_dlk.equiplk_ky = pen_process_eqp.equiplk_ky
AND pen_process_eqp.pn_eq_end_dm Between DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0)) AND getdate()-0.04
AND equip_dlk.equiplk_nm like '%-THA-SM2'
AND pen_link.fx_id Is Not Null
GROUP BY datepart(dd, pen_process_eqp.pn_eq_end_dm), datepart(hh, pen_process_eqp.pn_eq_end_dm),
datepart(mm, pen_process_eqp.pn_eq_end_dm), equip_dlk.modulelk_nm
order by max(pen_process_eqp.pn_eq_end_dm)
DECLARE @OUTS TABLE(iMonth int,
iDate int,
iHour int,
OUTS int,
Module varchar(20))
INSERT INTO @OUTS(iMonth,
iDate,
iHour,
OUTS,
Module)
select datepart(mm, start_dm) as Month,
datepart(dd, start_dm) as Date,
datepart(hh, start_dm) As Hour,
sum(starts_sm) as Outs,
equip_dlk.modulelk_nm As Module
from countsum, equip_dlk
where countsum.equiplk_ky = equip_dlk.equiplk_ky
AND equip_dlk.equiplk_nm like '%-THA-Rear_BC'
and countsum.start_dm >= DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0))
and countsum.end_dm <= getdate()-0.04
and convert(varchar, start_dm, 21) like '%00:00.000'
group by start_dm, equip_dlk.modulelk_nm
order by max(start_dm)
SELECT O.Module AS [Module],
O.iMonth As [Month],
O.iDate As [Date],
O.iHour As [Hour] ,
O.OUTS As [Outs],
F.FIDS As [Fids]
FROM @OUTS O, @FIDS F
WHERE O.iMonth = F.iMonth
AND O.iDate = F.iDate
AND O.iHour = F.iHour
AND O.Module = F.Module
ORDER BY 2, 3, 4
It seems very messy and over-elaborate to me but it's the only method I came up with that gave me the result set I needed.
December 16, 2008 at 8:37 am
Looks good John. A few comments:
1. There's really no reason to order the data going into the table variables, it just adds cycles to your query.
2. You want to be absolutely certain when you specify an index for the optimizer to use, and it's usually safer to just let it decide on its own.
3. I've converted your old style syntax to INNER JOIN syntax. You should get used to this, as once you do, it makes things significantly easier to read.
4. On your second query, you group by start_dm, rather than month/day/hour of start_dm. Is that what you intended?
5. You could have done this same thing with a couple of derived tables. While it's not necessarily faster, it's a bit easier to code.
Here is your original with some comments / INNER JOIN changes:
[font="Courier New"]DECLARE @FIDS TABLE(iMonth INT,
iDate INT,
iHour INT,
FIDS INT,
Module VARCHAR(20))
INSERT INTO @FIDS(iMonth,
iDate,
iHour,
FIDS,
Module)
SELECT DATEPART(mm, pen_process_eqp.pn_eq_end_dm),
DATEPART(dd, pen_process_eqp.pn_eq_end_dm),
DATEPART(hh, pen_process_eqp.pn_eq_end_dm),
COUNT(pen_link.pn_id),
equip_dlk.modulelk_nm
FROM equip_dlk
INNER JOIN pen_process_eqp pen_process_eqp WITH(INDEX(pn_eq_end_dm_idx)) -- You need to be sure on this.
ON equip_dlk.equiplk_ky = pen_process_eqp.equiplk_ky
INNER JOIN pen_link pen_link
ON pen_process_eqp.pn_id = pen_link.pn_id
WHERE
pen_process_eqp.pn_eq_end_dm BETWEEN DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0)) AND GETDATE()-0.04
AND equip_dlk.equiplk_nm LIKE '%-THA-SM2'
AND pen_link.fx_id IS NOT NULL
GROUP BY DATEPART(dd, pen_process_eqp.pn_eq_end_dm),
DATEPART(hh, pen_process_eqp.pn_eq_end_dm),
DATEPART(mm, pen_process_eqp.pn_eq_end_dm), equip_dlk.modulelk_nm
--order by max(pen_process_eqp.pn_eq_end_dm)-- No reason to order into a table variable
DECLARE @OUTS TABLE(iMonth INT,
iDate INT,
iHour INT,
OUTS INT,
Module VARCHAR(20))
INSERT INTO @OUTS(iMonth,
iDate,
iHour,
OUTS,
Module)
SELECT DATEPART(mm, start_dm) AS MONTH,
DATEPART(dd, start_dm) AS Date,
DATEPART(hh, start_dm) AS Hour,
SUM(starts_sm) AS Outs,
equip_dlk.modulelk_nm AS Module
FROM countsum
INNER JOIN equip_dlk ON countsum.equiplk_ky = equip_dlk.equiplk_ky
WHERE equip_dlk.equiplk_nm LIKE '%-THA-Rear_BC'
AND countsum.start_dm >= DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0))
AND countsum.end_dm <= GETDATE()-0.04
AND CONVERT(VARCHAR, start_dm, 21) LIKE '%00:00.000'
GROUP BY start_dm, -- Is this right? Or did youw ant to group by the Month/Day/Hour
equip_dlk.modulelk_nm
--order by max(start_dm) --No reason to order into a table variable
SELECT O.Module AS [Module],
O.iMonth AS [Month],
O.iDate AS [Date],
O.iHour AS [Hour] ,
O.OUTS AS [Outs],
F.FIDS AS [Fids]
FROM @OUTS O,
INNER JOIN @FIDS F ON O.iMonth = F.iMonth AND O.iDate = F.iDate
AND O.iHour = F.iHour AND O.Module = F.Module
ORDER BY 2, 3, 4[/font]
And here is the derived table version: (This may not work, I couldn't test it as I don't have your tables/data, but it should give you an idea of what I'm talking about).
[font="Courier New"]SELECT O.Module AS [Module],
O.iMonth AS [Month],
O.iDate AS [Date],
O.iHour AS [Hour] ,
O.OUTS AS [Outs],
F.FIDS AS [Fids]
FROM ( SELECT DATEPART(mm, start_dm) AS [Month], -- Derived table, replacing your first table var.
DATEPART(dd, start_dm) AS [Date],
DATEPART(hh, start_dm) AS [Hour],
SUM(starts_sm) AS Outs,
equip_dlk.modulelk_nm AS Module
FROM countsum
INNER JOIN equip_dlk ON countsum.equiplk_ky = equip_dlk.equiplk_ky
WHERE equip_dlk.equiplk_nm LIKE '%-THA-Rear_BC'
AND countsum.start_dm >= DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0))
AND countsum.end_dm <= GETDATE()-0.04
AND CONVERT(VARCHAR, start_dm, 21) LIKE '%00:00.000'
GROUP BY start_dm, -- Is this right? Or did youw ant to group by the Month/Day/Hour
equip_dlk.modulelk_nm
) O
INNER JOIN
( SELECT DATEPART(mm, pen_process_eqp.pn_eq_end_dm) [Month], -- Derived table, replacing your second table var.
DATEPART(dd, pen_process_eqp.pn_eq_end_dm) [Date],
DATEPART(hh, pen_process_eqp.pn_eq_end_dm) [Hour],
COUNT(pen_link.pn_id) [Fids],
equip_dlk.modulelk_nm [Module]
FROM equip_dlk
INNER JOIN pen_process_eqp pen_process_eqp WITH(INDEX(pn_eq_end_dm_idx)) -- You need to be sure on this.
ON equip_dlk.equiplk_ky = pen_process_eqp.equiplk_ky
INNER JOIN pen_link pen_link
ON pen_process_eqp.pn_id = pen_link.pn_id
WHERE
pen_process_eqp.pn_eq_end_dm BETWEEN DATEADD(dd,-2,DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0)) AND GETDATE()-0.04
AND equip_dlk.equiplk_nm LIKE '%-THA-SM2'
AND pen_link.fx_id IS NOT NULL
GROUP BY DATEPART(dd, pen_process_eqp.pn_eq_end_dm),
DATEPART(hh, pen_process_eqp.pn_eq_end_dm),
DATEPART(mm, pen_process_eqp.pn_eq_end_dm), equip_dlk.modulelk_nm
) F
ON O.iMonth = F.iMonth AND O.iDate = F.iDate
AND O.iHour = F.iHour AND O.Module = F.Module
ORDER BY 2, 3, 4[/font]
December 17, 2008 at 5:42 am
Nice job explaining Seth - you only forgot one thing...
John, if you look at the link in Seth's sig (and mine, too, same article) you'll see samples of how to generate the tables and data so that the next time you get stuck you can post something that Seth can just run and see exactly what your data is doing. That's what he means when he says
I couldn't test it as I don't have your tables/data,
Enjoy!
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 17, 2008 at 6:14 am
Seth,
Really appreciate the comments. Just to respond:
1. You're right, I should have taken out the order by.
2. This was me experimenting, something I had read that I wanted to try. I'll remove this also.
3. I have noticed it's more common to use the JOIN keyword, I just learned it the other way and find it hard to break the habit. Maybe I will make an effort to use it from now on.
4. I can group by start_dm because it always starts on the hour (the system records a count at the top of every hour).
5. I had attempted to go down this road last week but I couldn't figure out the right way to do it. But from your query I can see exactly where I was going wrong so many thanks for this. I'm going to try and implement this now, I'll post back with the results.
Jon,
Thanks for the tip, I'll take a look and use this in the future.
John
December 17, 2008 at 6:58 am
mcphillipsjohn (12/17/2008)
Seth,3. I have noticed it's more common to use the JOIN keyword, I just learned it the other way and find it hard to break the habit. Maybe I will make an effort to use it from now on.
John
There's more behind this than just good practice/ANSI standards, it's just not as easy to see in your example since you're doing one to one relationships. Basically, if you have two tables you're joining, tableA and tableB, and you have records in tableA that have no match in tableB, but you want to return the tableA value anyway with a NULL value from tableB, (one to many), you can't do it if you're joining in the where clause. But if you use the JOIN structure in your FROM clause, you can specify INNER/LEFT OUTER/RIGHT OUTER as needed (LEFT OUTER JOIN or just LEFT JOIN for the example I just gave) and return the data that you need.
HTH,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 17, 2008 at 7:04 am
jcrawf02 (12/17/2008)
mcphillipsjohn (12/17/2008)
Seth,3. I have noticed it's more common to use the JOIN keyword, I just learned it the other way and find it hard to break the habit. Maybe I will make an effort to use it from now on.
John
There's more behind this than just good practice/ANSI standards, it's just not as easy to see in your example since you're doing one to one relationships. Basically, if you have two tables you're joining, tableA and tableB, and you have records in tableA that have no match in tableB, but you want to return the tableA value anyway with a NULL value from tableB, (one to many), you can't do it if you're joining in the where clause. But if you use the JOIN structure in your FROM clause, you can specify INNER/LEFT OUTER/RIGHT OUTER as needed (LEFT OUTER JOIN or just LEFT JOIN for the example I just gave) and return the data that you need.
HTH,
Jon
Actually, you can, old style joins use =* and *= for LEFT and RIGHT joins. That said, you *shouldn't*, as that is crazy confusing and I usually won't even read a post like that.
December 17, 2008 at 7:10 am
Garadin (12/17/2008)
Actually, you can, old style joins use =* and *= for LEFT and RIGHT joins. That said, you *shouldn't*, as that is crazy confusing and I usually won't even read a post like that.
I stand, erm ... sit, corrected. Thanks for the info - Jon 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 18, 2008 at 8:11 am
Seth,
The derived table version worked a treat. Thanks again for the help 🙂
John
December 18, 2008 at 8:15 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply