Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Creating a query to return record set for each day of mo
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
00719788
Message ID:
00720282
Vues:
15
I think you'll find that the performance will be better if you stick closer to my example. Run the initial query that collects the data and then run another that hits the temp table to pull the dates and a zero for the dates that were not found in the initial query.

-Mike

>Michael
>
>Thanks for the sample, here is what I finally came up with as the stored procedure:
>
>
>CREATE PROCEDURE ctGetDayCount
>@ltQueryFrom datetime,
>@ltQueryTo Datetime
>
> AS
>
>
>create table #output(
>    thisDay datetime,
>    total int,
>   SchMin int,
>   ORMin int
>)
>
>while @ltQueryFrom <= @ltQueryTo
>begin
>    insert into #output
>    SELECT @ltQueryFrom, count(*),
>    isnull(sum(datediff(mi,begintime,endtime)),0) as SchedMinutes,
>    isnull(sum(datediff(mi,enteror, exitor)),0) as ORMinutes
>    FROM meetings
>    WHERE begintime BETWEEN @ltQueryFrom AND @ltQueryFrom + 1
>    and meetings.iscompleted=1
>    and meetings.iscancelled=0
>    select @ltQueryFrom = @ltQueryFrom + 1
>end
>
>select * from #output
>drop table #output
>GO
>
>
>This gives me all the dates between the date range passed.
>
>Kirk
Michael Levy
MCSD, MCDBA
ma_levy@hotmail.com
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform