I have a sql query that gets the count of exams held in each month.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
if I create a dimension from fact table, then it only has members which appeared in fact table. but if I create dimension from dimension table, then it shows all members, even those with no data. how can I show only those with existing data in member list?
I have a report that has 3 graphs and two tables that render to 5 pages in pdf. Some users should only get a subset of the 5 elements and some need all 5. When I set the Hidden property (in the designer or via a parameter) for any of the elements they become invisible but in pdf but the report still prints out a page for the hidden element. It works fine in the designer and rendered to html and excel but I get an extra page in pdf. it is like the hidden element still takes up the same amount of space but is just hidden.
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
I have a requirement where i have to insert empty string in column whose datatype is decimal.
I have to replace the column value if it is null then insert it as ' '.
Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.
IIF(Column1 IS NULL, ' ', Column1))
I used TRY_PARSE but it insert null value if there is conversion error.
A Fact table <<Company Revenue>> 2 Dimension <<Month.Dim>> , <<Company>>
I have 4 company say (A,B,C,D) but i have record for only company A & D that too only for December month .
When I create a SSRS report out of the cube i want a matrix where all the company names to be displayed in the rows & all the months in the columns and company's revenue in data
What my report is displaying :
Company | December ------------------------------------------------------- A | 20000 ------------------------------------------------------- D | 3900 -------------------------------------------------------
what is needed :
Company | January | February | March | ........... | December ----------------------------------------------------------------------------------- A | | | | | 20000 ---------------------------------------------------------------------------------- B | | | | | ---------------------------------------------------------------------------------- C | | | | | ---------------------------------------------------------------------------------- D | | | | | 3900 -----------------------------------------------------------------------------------
I have setup ssl encryption for SQL connections by using a self-signed certificate (not the best I know) and setting "Force Encryption" to Yes. Now I am trying to show that the connection is indeed encrypted and I like to see which the ssl-key is used for a connection; preferably the one that I provided. How can I do this? I am just not comfortable trusting the OS or SQL to do what I ask.The query "SELECT encrypt_option FROM sys.dm_exec_connections" Shows incrypted but no extra info.
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.
FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.
I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.
Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.
There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.
How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb go create table #ItemLedgerEntry ( ENTRYNO INT NOT NULL , ITEMNO VARCHAR (50) NOT NULL , POSTINGDATE DATETIME NOT NULL , ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0 group by itemno order by count(*) desc
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y' ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10)) END AS 'System Verified'
insert into #sample values ('customerid','15339119') insert into #sample values ('Title','15339119') insert into #sample values ('firstname','15339119') insert into #sample values ('prevcr','2146822710') insert into #sample values ('currcr','2146822710') insert into #sample values ('brandcode','2146822710')
I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:
This is printing everything in one line: SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
How do I do this: SET @BODYTEXT = ' Dear ' + @fname + ',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
Also how can I create a table in this variable, something like this:
(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME
(Course Name) (Last Completed) (Now due in Month/year)
My T-SQL code:
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100) ,@EMP_ID INT ,@fname varchar(100)
declare @xmldoc as xml select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>' select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
I explicitly set one column to have text qualifiers in a flat file connection mgr and specified to use double quotes as the qualifier, yet in the output file, the column is not qualified. What did I leave out ?
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Table2HTML] ( @TABLENAME NVARCHAR(500), @OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98 Unclosed quotation mark after the character string '</table></body><'. Msg 102, Level 15, State 1, Line 98 Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows FOR XML RAW ('node'), root('nodes'), ELEMENTS
I have been given a request at work that requires us to run the SQL scripts that are held in a report configuration table and output the results as .csv or.xls files in a desired folder with a file name that is also specified within the report config table.
An example of the table is as per script below with column A being the desired file name to be created and Column B contains the script that will be executed.
Ideally I require a script that i can drop into a Stored Proc that will run down each row in the table and export and create each rows results as a separate file in a desired folder.
------------------------------------------------------------------------------------------ -----SAMPLE TABLE SCRIPT ----------------------------------------------------------- ------------------------------------------------------------------------------------------ /****** Object: Table [dbo].[Test_Table_PS] Script Date: 21/09/2015 09:14:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Test_Table_PS]( [File_Name] [nvarchar](100) NULL,
