Need Help With A Simple Query - Grouping Data
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
ADVERTISEMENT
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
Jun 5, 2008
I'm sure this is very basic, I'll try not to be too embarrassed asking it.
Given a table called MainTable, and a second table called Details, with a one-to-many relationship between them, which is the better practice in regards to getting a number of values from each MainTable record plus an aggregate of a value from the related Details records...?
--=====================================
--Method 1
--Group by every selected field in MainTable
SELECT MainTable.Rec_Num,
MainTable.Name,
MainTable.Description,
SUM(Details.Amount) AS Amount
FROM MainTable
INNER JOIN Details
ON MainTable.Rec_Num = Details.MainTable_Rec_Num
GROUP BY MainTable.Rec_Num, MainTable.Name, MainTable.Description
--Method 2
--Group by PK record in MainTable and aggregate the others
SELECT MainTable.Rec_Num,
MAX(MainTable.Name) AS [Name],
MAX(MainTable.Description) AS [Description],
SUM(Details.Amount) AS [Amount]
FROM MainTable
INNER JOIN Details
ON MainTable.Rec_Num = Details.MainTable_Rec_Num
GROUP BY MainTable.Rec_Num
--=====================================
Or perhaps there's another option I'm not aware of?
Thanks!
Ron Moses
ConEst Software Systems
View 4 Replies
View Related
May 13, 2008
In SRS2000, I have a simple grouping matrix report setup like the following:
Code Snippet
=Fields!Date.Value Total
=Fields!Section.Value =Fields!Count.Value
Total
It's essentially a table of departmental statistics that would report like this:
Code Snippet
5/1/08 5/2/08 5/3/08 5/4/08 Total
Dept1 0 99 33 4 XX
Dept2 1 0 2 1 XX
Total XX XX XX XX XX
The report has 2 groups - 1 for the dates and 1 for the departments.
The report data itself displays correctly. My problem comes in when I try to add the Subtotals.
I simply right-click on each of the two Groups and chose "Subtotal". This action inserts the Total column & row respectively and you would think everything is just fine.
Unfortunately when I actually run the report, I see that the Totals it's coming up with are incorrect. In fact, they are the totals of the FIRST row/column only.
Meaning - (using the previous example) - I'd see this:
Code Snippet
5/1/08 5/2/08 5/3/08 5/4/08 Total
Dept1 0 99 33 4 0
Dept2 1 0 2 1 1
Total 0 99 33 4 0
It's like it's not taking anything into account except the first instance.
And it's not like you can actually *see* the expression or formula being used for the Total function. The data cells are just grayed.. all you can really change is the "Total" text.
I must be doing something really stupid here, but for the life of me I don't see what it is.
Any ideas?
Thanks!
View 1 Replies
View Related
Oct 6, 2015
I have query on grouping data on weekly basis..
1. Week should start from Monday to Sunday
2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).
3. I want output like below
Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12
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
Oct 22, 2007
Hi,
I have this query...
cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1)
It produces the following output (trunctated by me for this post example).
name
category_id
CNameID
link_id
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
7
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
22
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
24
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
40
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
45
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
89
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
134
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
137
Architecture
5
Architecture5
37
Architecture
5
Architecture5
90
I would like it to display instead (where 8 and 2 are the counts):
Accounting/Bookkeeping 8
Architecture 2
Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp. The data is in SQL Server 2000. I was hoping to do it in one SqlCommand statement if possible. I guess I can drop it into a view and then run my group by query on the view if I had to.
I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far.
Thanks in advance,
Stewart
View 6 Replies
View Related
Oct 29, 2007
Hi folks. Hopefully this is a simple question. What's the easiest and most efficient way to group by a dateTime field in an SQL query? Here is exactly what I'm trying to do. I have a database table that contains transactions from an email maillog, so there are dateTime entries every second or so. I'm trying to build a query that will group a count of messages per hour for a given day. How can I make an hourly grouping of the total number of messages?SELECT count(*)
FROM emailTable
WHERE (delivDate >= '2007-10-03 00:00' AND delivDate < '2007-10-03' 01:00)
Thanks, Chris
View 6 Replies
View Related
Jan 3, 2007
is grouping by sub query possible?
ie.
Code:
select
(select fieldx from tabley where pk = tz.fk) as field1,
field2
from
tablez tz
group by
field1
this doesn't work..i get an error that field1 is not valid...so is there a way to do this that does work?
please realize that the above example is exactly that..and had i needed to do something that easy, join would be the easy choice..what i'm trying to do requires a sub query
View 14 Replies
View Related
Jan 15, 2004
I have the following information in a table
ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-170002150000
27547920031202A-27412150000
27547920031202A-259215000-42501729
I need to create a query that totals TRANQTY and arrives at a result as in the following record.
ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-20000215000-42501729
and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..
SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
(SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
FROM CLIENTSHAREDEALS c1
WHERE (c1.ACCNO = '275479')
GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
WHERE (c2.ACCNO = '275479')
ORDER BY c1.RUNDATE
Thanks
View 2 Replies
View Related
Dec 5, 2005
I have a table with the following structure:main_category| category| sub_category| answer|dateBasically, the data will be along these lines:Neuro | LOC | Status | answer1|dateNeuro | LOC | Status | answer2|dateNeuro | LOC | Status | answer3|dateSenso| Visi | Clarity | answer1|dateSenso| Visi | Clarity | answer2|dateetc...I am trying to query the db and present the user with the data in thefollowing structure:Main CategoryCategorySub Categoryanswer1answer2answer3...Main CategoryCategoryEtc...There are literally 3 dozen main categories, categories, andsub-categories each with distinct answers.I could really use some help on a query to group the data in this way!Thanks in advance!!!Frank
View 4 Replies
View Related
Feb 26, 2008
HI all, I got a tsql that needs to be simplified.
Select * from Table1 where condition1 and id not in
( Select id from table1 where condition2 and id in
( Select id from Table1 where condition1 )
)
basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
View 9 Replies
View Related
Jun 13, 2004
I know this has been posted before, but I can't find the previous threads so please bear with me....
I want to grab the very 1st record of each product in a table like this
ID CLIENTID PRODID
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3
so that I'd get a record set like:
ID CLIENTID PRODID
1 a 1
4 a 2
7 a 3
Thanks for the hellp guru's
View 4 Replies
View Related
Jan 5, 2005
Hello, everyone:
I have a table like:
Col1Col2
1A
2B
1D
1P
2F
2W
How to query this table to return by Col1 like
Col1Col2
1A,D,P
2B,F,W
Thanks a lot
ZYT
View 11 Replies
View Related
Aug 22, 2014
I have below data in one of my table:
Calls|Status
4|-6
12|-11
1|0
1|-10
Desired Output required :
Total_calls|Zero|Non-Zero
18|1|17
View 3 Replies
View Related
Nov 8, 2005
Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
View 2 Replies
View Related
Jul 20, 2005
I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24
View 7 Replies
View Related
Apr 13, 2006
Hi,
How can I make a query and group the registries in a interval of 30 seconds...like
for each line I have a datetime field that have all the day, and I need it to return just like
TIME Contador_type1 Contador_type2 Total
01-01-2006 00:00:30.000 2 5 7
01-01-2006 00:01:00.000 3 7 10
It's just an example...but that's the result that I need and my table is
data_hora -- datetime field
tipo - 1 or 2 -- count
nrtelefone - that's is the number dialed.
Thanks
View 16 Replies
View Related
Dec 12, 2007
Hi,
Below is my DB Table..
Owner varchar(500)
contains...
OwnerA
OwnerB
Book varchar(500)
contains values...
Book1
Book2
Book3
Book1 might be owned by OwnerA
and Book2 might be owned by OwnerA and OwnerB
So in the Table is this...
ID Book Owner
1 Book1 OwnerA
2 Book2 OwnerA
3 Book2 OwnerB
How would I output this relationship in sql?
Thanks.
View 1 Replies
View Related
Jul 29, 2015
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as belowÂ
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
View 3 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
Mar 13, 2012
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
SAMPLE:
CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743
DESIRED RESULTS:
CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711
View 7 Replies
View Related
Mar 5, 2015
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
[Code] ....
View 2 Replies
View Related
May 22, 2008
I have a query that gets a supplier, a month, a year, status and sum of recpits.
returning the following. but my problem is I also need a col of totals. i tried to put a sub grouped query
in the select statement but keep getting an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
AR162600 ARROW ELECTRONICS 10424 Early 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 516 Late 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 279603 On-Time 1 2008 2007-12-31 23:59:59.997
my qurey is below. how can I get another col called total it will be the same value on each row.
AR162600 ARROW ELECTRONICS 10424 290543 Early 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 516 290543 Late 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 279603 290543 On-Time 1 2008 2007-12-31 23:59:59.997
SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty,
ot_rec_stat, datepart(Month,ot_rec_dt) as mth,
datepart(year,ot_rec_dt) as ryear,
DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date,
(SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, 'Totals' as ot_rec_stat,
datepart(Month,ot_rec_dt) as mth,
datepart(year,ot_rec_dt) as ryear,
DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007
group by ot_ven_num, ot_ven_name, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)),
datepart(Month,ot_rec_dt),
datepart(year,ot_rec_dt)) as total
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007
group by ot_ven_num, ot_ven_name, ot_rec_stat, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)),
datepart(Month,ot_rec_dt),
datepart(year,ot_rec_dt)
View 9 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
