Query Help
-
Below is a select statement that will be a stored procedure at some point. I will only be passing @startdate and @enddate as user defined. For the purpose of testing I set them to dates Note: I am trying to get the data for @totalatstart"total opened at start" and @totalatend "total opened at end" to be specifc to "Client" declare @startdate datetime declare @enddate datetime declare @totalatstart int declare @totalatend int DECLARE @difference int set @startdate ='03/01/2007' set @enddate = '06/11/2007' Set @totalatstart =(select count(*) from job_ticket where report_date > @startdate ) Set @totalatend =(select count(*) from job_ticket where report_date < @enddate) set @difference =(@totalatstart-@totalatend) select count(*) as 'Opened', @totalatstart 'Total Open at Start', @totalatend 'Total Open at End', @difference 'Total Closed', location_name 'Cient', AVG(datediff(d,report_date,getdate() )) AS [Average days open] from job_ticket j inner join location l on l.location_id = j.location_id where (report_date between @startdate and @enddate) group by l.location_name Current resluts look like this: Opened | Total Open at Start | Total opened at End | Total Closed | Client | ||||||| |||| AVG 47 |||||||| 341|||||| |||||||| 48 ||||||||||||||||||| 293 ||||||||| Alexandria, VA||||| 70 59|||||||||| 341|||||||||||| 48 ||||||||||||||| ||| 293 |||||||||| Austin, ||||||||||| 63 Obviously something is wrong with my set statment or my entire approach to this Any suggestions or help in the right direction would be greatly appreciated. I have been reading and can not seem to figure this out. Regards,
Regards, Hulicat
-
Below is a select statement that will be a stored procedure at some point. I will only be passing @startdate and @enddate as user defined. For the purpose of testing I set them to dates Note: I am trying to get the data for @totalatstart"total opened at start" and @totalatend "total opened at end" to be specifc to "Client" declare @startdate datetime declare @enddate datetime declare @totalatstart int declare @totalatend int DECLARE @difference int set @startdate ='03/01/2007' set @enddate = '06/11/2007' Set @totalatstart =(select count(*) from job_ticket where report_date > @startdate ) Set @totalatend =(select count(*) from job_ticket where report_date < @enddate) set @difference =(@totalatstart-@totalatend) select count(*) as 'Opened', @totalatstart 'Total Open at Start', @totalatend 'Total Open at End', @difference 'Total Closed', location_name 'Cient', AVG(datediff(d,report_date,getdate() )) AS [Average days open] from job_ticket j inner join location l on l.location_id = j.location_id where (report_date between @startdate and @enddate) group by l.location_name Current resluts look like this: Opened | Total Open at Start | Total opened at End | Total Closed | Client | ||||||| |||| AVG 47 |||||||| 341|||||| |||||||| 48 ||||||||||||||||||| 293 ||||||||| Alexandria, VA||||| 70 59|||||||||| 341|||||||||||| 48 ||||||||||||||| ||| 293 |||||||||| Austin, ||||||||||| 63 Obviously something is wrong with my set statment or my entire approach to this Any suggestions or help in the right direction would be greatly appreciated. I have been reading and can not seem to figure this out. Regards,
Regards, Hulicat
Well - part of your problem is that you only identify the total at start, difference and total at end once. These have no relation to the rest of your query. I would suggest that you put the logic for the creation of these values into the main query.
Please visit http://www.readytogiveup.com/ and do something special today. Deja View - the feeling that you've seen this post before.