YTD, MTD, WTD, queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

YTD, MTD, WTD, queries

Author

Topic

lb003
Starting Member

4 Posts

Posted - 2009-07-17 : 13:12:08

Hello all,
I am fairly new to sql 2005 and know enough to be dangerous. Anyway, I need a query that will return results based on today's date and how it relates to the current week, month, and year. I've written one that will return data based on getdate()-7, etc. The problem is (1) that for each time period of wtd, mtd, and ytd, I need a different query and (2) the query I wrote doesn't do exactly what i need it to do. Please help point me in the right direction.

Here is what i have so far:

select
REP.descrip as Rep,
count(ENRL.startdate) as StartsMTD
from
dbo.adenroll ENRL
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
ENRL.startdate > getdate()-31
and
SS.syschoolstatusid = '13'
group by
REP.descrip
--end--

Just to clarify, I need one query to return results from all three time periods (wtd, mtd, ytd) based on today's date. Thank you sooo much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:28:05

[code]select
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as WkTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as MTD,
count(ENRL.startdate) as YTD
from
dbo.adenroll ENRL
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
and
SS.syschoolstatusid = '13'
group by
REP.descrip
[/code]

lb003
Starting Member

4 Posts

Posted - 2009-07-17 : 14:19:56

Thank you very much visakh16!

This worked great. I was able to take what you wrote and add to it to include enrolls. The only problem is that it is returning 15 repid's when only a certain 12 are specified. Did I do something wrong?

Here's what I did:

select
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from
dbo.adenroll ENRL
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and
ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or
ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or
ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
and
SS.syschoolstatusid = '13'
or
ENRL.syschoolstatusid = '5'
or
ENRL.syschoolstatusid = '74'
group by
REP.descrip
--end--

Also,
Is it possible to return a column that shows a conversion percentage between enrollsMTD and starsMTD? (for YTD as well?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 14:29:56

quote:
Originally posted by lb003

Thank you very much visakh16!

This worked great. I was able to take what you wrote and add to it to include enrolls. The only problem is that it is returning 15 repid's when only a certain 12 are specified. Did I do something wrong?

Here's what I did:

select
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from
dbo.adenroll ENRL
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and
ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or
ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or
ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and
(SS.syschoolstatusid = '13'
or
ENRL.syschoolstatusid = '5'
or
ENRL.syschoolstatusid = '74')
group by
REP.descrip
--end--

Also,
Is it possible to return a column that shows a conversion percentage between enrollsMTD and starsMTD? (for YTD as well?)

modify like above

lb003
Starting Member

4 Posts

Posted - 2009-07-17 : 14:56:30

Hi visakh16,
like gives me syntax error:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ','.

Also,
Is it possible to return a column that shows a conversion percentage between enrollsMTD and starsMTD? (for YTD as well?) Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 01:35:15

are you sure you're using the exact query? the query looks fine to me

i think for latter you need something like this:-


select *, EnrollsMTD*100.0/StartsMTD as MTDConversion, 
EnrollsYTD*100.0/StartsYTD as YTDConversion
from
(
select 
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from 
dbo.adenroll ENRL 
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid	
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t
--end--

lb003
Starting Member

4 Posts

Posted - 2009-07-18 : 11:21:39

Hello again visakh16!
Thanks for all your help! I ran your query and got the following error, unfortunately:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:52:48

[code]
select *, EnrollsMTD*100.0/nullif(StartsMTD,0) as MTDConversion,
EnrollsYTD*100.0/nullif(StartsYTD,0) as YTDConversion
from
(
select
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from
dbo.adenroll ENRL
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources