SQL Server 2008 :: How To Change Query So Apple Does Not Show Up In List
Apr 2, 2015
I am trying to print Companies with less than 100 employees for all dates.Here's my table structure
Create table CompanyEmployeeArchive(
Company varchar(100) not null, Employees int, Dateinserted date)
Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')
Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')
Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')
Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')
Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')
Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')
I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.
View 4 Replies
ADVERTISEMENT
Jul 30, 2015
write a query that lists item, mfg serial number, and inspection date. The caveat is that, where there is more than one record for an item, he only wants item listed once. For example:
PartNumberInspectionDateManufacturerSerialNumber
1A144878/4/2013.1101VAS073-7
3L115931/28/2014009VAS206-1
1/29/2014009VAS206-1
2/21/2014009VAS206-1
7/22/2014009VAS206-1
2A285631/1/2015010115ND9CV02
1A203921/3/2015010315ND9PV45
1/4/2015010315ND9PV45
3/1/2015010315ND9PV45
2A2794310/20/20120103700P003
View 9 Replies
View Related
Jul 16, 2015
We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.
When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.
Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.
View 1 Replies
View Related
Mar 10, 2015
selecting table data in hierarchical XML .
Here is the sample table DDL and data
Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int
[code]....
View 8 Replies
View Related
May 6, 2015
Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.
View 3 Replies
View Related
Aug 6, 2015
I have below script
CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20
[Code] ....
-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180
****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows
ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid
View 2 Replies
View Related
Mar 17, 2015
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?
View 7 Replies
View Related
Nov 13, 2007
Lets say there is a data table called ShoppingBasket. It has fields like "product", "price", "quantity" and "total" and so on. It is possible to have duplicates, but I think it is better to run a query and remove them. And update quantities. In the end, I plan to calculate total price. For total price there seems to be a field or property "Formula" in column properties. As this is really two questions, I take any help. Leif
View 2 Replies
View Related
Aug 4, 2015
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
FROM BF_ORGN A
INNER JOIN BF_ORGN_CNSL_TBL B
ON A.CD=B.BF_ORGN_CD
WHERE A.BF_ACTY_CD IS NOT NULL
ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD
AC_21234_2 AC_21200_1 402
AC_21236_2 AC_21200_1 402
AC_21238_2 AC_21200_1 402
AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD)
AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
[code]...
View 9 Replies
View Related
May 15, 2015
How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?
01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014
I have this Select statement
select replace(convert(char(11),getdate(),113),' ','-')
But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement
View 4 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
View 6 Replies
View Related
Jul 9, 2015
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
View 9 Replies
View Related
May 24, 2015
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()
I'm guessing some sort of recursion is required here to produce this.
View 7 Replies
View Related
Jul 27, 2015
who i can to list the content of all store procedures and funtions in text file ?
View 3 Replies
View Related
Feb 10, 2015
I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.
My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.
select * from SourceTable -- produces 22461 records
--step 1: first, get row number of child records in each parent
SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;
[Code] ....
View 1 Replies
View Related
Mar 12, 2015
How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.
Here is the flat table
SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO
Output will be something like this
12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG
Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.
View 2 Replies
View Related
Apr 6, 2015
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
View 2 Replies
View Related
Apr 8, 2015
i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
View 9 Replies
View Related
Jun 8, 2015
best way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,
[Code] .....
method to track changes to this table with it being truncated every day?
View 1 Replies
View Related
Jul 3, 2015
Currently we have SQL instance on C: drive.We are now going to upgrade SQL Server 2008 RTM to SQL 2008 SP4.Can we change the path of SQL instance during upgrade?What services need to be stopped during upgrade.
View 2 Replies
View Related
Mar 3, 2015
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies
View Related
Jul 1, 2015
We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies
View Related
Aug 11, 2015
I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Example 1:
field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g
and I would like to get it in the form
Example 2:
field1_field3
1_____a,b,c
2_____f,g
View 2 Replies
View Related
Apr 28, 2015
USE master
Declare @db as nvarchar(258) = quotename (N'tempdb')
EXEC (N'USE' + @db + N'; EXEC(''SELECT DB_NAME();'');');
View 5 Replies
View Related
Jun 10, 2015
I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.
DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]
[Code] ....
View 1 Replies
View Related
Aug 5, 2015
Our SQL Server 2008 r2 has collation Latin general.
And my database 'DB1' , 'DB2' has collation set to Japanese unicode.
My sql team has informed they cannot change system collation as it hosts other db's as well.
My Query: What will be the impact of changing collation from JP to Latin.
View 1 Replies
View Related
Dec 13, 2007
Salam to All
Plz give me Sql query which return list of Database By providing these information(Server name, Uerid ,Pasword)
Thanks
View 2 Replies
View Related
Mar 9, 2011
IF you need to change the IP Address of a SQL Server Failover Cluster instance, here is the steps:
1. Open up Failover Cluster manager.
2. Expand the MS Cluster Instance that your SQL Cluster instance resides on.
3. Expand Services and applications.
4. Select the SQL Server Service.
5. In the main window pane (Middle Screen), you should see your SQL Server Cluster name, expand the plus to the left of it.
6. Under your SQL Server Cluster name, you should now see an 'IP address' section, right click on it and go to properties.
7. In the properties pane, you can change your IP address by entering a static IP.....or assigning a DHCP enabled IP.
View 1 Replies
View Related
Mar 24, 2015
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL.
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
[Code] ....
View 7 Replies
View Related
Aug 10, 2015
Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.
View 0 Replies
View Related
Feb 20, 2007
Hello Everyone,
Thanks for everyone's help on this mess so far especially Peso. Yes this was originally Oracle SQL and I now need to convert it to SQL.
Can someone start by showing me at least part of this the best way to convert this to regular SQL? Also are there any tips, guides out there that anyone knows of?
selectCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB as TECHN,
substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
Parser.dbo.OJB_JOBS.COMPL_DTE_OJB as COMPLET,
Parser.dbo.ELP_Codes.CodeDes as [DESCRIPTION],
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB as RES_NAME,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE as [ADDRESS],
CSG_Hist.dbo.OJB_JOBS.JOB_TYP_OJB as JOB,
CSG_Hist.dbo.OJB_JOBS.JOB_CLASS_OJB as TYP,
CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR as NUMB,
CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR as OPR,
Parser.dbo.ELP_Codes.CommissionAMT as NCommissionAMT,
COUNT(DISTINCT Parser.dbo.Parser_OCR.ORDER_NO_OCR) as QTYW
fromParser_OCR
Inner JoinParser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB
Inner JOINParser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOINCSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
Inner JOINCSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
Inner JOINCSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB
cross Join(
select 1 as p union all
select 4 union all
select 7 union all
select 10 union all
select 13 union all
select 16
) as pstn
WHERECSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB between 950 and 999
and Parser.dbo.Parser_OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
and CSG_Hist.dbo.Parser_OCR.prin_ocr = 8600
GROUP BYCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB,
substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3),
Parser.dbo.Parser_OJB.COMPL_DTE_OJB,
Parser.dbo.ELP_Codes.CodeDes,
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE,
Parser.dbo.Parser_OJB.JOB_TYP_OJB,
Parser.dbo.Parser_OJB.JOB_CLASS_OJB,
CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR,
CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR,
Parser.dbo.ELP_Codes.CommissionAMT
Again thanks for everyone's help especially Peso!
Have a great day!
Kurt
View 20 Replies
View Related
Jan 13, 2013
Or can it record before and after column changes based on the LSN only?
An extract from a file based legacy accounting system is performed every night. The system does not have a primary key because transactions are managed through program code. (the more things change...). The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays.
In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in tables that remember the before and after content of each column tracked. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work. This is in contrast to replication, where the requirement for the existence of a primary key is made plain.
Is it possible to use Change Data Capture against a table without a primary key? How to use it to change the extract from kill and fill to incremental.
View 9 Replies
View Related
Mar 10, 2015
Recently we had multiple production failures because one database was in single-user mode. I am trying to recreate this scenario to write a script that will kick out that connection that was holding database in single-user.In SSMS window 1 I run the following code:
use master
go
alter database test
set single_user
with rollback immediate
[code]....
Yes, it shows one record, but the database name is master, not test. And indeed, I run it from the context of master. Also tried sp_who, sp_who2, sys.dm_exec_requests - nothing works. For dm_exec_requests it shows nothing because it does not run at the current moment.Any other solutions to catch this SPID with a script?
View 5 Replies
View Related