I have 2 tables below:
Product No Quantity
Product No Grade Quantity
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Product No Grade Quantity
A Good 1
A Normal 0
A Bad 0
B Good 2
B Bad 0
C Good 3
C Normal 0
C Bad 0
I have this query that I have been using to find duplicate records works great except for now. The logic I am adding is pcs_rreas <> 'NG'. When I add this it does take out the NG, but it also excludes the reocords that have NULL data in this field. I don't want that to happen. How can I fix this? I tried adding (pcs_rreas <> 'NG' or pcs_rreas is null) but nothing is pulling now.
SELECT pcs_id1, pcs_rreas, pcs_lname, pcs_fname, pcs_minit, pcs_degree, pcs_xtyp, pcs_office, pcs_dba, pcs_dob, pcs_sex, pcs_eff, pcs_trm, pcs_spec1, pcs_spec2, pcs_spec3,
pcs_spec4, pcs_tax1, pcs_ssn, pcs_altid, pcs_upin, pcs_medic, pcs_mcaid, pcs_ecs, pcs_npi, pcs_status, pcs_dir, pcs_den, pcs_www, pcs_hold, pcs_email,
pcs_misc1, pcs_misc2, pcs_newdt, pcs_newby, pcs_chgdt, pcs_chgby, pcs_malp, pcs_pf, pcs_ctl, pcs_sys, pcs_pay, pcs_ann, pcs_bcert, pcs_pass, pcs_w9,
pcs_taxex, pcs_tax2, pcs_type, pcs_rreas, pcs_routo, pcs_rtime, pcs_rdate, pcs_force, pcs_flag, pcs_v419, pcs_bcity, pcs_bstate,
I have a matrix report:
My Expression in the data fields inside design mode is:
IIF(Fields!Row_num.Value mod 2 ,"White","LightGrey")
I am using a Dense rank Function at the dataset level in order to group id column wise. So Fields!Row_num.Value comes from that set.
Earlier it was BLANK values: Please see below for reference.
Tried IsNull on SQL Server already and does not work because there are no NULLs in the data I am retrieving. The empty cells happen when the matrix creates the crosstab report - where there is no data for a column. Everything else works well except the BLANK values being not colored as you see in the screen shot, im using ISNOTHING function to achieve those 0's if NULLS inside the report. But though we have a value inside the cell coming from report it does not colour the entire group.
My requirement is coloring the entire column group irrespective of the NULLs' or Blanks.
Have also tries several functions, but of no use. I am missing with a tiny thing I guess which I am unable to figure out.
Other Functions Tried:
=IIF(VAL(ReportItems!ROWCOLOR.Value) MOD 2,"WHITE","LightGrey")
=iif(RunningValue(Fields!City.Value,CountDistinct,Nothing) Mod 2, "LIGHTBLUE", "SILVER")
=iif(RunningValue(Fields!DQLogDateTime.Value,CountDistinct, Nothing) MOD 2, "LightGrey","White")
Now i must restore a database from a backup file of MS SQL Sever 6.5,but this file is bad. When i restore database from this file, SQL Server tell me that the format of this file is not file format of SQL Server 6.5.So that I must repair this backup file firstly. I don't know database file format of SQL Server 6.5.View 2 Replies
I have Oracle query which seperates a text with commas to column data. Can we achieve this in SQL Server?
with t as (select 'abcd,123,defoifcd,87765,aoiwerwe' as str from dual)
select level as n, regexp_substr(str,'[^,]+',1,level) as val
connect by regexp_substr(str,'[^,]+',1,level) is not null;
I'm working on SQL Server 2012, Windows 7.
I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.
GUID LastName FirstName
GUID1 Mulder Fox
GUID2 Scully Dana
GUID3 Skinner Walter
So I'm looking for a result like:
PID1 GUID 1 Mulder Fox
----- GUID 2 Scully Dana
I've been trying to access the file table shares exposed on a server (I can select, delete, update, etc via SQL) via untransacted access with no luck (the share just sits there doing something when I try to access it, no error message of any kind).
So I thought I'd look to see if there were any open file handles via
SELECT * FROM sys.dm_filestream_non_transacted_handles;
I found there were 10 non transacted handles open, and they've been open for 3 days for automated processes and the files are about 1-2Kb in size.
so I tried to kill them via
that's normally not something that takes very long (in fact when I cleared two for a different database it took just a second or two).
So leaving the stored proc running, I figured I'd connect and fire up Adam Mechanic's sp_whoisactive.
I see the processes to kill the open file handles, with a wait state of FFT_NSO_FILEOBJECT
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
=CountDistinct(IIf(Fields!Region.Value="AP", "PC_With_Active_SCCM_Clients"),(Fields!Name.Value, "PC_With_Active_SCCM_Clients"),Nothing)/CountDistinct(IIf(Fields!Region.Value="AP", "Total_Count_Of_AD_PC"),(Fields!name.Value,
Below is the error message....
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
I have this job that runs and Kills blocking sessions that are longer than 10 mins. I need to add a list of login's that are exclulded from this Job. I cannot seem to get it to exclude specific users in this script. Below is the script.
SET NOCOUNT ON
-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
so you have two place to configure alias, in SQL server configuration manager under aliases tab on and on the cliconfg.exe tool on a different machine. If I want another server to see this sql server by the alias name I will need to configure the alias on the cliconfg tool on the client machine. It looks like as long as I have the alias configured in the cliconfg on the client machine, I don't need the alias to exist on the config manager, the client can still see SQLserver using just the alias on cliconfg...if so, I wondering under what situation I would really need the alias on the configuration manager?View 7 Replies
HOW TO SELECT LAST ROW IN GROUP BY CLAUSE AND JOIN LAST ROW WITH ANOTHER TABLE IN ONE QUERY?
Query 1 -- select last record
(SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,*
How to link Query 1 with table Customer in one query?:
Desired output in ONE query:
I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.
Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?
I have to ask first, if in the command "Update model from database" the SQL Server Data Tools are involved?View 2 Replies
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
3- Rename Newdb to current DB.
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)