I have a query that looks like this:
----------------------------------------------------------------------------
SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE
FROM TABLEDATA.VW_DATA
INNER JOIN
TABLEDATA.MODELS ON
TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID
INNER JOIN
TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND
TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID
INNER JOIN
TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID
WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL,
TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE
HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete')
AND (TABLEDATA.VW_DATA.TEAM_ID = 27)
AND (TABLEDATA.MODELS.MODEL <> 'Model1')
ORDER BY TABLEDATA.VW_DATA.COMPLETIONDATE DESC
----------------------------------------------------------------------------
What I need is one row per site, and I can get that when I exclude the completiondate field, but I need to sort on that field. How can I use the GROUP BY to return only one row with the team_id, avgduration, site, model, duration, and teamtype. I do not need to display the completiondate, I just need it for sorting. The avgduration column is supposed to be an average of the duration of each of the records.
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, ...
I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)? The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned. SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect, dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect, CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration, SUM(duration)
FROM Calls
GROUP BY callingPartyNumber
I cannot normalize the tables (implies that I have to work on one and only one table).Anyone...
I have the following sql statement below;DECLARE @val intSET @val = 1WHILE (@val <= 7 )BEGIN SELECT TOP(1) id, queueFROM itn_articles WHERE asection = @valORDER BY queue DESCSET @val = @val + 1END Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable
Hi Guys,I have the following select sql (sql server) select statement SELECT dbo.AlternativesAssessment.AlternativeNumber, dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.MembershipValue, dbo.AlternativesAssessment.CriteriaID, dbo.CriteriaDefinition.CriteriaName, dbo.CriteriaDefinition.DecisionSessionID
FROM dbo.AlternativesAssessment INNER JOIN dbo.CriteriaDefinition ON dbo.AlternativesAssessment.CriteriaID = dbo.CriteriaDefinition.CriteriaID
WHERE (dbo.CriteriaDefinition.SessionID = @SessionID)
ORDER BY dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.AlternativeNumber and it produces a result like this:In this case there are 4 alternatives all evaluated through 3 criteria "c1_Cost", "c2_Durability" and "c3_Reputation".My objective is to sum up all the MembershipValue's for each alternative.it shoud look like this:AlternativeNumber|CriteriaName|MembershipValue| 1 Cost 2314 1 Durability 214 1 Reputation 981 2 Cost 814 2 Durability 914 2 Reputation 381.etcDo you guys have an idea how I could achieve that?I played around with SUM's and GROUP By's for hours now but it does not seem to work.Many thanks,polynaux
I have a vendor table with vendor codes of six characters. The same vendor could have two codes (I'm new to this accounting system so I don't know why this is the case). If a vendor has two codes, the first character for one vendor code would be an "H", then the first character for the other code would be a "P", with the other 5 characters being the same (i.e. HCAMER and PCAMER).
We want to print only one 1099 for the vendor. So, if the vendor has two codes, I need to get a total for that vendor. Can this be done in a query?
I have a table in which column are id, date, lname, fname, add In this table there are duplicate records but have different date for each duplicate record. I am trying to get just max(date) for each record.
when i try like these it works. select id, max(date) from table group by id
but when i try something like these it does not work select id, fname, lname, max(date) from table group by id, fname, lname
I get all the record. I am writing these query from perl script. any help.
Please help in writing a SQL query. I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.
Now I want get sum of both for a particular manager on a daily basis. Like
SaleDate sum(Directsales) sum(TeammemSales)
if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.
This is a fairly compliacted query and I just can't seem to figure it out. I will try to psuedocode it to see if anyone can figure out how to do it with sql.
Table Descriptions: I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house. Houses: HouseID, HouseDescription Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),
What I need: I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").
A query would be greatly appreciated.
Thanks,
DB
If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.
Can you group by a value returned by a UDF? Example:SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult'GROUP BY Col1, MyResultI've had no luck. Can this be done?
I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.
Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?
This is what the DMX query looks like...
SELECT t.[AgeGroupName], t.[ChildrenStatusName], t.[EducationName], Sum(t.[Profit]) as Profit From [Revenue Estimate DT] PREDICTION JOIN OPENQUERY([DM Reports DM], 'SELECT [AgeGroupName], [ChildrenStatusName], [EducationName], [Profit], [IncomeName], [HomeOwnerName], [SexName], [Country], [ProductTypeCode], [ProductName], [MailCount], [OrderAmount], [SalesAmount], [MailCost] FROM (SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode, CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount) AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey FROM [DataMining.RevenueEstimate.Predict] GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode, CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID HAVING (ClientID = 1)) as [Prediction] ') AS t ON [Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND [Revenue Estimate DT].[Education Name] = t.[EducationName] AND [Revenue Estimate DT].[Income Name] = t.[IncomeName] AND [Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND [Revenue Estimate DT].[Sex Name] = t.[SexName] AND [Revenue Estimate DT].[Country] = t.[Country] AND [Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND [Revenue Estimate DT].[Product Name] = t.[ProductName] AND [Revenue Estimate DT].[Mail Count] = t.[MailCount] AND [Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND [Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND [Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND [Revenue Estimate DT].[Profit] = t.[Profit] AND [Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName] GROUP BY t.[AgeGroupName], t.[ChildrenStatusName], t.[EducationName]
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
Hi, I am not a newbie to general SQL queries but i do get confused with the complicated ones. I have a table of applicants. I want to be able to count applications over the course of a month but group them by week. does that make sense? So essentially i would have 4 records for the query: week 1's total, week 2's total. etc.. Is this possible in one query? or do I need to create a query for each week with the date range i need?
the ids are something like this mod01-01 mod02-01 mod03-01 mod01-02 mod02-02 mod03-02 mod03-03 mod03-04 mod04-01 ....
now, i am trying to group by and count all of the ids that end with '01',
i was thinking of something like select count(id) group by id where id= ???
and the second query i need to make is one that counts not one but all the ids that end with the same last 2 digits but in one single query, how can i do that? as a possible hint, is that the only thing i KNOW that the last 2 digits can only be 01,02,03,04 and 05 but still im a bit stuck in here the result should look something like this
id total 01 4 02 3 03 1 04 1
or what suggestions can you give me for this query? even if you just help me with the first query, that would be great, thanks!! thanks!!!
ok, so i have this table "SOLD_PRODUCTS" with this columns: idProduct (int), quantitySold (int)
so i want to know how many products i have sold.
i have this query
Code:
SELECT idProduct, COUNT(idProduct) AS Total FROM SOLD_PRODUCTS GROUP BY idProduct
and get this 74 5 75 2 79 1 etc etc etc
this works correctly, but it doesnt counts the quantity sold, so i changed the query to
Code:
SELECT idProduct, quantitySold, COUNT(idProduct) AS Total FROM SOLD_PRODUCTS GROUP BY idProduct
but it crashes, mssql says i must add quantitySold to the group by clause.
but if i put the query
Code:
SELECT idProduct, quantitySold, COUNT(idProduct) AS Total FROM SOLD_PRODUCTS GROUP BY idProduct,quantitySold
i dont get the expected data, the idProducts appear more than once, they are no longer grouped by, the results in the count(idProduct) are wrong (i am not sure what they are counting now)
so now i dont understand how i can make my query, all i want is a query where in one collumn i have the product id, and in the other the quantity sold (that would be the count of all the product ids found multiplied by the quantity sold)
so now i am completelly lost, now i dont know what to do with my query.
I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.
Hi, I have two tables in my DB: tbl_Users: callSign(Char), FirstName(Char), LastName(Char) tbl_Events: CallSign(Char), TotalKM(Char), EventDate(SmallDateTime)... Plenty of others, but they're not relevnt.
The result that I want to see is:
CALL SIGN Last Name First Name Date Number Of Events TotalKM 111 MR. X 01/01/2008 3 40
I know this: The number of events is countable. if you do the following convert "Convert(int, TotalKM) you can sum up the Total KM.
Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ? I'm using sql Server 2000 Thank's
This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ
Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai
I am wondering how can I group the returned results from the sql query. For example, the following sql qurey retrieved data from a few tables from the database on conditions using "inner join". The returned results are not grouped or organized into groups. I would like to group the retrieved data based on three different tables such as "Overall", "Spectography" and "Other" using the CASE statement.
psql += "select s.szID as SampleID, rtp.nSequence as Sequence, rt.szName as TestType, ";
psql += "rtp.szName as TestName, p.szID as PointTest, p.szDescription as PointDescription, ";
psql += "r.nAlarmStatus as AlarmStatus, s.dtCollected as DateCollected, ";
psql += "rd.nDataType as DataType, rd.byData as EncodedData ";
Hi, I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view. Problem: I can't associate a field with a column that i am returning with my record set. Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname) that is associate with that loan agent and that loan state. Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header. if the query works, my Gridview should look like this ('Name' refers to the borrower name) UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met Bob | | | | Name | | | | Bob | | Name | | | | | | Bob | | | | | | | Name | Steve | | | Name | | | | | Steve | | | | | | Name | |
Here is my sql call: SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
I am developing Staff Allocation System, database is sql server 2000. I have problem in retrieve the staff informations,
employee working which Project and what project have assign to him, what is his assign project or contract no,
One employee working more then one project, retrieve information one employee how many projects are working,
What is his approved position, what is his assign position. It the main data have to retrieve, as well as retrieve all fields which related to those tables.
I use this query.
select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'
above query retrieve no data,
how can use group by clause in the above query ?
group by apprecords.appcontract
group by clause give error.
above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.
Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on… Group by apprecords.appposition ……….
Employee Table (basic data entry employee table) --------------------------------------------------------- rec_id EmpNo Name Position Department EmployeeType
Approved Records Table (in this table all information about the employee and his approved position and contract ) ------------------------------------------------------------------------ rec_id Name Approved Date MunicipalityRefNo DCILRefNo ApprovedPosition ApprovedContract
Assign Project Table (in this table all information about the employee his assign the project) -------------------------------------------------------------------- rec_id Name AssignPosition AssignContract EmpProjectStartDate EmpProjectEndDate ShiftNo ProjectStatus
I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID FROM (SELECT b.Deal_ID FROM btransactions b WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN orders o ON o .Rep_ID = s.ID INNER JOIN b ON o.ID = b.Deal_ID GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID HAVING (SUM(b.orderamount) > 0)???.NetSports
Hi,I guess my brain stopped but I need help with this. create table #RETURN( uid int, cid int, serviceid int ) insert into #RETURN select 44,75,2 insert into #RETURN select 44,76,1 insert into #RETURN select 44,77,3 insert into #RETURN select 45,78,3I need to query this table that will get me a distinct UID with matching CID ordered by serviceid from lowest to highest.The solution should look likeuid cid44 7645 78Thanks
