Transact SQL :: Find The Number Which Exists In Different Groups
May 25, 2015
I have a table which has 2 columns and the data is like below
API_Number       Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A
3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group B
4567Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group C
7896Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group D
3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group E
 I wanted to find the API numbers which are repeating in different groups. In the output I want
 API_Number          Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group B
3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group E
I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth)Â select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
The following SQL works on Access and Oracle to return the number of Groups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is the equivalent in MS-SQL?
Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion )
Counting groups I have the following code that is grouped by name
Mike G ( from 2000 €“ 2003) Address Raise Test 1 Test 1
Mike G ( from 2004 €“ 2007) Address Raise Test 2 1 Test 2 1
Mark G ( from 2004 €“ 2007)
Address Raise Test 2 1 Test 2 1
Total 6 (I get this without any problem)
The problem I am having is trying to find out how many groups we have to use the value for some other calculation. So I did this Count(Field .Name) wich will give me 6 instead of 3 . The reason is in my dataset it will be something like that
Name add raise Period Mike G Test 1 2000-2003 Mike G Test 1 2000-2003 Mike G Test 1 2004-2004 Mike G Test 1 2004-2007 Mike G Test2 1 2004-2007 Mike G Test2 1 2004-2007
If I do CountDistinct(Field .Name) it will give me 2 which is also wrong . So how can I count many groups we have. I spend a lot of time on this please help!
IF OBJECT_ID('tempdb..#Test') IS NOt NULL DROP TABLe #Test --===== Create the test table with create table #Test([Year] float, Age Int, ) INSERT INTO #Test ([Year], Age)
[Code]...
I queried below to get additional column
Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as
FROM DISK='\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'
WITH NORECOVERY, FILE=2,
MOVE 'ReportServer2005' TO
'G:DataReportServer.mdf',
MOVE 'ReportServer2005_log' TO
'J:LogsReportServer_Log.ldf';
GO
Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device '\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I see file ID 2 identifying the log file. I've never done a restore like this - using copy_only and backing the database and log into separate .bak files, so maybe I'm misunderstanding something here.
Acct No     Sales 1           90 2          129 3          1200 4         270
The table in real life has much more data and more columns. I want to be able to select Acct No and sum of sales for each band
Bands: <250 Â // Â 250-500 Â // 500-1000 // 1000+
I want to be able to get a table as below:
Account No //Â Â <250 Â // Â 250-500 Â // 500-1000 // 1000+ Â 1 Â Â Â Â Â Â Â // Â Â 90 Â Â // Â Â Â Â 0 Â Â // Â Â Â Â Â 0 Â Â Â // Â Â Â 0 Â 2 Â Â Â Â Â Â Â // Â Â 129 Â // Â Â Â Â 0 Â Â / / Â Â Â Â Â 0 Â Â // Â Â Â 0 Â 2 Â Â Â Â Â Â Â // Â Â 0 Â Â Â // Â Â Â Â 0 Â Â / / Â Â Â Â Â 0 Â Â // Â Â Â 1200 Â 2 Â Â Â Â Â Â Â // Â Â 0 Â Â // Â Â Â Â 270 Â Â / / Â Â Â Â Â 0 Â Â // Â Â Â 0
i need to check that the column exist in the table if yes the update/insert value in the column else i need to add the new column like new browser name in the table.
I have textboxes that work as checkboxes on my form. I have a stored procedure that brings a set of values back. I need to see if the specific values are in the dataset. If so, I put an X in the textbox if that value is in the dataset. How do I do this? [URL] .....
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
I have a policy table which has policyNumbers and createDate. I need to retrieve all rows where createDate is between 2 dates and there is more than 1 row with the same policy number. I cannot figure out the SQL to return all policy number rows if there are more than 1 row with the policy. Most policies number will be in the table once. I need the others.
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:
I have a table with about 50,000 rows. I open several connections and use a command to ExecuteResultSet against each command, with CommandType.TableDirect, CommandText set to the name of the table, and IndexName set to various indexes. In the end, I have several SqlCeResultSet instances which are then maintained for the life of the AppDomain.
In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.
In a Visual Studio debug session, this approach goes swimmingly for a short time, and then after a successful Read(), I'm pegged with an InvalidOperationException (text: "No data exists for the row/column") for a column which was succesfully read on the previous Read(). If, in the immediate window, I call SqlCeResultSet.Read() again on the result set instance, the Get___ methods work as they had been in the previous reads.
It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?
I am using SQL Server 2008 - and what I want to do is set my variable @dh. Â If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. Â How can I do that?
Is there a way to find active connection to a database so you don't have to wait for a restore or similar operation to fail. (Which take forever).?
Is there a SMO method or a transact SQL command or something that can be used in an application to detect active connections?
I don't want to have to wait forever to get a failure when you can't perform a operation that require that there be no active connection such detach a database. I want to be able to tell when I can perform a operation which requires no active connection and when I need to use a task without out dropping the connection like copying the schema using SMO methods.
However if there are no connection I can perform a detach copy and attach to create a new database which is considerable faster.
I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.Â
declare@startdate date = '2014-04-01' declare@enddate date = '2014-05-01' ; with SM as ( SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row, u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL )) ) INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id AND s.Service_Delivery_Unit_Id = 200 ) SELECT count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
This is my query, from my nonono table I need to return the most recent date from attempted (column name) Â How could I modify this to do such
Select * FROM [nonono] td WHERE (NOT EXISTS (SELECT [First], [second] FROM [yesyesyes] AS d WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
I am trying to bulk update about 50,000 rows in a SQL table. Â The values that the table MUST contain are:
1-3 days 4-7 days 8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days 4 - 7 days 8 - 10 days
What would be my best methodology of removing the space between numerics only? Â I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
I'm currently setting up a Tabular Model to do some research between several fact tables. Â In this example i have two fact tables (table 1 and table 2)Â which I've created a 1 to 1 relationship on phone number. Â Typically I create a relationship between these tables to find common data between the two. Â However, in this case I am trying to figure out the best way to model the data so that I can easily surface data from one table that does not exist in the other. Â I would liken this to a LEFT JOIN or a WHERE NOT EXISTS in SQL.
Table 1 has all of the data and Table 2 Only has a subset of the data from Table 1. Â What I'm trying to do here is display what attributes in Table 1 may play a part in records not existing in Table 2. What is the best way to model this?
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10], MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10], MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11], MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11], MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
[Code] ....
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from AÂ where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
