Weeks In A Year

Nov 26, 2004

This is one thats got me perplexed...

I need to find out all the weeks in a year,
then for each week work out the numbers of records entered into the table that week that fit a certain criteria,
and then report back the dates involved and the amount of records that were counted! Phew...

Basically I'm trying to find the week in a year(s) that had the most records entered into the table during that week...

And I'm stuck, I've got it working with individual dates but is it possible to group those dates into weeks???

Any help would be appreciated...
Cheers

View 6 Replies


ADVERTISEMENT

Last 14 Weeks Of Year

Apr 28, 2008

hey all
i am trying to get a filter in a report that return the last 14 complete weeks. so sunday through to Monday but only display this week once it's comlete next week. i have this
FTDW.dbo.day.sql_date < dateadd(w,-1,getdate()) and FTDW.dbo.day.sql_date > dateadd(w,-15,getdate())
but i get incomplete week 3 and last week. any ideas?

View 2 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

                                                      Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

View 5 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

SQL Server 2012 :: How To Get This Year And Last Year Totals In Two Separate Columns

Jun 19, 2014

I have two queries that give me the total sales amount for the current year, and the last year.

SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'

[Code] ....

I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.

This is my query without the columns I need:

SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'

[Code] ....

View 1 Replies View Related

Select Month/year When Range Spans Year

Feb 25, 2004

I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:


where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)


and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View 5 Replies View Related

How To Find Year And Past Year Compare In Business

May 5, 2014

We have customers who are new to this year (2014) and there same customers in last year (2013). also there are customers we have not received business this year but only last year. so there are 4 conditions.

1) New customer (2014) - Customer(B)
2) Old Regular customer (2013 and 2014) - Customer(A)
3) Last Year (Lost) customer (2013) - Customer(C), no business received in year(2014)

For example we have a transaction table:

TransactionId, ReceivedDate, Customer
1, 2-Dec-2013, A
2, 3-Jan-2014, A
3, 2-Mar-2014, B
4, 25-Nov-2013, C

I want results like

Customer, Business (this year activity/last year activity)
A, 1/1
B, 1/0
C, 0/1

How can i show this for each year? I used to separate it month wise as below but it does not return applying year with each customer anyhow...

select t.customerId, YEAR(Receiveddate),
sum(case month(ReceivedDate) when 1 then 1 else 0 end )as Jan,
sum(case month(ReceivedDate) when 2 then 1 else 0 end )as Feb,
sum(case month(ReceivedDate) when 3 then 1 else 0 end )as Mar,
sum(case month(ReceivedDate) when 4 then 1 else 0 end )as Apr,

[Code] ....

View 2 Replies View Related

Compare Given Period In Current Year / Previous Year

Mar 15, 2006

HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View 3 Replies View Related

How To Do A Year-to-date SQL Query Where Year Commences In August?

Jul 20, 2005

Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View 2 Replies View Related

Power Pivot :: DAX Ranking Events Year Over Year?

Jul 14, 2015

I have a table of data that has a format similar to the following:

EventID | Event Date
--------------------
1 | 1/1/2014
2 | 2/8/2014
3 | 10/1/2014
4 | 2/5/2014
5 | 4/1/2014
6 | 9/1/2014

What I am trying to do is create a DAX formula to rank each event in the order that it happened for the year. So I want to end up with something like this. This way I can compare the events year over year as the events don't happen on any regular time schedule.

Event Date | Year | Rank
------------------------
1/1/2014 | 2014 | 1
2/8/2014 | 2014 | 2
10/1/2014 | 2014 | 3
2/5/2015 | 2015 | 1
4/1/2015 | 2015 | 2
9/1/2015 | 2015 | 3

I have tried to do this by creating a formula that will give me the day number of the year:

Day of Year =(YEARFRAC(CONCATENATE("Jan 1 ", YEAR([Event Date])),[Event Date])*360)+1

Then using rankX on this table, but I cant seem to get the proper result. Perhaps I am not understanding the use of rankX or going about this the right way.

=RANKX(FILTER(Event,EARLIER(Event[Event Year])=Event[Event Year]),Event[Day of Year])

or

=RANKX(All(Event[Event Year]),[Day of Year],,1,Dense)

View 5 Replies View Related

How To Add The Total Balances Of Previous Year Into The Next Year?

Apr 13, 2006

I'm working with a table called SALFLDGAMS where it has two columns that I need to work with. One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year. Let me explain in a little more detail. There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value. What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001. So what I'm saying is all the values from the previous year I want them in the current year. All in one column. Thanks for the help guys.

View 8 Replies View Related

Parameter Year And Previous Year (Beginner)

Dec 14, 2006

I have one matrix that shows the CrashCount (measure) by month. Looks like this:



Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec

25 90 100 55 52 55 22 55 22 35 65



The user selects a Year as a parameter. I want to put another matrix in that displays the previous year, just as the first year is displayed. How do i edit the second matrix? Do i put the parameter as

=(Parameters!CrashStatisticalYear.Value)-1 or is there some other way this can be done. Without having the user put 2 years. I just want them to pick one. And the previous year shows up in the matrix below this one. Can anyone help me with this...what should i do?

View 6 Replies View Related

Sales For Current Year To Previous Year

Oct 20, 2007

Guys,

I wanted to find the ratio: (sales made for current year 2007 - sales made for previous year 2006)/sales made for previous year 2006.

so, the result should be something like this:

Year: Sales: %change in sales:
2005 100 10%
2006 200 20%
2007 300 30%

How do I write a query for this...??? so that i can plot this in a chart in SSRS.

somebody help me.

View 4 Replies View Related

12 Weeks Ago

Mar 6, 2008

hi,
how can I get the first day of the week 12 weeks ago ?

View 10 Replies View Related

Birthdays In The Next 2 Weeks

Mar 7, 2008

Im trying to get out all peoples names whos birthdays are coming up in the next 2 weeks, can anyone tell me why this :  SELECT FullNameFROM UsersWHERE (CONVERT(datetime, DOBMonth + '/' + DOBDay + '/' + DOBYear) >= DATEADD(dd, -14, GETDATE())) AND (CONVERT(datetime,                       DOBMonth + '/' + DOBDay + '/' + DOBYear) <= DATEADD(day, 0, GETDATE()))returns nothing when I know theres a few birthdays that should be returned. Thanks Si! 

View 2 Replies View Related

DATE Between Or In Weeks

May 23, 2008

I'm trying to find a simple way to see if a date in a week. I would like to try something like this, but haven't gotten it to work yet:

case when snapshot_date in (datepart(week,getdate())) then 'yes' else 'no' end as week1,
case when snapshot_date in (datepart(week,getdate())-1) then 'yes' else 'no' end as week2

What am I doing wrong and how would I correctly go about this?

View 5 Replies View Related

Cumulative Weeks

Jul 20, 2005

SQL Server 2000 SP3Hi,How can I get the cumulative weeks from a givedate to the currentdate. I know I can get the weeknumber by using datepart(wk,getdate())but this will giveme the week number for this year. What if I want to know the number ofweeksthat have passed since june 1 2001. If I use datepart(wk,'20010106') Iwillget the week number for 2001 but I would like the number of weeksexpired between then now.Thanks,Reg

View 1 Replies View Related

Weeks Query

Sep 24, 2007

hi
i want to write query in such way that if employee who not worked from last 4 or 6 week will listed...

how we can do this?

thanx

View 10 Replies View Related

Problems With Datepart And Weeks

Feb 2, 2004

Hi all,

I have a problem with datepart. I want to obtain the week from one date. I have seen the documentation about datepart and the problem with the week counter but I have still problems.

For example.

I want to get the week from this date 29 Dec 2003. This week with DATEFIRST=1 must be the first of 2004 (datepart(ww,'12/29/2003')) but the system give me the 53 week from 2003

the first day of 2004 is on the same week an this function give me different results.

SELECT datepart(ww,'12/29/2003')
SELECT datepart(ww,'1/1/2004')

I don't understand anything ¿?

Thanks a lot.

View 2 Replies View Related

Number Weeks Of Work

Dec 23, 2012

I should check that the the number weeks of work for the five names is the same for all... that is, there can not be a person who works more or less of other...

I create a table `tbl_dates` where the primary key is the date and another field used for the holiday.

How do distribute equally these five names for the working weeks in the year?

[URL] ....

View 1 Replies View Related

Group Data By Weeks

Apr 22, 2008

hi, I am trying to group my data into weeks in a month.
I am using :
GROUP BY DateAdd(day, -1 * datepart(dw, convert(char(10),date1,23)),
convert(char(10),date1,23))

Which works ok, but my first day is the monday of each week.
Do you know how I can group by weeks,
where dates run from monday to sunday ?
So IN march I would have

week begin 3/3
week begin 10/3
week begin 17/3
week begin 24/3
week begin 31/3

But for the last day I only want to count the 31st, not the entire week.
Same goes for the beginning of the month,
eg for april, I want it to show as week beginning 31st march but I only count aprils data.

I hope that makes sense.

can you help ?

View 3 Replies View Related

How To Get Data For Past 7 Weeks

Nov 21, 2007

All,

I am trying to design a graph to shows out build status for past 7-8 weeks. I have to write the SQL query first.

After spending a good chunk of time, I could get some basic chart going. Problem is that I could give it a starting time till now (static) but I am not sure how to get the results for past 7-8 wekks.

1) What is a good approach for this since time is not static and it is changing.

any example or piece of sql syntax is greately appreciated.

Thaks in advance,

aa-

View 6 Replies View Related

European Weeks In SQL Server CE

Apr 8, 2007

Hi, I have a problem with the region settings on SQL Server Compact Edition.

To begin with, in my C# Windows Mobile app, I start with fetching records for the

current month from SQL Server 2005 Express. That works without any problems,

however, when I try to filter these records locally by weeknumber something happends

SQL Server CE uses Sunday as the first day of the week, which is inconvenient

since I live in Europe.



I tried to "SET DATEFIRST 1" in a query prior to the one fetching weekly records

but that just result in an exception.



Does anyone know how to change the regional settings on SQL Server CE?

View 1 Replies View Related

Group Data By Weeks

Nov 2, 2006

I have data entered into a table using a datetime field. How can I group the data one week at a time and show mulitple weeks at a time?

View 14 Replies View Related

Query For 4 Weeks Average ..Need Help

Sep 15, 2006

i have 3 tables, each with a date(it has daily dates) column(column name is same in all tables)
Each table has columns say "value1","value2", "value3"

i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week.

weeks count(value1) count(value2) count(value3 )
1/1/2005 101 88 221
1/8/2005 100 81 151
1/15/2005 87 96 301

Average calculations Here :
week 1 2 3 4
Count1: 101 = ( 99 + 105 + 110 + 87 )/4
100 = (105 + 110 + 87 + 98 )/4


Plz lemme know if u have any suggestions..

View 7 Replies View Related

Fiscal Year Date Help??? I Need The First Day Of The Year To Be 01/27.

Feb 4, 2008


Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck

View 2 Replies View Related

Count By Weeks - Created Dates?

Nov 21, 2013

Aim - Looks at the “CreatedDate” for the latest month and count how many created dates fall within a given week

Currently the created date is displayed as

2013-11-01T09:08:54.000Z
2013-11-05T12:32:26.000Z
2013-11-15T12:37:17.000Z

So for Example

Week 1 = 2
Week 2=1
Week 3=0
Week 4= 0

My table is

select
[CreatedDate],
from #build

View 6 Replies View Related

My Db Grew 8.2GB In Two Weeks BUT Only 4MB Of Data Added

Jul 23, 2005

I have a db that grew 8.2GB in one week and don't understand why.There are three tables added to the db daily. I calculated thespaceused by each of the three tables for a period of two weeks. Thetatal amount of data added to the db for the three daily tables overthe past two weeks was about 4MB yet the db grew approximately 8.2GB.WHY?Can someone please tell me what I should look at so that I canunderstand what is going on?

View 4 Replies View Related

Transact SQL :: Count Weeks With More Than Two Records

Aug 22, 2015

My table is test and I have an ID and DateTest columns

I would like to count the weeks with more then one record.

So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record

select sum(c)
from (
select c = count( id) over (partition by id, datepart(week, DateTest))
from test where id = '1' and DateTest >= '7-7-2015'
) a where c = 1

View 11 Replies View Related

My Server Stop Responding Every Few Weeks

Mar 4, 2008

Hi,
i've this phenomenon that my SQL SERVER 2005 SP2 Build 3042 (W2K3 STD SP2) stop responding to connections.
when i make stop and start to the sql server service it's back to life.
when i observer the windows event log and sql events i see those errors

from windows eventvs

AppDomain 24 (system.dbo[runtime].24) is marked for unload due to memory pressure.


AppDomain 24 (system.dbo[runtime].24) unloaded.


SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.165.2.173]


from SQL events


SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.165.2.173]


Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: <local machine>]

Unsafe assembly 'hebfilts, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 28 (system.dbo[runtime].28).

Plz help me

Thx

View 4 Replies View Related

SQL Server 2012 :: Compare Current Year Data With Last Year Data - Day Adjusted

May 25, 2015

Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.

I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.

View 5 Replies View Related

SQL Server 2012 :: Create Weeks Calendar From Date

Sep 30, 2015

I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:

WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved