How to get count for week range?

Question

How to get count for week range?

thundersplash845

SSC Eights!

Points: 957
More actions
July 14, 2011 at 11:50 pm

#239622

Hi All,
From the below query, i want to get a count and display the output on the weekly basis...
Say if i give from and to date as input parameters, the output should show the count for each week based on the given input date... Can any one tell me how to implement this??
with docs as
(
select a.dnum, a.dvers, b.prjid, a.dname from docsm a
inner join prosm b on a.dnum = b.inum
where a.dsize = 0
)
select a.dname, a.dnum, a.dvers, b.prjid from docsm a
inner join prosm b on a.dnum = b.inum
inner join docs c on c.prjid = b.prjid
where 1=1
and a.dname = c.dname
and a.dsize > 0
-----------------------------------------------------------------
input parameter:01 Mar 2011 to 07 Mar2011
08 Mar 2011 to 14 Mar2011
Output should be something like:
---Totalcount------------|----Count for period------|-------Count for period------|
01 Mar 2011 to 07 Mar2011 08 Mar 2011 to 14 Mar2011
-------125--------------|---------50---------------|-------------75-------------|
July 15, 2011 at 3:04 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I strongly vote against using DATEPART(ww) since it depends on the setting of @@DATEFIRST which can be changed either by SET DATEFIRST or SET LANGUAGE or the language setting of the login running the query.

use DATEADD(wk,DATEDIFF(wk,0,DateColumn),0) instead.

You might need to adjust it to point to set the beginning on the week to the weekday of your choice.

You might also use DATEPART(ISO_week,DateColumn) if the week should always start on a Monday.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Except for in a function or view where you can't actually use a SET statement, I have no problem with the idea of setting DATEFIRST to whatever I need it to be to get something done with DATEPART. I know a lot of people avoid both like the plague and I sometimes see an advantage of doing something without them, but I find both useful and easy to use compared to some of the crazy formulas that some folks come up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 3

Jeff Moden (7/15/2011)
Except for in a function or view where you can't actually use a SET statement, I have no problem with the idea of setting DATEFIRST to whatever I need it to be to get something done with DATEPART. I know a lot of people avoid both like the plague and I sometimes see an advantage of doing something without them, but I find both useful and easy to use compared to some of the crazy formulas that some folks come up with.

I'm one of those paranoid people, I admit. The reason is rather simple: I have to deal with different language settings (U.S. English and German) for various users since the folks adding the logins sometimes set the language to DEFAULT (English) and sometimes change it to German.

I could make it a habit to set the language to a known value at the beginning of each proc, but that would add another level of complexity (e.g. for functions or ad-hoc queries or views).

Finally, I don't like the effect of SET DATEFIRST affecting the setting of SET LANGUAGE: if DATEFIRST is used in a batch prior to SET LANGUAGE, the value of @@datefirst will remain unchanged (still the value as it has been set using DATEFIRST, no matter if LANGUAGE implies another value).

The formulas might look crazy, but at least return consistent results 😉

The code required to get always consistent results with SET DATEFIRST would add some overhead, too (e.g. store the old values of @@datefirst and @@language prior to any SET LANGUAGE statement (and @@datefirst for SET DATEFIRST, respectively), always change DATEFIRST independent of LANGUAGE to avoid the side effects described above and revert it back to the original values at the end of the section where it is required).

There might be environments where the language setting of each login is strongly enforced and monitored and there will never, ever be the requirement of adding a user with a different setting. In such an environment I wouldn't have any problem using DATEFIRST. Paranoid, I know. 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

thundersplash845 SSC Eights! Points: 957 More actions · Answer 4

Lutz,Thanks for your reply.. But how do i get the count of records per week??

I want something like the below one...How do i include in my code to get this results?? Any suggestion??

-Totalcount -|-Count for period(01 Mar 2011~07 Mar2011)-|-Count for period(08 Mar 2011~14 Mar2011)

---125------|---------50---------------------------------|-------------75-------------|

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

thundersplash845 (7/17/2011)
Lutz,Thanks for your reply.. But how do i get the count of records per week??
I want something like the below one...How do i include in my code to get this results?? Any suggestion??
-Totalcount -|-Count for period(01 Mar 2011~07 Mar2011)-|-Count for period(08 Mar 2011~14 Mar2011)
---125------|---------50---------------------------------|-------------75-------------|
Thanks

Since 01 Mar 2011 was a Tuesday, I have to ask, what do you define as a week?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thundersplash845 SSC Eights! Points: 957 More actions · Answer 6

Week should start with Monday by default or based on the input date parameters...