I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date
DECLARE @table TABLE
( Prop_Code INT
,Current_Rent INT
,Revised_Rent INT
,Rent_Review_Date varchar(10)
,Rent_Review_Time DATEtime)
[Code] .....
CREATE TABLE #Names
( ID INT IDENTITY(1,1),
NAME VARCHAR(100)
)
INSERT INTO #Names VALUES ('S-SQLXX')
INSERT INTO #Names VALUES ('S-SQLXX.NA.SN.ORG')
INSERT INTO #Names VALUES ('S-SQLYY')
INSERT INTO #Names VALUES ('S-SQLYY.NA.SN.ORG')
INSERT INTO #Names VALUES ('S-SQLCL-HR')
INSERT INTO #Names VALUES ('S-SQLCL-MIS')
SELECT * FROM #Names
--I want to filter out S-SQLXX.NA.SN.ORG because S-SQLXX.NA.SN.ORG is a duplicate of S-SQLXX eliminating .NA.SN.ORG from it.
--I want to filter out S-SQLYY.NA.SN.ORG because S-SQLYY.NA.SN.ORG is a duplicate of S-SQLYY eliminating .NA.SN.ORG from it.
--However I want to keep S-SQLCL-HR and S-SQLCL-MIS in my list of names as they do not have .NA.SN.ORG as a part of their name
--I want ONLY these returned IN the SELECT
SELECT * FROM #Names WHERE ID IN (1,3,5,6)
DROP TABLE #Names
Trying to build a list of order numbers based on stock availability.
The data looks something like this:
OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
declare @YourTable table (
CustomerID int,
ReadDate datetime,
usage int
)
insert into @YourTable select 1,' 1 mar 2014',0
insert into @YourTable select 1,' 1 feb 2014',0
[Code] ....
This should return
1,3
2,1
This is what I am currently using but it isn't working right
WITH cte
AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt,CustomerID) pt FROM
(
SELECT tt.*
[Code] .....
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00
We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:
Id int identity,
ParentId int,
GroupId int
Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.
A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child
The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.
Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child
And the filter would look like this:
WHERE GroupId = 200
However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…
I am writing a script to get all of the user objects out of a database. What I am having a hard time doing is filtering out the MS replication system objects, such as:
dbo.MSsnapshotdeliveryprogress
dbo.MSreplication_objects
dbo.MSreplication_subscriptions
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties
dbo.MSsavedforeignkeys
dbo.MSsnapshotdeliveryprogress
dbo.MSsubscription_agents
is there a way to turn off or reduce the logfiles of the MSSQL Full-text Filter Daemon Service?
The entries are very useless and on server with many databases and many fullindex tables it is very inefficient to write all these files (no SSD).
(SQL Server 2012 Express)
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
Guys,
I'm using the following code and I just want display all glcodes that have a count less than 2:
select glcode,
sum(abs(sysvalue)) 'Movement',
count(glcode) 'Occurances'
from jet
group by glcode
order by occurances
The following sytax works i know i need to use the where function. Does anyone know the syntax?
Cheers
Michael
Is there a way to get a count of all the rows (total) after a filter has been applied at the row level?
Ie. Revenue has 11 rows but once the 6 blank ones are filtered count the total number of rows. So it should return 5.
hi
how do i write a filter to filter data only for last day of month.
for ex: we are running a report on jan 25/2008, but report should filter on last day of current month..
help me guys
thanks
phani
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
I am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:
Gross Amount applies to all clients, whether Type A or Type B. I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither. I would like to return blank/null, not $0.00, for those values that should not be displayed.
Here's the basic query.
SELECT { [Measures].[Gross Amount],
[Measures].[Type A Net Amount],
[Measures].[Type B Net Amount]
} ON COLUMNS,
NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] *
[Dim Client].[Child Client Code].[Child Client Code] *
[Dim Client].[Is Type A].CHILDREN *
[Dim Client].[Is Type B].CHILDREN
} ON ROWS
FROM ClientInfo
Here's the DESIRED output........
I am running into an issue trying to declaratively set up a SqlDataSource. I want to be able to filter some of my queries based on the user that is currently logged into the web site. I want to do it Declaratively as that's one of my favorite 2.0 features.Is there any way to do this with the Membership information? I know I can use the code behind to set the parameter, or store the User Name in Session Variable, and use a <asp:SessionParameter> but I think there should be a way to bind directly to the Membership user...Am I missing another option, or is there no built in way to do this? Any other suggestions...Thanks
View 1 Replies View RelatedI am loading data from Source to Target SQL table.
Source data format:
#19067;#90;#19068;#91;#19069;#92;#19070;#93;
Expected data in Target : 90;91;92;93
can you let me know how this can be achieved through transformations?
Good morning,
View 7 Replies View RelatedMy table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
Consider following script
create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set
I tried this below
Declare @child_ids int
@col_val varchar
@count int
select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1
But I want my output like below .....
using below script to compare two tables and get the values.
how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.
Ex:
'Table A' -- 150
'Table B' -- 300
'Table A & Table B' -- 150
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;
Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.
if the output for the sql below is:
101, 10, Jon, Smith, MD, Ortho, OR, 15
101, 10, Jon, Smith, MD Ortho, 1, 12
101, 10, Jon, Smith, MD, Ortho, 2, 10
24, 3, Mike, Jones, MD, Neuro, OR, 21
24, 3, Mike, Jones, MD, Neuro, 2, 43
I'd like to have the query rewritten so the results are as:
101, 10, Jon, Smith, MD, Ortho, OR, 15
24, 3, Mike, Jones, MD, Neuro, 2, 43
SELECT
a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count
FROM accounts a
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')
group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname
order by a.AttendingMdKey
I have an entity (A), in which I use domain based attribute. The second entity (B) has several attributes. My problem is that, I would like to filter the first entity (A) based on an attribute that belongs to the second entity. The only way I can filter it (in MDS Excel add-in or Explorer) is by using Code or Name from the second entity.
I have in mind a couple of solutions, but they require some coding with xml saved query from Excel.
trying to create indexes on two tables:
SF_Affiliate_Customer
SF_Affiliate_Customer_Account
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND
[code].....
There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:
Step One:
We create a Server Role called SQL_APPLICATIONS – for the application accounts
We create a Server Role called SQL_DBA – for the DBA accounts
and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two:
I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three:
I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
i am using the followig query :
select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.
View 9 Replies View RelatedI run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than a day just to compare the results.
Mon - Run script to collect row count
Tues - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Monday and insert current
Wed - Run script to collect current row into temp table
,compare all row count in both tables
[code]....
I Want to monitor Replication count of object (Table )if it is not equal to Publication (Table ) and subscriber (Table ), It have to send mail with count difference.
View 9 Replies View RelatedI have two tables with this info:
TABLE 1
COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB
TABLE 2
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352
I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below you will find the code I am running:
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int
[Code] ....
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
I have a very simple query like the following…
SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)
From this query, its returning me a result set of something like below:
--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------
RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER
Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…
Something for example (I am not trying to write a condition: my question is how to write it),
if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.
Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…
See the attachment for better formatting...
---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH
What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.
Table 1:
DealerID RepairID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 11
2 12
2 13
Table 2:
RepairID Tire
1 225/65R17 102T
2 225/65R17 102T
3 225/65R17 102T
4 235/60R18 102V
5 235/60R18 102V
6 235/60R18 102V
7 235/60R18 102V
8 205/55R16 89H
9 205/70R15 89H
13 225/65R17 102T
Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.
Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.
Desired Output:
DealerIDTire RepairsOfThisTireRepairCount PercentOfTotalRepairOrders
1 235/60R18 102V4 10 40
1 225/65R17 102T3 10 30
1 205/55R16 89H1 10 10
2 225/65R17 102T1 3 33
The equation I am given to calculate the top tires per dealer is as follows: Total # of tires for the size / Total # of repair orders per dealer.
Here is what I have so far though I think I might have to rewrite all of it.
SELECT
DealerID ,
COUNT(RepairID)
INTO #TotalDealerRepairOrders
FROM
dbo.Table1
GROUP BY
DealerID
SELECT
[code].....