Simplifying this select
-
SELECT '01' as the_month,'2003' as the_year, isnull((SELECT SUM(num_units) from active_units_static where month_end = '1/31/2003'),0) + count(termnum) as active_units from active_Units where on_contract <= '1/31/2003' and (off_contract >= '1/31/2003' OR off_contract is null) UNION
SELECT '01','2002', isnull((SELECT SUM(num_units) from active_units_static where month_end = '1/31/2002'),0) + count(termnum) as active_units from active_Units where on_contract <= '1/31/2002' and (off_contract >= '1/31/2002' OR off_contract is null) UNION
SELECT '01','2001', isnull((SELECT SUM(num_units) from active_units_static where month_end = '1/31/2001'),0) + count(termnum) as active_units from active_Units where on_contract <= '1/31/2001' and (off_contract >= '1/31/2001' OR off_contract is null) UNION
SELECT '01','2000', isnull((SELECT SUM(num_units) from active_units_static where month_end = '1/31/2000'),0) + count(termnum) as active_units from active_Units where on_contract <= '1/31/2000' and (off_contract >= '1/31/2000' OR off_contract is null) UNION
SELECT '02','2003', isnull((SELECT SUM(num_units) from active_units_static where month_end = '2/28/2003'),0) + count(termnum) as active_units from active_Units where on_contract <= '2/28/2003' and (off_contract >= '2/28/2003' OR off_contract is null) UNION
etc for all 12 months and the 4 years you see up above. This select is quite long and I was trying to somehow shorten it, if its possible.
I have been trying myself to come up with a select that would accomplish this not using so many lines of code but not having much sucess.
SELECT '01' as the_month,year(on_contract) as the_year, isnull((SELECT SUM(num_units) from active_units_static where month_end = '1/31/2000'),0) + count(termnum) as active_units from active_units where on_contract <= '1/31/2000' and (off_contract> = '1/31/2000' OR off_contract is null) group by year(on_contract),month(on_contract)
this is how I started to try to simplify it but obviously it does not work. I am still unclear how just to get >= 2000 data but as it stands I am not even getting the corrent number of active_units.
So if anyone wants a challenge, fill free to offer suggestions.
FYI...on_contract,off_contract, and month_end are all datetime fields.
Matt
-
How about something with case? Such as
Select the_month =
case
when convert(char(12), on_contract, 101) = '1/31/2003' then '01'
...
end
I realize that you have date ranges, so you'll have to parse out the month and year. I don't know how much shorter the query will be, but it might run faster.
-
Thanks for the idea, but it wont be any any faster, if anything it will be slower since there is more processing as the month and year are both static currently and I don't see how your method would reduce the amount code, it looks like it would be more code with all the case statements I would need.
I do understand there will be more processing overhead, if I make it more dynamic but the case statements dont seem to be what I am looking for.
Matt
Edited by - matt101 on 12/18/2003 2:15:10 PM
-
Select DatePart(m, EndOfMonthDate) as the_month,
DatePart(yyyy, EndOfMonthDate) as the_year,
isnull( (SELECT SUM(num_units) from active_units_static where month_end = EndOfMonthDate), 0)
+ count(termnum) as active_units
from active_Units
where on_contract <= EndOfMonthDate
and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate
Join
-- Select * From
(
Select Convert(DateTime, Convert(Varchar(8), DateAdd(m, 1-AMonth, DateAdd(yy, -ManyYears, GetDate())), 1)) - DatePart(d, GetDate())
As EndOfMonthDate
From (
Select Number as AMonth
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 12) Months
Cross Join (
Select Number as ManyYears
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 50) ManyYears -- may adjust the 5 to go back further
) EOMDates
Where EndOfMonthDate Between '1/31/2000' and '11/30/03' -- adjust WHERE to suit date needs
/*
The EOMDates derived table and WHERE clause can be run on there own to get a quick list of "last day of month" dates.
GHo ahead an run the derived table query by itself... from the "-- Select * From " line, just highlight "Select * From ..." to the end.
I went ahead an did the JOIN, but I'd rather see you rework the query to JOIN the active_units_static table.
Hope this helps.
*/
Once you understand the BITs, all the pieces come together
-
I thought case might be faster despite the processor overhead if it could reduce the io.
-
I reworked the join, though I think you meant the active_units table and not the active_units_static, I almost got it 100% sytaxally working with one error saying
Line 18: Incorrect syntax near 'EOMDates'
I am not seeing anything wrong near line 18 which is right after the cross join. I do believe there are a few ) missing but I add them and still get the same error so I just removed them and the derived table did work so I am not 100% following where the incorrect syntax lies.
Below is the reworked query:
Select DatePart(m, EndOfMonthDate) as the_month,
DatePart(yyyy, EndOfMonthDate) as the_year,
isnull( (SELECT SUM(num_units) from active_units_static where month_end = EndOfMonthDate), 0) + count(termnum) as active_units from active_Units
Join EOMDates On the_month = Month(EndofMonthDate) AND the_year = year(EndofMonthDate)
-- Select * From
(
Select Convert(DateTime, Convert(Varchar(8), DateAdd(m, 1-AMonth, DateAdd(yy, - ManyYears, GetDate())), 1)) - DatePart(d, GetDate())
As EndOfMonthDate
From (
Select Number as AMonth
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 12) Months
Cross Join (
Select Number as ManyYears
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 50) ManyYears -- may adjust the 5 to go back further
) EOMDates
Where EndOfMonthDate Between '1/31/2000' and '12/31/03' -- adjust WHERE to suit date needs
where on_contract <= EndOfMonthDate and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate
Matt
P.S. If someone knows how to actually format the code so its more readable, let me know how to do it.
-
Matt, 1st thing I see is you have "WHERE" twice in one WHERE CLAUSE....
"where on_contract <="
should be
"and on_contract <="
As far a formatting the post, I use the "#" tool on the "post edit toolbar". This is {CODE} insert block. It also seems to be much better if I put an added [space] at the end of each line??.
Once you understand the BITs, all the pieces come together -
Ahh I thought the first where statement was for the subquery and the 2nd where was for my original select, so I joined the 2 where's together and I am getting the same error message. ALso made a change to how I joined the tables together slightly. I also tried moving the On portion of the join after the subquery where i was getting my error at and that produced an Internal SQL Server error so I figured where I had it was correct. Below is my updated code.
Select DatePart(m, EndOfMonthDate) as the_month,
DatePart(yyyy, EndOfMonthDate) as the_year,
isnull( (SELECT SUM(num_units) from active_units_static where month_end = EndOfMonthDate), 0) + count(termnum) as active_units from active_Units
Join EOMDates ON DatePart(m, EndOfMonthDate) = month(EndofMonthDate) AND DatePart(yyyy, EndOfMonthDate) = year(EndofMonthDate)
-- Select * From
(
Select Convert(DateTime, Convert(Varchar(8), DateAdd(m, 1-AMonth, DateAdd(yy, - ManyYears, GetDate())), 1)) - DatePart(d, GetDate())
As EndOfMonthDate
From (
Select Number as AMonth
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 12) Months
Cross Join (
Select Number as ManyYears
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 50) ManyYears -- may adjust the 5 to go back further
) EOMDates
Where EndOfMonthDate Between '1/31/2000' and '12/31/03' AND on_contract <= EndOfMonthDate and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDateThanks for all your help so far,
Matt
-
Sorry Matt, I had the WHERE above the JOIN in my initial post... anyway, let's splitup the complexity...
-- Make EOMDates table
If Object_ID('TempDB..#EOMDates') id Not NULL Drop Table #EOMDates
Select * Into #Temp From
(
Select Convert(DateTime, Convert(Varchar(8), DateAdd(m, 1-AMonth, DateAdd(yy, -ManyYears, GetDate())), 1)) - DatePart(d, GetDate())
As EndOfMonthDate
From (
Select Number as AMonth
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 12) Months
Cross Join (
Select Number as ManyYears
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 50) ManyYears -- may adjust the 5 to go back further
) EOMDates
Where EndOfMonthDate Between '1/31/2000' and '11/30/03' -- adjust WHERE to suit date needs
-- Now Simplified SELECT
Select DatePart(m, EndOfMonthDate) as the_month,
DatePart(yyyy, EndOfMonthDate) as the_year,
isnull( (SELECT SUM(num_units) from active_units_static where month_end = EndOfMonthDate), 0)
+ count(termnum) as active_units
from active_Units
Join EOMDates
On on_contract <= EndOfMonthDate
and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate
Let me know.
Once you understand the BITs, all the pieces come together -
Let's try again...
-- Make #EOMDates table
If Object_ID('TempDB..#EOMDates') id Not NULL Drop Table #EOMDates
Select * Into #EOMDates From
(
Select Convert(DateTime, Convert(Varchar(8), DateAdd(m, 1-AMonth, DateAdd(yy, -ManyYears, GetDate())), 1)) - DatePart(d, GetDate())
As EndOfMonthDate
From (
Select Number as AMonth
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 12) Months
Cross Join (
Select Number as ManyYears
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and 50) ManyYears -- may adjust the 5 to go back further
) EOMDates
Where EndOfMonthDate Between '1/31/2000' and '11/30/03' -- adjust WHERE to suit date needs
-- Now Simplified SELECT
Select DatePart(m, EndOfMonthDate) as the_month,
DatePart(yyyy, EndOfMonthDate) as the_year,
isnull( (SELECT SUM(num_units) from active_units_static where month_end = EndOfMonthDate), 0)
+ count(termnum) as active_units
from active_Units
Join EOMDates
On on_contract <= EndOfMonthDate
and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate
Once you understand the BITs, all the pieces come together -
It's getting close to vacation time 🙂 so my typing is getting rough :(.... last post I had "is" misplelled as "id". Please correct... It's hard to test some of these things when you don't have the core source tables.
I'll stick with you though.
Once you understand the BITs, all the pieces come together -
Yes I noticed that, but I figured that out without your help. :]
ALso your temp tables where messed up as well. Yes, you need a vacation. That gets me my data and got the right # of active_units.
Only issue I see now, is I am only getting up to 2002 data no 2003 data but I think I can fix that one.
Also thinking about making that a regular table and not a temp table as i have other selects that will be following what we just did.
Enjoy your vacation and thanks for all your help,
MAtt
-
Matt, I apologize again for the errors
.Thanks for your patience.
I hope I at least provided some things to help you and maybe others.
Vacation is indeed long over due. It's to the point of "use it or lose it", which means I really need it. Hope I don't spend the entire vacation in bed trying to get over this cold/flu bug I have.
I'll be araoung 'til ~2PM EST today, then back on the 29th.
Happy Holidays
Once you understand the BITs, all the pieces come together
.
Happy HolidaysViewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply