T-SQL (SS2K8) :: How To Return Top N And Sum Up The Rest
Feb 10, 2015I am looking for an elegant way to return top n and sum up the rest, in my case CTE/temp table/variables are not allowed.
View 9 Replies
ADVERTISEMENT
T-SQL (SS2K8) :: Take ID Value From Maximum ID And Compare Rest ID Value From Table
Jul 2, 2014--drop table #temp
create table #temp (id int, idvalue int)
insert into #temp(id,idvalue)
select 1095,75
[code]...
I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.
T-SQL (SS2K8) :: How To Return Jobs That Are In Progress
Mar 8, 2014I have a query that returns the jobs that have completed for the nightly processing.
If I run the query it does not reflect jobs that are in progress.How can the following query be modified to reflect jobs that have started but have not completed?
SELECT DISTINCT j.Name AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
[code]....
T-SQL (SS2K8) :: How To Return Zero If No Rows Found
Jun 25, 2014I have a report that needs to return a count of zero for the rows that have no data, I have tried to use the Left Outer Join but my where clause is excluding the rows with no data and I need to filter the report with the Year, day and Month.
The date filters are from different table(dimDate), not sure how to include them in the #tmpOperationalTypes join as filters
ALTER PROCEDURE [dbo].[spcAdvancedComparisonDateDWReport]
@Year varchar(4000) = '',
@Day varchar(28) = '',
@Month varchar(28) = '',
@Locations varchar(4000) = '',
[Code] .....
T-SQL (SS2K8) :: How To Return Max From A Varchar Column
Jan 21, 2015I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example
1.0.0
1.0.1
1.1.0
1.2.1
2.0.0
2.1.1
etc
These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.
T-SQL (SS2K8) :: Return All Values From Table
Oct 1, 2015I have a small problem with a join clause, because i need to return all values from my table BL:
my code is:
SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL
[code]....
In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).
T-SQL (SS2K8) :: Always Return X Number Of Records Even If Less In Recordset
Jun 27, 2015I am using SQL Server 2008 as a back end for a Microsoft Access front end. I have created a report that is essentially a Bill Of Lading. The detail section lists all the purchase orders that are being shipped on a single load. The problem with the Access Report is that I always need a set number of records (8) so that the layout is consistent. So, if the query returns 5 records, I need an additional 3 blank records returned with the recordset. If there are 2 records, I need an additional 6, and so on. For simplicity sake the query is:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber=@BOLNumber;Now, I can get the results I want by using a union query for the "extra" records.
For instance, if there are 6 records returned for BOLNumber '12345', I can get the expected results by this query:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber='12345'
UNION ALL SELECT '12345',Null
UNION ALL SELECT '12345',Null;
Another solution would be to create a temporary table with the "extra" records and then have only one Union statement. Not sure which is better, but I'm not really sure how to programmatically do either of these. I'm guessing I need to do it in a stored procedure. How do I programmatically create these extra records? One other note.... If there are more than 8 records, I need to return 8 of these "blank" records and none of the real records (hard to explain the reason behind this, but it has to do with the report being only a summary when there are more than 8 records while the actual records will go on a different supplemental report).
T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours
Mar 4, 2014How can I calculate and return the previous Date at 18:00 Hours?
Here is a miserable attempt:
DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()
SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate
-- Desired Result is the following:
-- 2014-03-03 18:00
T-SQL (SS2K8) :: Convert Column In Query1 And Return Unmatched?
Apr 14, 2014I have cpt codes in qry1 that need to be converted then match against qry2 and return the unmatched.
Example:
Cpt 88305 in qry1 can be either g4164 or g4165 in qry2
Cpt 88174 in qry1 can be either g3564 or g3565 or g3566 in qry2
select
Invoice_number
,cpt
From qry1
Select
Invoice_number
,cpt
From qry2
What is the easiest way to get the unmatched from qry1 against qry2 joining on invoice_number and converted cpt?
T-SQL (SS2K8) :: Return Primary Key From (updated) Record Of Merge Statement
Apr 16, 2014I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
T-SQL (SS2K8) :: Return A Count Per Unique Check In Invoice Table?
May 12, 2014We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
T-SQL (SS2K8) :: Return Single Row For Matching Columns Based On 3rd Column
Sep 3, 2014I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
T-SQL (SS2K8) :: Stored Procedure And SELECT Statement Return Different Results
Dec 4, 2014I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
T-SQL (SS2K8) :: Multiple Databases - Return A Single Table With Three Columns
Jan 13, 2015I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
T-SQL (SS2K8) :: How To Return 3 Month Rolling Average Count Per Username
Mar 30, 2015how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[code]....
T-SQL (SS2K8) :: Return Value In A Status Field Which Has Latest Year And Month
May 11, 2015I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
SQL: How To Display Top 5 Then Sum The Rest
Apr 6, 2006Hello,I would like to query the top 5 best companies' sales (total sales),then total the rest, what is the quickest and effective SQL to queryit?Thanks in advance
View 8 Replies View RelatedAnalysis :: TOP N Plus Rest
Nov 17, 2015I need to create a dynamic top count of customers, plus the rest. I've seen many posts on this, however the difference I want is that the rest needs to be expandable to show what customers actually make up the rest category. For example:
Top
Cust1
Cust5
Cust2
Rest
Cust3
Cust4
I was thinking potentially the top/rest could be a shell dimension and then scope on these members to show the top and rest, however I haven't been able to achieve this.
How Can I Keep Only The Top Ten Rows And Delete The Rest
Dec 17, 2007I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?
View 17 Replies View RelatedRest Primary Keys?
Mar 20, 2008HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it?
View 6 Replies View RelatedPulling Out PO# From The Rest Of The Job Descrip.
Apr 29, 2008Hello, I am new here and to sql so please bare with me.
I am trying to create 2 select statements from the same field. The field is a job_description field that sometimes contains PO#'s. One select statement needs to extract the PO# if it has been written in the field and the other needs to extract all data that is not the PO#
Here is some example of the data:
New shirts for 2007
Magazine cover for may edition
Way to go postcards PO# 45687
PO#879
For the first select statement I need to capture just the PO#. the result for the above examples would be:
NULL
NULL
45687
879
For the second select statement I need everything but the PO#. The result would be:
New shirts for 2007
Magazine cover for may edition
Way to go postcards
NULL
So far I have put together a basic idea for the PO# part:
(SELECT SUBSTRING (job_description , (SELECT patindex('%PO#%', job_description)+3),10)WHERE job_description LIKE '%po#%')
If i could figure out a way to only return numeric data from the above i think it will work.
As for the second select statement i am at a loss on how to select everything but the PO#.
Any help at all is very much appreciated.
Thanks
If One Of The BCP Fail. Does It Affect The Rest Of The Process??
Dec 8, 1998Hi, I am wrote the following code in one store procedure called p_bcp_all.
and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table?
thanks for your adivce
regards Ali
create procedure p_bcp_all as
Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tbltwo in d:bltwo.txt /fd:formatfilebltwo.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tblthree in d:blone.txt /fd:formatfileblthree .fmt /servername /Usa /password/b250000 /a8000"
GO
Capitalizing First Letter, And Lower The Rest
Oct 25, 2006I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion
I am getting this error:
Invalid length parameter passed to the RIGHT function.
With User Sa I Can See Mot Databases How Can The Rest Of The Users See Them All?
Jun 26, 2007I have a third party application that creates several databases on my sql server. This party uses the same user for accessing all this databases. However after moving to a new server this databases the user is no longer able to query them whith the exception of the "sa" user. Could anyone explain me how to add permissions to this databases manually so the user can query them again.
Thanks in advance
brgds
Miguel
Retaining Records Of Top N Rows And Deleting The Rest
May 15, 2008Hi All,
I am writing a SP where I need to pass an value to maintain records of last n days. In this SP I am deleting a couple of tables based on the value passed to this SP. For e.g. If the SP is passed the value 10, then only TOP 10 records is maintained, the rest are deleted.
I have formed the following logic, which I feel can be improved vastly.
I create a temp table and
CREATE TABLE #TempAuditTbl (Rownum int PRIMARY KEY, Orderid uniqueidentifier)
INSERT INTO #TempAuditTbl
SELECT ROW_NUMBER() OVER (ORDER BY orderdate desc) AS rownum, Orderid FROM Orders
DELETE Orders FROM Orders INNER JOIN #TempAuditTbl adt ON adt.Orderid = Orders.Orderid AND rownum > @TopnRows
DROP TABLE #TempAuditTbl
OR
DELETE FROM Orders WHERE orderid NOT IN ( SELECT TOP @TopnRows OrderID FROM Orders ORDER BY OrderDate desc)
This way I am able to keep the top n records.
Which of these two solutions is more efficient? Is there a more efficient way to achieve the same.
Please help.
Thanks & regards
Sunil
Delete/Move Certain Info From Column - Leave The Rest?
Nov 10, 2005I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?
EXAMPLE
MYTABLE >COLUMN1
May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1
WHAT I NEED TO DO IS
Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:
COL1 COL2
ABCDE123 SER1
XYZ12DEF Ser1:1
Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.
One more question - What would be a great resource to learn MS SQL in more depth?
I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?
Thank you.
How To Commit The Rest Of The Transactions Of An Update In Large Bulk?
Feb 8, 2004I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed.
However, there is no more spare disk space for it to commit the transaction. Anyone can help?
Display Data First Letter Uppercase Rest Lowercase
Sep 19, 2006I know you are able to display data all uppercase or all lowercase, but how do you display it First letter capital rest lower. Like a First or Last name?
View 3 Replies View RelatedFriendly Way To Look At Sqlagent Ssis Messages Without Exporting Rest Of Log
Apr 13, 2008I just started playing with sqlagent and am finding that viewing a specific message from an ssis step is cumbersome when other entries are also in the log. Exporting seems only able to export all history entries. Viewing right in the log viewer leads to a series of boundary expansions and right scrolls that dont seem convenient for quick identification of a problem in a medium sized ssis message. Right clicking on the message in the viewer seems like it is preparing the message for possible copy but it seems to end there. I'm already familiar with the different levels of verbosity available in the command line. Dont see anything useful on the web.
Is there a user friendly way to copy the contents of a specific message for perhaps pasting to notepad , so that problem identification is facilitated? Is there a not so user friendly way available, one taht doest involve 1st exporting the entire log?
SQL Server 2008 :: Using Left And Charindex To Parse String / Getting Rid Of Rest Of Data
Jun 16, 2015I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
Reporting Services :: Expression To Change All Caps To First Letter Uppercase And Rest Lowercase
Aug 1, 2015The states in this report are all in caps TEXAS, CALIFORNIA, etc.. Is there a way to use expressions to only have the first letter in uppercase and rest in lowercase?
View 3 Replies View RelatedT-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE
Apr 16, 2015If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE