Recently Indexed Messages:-
Transact SQL :: Get Only Those Distinct Records Which Satisfied Condition
Jun 3, 2015I need to write a query on a table..
table defination .. table Client_Master
file_id int identity(1,1) primary key
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit
has Data ..
FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1
Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 out of which 3 are rejected ..
Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE from Client_Master where all files has been rejected ..
means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..
I have wrote as below .. got the result but not satisfy with the query
SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM dbo.FILE_MASTER
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )
SQL Security :: Permissions Disappearing From Tables And Views
Feb 26, 2014As a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will
Go into SQL Server Management Studio 2008Open the database
Locate either the table or the view
Right click on permissions.
Add in the role or user that's needed
Grant them Select permissions
This allows the report in CRM to access the database and this works.
The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.
I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.
SQL 2012 :: Read Only Copy Of Particular Database?
Aug 28, 2014We are using 208r2. We used to generate simple reports against to prod. The production is running slow. Just we need the production fresh copy all the time. We are using mainly one database. So I need to have read only copy of that particular database which is sync with the production.
View 3 RepliesSQL 2012 :: Unable To Export Tables From GIS Application To The Database
Oct 1, 2014I am using SQL server 2012. An user tried to export tables from GIS application to the SQL database.
After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.
He was added as a login and user as in a windows group. Meaning he is a member of the windows group.
I assume when export, the default schema should be dbo. but apparently not.
I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables. just wondering why is this?
Insert Into A Table From Another Table
Jan 22, 2014I have table called Table1. I want to insert data into this table from Table2 but only the rows that are not already in Table1.
Both tables have the exact same structure/column names.
I'm guessing this requires a join of some type.
Let's say the table has the following structure
Table 1
Name Age Address
Dave....21...81 Grove
Mike.....30...52 Vauxhall Road
Table 2
Name Age Address
Dave....21...81 Grove
Mike.....30...52 Vauxhall Road
Dave....23...77 Temple Avenue
Ella......24...22 Bridge Street
Anne....35...55 London Road
I want to copy only the bottom three records from Table 2 into Table 1 since they do not exist in Table 1
The primary key would be a composite key with all three columns.
Transact SQL :: Updating Table Set Sent To 1 For All Status IDs
Nov 30, 2015SID statusid listindex listsize sent
1 12 25 25 1
2 12 25 50 0
3 12 75 150 1
4 14 25 25 1
I have a table like above where cid is unique but status is not for all the status id i need put 1 as they sent out .but till now i used max listindex because they used to send files sequentially but now list index is random so how to update sent to 1 for all the status ids.
SQL 2012 :: Allow For User Provided Number Of Columns In Select Statement
Aug 20, 2014I am currently trying to write a query that pulls a summation of item specific data from sales orders. For simplicity's sake, the column structure can be something like the following...
Item#,
PoundsDuringWeekNumber (this would be the current week number out of the 52 weeks in a year and the pounds of the item during that week)
However, those are not going to be the only 2 columns. The idea of the query is that the user would be able to provide the query a date range (say 2 months) and the columns would then morph into the following...
Item#,
PoundsDuringWeekNumber (current),
PoundsDuringWeekNumber(current - 1),
PoundsDuringWeekNumber (current - 2),
etc. etc.
PoundsDuringWeekNumber(current - 8)
Initial ideas where to create a function to execute the summation of the pounds during the date range of the week in question and execute it across the columns, but with the indeterminable number of columns, the query would not know how many times to execute the function.
Replication :: Snapshot Agent Updates Statistics
Jun 26, 2015We have transactional replication publications from SQL 2014 to SQL 20122008 R2. When we add new tables to the existing publication and start the snapshot agent, Snapshot agent updates the statistics for random tables on the publisher DB. It appears that all the outdated statistics are being updated regardless which table is being initiated. In a highly transactional environment we can't update the statistics every time. its causing huge issues for us. Is there any work around for this ?
View 8 RepliesTransact SQL :: Parameter Replace And Add Quotes
Sep 25, 2015Declare @tragetdb Varchar(max)
SET @tragetdb='xyz1'
Declare @RestoreCmd Varchar(max)
SET @RestoreCmd= 'RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT='+''+ ' @targetdb'+ ''
print @RestoreCmd
O/p:
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= @targetdb
But i am looking for
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= 'xyz1'
i tried using "" + "" , ' + ' , no luck..
How To Convert Float To Timestamp In Single Select
Mar 18, 2015how to convert float to timestamp in single select query..for exp. i have float as 1.251152515236 ,i want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 6 RepliesData Warehousing :: How To Use Temporary Table In SSIS
Jul 23, 2015How to use temporary table in SSIS ?
View 2 RepliesT-SQL (SS2K8) :: Dynamically Set Database
Jun 26, 2014A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
Reporting Services :: If Divisor Is 0 Print Field Value But It Displaying Error
Oct 8, 2015If divisor is "0" I need to print the Field, but for me it displaying #Error. If I pass any value it is working fine. It is not working only for the Field.
RULE: Fields!B.Value=0, PRINT Fields!A.Value.(IIf(B=0,A,A/B))
Working: IIF(Fields!B.Value=0,0,Fields!A.Value/IIF(Fields!B.Value=0,0,Fields!B.Value))
Not working: IIF(Fields!B.Value=0,Fields!A.Value,Fields!A.Value/IIF(Fields!B.Value=0,Fields!A.Value,Fields!B.Value))
A B Cal(A/B)
E 1 0 1
F 4 2 2
G 9 3 3
How To Set Date Format To DD/MM/YYYY
May 1, 2014When creating fields in MS SQL is there any way I can set the date format of the Date or DateTime characteristic to DD/MM/YYYY?
View 2 RepliesOnly Get Even Values Of Column
Mar 15, 2014Say my dataset has a column with numbers 1-15. Those numbers are attached to other information.
CUSTID Name
1 Jim
2 Pat
3 Mary
4 Sally
5 Jane
How can I pull only even customer IDs?
Will something like this work?
SELECT * FROM TABLENAME
WHERE CustomerID/2 = 'Even/Int'