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


ADVERTISEMENT

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

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

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

Accessing Data Related To Current Week From List Of All Weeks

Apr 4, 2007

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.

View 12 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

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 View Related

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

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

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

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

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

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 :: 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

Rolling Weeks Date Range Of Current Week

Apr 22, 2015

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.

View 1 Replies View Related

How To Write A Stored Procedure To Report Weekly Sales For The Last 5 Weeks

Feb 3, 2006

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 Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Collapsing/Expanding Group Data In Table And Matrix Data Regions

May 30, 2007

Hi,



Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?



Thanks.

View 1 Replies View Related

SQL Server 2008 :: MDX Query Filtering - Return Sales For Each Customer For Past 6 Weeks

Feb 4, 2015

I'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.

View 0 Replies View Related

How To Group Certain Rows And Get The Data.

Jul 2, 2007

Hi Folks,

I am stuck in forming a query.

My age wise employee count sample data (department wise) is as shown below.

Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7

I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.

Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.

Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36

Thanking you in anticipation.

Jabez.

View 4 Replies View Related

Group Data For Survey

Feb 5, 2008

hi,

i'm having following data:


create table survey
(
clientID int not null,
questionID int not null,
Answer int not null
)

insert into survey (clientID, questionID, Answer) values (1, 1, 1)
insert into survey (clientID, questionID, Answer) values (1, 1, 2)
insert into survey (clientID, questionID, Answer) values (1, 1, 4)
insert into survey (clientID, questionID, Answer) values (1, 1, 5)
insert into survey (clientID, questionID, Answer) values (2, 1, 1)
insert into survey (clientID, questionID, Answer) values (2, 1, 5)
insert into survey (clientID, questionID, Answer) values (2, 1, 8)
insert into survey (clientID, questionID, Answer) values (3, 1, 2)
insert into survey (clientID, questionID, Answer) values (3, 1, 4)
insert into survey (clientID, questionID, Answer) values (3, 1, 6)
insert into survey (clientID, questionID, Answer) values (3, 1, 9)
insert into survey (clientID, questionID, Answer) values (4, 1, 8)
insert into survey (clientID, questionID, Answer) values (5, 1, 1)
insert into survey (clientID, questionID, Answer) values (5, 1, 5)
insert into survey (clientID, questionID, Answer) values (5, 1, 9)


and i want to get it out like this:

ClientIDQ1_1Q1_2Q1_3Q1_4
----------------------------------------
1|1|2|4|5|
2|1|5|8|0|
3|2|4|6|9|
4|8|0|0|0|
5|1|5|9|0|

but i get the following output:

clientID Q1_1 Q1_2 Q1_3 Q1_4
----------- ----------- ----------- ----------- -----------
1 12 12 12 12
2 14 14 14 14
3 21 21 21 21
4 8 8 8 8
5 15 15 15 15

(5 row(s) affected)

with this code:



SELECT

distinct(clientID)
,Q1_1
,Q1_2
,Q1_3
,Q1_4


from
(select
distinct(clientID)
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_1
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_2
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_3
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_4


from
dbo.survey

group by clientid) as x

group by
x.clientID
,x.Q1_1
,x.Q1_2
,x.Q1_3
,x.Q1_4


order by clientID asc


what am i doing wrong? Thank you

View 9 Replies View Related

Group Data By Month

Feb 20, 2008

Hi,
how can i group data using specified date like for example per month.
here is my sample data.

fielddate | fieldnumber
january 1, 2007 | 1
january 5, 2007 | 2
February 2, 2007| 3
March 4, 2007 | 3
March 5, 2007 | 4

i want to sum all fieldnumber by month
result will be:

resultdate | resultnumber
january 2007 | 3
February 2007 | 3
March 2007 | 7

thnx for the help in advance.

View 3 Replies View Related

GROUP BY For Heirarchical Data

Mar 7, 2008

I have a table:

ID int, AreaID int, SaleAmount float, SaleDate smalldatetime

AreaID is an foreign key to a second table of heirarchical area data:

ID int, ParentId int, AreaName nvarchar.

The heirarchy varies in depth for different parts of the country - sometimes 3 levels deep, sometimes up to six.

My problem is this: how do a construct a sproc that will allow me to pass in any area id and then return one or more result sets with all the child data of that area id grouped (to allow a SUM() of the sales data and a MIN() of the dates) by EACH LEVEL of the heirarchy?

Sales data only exists in the bottom one or two levels.


I've tried looking at CTEs but can't seem to crack the problem. I got close with a WHILE loop, but that kept grouping the data at the same level.....

Thanks

John

Expected output from this data if, say, I passed in 1 (the National level)

'National', 47.81, 20-Feb-08
'Super Region A', 37.81, 21-Feb-08
'Region 1', 16.81, 21-Feb-08
'Region 2', 21.00, 22-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08
'Sub Region 3', 7.70, 23-Feb-08
'Sub Region 4', 12.30, 22-Feb-08

and so on. Note that it doesn't have to be one table - each level could come back as a separate table (in fact, that might be helpful).

If I passed in 3 I'd get

'Region 1', 16.81, 21-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08


(NB these results are just typed in, so forgive typos please, and the sums and min dates are not necissarily the same as from the sample
data below)

CREATE TABLE [dbo].[SalesData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[AreaID] [int] NULL,
[SalesDate] [smalldatetime] NULL ,
[SalesAmount] [float] NULL
)


CREATE TABLE [dbo].[SalesAreas](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL ,
[Name] [nvarchar](64) NOT NULL
)

DECLARE @ID int
DECLARE @ID2 int
DECLARE @ID3 int

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( 0, 'National') --1
SELECT @ID = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region A')--2
SELECT @ID2 = @@IDENTITY

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 1') --3
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 1') --4
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 2') --5

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 2') --6
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 3') --7
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 4') --8

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region B')--9
SELECT @ID2 = @@IDENTITY

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 3') --10
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 5') --11
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 6') --12
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 7') --13

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 4') --14
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 8') --15
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 9') --16
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 10') --17
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 11') --18



INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-5, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-6, GETDATE()), 2.81)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-8, GETDATE()), 4.14)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-9, GETDATE()), 1.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-2, GETDATE()), 1.02)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-3, GETDATE()), 3.13)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 6.17)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-1, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-4, GETDATE()), 4.29)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-5, GETDATE()), 4.46)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-6, GETDATE()), 3.33)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-1, GETDATE()), 3.92)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (12, DATEADD(d,-7, GETDATE()), 5.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-5, GETDATE()), 6.16)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-3, GETDATE()), 3.34)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-2, GETDATE()), 2.61)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-3, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (16, DATEADD(d,-4, GETDATE()), 8.28)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (17, DATEADD(d,-5, GETDATE()), 2.44)

View 14 Replies View Related

USING 'CASE' TO GROUP DATA

Aug 22, 2006

Hi,Can anyone please help me with SQL syntax to create a second variablebased on the value of another (both numeric)?My effort is below but I get a syntax error.SELECTcharA,CASE charAWHEN < -199 THEN 2WHEN < 31 THEN 3WHEN < 82 THEN 4WHEN < 100 THEN 5WHEN < 105 THEN 6WHEN < 111 THEN 7WHEN < 143 THEN 8WHEN < 165 THEN 9WHEN < 233 THEN 10WHEN >= 233 THEN 11END AS Bin_charAFROM XServer: Msg 170, Level 15, State 1, Line 6Line 6: Incorrect syntax near '<'.

View 3 Replies View Related

Using Group By For Text Data Type

May 22, 2006

Hi,I have a problem on an old app. This application is a Help Desk, any Computer related problem (Motherboard Burn Out, Software Crash, dll) on my company will be recorded using this Help Desk application.One month ago, some programmer found a bug in this program lead to many duplicate data on database. But too late, This program has entered production stage and used for more than 2 years. This bug only happen at some condition so I found out after 2 years running (when my boss reported there are some duplicate data on "helpDesk" table).The bug is solved but helpDesk table need some "cleaning" operation. Here is structure of that table :helpDesk Table- id (primary key, integer auto increment)- reported_by (varchar 200)- problem_title (varchar 200)- problem_description (text)- date_summited (datetime)well usualy if I want to find some duplicate data I wil type :SELECT     MAX(uid) AS uid, reported_by, problem_title, COUNT(uid) AS total_duplicateFROM         problemsGROUP BY problem_title, problem_description,uidHAVING      (COUNT(uid) > 1)But that query will not work because problem_description data type is "text" which not support "group by" statement.Any other Idea how to locate and cleaning up this duplicate data?thanks

View 2 Replies View Related

SQL Server 2014 :: How To Sum And Group Data

Jan 20, 2015

I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.

Is this possible?

This is my current SQL

SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,

[Code] ....

View 9 Replies View Related

Selecting Distinct Data Using Group By

Aug 4, 2013

I have an action log table which records when a registrant record was viewed by a compnay employee. I have an sql query like this:

SELECT [ID]
,[RegistrantID]
,[EmployeeID]
,[UserID]
,[CompanyID]

[Code] ....

and data is like this:

IDRegistrantID EmployeeIDUserID CompanyID VacancyID Action ActionDate
17931629515163213NULL 42013-08-04 16:45:40.457
17921629215163213NULL 42013-08-04 16:45:33.003
1791NULL15163213NULL 32013-08-04 16:45:23.660
1790162959162893NULL 42013-08-04 16:45:09.543

[Code] ....

I want to select distinct views to a registrantid record ( the first ones) in one year. if a registrant was viewed 10 tmes a year then it will show only first time it was viewed. If it was viewed 10 times by an employeed in 2 years then it will show first time it was viewed. if it was viewed by 2 employees of same company 10 times in one year then it first time viewed record will be shown. if it was seen 10 times by 2 employees of two different companies in one year then first record of two companies will be shown. do i need to use group by or what ?

View 1 Replies View Related







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