Grouping Data In Consecutive Values
Oct 12, 2007
Hi Fellows
I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.
geomseq polygon xc yc x1 y2
0 17 21 22.5 0 0
3 17 21 22.5 40 40
2 17 21 22.5 0 20
4 17 21 22.5 20 0
1 17 21 22.5 0 10
5 17 21 22.5 10 10
1 18 40 40.5 0 20
4 18 40 40.5 20 30
0 18 40 40.5 0 0
3 18 40 40.5 10 20
2 18 40 40.5 5 15
5 18 40 40.5 30 35
6 18 40 40.5 40 40
9 18 40 40.5 80 80
7 18 40 40.5 45 45
8 18 40 40.5 50 60
I want something like this
geomseq polygon xc yc x1 y2
0 17 21 22.5 0 0
1 17 21 22.5 0 10
2 17 21 22.5 0 20
3 17 21 22.5 40 40
4 17 21 22.5 20 0
5 17 21 22.5 10 10
0 18 40 40.5 0 0
1 18 40 40.5 0 20
2 18 40 40.5 5 15
3 18 40 40.5 10 20
4 18 40 40.5 20 30
5 18 40 40.5 30 35
6 18 40 40.5 40 40
7 18 40 40.5 45 45
8 18 40 40.5 50 60
9 18 40 40.5 80 80
regards and thanks in advance
Edwin
View 3 Replies
ADVERTISEMENT
Feb 21, 2013
SQL Server 2008.
From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.
Part of the table:
logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413
[Code] ....
So as long the field LocationID is the same in the next row, it needs to be grouped.
I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp
By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.
View 12 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 2 Replies
View Related
May 31, 2015
I've;
Id.........|......type....|.....Value
2001................1...............20
2001................2...............32
2002................1...............19
2002................2...............21
2003................1............... 3
2003................2...............30
I want;
Id........|.......Value
2001.................12
2002..................2
2003.................27
View 7 Replies
View Related
Jul 31, 2014
I have a data set that looks something like like this:
Row# Data
1 A
2 B
3 B
4 A
5 B
6 B
7 A
8 A
9 A
I need wanting to assign a group ID to the data based on consecutive values. Here's what I need my data to look like:
Row# Data GroupID
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5
You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.
View 2 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Mar 17, 2015
I want to assign consecutive numbers to a block of data, where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
[Code]....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
View 4 Replies
View Related
Jul 20, 2005
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View 22 Replies
View Related
Mar 17, 2015
I want to assign consecutive numbers to a block of data where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000
1 2011-07-31 00:00:00.000
[Code] ....
My Attempt:
WITH cte AS
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER
(
PARTITION BYTestId
[Code] .....
Expected Output:
TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3
1 2011-07-30 00:00:00.000 3
[Code] ....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
View 7 Replies
View Related
Aug 30, 2013
I want to get the summation of the amount column against all the distinct values of Description fields. May be the "Where" (applied on Tdate Column) Clause unable to contains all the Unique(Distinct) values from the single Column (Description).
Table Structure :
CREATE TABLE [dbo].[TransLine](
[TransID] [int] NULL,
[No] [int] NULL,
[Description] [varchar](50) NULL,
[Code] ....
Sample data : [URL] ....
View 5 Replies
View Related
May 7, 2007
I have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!
Owner
Priority
Jack Daniels
Critical
High
Jim Beam
Medium
Low
Informational
View 3 Replies
View Related
Jan 29, 2008
I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3. 890/EG4. 012/AS5. Trainee6. Contractor 7. Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups.
View 4 Replies
View Related
Jan 22, 2006
What is the best way to build a SQL query to extract data from a table in this manner:
COLA COLB COLC DATE
01 01 01 04/15/1988
01 01 01 11/23/1997
01 01 01 02/02/2005
01 02 01 6/14/1990
01 02 01 10/30/1999
01 02 02 4/24/2001
How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?)
Thanks!!!
View 1 Replies
View Related
Nov 6, 2015
I have two tbles that have ItemName and their bill amount
a)Â tblLunch
which shows records like below
Invoice  Item                    Amount Â
--------------------------------------------
1 Â Â Â Â Â Â COFFEE Â Â Â Â Â Â Â Â Â Â Â 1000.00Â
2 Â Â Â Â Â Â TEAÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2000.00
3 Â Â Â Â Â Â ICE CREAM Â Â Â Â Â Â Â Â 1000.00
b)Â tblDinner
which shows records like below
Invoice  Item                Amount Â
------------------------------------------------------------
1 Â Â Â Â Â Â COFFEE Â Â Â Â Â Â Â Â Â 1000.00Â
2 Â Â Â Â Â Â TEA Â Â Â Â Â Â Â Â Â Â Â Â 2000.00
3 Â Â Â Â Â Â PASTA Â Â Â Â 1000.00
I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result
Item                Amount Â
------------------------------
COFFEE Â Â Â Â Â Â Â Â Â 2000.00Â
TEA Â Â Â Â Â Â Â Â Â Â Â Â 4000.00
ICE CREAM Â Â Â Â Â Â 1000.00
PASTA Â Â Â Â Â Â Â Â Â Â 1000.00
View 3 Replies
View Related
Oct 18, 2001
There's some SQL below (T-SQL) & I'm wanting to have this result set
grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate
values. I only want to get rows for one instance of the Venue_ID (per
BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks,
Darren
darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID,
Booking_Header.Booking_Header_Description,
Booking_Header.BStat_ID, Booking_Header.BT_ID,
Booking_Header.Tagged, Booking_Header.Status_Timestamp,
Booking_Header.Start_Date, Booking_Header.Days_Qty,
Proposal.PPL_ID, Proposal.PPL_Status,
Booking_Component.BCOM_ID,
Booking_Component.Component_Description,
Booking_Component.Venue_ID, Venue.Venue_Code,
Venue.Description, Address.Address_ID, Address.Town,
Booking_Status.BStat_Description,
Booking_Type.Type_Description
FROM dbo.Booking_Header INNER JOIN
dbo.Proposal ON
dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN
dbo.Booking_Component ON
dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER
JOIN
dbo.Venue ON
dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN
dbo.Address ON
dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN
dbo.Booking_Status ON
dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER
JOIN
dbo.Booking_Type ON
dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID
WHERE (dbo.Proposal.PPL_Status = 1) AND
(dbo.Booking_Header.BH_ID = 10)
Thanks,
Darren
View 2 Replies
View Related
Aug 4, 2014
I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
What would be the best way to accomplish this?
View 1 Replies
View Related
Mar 26, 2008
I want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
View 1 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related
Sep 16, 2004
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
View 2 Replies
View Related
Jul 6, 2004
Hi all,
Hope u could help me
I have a table as follows
SupID | Week | ..| ........
-------------------------
234 1/2/03
235 1/2/03
236 2/2/03
237 2/2/03
238 2/2/03
239 3/2/03
and
I need to display theses data like
1/2/03(week)
-------------
234
235
2/2/03(week)
-------------
236
237
238
3/3/03(week)
-------------
239
If i go for Group by clause (SQL) then I can group them by week but cant display the individual rows.
Is there any way to do this (better if in a single query)
Thnx
View 3 Replies
View Related
Mar 11, 2004
some one plz help me.
I had a table with these columns.
Table(Id int,Name varchar,Value Varchar).
I have to group them by ID and each Name becomes column name of the new table
ex:-
Id Name Value
--------------------
1 x a1
2 x a2
3 x a3
1 y b1
2 y b2
3 y b3
1 z c1
2 z c2
3 z c3
I need it in this way
x y z
------------
a1 b1 c1
a2 b2 c2
a3 b3 c3
(no of columns in the new table can't be pre determined)
and which one would be better option to do this
in VB.Net code or in a Storedprocedure?
View 14 Replies
View Related
May 21, 2014
I am joining multiple tables and my result is following:
UserGroup UserName
UG1 Tom
UG1 Harry
UG2 Albert
UG3 Jim
UG3 Sam
I want xml in following format:
<UserGroupsInfo>
<UserGroups>
<UserGroup name="UG1">
<User>
Tom
[Code] ....
I have tried all combinations of for xml path but no success yet.
View 7 Replies
View Related
Mar 26, 2008
I have a set of data that contains duplicates and I am running a group by query to get a unique set of rows.
The group by works upto a certain state, but I need to be able to tell it to take the first appearance of an address field as you can in Access?
current query is:
select ID,Address1,max(value)
from test
group by id,address1
I have tried using First(Address1) but it doesn't recognise that as a function?
View 3 Replies
View Related
Feb 9, 2007
Hi Exports
I have a simple question, but I can't seem to get around it. Please help.
I have a table like this: [select * from Enrollment]
Course ID PersonID Role
BCC110 123 Student
BCC110 321 Student
BCC110 456 teacher
BCC123 457 Student
and I want to have a report like
Course ID Total Students Total Teachers
BCC110 2 1
BCC123 1 0
How do I achieve this?
Sam
View 8 Replies
View Related
May 8, 2007
hi,
I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".
I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.
Now i want something like this.
Group my expenditure in three categories like
1. My expenditure on monday
2. My expenditure on tuesday
3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)
what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.
how can i do this?
Thanks
rohit
View 2 Replies
View Related
Feb 8, 2008
Hi Everyone,
I am trying to develop a report query that will dynamically group the data by day,week, quarter, month, or year. The user will select the interval from the site and pass it to the query. The query will convert the date field using a function I created. Is this the most efficient way to do this?
QUERY
------------------
SELECT dbo.FormatDate(DateCreated,3) As DateCreated, Count(*) As NumOfCalls
FROM Agent.LeadTracker
WHERE DateCreated BETWEEN '1/1/2008' AND '2/7/2008'
GROUP BY dbo.FormatDate(DateCreated,3)
RESULT
------------------
DateCreated NumOfCalls
2008-02-01 5442
2008-01-01 14150
FUNCTION------------------
CREATE FUNCTION [dbo].[FormatDate]
(
@Date datetime,
@Time int
)
RETURNS datetime
AS
BEGIN
DECLARE @Res datetime
IF @Time = 1 --Day
SELECT @Res = DATEADD(day,DATEDIFF(day,0,@Date),0)
ELSE IF @Time = 2 --Week
SELECT @Res = DATEADD(week,DATEDIFF(week,0,@Date),0)
ELSE IF @Time = 3 --Month
SELECT @Res = DATEADD(month,DATEDIFF(month,0,@Date),0)
ELSE IF @Time = 4 --Quarter
SELECT @Res = DATEADD(quarter,DATEDIFF(quarter,0,@Date),0)
ELSE IF @Time = 5 --Year
SELECT @Res = DATEADD(year,DATEDIFF(year,0,@Date),0)
RETURN @Res
END
View 4 Replies
View Related
Nov 26, 2007
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
View 1 Replies
View Related
Feb 13, 2012
I am trying to create a group on this data example
RecordNo, Item, Date,NULL
1, ABBB, NULL, 0 << first group
2, ABBB, 01-06-2011,NULL << first group
3, ABBB,NULL,NULL << 2nd group
4, ABBB, 02-01-2011,NULL << 2nd group
5,CAAA,NULL,NULL << 1st group
6,CAAA,NULL,NULL << 1st group
7,CAAA,01-01-2010,NULL << 1st group
8,CAAA,01-01-2011,NULL << 2nd group
9,CAAA,01-05-2012, NULL << 3rd group
Basically 3 groups on date, by item, so for each date change/ per part the group increments been playing with row_number partitions, but cant seem to get what i need.
View 14 Replies
View Related
Jan 16, 2007
I€™m trying to build a report that contains bank account activity. For 3 customers their activity is this:
Deposits
Date Amount
John 1/2/2007 500.00
1/7/2007 250.00
Mary 1/3/2007 100.00
Withdrawals
Date Amount
John 1/3/2007 100.00
Mary 1/2/2007 100.00
1/4/2007 200.00
1/6/2007 50.00
Sam 1/6/2007 50.00
I would like the report to have deposit/withdrawal info, with subtotals, grouped by customer, looking something like this:
Deposit Withdrawal
Cust Date Amount Date Amount
John 1/2/2007 500.00 1/3/2007 100.00
1/7/2007 250.00
750.00 100.00
Mary 1/3/2007 100.00 1/2/2007 100.00
1/4/2007 200.00
1/6/2007 50.00
100.00 350.00
Sam 1/6/2007 50.00
0 50.00
Because the deposit and withdrawal data is only related by customer, I€™ve broken it into 2 datasets.
I€™m totally stumped! Can someone get me going in the right direction on this?
Thanks
View 1 Replies
View Related
Sep 25, 2006
Hello all
I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.
e.g
debit £100 - credit £100 - debit of £125 ( I only want to see the debit of £125)
I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.
How can I achieve this in SQL?
All help appreciated.
View 4 Replies
View Related
Oct 7, 2015
I am using sql table named as product which is having columns partno,partnm,weight,surfacearea,totalhr,type
I want sum of weight,surfacearea,totalhr and grouping on partno,partnm,type
If I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr) from product GROUP BY partno,partnm then its working correctly with sum and grouping but if I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr),type from product  GROUP BY partno,partnm,type then it is not grouping as expected.
why if third column included in group by clause its not working correctly...Is there any way to group as I want.
View 9 Replies
View Related
Jul 30, 2007
Hello,
I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.
So my goal results are:
User Count
Dan 2
Dave 1
Table XYZ
ID User Item Value Date
1 Dan 1 20 5/5/2007
2 Dan 1 30 6/5/2007
3 Dave 2 25 6/1/2007
4 Dan 2 22 5/1/2007
5 Dan 3 23 5/1/2006
View 6 Replies
View Related
Aug 28, 2007
I have what at first site should be a simple reporting services issue but cannot resolve:-
I have a complex report comprising over 90 pages of various sections but when analysed in detail , 80% of it follows a simple pattern i.e. it comprises around 100 instances of the same sub-report bound to the same data source BUT grouped and filtered on different groupings and filter values.
The pattern is as follows:-
Each sub-report instance is bound to an SSAS cube which has fields L1, L2...L7
Each sub-report instance groups the data dynamically by one or more groups G1,G2..G7, the actual fields to be used being defined by the parent report (i.e. one instance may group by fields L7, L3, L4, another by L2,L3 etc)
Each sub-report instance has up to 7 filters defined (F1..F7) may filter the data i.e. the parent may pass a filter stipulating that L7="A", L3="B". To do this, the parent sets filter parameters F7="A" and F3="B"
The approach I have adopted is to set up parameters in the sub-report for dynamically grouping the data i.e. the
sub-report has 7 parameters defined G1..G7 and the parent report populates G1..G7 with the grouping breakdown required for any given instance. This works fine!!!!
However what I am trying to do is prevent each sub-report performing major round trips to the underlying cube for each sub-report instance by specifying the filter as part of the MDX query by defining the 7 filter parameters as query parameters. (I could of course not filter the query and place the filter in the report but this would almost certainly lead to performance issues). Instead what I am struggling to do is to create the MDX query in such a way that when a filter is not supplied by the parent, the MDX query does not filter the data.
Can anyone advise on this. I guess my question is more of an MDX question than a reporting services one.
View 1 Replies
View Related
