SQL Server 2000 SP3
Hi,
How can I get the cumulative weeks from a givedate to the current
date. I know I can get the weeknumber by using datepart(wk,getdate())
but this will give
me the week number for this year. What if I want to know the number of
weeks
that have passed since june 1 2001. If I use datepart(wk,'20010106') I
will
get the week number for 2001 but I would like the number of weeks
expired between then now.
Thanks,
Reg
hi,
how can I get the first day of the week 12 weeks ago ?
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 RelatedThis 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
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?
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?
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
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.
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] ....
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 ?
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-
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?
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 Relatedi 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..
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
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 RelatedMy 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
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
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
I require some scripting to return data which sits in a rolling weeks date range of the current week.
For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)
And so forth.
So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.
hi friends,
I m new to asp.net... actually i have an application where the data for current week needs to be displayed from all the data present in different weeks.actually i want to retrieve the syllabus of current week from all weeks
i used the below query but itz not working
sqlString = "SELECT DATEPART(ww,sylWeeKID)AS Week FROM SylWeeks WHERE SyllabusIDFK= " & SylId & " GROUP BY DATEPART(ww,sylWeeKID) "
..can anyone suggest me how to retrieve data for current week , for a related item
thanks in advance
i need urgent help.
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
View 7 Replies View RelatedI'm having problems filtering in my MDX query.
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS
FILTER(
([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]),
[Measures].[Sales] >= 10000
AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
[Code] ....
The dates are hard-coded for testing purposes.
Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS.
thoughts?
thanks
I'm trying to get the final result using an aggregate function.
Table looks like below;
ID ACRE Probability
1 3 0.3
2 1 0.6
3 6 0.2
4 5 0.5
5 2 0.1
First, I want to descend by probability then select all records that cumulative acre <= 8
So, final result will be:
ID ACRE Probability
2 1 0.6
4 5 0.5
Any idea how to write a script?
Thanks in advance,
Mike Jun
hyunhyo@hotmail.com
GIS reserch Group
University of Colorado
303-492-8781
I have to group the no. of employees based on month. i.e
Jan 10 employees
Feb 20 employees
Mar 30 employess
The result is to be
Jan 10
Feb 20
Mar 60.
Kindly provide the sql for getting the above result.
Thanks,
Lakshmi
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.
The table is setup as follows:
Parent Child Pop
----------------------------------
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0
This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.
I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):
World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100
Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.
Any help/ideas would be appreciated! Thank you.
In the emp table :
>
> EMPNO ENAME SAL
> ==================
> 7369 SMITH 1000
> 7499 ALLEN 2000
> 7521 WARD 3000
> 7566 JONES 4000
> 7654 MARTIN 5000
>
> there is a requirement to have a calculated col.
> called cummulative sal
>
> EMPNO ENAME SAL CUMMULATIVE_SAL
> 7369 SMITH 1000 1000
> 7499 ALLEN 2000 3000
> 7521 WARD 3000 6000
> 7566 JONES 4000 10000
> 7654 MARTIN 5000 15000
>
>
How to show this calculated col. by using one select statement?
I could get the result using Empno. in my query as --
select ename,sal,(select sum(sal) from emp k where k.empno <= e.empno) as cum from emp e order by ename
but I was asked to get the same result if empno. col is not there?
SUCKS........... the below code is giving the result but I don't know how you could use operator (<) on two strings to compare......????
select ename,sal,(select sum(sal) from emp k where k.ename <= e.ename) as cum from emp e order by ename
Someone tell me if this is the right approach or is there a better way of getting the thing done.
Hi,There are 3 tablesTable,TableDetails,TableDaily.With structureTABLE:TableID UserID Money---------- ---------- ----------(int) (int) (money)TABLEDETAILS:TableDetailsID TableID ItemID PaidForItem DayID---------- ---------- ---------- ---------- ----------(int) (int) (int) (money) (int)TABLEDAILY:TableDailyID TableID PaidForItem Money Total Change---------- ---------- ---------- ---------- ---------- ----------(int) (int) (money) (money) (PaidForItem + Money) (money)"Table" holds id for user and his money amount, which changes during time. "TableDetails" holds data about items user bought, amount paid for them and dayid which relates to one particular day."TableDaily" holds history. I do not know how to update this table.I created job whish runs stored procedure. This procedure sums "PaidForItem" using group by TableID and WHERE DAYID = '11'.Problem is with Change column. This column sould hold difference between today's Total and previous one etc.Current procedure looks like this:INSERT INTO TableDaily (TableID, PaidForItem, Money, DayID)SELECT TableDetails.TableID, SUM(PaidForItem) AS PaidForItem, Table.Money, (SELECT DayID FROM Days WHERE (Aktive = 1)) AS DayIDFROM TableDetails INNER JOIN Table ON TableDetails.TableID = Table.TableID GROUP BY TableDetails.TableID, Table.Money
View 3 Replies View RelatedI have the following:
SELECT '201305' AS PAYPERIOD,
EMPLOYEE,
RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
COUNT (EMPCODE) AS BONUSCOUNT_YTD
FROM Table1
WHERE (YEAR = 2013 AND PERIOD < 2)
GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID
[Code] ...
How can I get the counts to be cumulative? In other words, if an employee appears in pay period 201305 that's 1, if they then appear in pay period 201306 that becomes 2.
Is there any way to calculate Cumulative result.
Manpower|Count|Cum_Count|Formula
Apr|70|70|Sum(APR)
May|40|110|Sum(Apr+May)
Jun|110|220|Sum(Apr+May+Jun)
How to calculate the cumulative of months.
Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686
if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.
I tried to install "Cumulative update package 2 for SQL Server 2005 Service Pack 2"
Everything went well except for the "SQL Server Database Services" update.
It errored out as it was trying to "Finalize" the update.
Kicker to this whole thing is the database file "temp_MS_AgentSigningCertificate_database.mdf" does not even exist.
I could not see any references to it in the master database.
I checked the registry and I can find a couple of search references for it.
It apparently may have been a database that existed in the server at one time.
I am not sure if I should remove the registry references to the database.
Below is a part of the install summary where it has failed:
===========================================================
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3050
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB936305_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]A file activation error occurred. The physical file name 'F:Data emp_MS_AgentSigningCertificate_database.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.. To continue, correct the problem, and then run SQL Server Setup again.
If someone can tell me what I need to do to resolve this issue, I would greatly
appreciate it.
Thanks,
Larry :-(
Larry :-)