GROUP BY Not Working?
Mar 8, 2004
I'm having a really odd problem.. this is basic SQL, so it should be easy for most of you. For some strange reason, the quantity is not being summed up. Any idea why this would be happening? Everything else is grouped. The SQL is below, and some of the results are below that (you can see that it SHOULD be grouping them properly).
SELECT TOP 100 PERCENT dbo.cp_elements.campaign_id, dbo.cp_campaigns.name AS campaign_name, dbo.cp_elements.item_no, dbo.cp_elements.name,
dbo.cp_orderables.est_qty, dbo.cp_orderables.qty_increment, SUM(dbo.cp_order_detail.qty) as qty, dbo.cp_attribs.price
FROM dbo.cp_orderables INNER JOIN
dbo.cp_elements ON dbo.cp_orderables.element_id = dbo.cp_elements.element_id INNER JOIN
dbo.cp_attribs ON dbo.cp_orderables.orderable_id = dbo.cp_attribs.orderable_id INNER JOIN
dbo.cp_order_detail ON dbo.cp_attribs.attrib_id = dbo.cp_order_detail.attrib_id INNER JOIN
dbo.cp_selected_shiptos ON dbo.cp_order_detail.shipto_id = dbo.cp_selected_shiptos.shipto_id INNER JOIN
dbo.cp_campaigns ON dbo.cp_elements.campaign_id = dbo.cp_campaigns.campaign_id
GROUP BY dbo.cp_elements.campaign_id, dbo.cp_campaigns.name, dbo.cp_elements.item_no, dbo.cp_elements.name, dbo.cp_selected_shiptos.state,
dbo.cp_orderables.qty_increment, dbo.cp_attribs.price, dbo.cp_orderables.est_qty
ORDER BY dbo.cp_elements.item_no
RESULTS:
16,Project Z,10S,Ten Spot,10,1,0,9.9900
16,Project Z,10S,Ten Spot,10,1,15,9.9900
16,Project Z,10S,Ten Spot,10,1,0,9.9900
That 2nd from last column is the qty_increment.. it's spitting out 3 rows when it should just show one, with 15 as the qty_increment.
Thanks for any help/clues!
View 2 Replies
ADVERTISEMENT
Apr 17, 2008
This query gives me Customer Sales data. SQL Server 2005. This is grouped by Region and Location. A customer such as 'ABC' should only show up once per Location, but it is pulling in some companies more than once. Can anyone help me figure out how to group this so this gets fixed?
SELECTRegion,
Location,
WarehouseCode,
CASE
WHEN CustRank <= 19 THEN CustomerNumber
ELSE 'AllOthers'
END AS CustomerNumber,
CustomerName,
MonthLessEleven,
MonthLessTen,
MonthLessNine,
MonthLessEight,
MonthLessSeven,
MonthLessSix,
MonthLessFive,
MonthLessFour,
MonthLessThree,
MonthLessTwo,
MonthLessOne,
CurrentMonth,
CurrentYearTotal,
LastYearYTD,
LastYearTotal,
CASE
WHEN CustRank <= 19 THEN CustRank
ELSE 20
END AS CustRank
FROM(
SELECTgla.Region,
gla.Location,
ihh.WarehouseCode,
ihh.CustomerNumber,
cm.CustomerName,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD(YEAR, - 1, GETDATE()) > ihh.SOTransDate AND DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,
ROW_NUMBER() OVER (PARTITION BY gla.Region ORDER BY gla.Region, SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) DESC) AS CustRank
FROMMAS_BIF_AR1_CustomerMaster AS cm
INNER JOINMAS_BIF_ARN_InvHistoryHeader AS ihh ON ihh.CustomerNumber = cm.CustomerNumber
INNER JOINMAS_BIF_ARO_InvHistoryDetail AS ihd ON ihd.InvoiceNumber = ihh.InvoiceNumber
INNER JOIN
(
SELECTAccountKey,
Account,
CASE SUBSTRING(Account, 5, 3)
WHEN '936' THEN 'North Region'
WHEN '908' THEN 'East Region'
ELSE 'Unknown'
END AS Region,
CASE SUBSTRING(Account, 5, 3)
WHEN '900' THEN 'ALE'
WHEN '902' THEN 'ATO'
WHEN '904' THEN 'BOW'
WHEN '906' THEN 'BRY'
WHEN '908' THEN 'BPT'
WHEN '910' THEN 'BYD'
WHEN '912' THEN 'BUF'
WHEN '914' THEN 'CLE'
WHEN '916' THEN 'GRN'
WHEN '920' THEN 'DXN'
WHEN '924' THEN 'CTH'
WHEN '926' THEN 'ELC'
WHEN '928' THEN 'FTL'
WHEN '930' THEN 'FTW'
WHEN '932' THEN 'I35'
WHEN '936' THEN 'GAI'
WHEN '000' THEN 'GAI'
WHEN '939' THEN 'STW'
WHEN '940' THEN 'GRE'
WHEN '942' THEN 'HEN'
WHEN '944' THEN 'FTS'
WHEN '948' THEN 'JAC'
WHEN '952' THEN 'JEN'
WHEN '956' THEN 'KIL'
WHEN '957' THEN 'MCA'
WHEN '958' THEN 'MIN'
WHEN '960' THEN 'NOC'
WHEN '962' THEN 'ODE'
WHEN '964' THEN 'BTP'
WHEN '966' THEN 'RA'
WHEN '968' THEN 'RIF'
WHEN '970' THEN 'SWD'
WHEN '971' THEN '3PS'
WHEN '972' THEN 'ROC'
WHEN '976' THEN 'SJO'
WHEN '978' THEN 'SMB'
WHEN '980' THEN 'STO'
WHEN '982' THEN 'TOL'
WHEN '984' THEN 'VEL'
WHEN '985' THEN 'CFP'
WHEN '986' THEN 'CLM'
WHEN '988' THEN 'WHI'
WHEN '992' THEN 'WRA'
WHEN '995' THEN 'ADM'
ELSE 'Unknown'
END AS Location
FROMMAS_BIF_GL_Account
) AS gla ON gla.AccountKey = ihd.SOGLSalesAcct
GROUP BYSUBSTRING(Account, 5, 3),gla.Region, gla.Location, ihh.CustomerNumber,
cm.CustomerName,
gla.Account,
ihh.WarehouseCode
) AS x
View 2 Replies
View Related
Oct 21, 2015
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
[Code] ...
The return data looks like this:
3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0
[Code] ....
I want it to look like this:
3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0
View 8 Replies
View Related
May 28, 2008
Hi,
I have the following query which is working fine, but its returning a row for each day.
I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?
Any help is much appreciated !!
thanks once again,
mike123
CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]
(
@numDays int
)
AS SET NOCOUNT ON
SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique
FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC
table structure below
CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
View 1 Replies
View Related
Apr 21, 2014
I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different failover scenarios to see that everything is working properly. Everything works fine until I try testing the failure of the SQL Server service. When I stop the SQL Service on the primary server, it fails over to the secondary server as expected. I then start the service on the (now) secondary server and it comes back online as the secondary server. I then try to test that the service will fail back over when I stop the service on the new primary server.
However, when I stop the service, the secondary server now shows "resolving" and never comes back online. When I bring the service back up on the primary server, the secondary now shows as secondary instead of resolving. So to see if it's something about failing over from one server to another, I do a manual failover making the original primary server the primary again and everything is as it was originally.
I then stop the service on the primary server, but the secondary server now says resolving and the AG will not become available again until I start the service on the primary server.
It seems that when I first configured the quorum it worked fine the first failover scenario, then stopped working. I then added the file sharing witness, and failover worked the first time again, but not after that. For some reason after the initial failover it won't automatically failover again after that.
Config:
Servers: Windows Server 2012 Standard
SQL : SQL Server 2012 Enterprise SP1
View 5 Replies
View Related
Mar 18, 2008
We are having a problem with cross database ownership chaining. Below is a description of the problem:
I have a domain group named DOM1GROUP1
I have a domain user DOM2USER1 who is a member of DOM1GROUP1 (note that they are in different domains)
I have a database DB1 which contains a stored procedure (st_insertdata) that does an insert in a table (tb_data) on DB2
DOM1GROUP1 has been granted login rights on the SQL Server
DOM1GROUP1 is a user in both DB1 and DB2
DOM1GROUP1 has execute rights on procedure st_insertdata and insert rights on table tb_data.
All objects are owned by the dbo schema.
The database owner for DB1 and DB2 is sa
When DOM1USER1 executes st_insertdata an error is returned:
The server principal "DOM1USER1" is not able to access the database "DB2" under the current security context.
I've played around with the options "trustworthy" and "db chaining" but these do not make any difference. The only thing that fixes this problem is if I create a login for DOM2USER1 and grant it access to DB2 (with no other rights other than membership of the public role).
It seems that SQL Server does not recognize that DOM2USER1 is a user in DB2 by virtue of its membership of the domain group DOM1GROUP1. Is there a way to get this to work without granting explicit rights to DOM2USER1?
View 4 Replies
View Related
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
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Nov 27, 2007
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
View 1 Replies
View Related
Nov 6, 2015
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
View 4 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Sep 25, 2003
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
View 3 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies
View Related
Jan 5, 2008
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
View 1 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Feb 6, 2008
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
View 1 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
May 28, 2007
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
View 4 Replies
View Related
Jun 6, 2007
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
View 2 Replies
View Related
Feb 12, 2015
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
View 1 Replies
View Related
Jul 23, 2005
Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks
View 6 Replies
View Related
Sep 6, 2007
Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000
For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000
2 a 100 2000 0
Team Total 100 0
Total 3400 3000
BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)
I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount
And the finial total it turns out 2400 (3400 - 1000)
I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0
2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)
this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ??
thank you
View 9 Replies
View Related
Nov 20, 2015
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
View 17 Replies
View Related
May 19, 2015
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
View 9 Replies
View Related
Apr 9, 2008
How do i do a sum(IIF in a group footer. I just want to display one year total.
=SUM(IIF(Fields!Column_Text.Value="2007", Fields!Period_1.Value, 1))
This works in the details section, but gives me an error in the footer section. What should i do? PLease help, this is driving me crazy!
View 1 Replies
View Related
Dec 4, 2007
Hi all,
I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.
The Sample data which comes is:
Group1 Group2 Group3 Group4 ResourceBehavior BgtHrs BgtAmt
A1 B1 C1 D1 NML 12 12
A1 B1 C1 D1 ODC 0 12
Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs
But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)
I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.
Any help is appreciated.
Thanks in advance.
Swati
View 6 Replies
View Related
Mar 14, 2008
OK, hope I can explain what I need....
I have a report, which SUMS a value and groups on another field (campaign). What I need to be able to do, is take the SUMMED value from the 1st group, and subtract from this value, the value of the SUMMED value of the second group.
Example:
CAMPAIGN VALUE
CAMPAIGN A 5,000
CAMPAIGN B 1,100
I'd like to be able to subtract 1,100 from 5,000 - but can't find out how this can be done....don't forget these are grouped item's not just rows from the dataset.
I am fairly new to SSRS, so please go easy on me if this is something simple (i hope it is!)
Thanks for your help
Chris
View 6 Replies
View Related
Oct 23, 2007
Hi,
I use group by myItem, that displays rows for each value of myItem.
I want at the end of each group for each specific myItem, I want an extra row to tell me the count of the rows for that group.
How do i do that please. Here s what I need, for example for this set of rows:
myItem colum2 colum3
711056 22662 Finances / -2291 -2479916
711056 22663 Finances / -2380 -2576255
711056 43428 Cda) -323 -349635
711056 43428 . -B-(Cda) -44 -47628
711057 44348 . -B-(Cda) -355 -384273
711057 47033 . -B-(Cda) -1278 -1383384
I need 2 extra rows, one at the end of the items 711056 telling me there are 4 rows for the item 711056 and one row at the very end telling me there are: 2 rows for the item: 711057
Thanks a lot.
View 6 Replies
View Related
Feb 23, 2008
Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing?
using SQL 2000 server
SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE (LinksInfo.L_ID IN (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip
View 2 Replies
View Related
Mar 25, 2008
Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
View 1 Replies
View Related
Jun 17, 2004
Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciated
View 1 Replies
View Related