sql count for a week

I have a query where I am counting number of session per weekdays and this part works.

What the problem is when the date is passed I want to check the week and count the number of session in that week, instead I get the number of sessions for that day

Select WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)

			FROM SessionUnit SU
			INNER JOIN Session S on S.sessionKey = SU.sessionkey
			INNER JOIN product P on p.productKey = S.productKey
			CROSS APPLY (
			    SELECT item AS SPLITLOCATIONKEY
			    from DelimitedSplit8K(S.locationKeyList, ',')
			)as ca
			INNER JOIN location AS L2 ON CA.SplitLocationKey = L2.LocationKey AND (L2.locationKey in (228,260))
			WHERE  su.sessionStart >= DateAdd(day,45,dateadd(day, datediff(day, 0, getdate()), 0))
			AND su.sessionStart <  DateAdd(day,91,dateadd(day, datediff(day, 0, getdate()), 0))
			AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6)
			AND p.productKey = 2  and SU.instructorKey = 13879
			and convert(date,SU.sessionStart) = '2015-05-13'
			GROUP BY
			 dateadd(week, datediff(week, 0, SU.sessionStart), 0)
			 --HAVING COUNT(S.sessionKey) >= 9

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You have a WHERE condition:
            and convert(date,SU.sessionStart) = '2015-05-13'
that limits sessionStart to just that one day.

You need to change the WHERE conditions for sessionStart to include the entire week you want.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I know When the date get passed I wan to  use that day to figured out which week is belong to and count a number of sessions for that week
0
Scott PletcherSenior DBACommented:
where ...
and SU.sessionStart >= '20150513' and
    SU.sessionStart < DATEADD(DAY, 8, '20150513')
and ...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

erikTsomikSystem Architect, CF programmer Author Commented:
hi ScottPletcher I try using your logic but for some reason I am getting 2 records. Also If I change the date to be 5-12 we still in the same week the count is 6

WeekStart      CountID
11-05-2015      5
11-05-2015      9
0
Scott PletcherSenior DBACommented:
I thought 05-13 was the week start date.  If it's week end date, you need to do this:

SU.sessionStart >= DATEADD(DAY, -6, '20150513') and
SU.sessionStart < DATEADD(DAY, 1, '20150513')
0
erikTsomikSystem Architect, CF programmer Author Commented:
05-13 is the random date in my select statement I am working out what the start date is  
Select WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105)
0
erikTsomikSystem Architect, CF programmer Author Commented:
Any suggestions
0
PortletPaulEE Topic AdvisorCommented:
suggestion:

      AND SU.sessionStart >= DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513'), 0)
      AND SU.sessionStart <  DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513')+1, 0)

This is the same algorithm you are using to do the grouping, so for any specific date this should give you the whole week it relates to.

HOWEVER:
Please look at the where clause as a whole as there are several parts of it that relate to dates. These could easily lead nothing being selected.

I have rearranged it slightly for emphasis
WHERE P.productKey = 2
      AND SU.instructorKey = 13879

      AND SU.sessionStart >= DATEADD(DAY, 45, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
      AND SU.sessionStart < DATEADD(DAY, 91, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

      AND DATEDIFF(DAY, 0, SU.sessionStart) % 7 NOT IN (5, 6)

      AND SU.sessionStart >= DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513'), 0)
      AND SU.sessionStart <  DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513')+1, 0)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.