December 17, 2010 at 12:20 pm
/* I have the following code. It executes fine when I try it.
I need to use this query to create a stored procedure. I need to pass the @strStartTime and @strEndTime as varchar and then use them in the conditional query after being converted to datetime.
*/
Declare @strStartTime varchar(50), @strEndTime varchar(50)
select @strStartTime = '12/10/2010'
select @strEndTime = '12/16/2010'
select tB1.Date_Stamp, tB1.KWH B1_KWH, tB2.KWH B2_KWH,tEB.KWH EB_KWH, tF1.KWH F1_KWH, tF2.KWH F2_KWH,tEF.KWH EF_KWH,
tD1.KWH D1_KWH, tD2.KWH D2_KWH,tED.KWH ED_KWH,
tDE.KWH DE_KWH, tU1.KWH U1_KWH, tU2.KWH U2_KWH,
tU3.KWH U3_KWH, tU4.KWH U4_KWH, tMMD.KWH MMD_KWH
from
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB1_Daily B1
Where B1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
B1.Date_Stamp<= convert(datetime, @strEndTime , 101) and B1.Pk_Dmnd_on is not Null
) tB1
full outer Join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB2_Daily B2
Where B2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
B2.Date_Stamp<= convert(datetime, @strEndTime , 101) and B2.Pk_Dmnd_on is not Null
) tB2
on tB1.Date_Stamp = tB2.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubEB_Daily EB
Where EB.Date_Stamp>= convert(datetime, @strStartTime , 101) and
EB.Date_Stamp<= convert(datetime, @strEndTime , 101) and EB.Pk_Dmnd_on is not Null
) tEB
on tEB.Date_Stamp = tB2.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubF1_Daily F1
Where F1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
F1.Date_Stamp<= convert(datetime, @strEndTime , 101) and F1.Pk_Dmnd_on is not Null
) tF1
on tF1.Date_Stamp = tEB.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubF2_Daily F2
Where F2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
F2.Date_Stamp<= convert(datetime, @strEndTime , 101) and F2.Pk_Dmnd_on is not Null
) tF2
on tF2.Date_Stamp = tF1.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubEF_Daily EF
Where EF.Date_Stamp>= convert(datetime, @strStartTime , 101) and
EF.Date_Stamp<= convert(datetime, @strEndTime , 101) and EF.Pk_Dmnd_on is not Null
) tEF
on tEF.Date_Stamp = tF2.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubD1_Daily D1
Where D1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
D1.Date_Stamp<= convert(datetime, @strEndTime , 101) and D1.Pk_Dmnd_on is not Null
) tD1
on tD1.Date_Stamp = tEF.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubD2_Daily D2
Where D2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
D2.Date_Stamp<= convert(datetime, @strEndTime , 101) and D2.Pk_Dmnd_on is not Null
) tD2
on tD2.Date_Stamp = tD1.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubED_Daily ED
Where ED.Date_Stamp>= convert(datetime, @strStartTime , 101) and
ED.Date_Stamp<= convert(datetime, @strEndTime , 101) and ED.Pk_Dmnd_on is not Null
) tED
on tED.Date_Stamp = tD2.Date_Stamp
full outer join
(select distinct [DateTime], KWH
from Elect_Sub_DE_Daily DE
Where DE.[DateTime]>= convert(datetime, @strStartTime , 101) and
DE.[DateTime]<= convert(datetime, @strEndTime , 101) and KWH is not Null
) tDE
on tDE.DateTime = tED.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubU1_Daily U1
Where U1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
U1.Date_Stamp<= convert(datetime, @strEndTime , 101) and U1.Pk_Dmnd_on is not Null
) tU1
on tU1.Date_Stamp = tDE.DateTime
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubU2_Daily U2
Where U2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
U2.Date_Stamp<= convert(datetime, @strEndTime , 101) and U2.Pk_Dmnd_on is not Null
) tU2
on tU2.Date_Stamp = tU1.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubEU_Daily EU
Where EU.Date_Stamp>= convert(datetime, @strStartTime , 101) and
EU.Date_Stamp<= convert(datetime, @strEndTime , 101) and EU.Pk_Dmnd_on is not Null
) tEU
on tEU.Date_Stamp = tU2.Date_Stamp
full outer join
(select distinct [DateTime], KWH
from Elect_Sub_U3_Daily U3
Where U3.[DateTime]>= convert(datetime, @strStartTime , 101) and
U3.[DateTime]<= convert(datetime, @strEndTime , 101) and U3.KWH is not Null
) tU3
on tU3.[DateTime] = tU2.Date_Stamp
full outer join
(select distinct [DateTime], KWH
from Elect_Sub_U4_Daily U4
Where U4.[DateTime]>= convert(datetime, @strStartTime , 101) and
U4.[DateTime]<= convert(datetime, @strEndTime , 101) and U4.KWH is not Null
) tU4
on tU4.[DateTime] = tU3.[DateTime]
full outer join
(select distinct [DateTime], KWH
from Elec_WestBldg_KWH_Daily MMD
Where MMD.[DateTime]>= convert(datetime, @strStartTime , 101) and
MMD.[DateTime]<= convert(datetime, @strEndTime , 101) and MMD.KWH is not Null
) tMMD
on tU4.[DateTime] = tU2.Date_Stamp
order by tB1.Date_Stamp desc
December 17, 2010 at 12:32 pm
Can you check "CREATE PROCEDURE " on BOL?
December 17, 2010 at 12:35 pm
/*Sorry I posted a lengthy query. Here is the short version..*/
Declare @strStartTime varchar(50), @strEndTime varchar(50)
select @strStartTime = '12/10/2010'
select @strEndTime = '12/16/2010'
select tB1.Date_Stamp, tB1.KWH B1_KWH, tB2.KWH B2_KWH
from
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB1_Daily B1
Where B1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
B1.Date_Stamp<= convert(datetime, @strEndTime , 101) and B1.Pk_Dmnd_on is not Null
) tB1
full outer Join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB2_Daily B2
Where B2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
B2.Date_Stamp<= convert(datetime, @strEndTime , 101) and B2.Pk_Dmnd_on is not Null
) tB2
on tB1.Date_Stamp = tB2.Date_Stamp
order by tB1.Date_Stamp desc
December 17, 2010 at 1:13 pm
OK. I got it to work . Here is the final query
/****** Object: StoredProcedure [dbo].[proc_Site_All_KWH] Script Date: 12/17/2010 14:53:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Site_All_KWH]
@strStartTime varchar(50),
@strEndTime varchar(50)
AS
select tB1.Date_Stamp, tB1.KWH B1_KWH, tB2.KWH B2_KWH,tEB.KWH EB_KWH,
tF1.KWH F1_KWH, tF2.KWH F2_KWH,tEF.KWH EF_KWH,
tD1.KWH D1_KWH, tD2.KWH D2_KWH,tED.KWH ED_KWH,
tDE.KWH DE_KWH, tU1.KWH U1_KWH, tU2.KWH U2_KWH,
tU3.KWH U3_KWH, tU4.KWH U4_KWH, tMMD.KWH MMD_KWH
from
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB1_Daily B1
Where B1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
B1.Date_Stamp<= convert(datetime, @strEndTime , 101) and B1.Pk_Dmnd_on is not Null
) tB1
full outer Join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB2_Daily B2
Where B2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
B2.Date_Stamp<= convert(datetime, @strEndTime , 101) and B2.Pk_Dmnd_on is not Null
) tB2
on tB1.Date_Stamp = tB2.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubEB_Daily EB
Where EB.Date_Stamp>= convert(datetime, @strStartTime , 101) and
EB.Date_Stamp<= convert(datetime, @strEndTime , 101) and EB.Pk_Dmnd_on is not Null
) tEB
on tEB.Date_Stamp = tB2.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubF1_Daily F1
Where F1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
F1.Date_Stamp<= convert(datetime, @strEndTime , 101) and F1.Pk_Dmnd_on is not Null
) tF1
on tF1.Date_Stamp = tEB.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubF2_Daily F2
Where F2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
F2.Date_Stamp<= convert(datetime, @strEndTime , 101) and F2.Pk_Dmnd_on is not Null
) tF2
on tF2.Date_Stamp = tF1.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubEF_Daily EF
Where EF.Date_Stamp>= convert(datetime, @strStartTime , 101) and
EF.Date_Stamp<= convert(datetime, @strEndTime , 101) and EF.Pk_Dmnd_on is not Null
) tEF
on tEF.Date_Stamp = tF2.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubD1_Daily D1
Where D1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
D1.Date_Stamp<= convert(datetime, @strEndTime , 101) and D1.Pk_Dmnd_on is not Null
) tD1
on tD1.Date_Stamp = tEF.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubD2_Daily D2
Where D2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
D2.Date_Stamp<= convert(datetime, @strEndTime , 101) and D2.Pk_Dmnd_on is not Null
) tD2
on tD2.Date_Stamp = tD1.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubED_Daily ED
Where ED.Date_Stamp>= convert(datetime, @strStartTime , 101) and
ED.Date_Stamp<= convert(datetime, @strEndTime , 101) and ED.Pk_Dmnd_on is not Null
) tED
on tED.Date_Stamp = tD2.Date_Stamp
full outer join
(select distinct [DateTime], KWH
from Elect_Sub_DE_Daily DE
Where DE.[DateTime]>= convert(datetime, @strStartTime , 101) and
DE.[DateTime]<= convert(datetime, @strEndTime , 101) and KWH is not Null
) tDE
on tDE.DateTime = tED.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubU1_Daily U1
Where U1.Date_Stamp>= convert(datetime, @strStartTime , 101) and
U1.Date_Stamp<= convert(datetime, @strEndTime , 101) and U1.Pk_Dmnd_on is not Null
) tU1
on tU1.Date_Stamp = tDE.DateTime
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubU2_Daily U2
Where U2.Date_Stamp>= convert(datetime, @strStartTime , 101) and
U2.Date_Stamp<= convert(datetime, @strEndTime , 101) and U2.Pk_Dmnd_on is not Null
) tU2
on tU2.Date_Stamp = tU1.Date_Stamp
full outer join
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubEU_Daily EU
Where EU.Date_Stamp>= convert(datetime, @strStartTime , 101) and
EU.Date_Stamp<= convert(datetime, @strEndTime , 101) and EU.Pk_Dmnd_on is not Null
) tEU
on tEU.Date_Stamp = tU2.Date_Stamp
full outer join
(select distinct [DateTime], KWH
from Elect_Sub_U3_Daily U3
Where U3.[DateTime]>= convert(datetime, @strStartTime , 101) and
U3.[DateTime]<= convert(datetime, @strEndTime , 101) and U3.KWH is not Null
) tU3
on tU3.[DateTime] = tU2.Date_Stamp
full outer join
(select distinct [DateTime], KWH
from Elect_Sub_U4_Daily U4
Where U4.[DateTime]>= convert(datetime, @strStartTime , 101) and
U4.[DateTime]<= convert(datetime, @strEndTime , 101) and U4.KWH is not Null
) tU4
on tU4.[DateTime] = tU3.[DateTime]
full outer join
(select distinct [DateTime], KWH
from Elec_WestBldg_KWH_Daily MMD
Where MMD.[DateTime]>= convert(datetime, @strStartTime , 101) and
MMD.[DateTime]<= convert(datetime, @strEndTime , 101) and MMD.KWH is not Null
) tMMD
on tU4.[DateTime] = tU2.Date_Stamp
order by tB1.Date_Stamp desc
December 17, 2010 at 1:41 pm
I would point out that if you compile this code on a new database it will fail..
The ALTER statement only works when it already exists. you might look at a drop/create process where it drops the existing sproc if it exists and uses a create statement EVERY time.
CEWII
December 17, 2010 at 1:46 pm
Yes this procedure already exists.
Thanks
December 17, 2010 at 2:05 pm
Why don't you use datetime variables instead of varchar and convert them over and over and over and over???
MMD.[DateTime]>= convert(datetime, @strStartTime , 101) and
MMD.[DateTime]<= convert(datetime, @strEndTime , 101)
Additionally you could make this a little easier to read:
MMD.[DateTime] between @strStartTime and @strEndTime
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2010 at 2:13 pm
techzone12 (12/17/2010)
Yes this procedure already exists.Thanks
So what happens when you move it to production? Also, what kind of source control tool or methodology are you using? Do you have a copy of the sproc outside of the SQL Server?
Also, @sean, I'm guessing he is only wanting to potentially use the date portion of the date.. However, there are better ways to accomplish than the way it is being done.. In my mind the best way would be to specify the two input variables as datetime and then perform some statement like:
SELECT @strStartTime = CONVERT( varchar(10), @strStartTime, 101 ),
@strEndTime = CONVERT( varchar(10), @strEndTime, 101 )
This shaves off the time portion and then the dates can be compared directly. It is a little bit of a cheat in that I use an implicit coversion from varchar(10) to datetime but I'm ok with that.
CEWII
December 17, 2010 at 2:15 pm
Yeah i too was guessing he was forcing time to 00, I was really just suggesting doing the conversion once rather than over and over. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2010 at 2:30 pm
Agreed..
CEWII
December 21, 2010 at 10:05 am
Sean, I agree, The code was repeating itself.. not good.
The variables are being passed from an "ASP" page. The stored procedure is under the database in SQL server.
Here is the new code according to your suggestion:
ALTER PROCEDURE [dbo].[proc_Site_All_KWH]
@strStartTime varchar(50),
@strEndTime varchar(50)
AS
select @strStartTime = CONVERT( varchar(10), @strStartTime, 101 )
select @strEndTime = CONVERT( varchar(10), @strEndTime, 101 )
select tB1.Date_Stamp, tB1.KWH B1_KWH, tB2.KWH B2_KWH,
tEB.KWH EB_KWH, tF1.KWH F1_KWH, tF2.KWH F2_KWH,tEF.KWH EF_KWH,
tD1.KWH D1_KWH, tD2.KWH D2_KWH,tED.KWH ED_KWH,
tDE.KWH DE_KWH, tU1.KWH U1_KWH, tU2.KWH U2_KWH,
tU3.KWH U3_KWH, tU4.KWH U4_KWH, tMMD.KWH MMD_KWH
from
(select distinct Date_Stamp, (isnull(Pk_Dmnd_On,0) + isnull(Pk_Dmnd_Off,0)) KWH
from Elect_SubB1_Daily B1
Where B1.Date_Stamp>= @strStartTime and
B1.Date_Stamp<= @strEndTime and B1.Pk_Dmnd_on is not Null
) tB1
full outer join
....
December 21, 2010 at 10:17 am
Assuming Date_Stamp is a datetime datatype you should make the variables datetime and not varchar otherwise you are forcing sql to do an implicit conversion over and over. If you have to pass in strings from asp (which should be easy enough to correct if you have the ability to modify the code), then you need to put your parameters into datetime variables. Of course then we start to open parameter sniffing and the performance issues associated with that.
That being said you could really benefit from Joe's comments above. For example there is no need for isnull(Pk_Dmnd_On,0) in the select statement because the where clause filters them out. Do you need to use distinct?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2010 at 10:49 am
Thanks for the feedback..
1. I did not know about COALESCE. I looked it up, and it looks like it's a powerfull command that I can utilize.
2. Yes, I could use CAST instead.
3. Yes, I could use Date.
4. ??
5. KWH/day is what is being measured.
6. I am using distinct, because there are some duplicates. When they created the table, they did not assign primary keys!!. I am going to have to delete the sup rows and assign keys, then I can do away with distinct.
7. OK
8. On_Peak_Demand is KWH used up during peak time (not bit flags, but actual values).
9. ?
10. I have multiple tables. Each table has a Date_Stamp (or DateTime)column. The seconds colum is KWH. I am trying to get results form all tables combined (after applying the datetime filter). I apply the filter on each one (to narrow down results) before joining.
Thanks again. Great advice!
December 21, 2010 at 11:28 am
Removed isnull(Pk_Dmnd_On,0).
Good point..
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply