CODEWITHSUNDEEP
sql-serversql-server-2005
Amin SCO
Amin SCO

Reputation: 1972

How to calculate weekly permonth in aqua data studio

i want to ask question about weekly. How to calculate weekly permonth.For example one month have 30 day and 4 week permonth. if i select the second week in january. it will show the number of weekly permonth and name of month. i want the result show the number of weekly(not all the total weekly peryear) in month and name of the month.Thank you.

i want to know weekly calc per month.

eg. for january month

1-7 days------------   1 week
8-14 days------------  2 week
15-21 days------------ 3 week
22-28 days------------ 4 week
29 days - other------- 5 week

eg. for Feb month

1-7 days------------   1 week
8-14 days------------  2 week
15-21 days------------ 3 week
22-28 days------------ 4 week

and so on.

Upvotes: 1

Views: 243

Answers (3)

Amin SCO
Amin SCO

Reputation: 1972

I already got the answer. You just put:

select * from customer((DATEPART(day, [date_field] - 1) / 7 + 1 = :weeks) or (:weeks = ' '))

Upvotes: 1

Andriy M
Andriy M

Reputation: 77707

You could use this grouping in your query:

GROUP BY DATEADD(DAY, -(DAY(DateColumn) - 1) % 7, DateColumn)

The following chart shows how the above expression works:

DateColumn  DAY(DateColumn)  DAY(…) - 1  (DAY(…)-1) % 7  DATEADD(…)
----------  ---------------  ----------  --------------  ----------
2012-04-01  1                0           0               2012-04-01
2012-04-02  2                1           1               2012-04-01
2012-04-03  3                2           2               2012-04-01
2012-04-04  4                3           3               2012-04-01
2012-04-05  5                4           4               2012-04-01
2012-04-06  6                5           5               2012-04-01
2012-04-07  7                6           6               2012-04-01
2012-04-08  8                7           0               2012-04-08
2012-04-09  9                8           1               2012-04-08
…           …                …           …               …
2012-04-13  13               12          5               2012-04-08
2012-04-14  14               13          6               2012-04-08
2012-04-15  15               14          0               2012-04-15
…           …                …           …               …
2012-04-21  21               20          6               2012-04-15
2012-04-22  22               21          0               2012-04-22
…           …                …           …               …
2012-04-28  28               27          6               2012-04-22
2012-04-29  29               28          0               2012-04-29
2012-04-30  30               29          1               2012-04-29
2012-05-01  1                0           0               2012-05-01
2012-05-02  2                1           1               2012-05-01
…           …                …           …               …

As you can see, the result is the beginning of the week (week in the sense you define it).

Upvotes: 2

Pondlife
Pondlife

Reputation: 16260

Your question is rather unclear (what does "weekly" mean? weekly what?) and I'm not sure I really understand it. But I'm guessing that for a given date, you want to know what week in the month it is?

If so, The simplest solution is a calendar table that stores the week number and any other useful information you need for each date in the year. You can populate it in advance for as many years as you like and then you can just write a query like this:

select WeekInTheMonth
from dbo.Calendar
where BaseDate = @SomeDate

If this doesn't help, you will need to show exactly what input parameters and output data you expect to see.

By the way, I assume you're working with Aqua Data Studio as your database client, but it has nothing to do with this question so you may want to edit the title.

Upvotes: 0

Related Questions