SQL Server 2014 :: Need A Query On Group By Condition
Oct 11, 2013
I have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
View 9 Replies
ADVERTISEMENT
Apr 4, 2015
Following is a working code
declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
[Code] ...
I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id
I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
When I tried this code I am getting error.
View 3 Replies
View Related
Oct 1, 2015
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription BugID BugStatus
1 TestCase1 1 Closed
2 TestCase1 2 Open
3 TestCase2 11 Closed
4 TestCase2 12 Closed
5 TestCase2 13 Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
View 3 Replies
View Related
Jun 12, 2014
I have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. condition check part of the code?
use msdb
go
create table ##resultset (
total int)
[code]...
View 5 Replies
View Related
Apr 22, 2014
I have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
[Code] ....
I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
[Code] ....
View 3 Replies
View Related
Jun 10, 2014
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine.
View 8 Replies
View Related
Jan 25, 2015
We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".
View 3 Replies
View Related
Jan 20, 2015
I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.
Is this possible?
This is my current SQL
SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,
[Code] ....
View 9 Replies
View Related
May 28, 2015
We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.
Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department
If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data
--===== Create the test table with
SET ANSI_NULLS ON
[Code] .....
View 3 Replies
View Related
Dec 19, 2014
Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,
[code]...
How can I get the result orderd by the month number?
View 3 Replies
View Related
Jul 3, 2015
We have 4 servers : Server 1 , Server 2 and HA , DR servers.
I designed 2 Plans to get HA support for my databases .
Which of them are better , And is there any problem in my design ?
View 4 Replies
View Related
Nov 10, 2014
Is there any way to trigger an event (ie. call a procedure/job/etc.) when/if an AG fails over?
Not looking to use agent alerts.
View 0 Replies
View Related
Dec 24, 2014
Recently after turning on trace I restarted the sql services on a box which is configured for automatic failover availability groups. The ag has not failed over to other node. The other node was in resolving state. When the restarted server is back, the AG went back to that server. I checked the sys.availability groups field for failover property failure condition level, it's set to 1 which means service restarts should initiate the failover.
View 3 Replies
View Related
Jun 17, 2015
What I asked for: Three Windows Server 2012 R2 machines with independent storage running a SQL Server 2014 AlwaysOn Availability Group. DB1 would be the primary, DB2 would be a synchronous replica, and DB3 would be a remote asynchronous replica.
What I was given: a two-node Windows Server 2012 R2 WSFC to run SQL Server 2014 Enterprise with shared storage and a third (remote) Windows Server 2012 R2 machine with independent storage, also with SQL Server 2014 Enterprise, to host an AlwaysOn Availability Groups asynchronous replica.
DB1 and DB2 (as Cluster1) share an E: drive. The remote DB3 has its own E: drive. Initially, DB3’s E: drive was claimed as a cluster resource and I couldn’t even see it. I’ve had several ugly days trying to make this work and have temporarily given up, installing DB3 as a standalone SQL Server that is no longer part of the WSFC and pointing everything towards that (it was originally a third node in the WSFC).
Is it possible to create an AlwaysOn Availability Group with nested clusters (i.e. create the AOAG with Cluster1 and DB3 and somehow ignore the individual nodes that comprise Cluster1)?
View 6 Replies
View Related
Jul 6, 2015
I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.
Say for example, following is the list of records I have in my table which I want to group into 5 batches
A123
A124
A124
A123
A127
After Ntile I will get the below,
Desired output is, Need output like Ntile but all same id should reside in single batch
Even if I n=5, maximum possibility of batches are 3 only.
View 2 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
Jun 24, 2014
I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:
declare @tv table (member varchar(255), relatedTo varchar(255))
insert into @tv
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Peter', 'Albert' union all
[Code] ....
I would like my result to look like this:
familyID Name
1 John
1 Mary
1 Jessica
1 Fred
2 Peter
2 Albert
2 Nancy
3 Abby
4 Joe
4 Frank
View 3 Replies
View Related
Sep 15, 2014
I have a user, who is trying log into the server, but everytime he gets this error saying something about the Group policy denies him access.
This user needs access and i'm trying to understand how to grant it to him.
I have been looking into how i can access the group policy editor, but the farthest i can get is the Local group policy editor. How do i make sure this specific user has access?
View 1 Replies
View Related
Nov 14, 2014
Where can I find dates and times to when an availability group was moved outside of the SQL error log?
View 1 Replies
View Related
May 1, 2006
I need to check groups of records for a condition that doesn't exist in any record in the group.
Example:
Accountno code
1 A
1 C
1 M
1 P
1 Z
If the group does not contain a code 'Z', I want to select the accountno of the group. In the above example, the accountno should not be selected. If none of the records had a code 'Z', the accountno should be selected.
I can't simply say 'where code <> 'Z'. When the system looks at the first record in the above example, it sees that A is not equal to Z and the accountno would be selected. But this is wrong. As there is a 'Z' in the group, the accountno should not be selected.
I've tried using 'top 1' and count. Nothing has worked.
Any ideas?
View 5 Replies
View Related
Aug 20, 2007
Hi,
I Have the following "select statement" with "group by condition", I want the outgoing result for the query to be vno with (scamt <> sdamt) the example is as following:-
select vno,vtype,tdate,sum(camt) as scamt,sum(damt) as sdamt
from transact
where year = 2007
group by vno,vtype,tdate
HAVING (scamt <> sdamt)
order by vno
but i have the following error
Invalid column name 'sdamt'.
View 2 Replies
View Related
Jul 20, 2005
Data:PROJ PLAN TOTTIME UNITA P1 10 DAYA P2 10 HOURA P3 1 MONTHWHEN I'M DOING GROUP BY ON PROJAND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTHI WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.10 DAY=10 DAYS10 HOUR=1.25 DAYS1 MONTH=20 DAYSTHE RESULT SHOULD BE LIKE THIS:PROJ PLAN TOTTIME (IN DAYS)-------------------------------A ALL 31.25-------------------------------THANKS IN ADV.T.S.NEGIJoin Bytes!
View 1 Replies
View Related
Jan 27, 2015
I Create an availibility Group with a primary and a secondary. (For test)
Then run Planed Failover.
Switched to secondary. That's ok.
After that I update some tables on secondary (That now is primary)
I Run Again Planed Failover on server 2.
Switched. OK
But primary database Get (Not synchronizing) Status.
And in primary I don't have that updates.
How to sync these databases and exit from Not synchronizing.
View 5 Replies
View Related
Jun 27, 2015
I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22
I also define an listener . IP .. 23
1- In First step I connected To Listener (23) And in a while I inserted A record to a table .
While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)
2- in second, I Stop the primary .
- I expected this while whitout disconnect, continue.
3- The while code stopped whit this message :
Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
4- I execute again the script, And it worked in new primary.
My questions :
1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?
2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :
Msg 9002, Level 17, State 2, Line 27
The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.
Is this (Fill All space) a reason to switch primary? Or not ?
View 2 Replies
View Related
Aug 14, 2015
I'm running a primary and secondary on sql server 2012 enterprise edition on windows server 2012, and it runs fine except when a network outage occurs.
Then the handshaking keeps failing, the databases on the replica show as not synchronizing and the only way to fix this is to reboot both primary and secondary.
We keep getting 3520's, etc. on the DR error log
How to eliminate all these prod reboots?
I increased query connection timeouts to 60, but saw no change.
View 0 Replies
View Related
Oct 16, 2007
Hi All,
I have a tricky issue and plesae help:
My dataset likes this:
Level people
1 A1
1 A2
1 A1
2 A3
2 A3
2 A4
2 A4
2 A4
I want to create a summary report and get the results like:
Records People
Level 1 3 2
Level 2 5 3
I only two levels: Level 1 and level 2 and don't need consider the lable of level.
How can I implement this in the Table or Metrix?
TIA
Micror
View 1 Replies
View Related
Jun 14, 2014
tblScore contains score for each problem
id problemID score
------------------------
1 1 10
2 2 30
tblSubmission contains problem submissions for each user
id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1
For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
Now I would like to process the table and want to get the following result :
user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)
Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30
so final score for UserA = 30 + 6 = 36
Similar for UserB.
View 2 Replies
View Related
May 7, 2014
A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.
I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.
I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.
One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.
View 2 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
Jun 27, 2014
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
View 9 Replies
View Related
Dec 16, 2007
Hi all,
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.
View 3 Replies
View Related
Jan 28, 2015
I wish to make a query with if condition Implemented in a database sql server 2008 R2.
I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place. The principle of my request is:
if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request).
else if amount of movement (QTEMVT) <amount entered by the user
then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions:
the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0
by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.
Basically I want to set up an item management query based on batch number and expiration dates.
ps: QTEMVT = quantity of the item stored in my database
NUMLOT = batch number items
DATFABRIC = manufacturing date items
DATPEREMP = expiry date items
Here is my request:
SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT
FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT
INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT
INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT
INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
[Code] ....
View 9 Replies
View Related
Apr 23, 2015
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date)
from #table)
IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month
<Something here to kill the query>
ELSE
SELECT email_address
FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
View 2 Replies
View Related
