Transact SQL :: Break Down Content Based Of Hourly Basis
Oct 12, 2015
I am trying to break down the content based of hourly basis. It works fine when there are values for that specific hour but if there are entries or values for a specific hour then it returns null instead of 0. How not to get null instead get zero.
Here is the code below:
With temp_exp As
(Select pl.state,Cast(signeddate As date) As signatureDate, signeddate As DoneTime From contract c with(nolock) where c.signeddate>DateAdd(Day, Datediff(Day,0, GetDate()), 0)
)
Select
Sum(Case When CONVERT(varchar(8),DoneTime,108) Between '07:00:00' And '07:59:59' Then 1 Else 0 End) '8AM',
I am using the following query in a view to retrieve the latest 24 hourly records for a site.This returns 24 hourly records for the last day of measurements at a Site.This works great. However, I now need to retrieve the latest hourly records from the current hour. For example, hours will run from 00:00 to 23:00 and if the query is executed at 15:00, I will return only hourly records for 00:00 to 15:00 etc. I believe I need to filter the result set or modify the query to exclude records greater than the current hour.
I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to run this script on a daily basis and save the results as a .csv file.
I've tried creating a job in SQL Manager - that didn't work. Then I found a tutorial that had me use MS Visual Studio... couldn't get that to function either.
what program I should be using, and how to go about creating this as a task / job that can run on a scheduled basis?
I have a query which sends email notification of count of pending name and suggested name.. on a daily basis. So this works fine but now I want like if the pending and suggested are ' o count' I don't want the email notification. It should not send an email, if it is o count, but if we have the number for pending and suggested this email should be sent.
Tried to customize footer content by setting variable in code using VB setter/getter methods in the body of the report. Successfully able to change footer content by changing variable value via setter method in the header, but not from the body. I have report content in two table in the body of the report. I would like to change the footer when second table is displayed. Is this even possible?
I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:
-Delete from table where Date_Imported like '2011-11-18%' -Delete from table where Date_Imported like '2011-11-18' -Delete from table where Date_Imported = 2011-11-18
It comes back saying "0 rows affected", even though I know there are records with that date in the table.
Do some one has idea how we can create pagebreak in matrix based on RowNumber.Say I have 40 rows in matrix(I don't have group on which I can create a group by setting PAGE BREAK AT THE END)
=ROWNUMBER(NOTHING) MOD 10 = 0 ,TRUE,FALSE
I don't have any distinct value on which I can create Group like
= IIF(Parameters!PageBreak.Value,Fields!CategoryID.Value,1) and setting Page Break At The End
I have a detailed report in ssrs in which data can come from start date and end date parameters.
but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and sooo on to sep month data in new tab when i export to Excel.
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment]( [EQU] [VARCHAR](50) NOT NULL, [Notes] [TEXT] NULL, [Facility] [VARCHAR](50) NULL) INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility]) SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA' SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
I have a detailed report in ssrs in which data can come from start date and end date parameters.but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and so on to sep month data in new tab when i export to Excel.
ID Name Date 1 A null 2 B 01/01/2012 3 C 01/02/2013
Also, I have a sort parameter @sort and values are (Name, ID, Date)
I want to apply page break whenever @sort=Name. There should be no page break when user selects @sort = ID or Date. Page break should happen only when @sort value = Name
it should be like this...
Page 1: ID Name Date 1 A null Page 2: ID Name Date 2 B 01/01/2012 Page 3: ID Name Date 3 C 01/02/2013
I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:
Row Leg-ID From To On DateFrom DateTo DOW 1 ABC123 AAA BBB CCC 01JAN15 14JAN15 1 3 ABC123 XXX YYY ZZZ 14JAN15 14JAN15 1
The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:
check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..
I have two columns, one column has a document ID and a given document can have many pages. The second column has the pages. Now I want to find out when the page number is broken. For example, if doc ID 1 has 3 rows and each of the three has 1,2,3 and then the fourth row has document 1 but the value jumps from 3 to 7 and then goes to 8,9,10 and then jumps again and starts from 17, i want to have the ranges identified.
1 Banana2 Orange3 Apple Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
I have a report with several matrix objects. The data contained in each matrix is simple. One matrix has one column of labels and a column of data. The other has 3 columns of data. Both matrix's only show about 25 rows of data. A variable amount of data is displayed above the matrix's so that sometime the render across a page boundary. Whenever this happens the matrix doesn't render rows down to the bottom of the page. A considerable amount of empty space is left on the page, the completed matrix is displayed on the next page. I'm wondering how to get the matrix to render in this empty space.
This problem only occurs when I view the report on screen, in the Visual Studio "Preview" window, or from the Report Server web site. Tiff, pdf and printed output doesn't contain the extra space.
I've tried adding my matrix to a List and a Rectangle to see of this would fix the problem but it didn't help.
I've check the dimensions and margins or the page and I don't think I have any sizing problems, everything should fit on the page.
select [Parent Name],[ID],[Year],[Sales Name], sum([Total VtM]) as 'Total Sales' from RegData group by [Parent Name],[ID],[Year],[Sales Name] order by [Total Sales] desc
I need to modify this query to get the top 5 of each category based on Total Sales amount.
Display customized data based on customized where statement from UDT.
The UDT is a parameter inside of a stored procedure.
Problem:
A parameter from a stored procedure is @communication communications readonly
This parameter is a User-Defined Table Types (UDT) It contains criteria based on end-user's selection from a filtration functionality from a webpage.
Four example of filtration critera based on four end-users' selection that is located inside of a table below.
Each UDT table contains different criteria:
Number Criteria ------ -------- 1 Phone 3 Email
Number Criteria ------ -------- 1 Phone 2 Cellphone 3 email
Number Criteria ------ -------- 4 None
Number Criteria ------ -------- 2 Cellphone 1 Phone
Is it somehow possible to use the criteria's value as a column name in the where statement? I want to filtrate the data of the table datatable based on id, name and the UDT's criteria.
I was enable to apply the criteria inside of a variable by looping the UDT's table but the next thing is to paste it in the where statement after "id=1 and name" below
select * from datatable where id = 1 and name = 'Cost'
How should I do it?
[URL] .....
create table datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) );
I am looking for some study meaterial focussed on performance based transact SQL development. I am a fairly well seasoned (3+ years self taught), and I am getting into situations where different query constructs yield the same results, but the performance (execution time and disk I/O) varies.
I have found that sometimes nested select statements execute faster than joins, but usually the opposite is true, for example, and I would like to learn why.
TeamStatus T 1 Complete or Escalate T 2 Pick Up T 2 Resolve Case T 1 Pick Up T 1 Complete or Escalate T 1 Pick Up T 1 Complete or Escalate
I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,
Group TeamStatus Group 1 T 1Complete or Escalate T 2 Pick Up T 2 Resolve Case
Group 2 T 1Pick Up T 1Complete or Escalate T 1 Pick Up T 1 Complete or Escalate
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' = CASE WHEN open.date >= 30 THEN '1' ELSE '0" END
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate order by case when pr.status=2 then 1 when pr.status=4 then 2 when pr.status=3 then 3 when pr.status=1 then 4 end ,pr.sdate )pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
I'm running into an interesting issue when returning a response from an XML-based web service. The following code returns good values from PRINT
@response DECLARE @address varchar(50) = '90210' DECLARE @URL varchar(MAX) SET @URL = 'https://somewebsite/map.xml?zip=' + CASE WHEN @Address IS NOT NULL THEN @Address ELSE '' END SET @URL = REPLACE(@URL, ' ', '+')
[code]...
However, on some calls to the web service, the value returned is longer than 4000 characters. In these instances, it appears as though it breaks the whole thing and returns nothing. However, if I change the @Response parameter to nvarchar(max), it never returns anything, even on responses that are shorter than 4000 characters. what the fundamental difference is here between nvarchar(4000) and nvarchar(max) with respects to how it stores responseText. More importantly, how to get this to return a value even when it exceeds 4,000 characters?
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5. Desired data format: Name1 Name2 Name3 Name4 Name5. Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally Bush/Jim/Sr/Etal/Trustee
> SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans FROM mytab WHERE mrno = 'MR1514' and tranno = 1111 ORDER BY no_of_trans tranno medrecno createdon createdat nooftrans
I want to frame a range of data based on particular group of columns
If OBJECT_ID('tempdb..#ResellerRange') IS NOT NULL drop table #ResellerRange create table #ResellerRange ( ResID varchar(10) , amt decimal(18,2) , serialno int)
insert into #ResellerRange ( ResID,amt, serialno ) values ('Raja',10,67),('raja',10,68),('raja',10,89),('Prabu',20,56)
I want below output
resid amt min max ---------------------------------- raja 10 67 68 raja 10 89 89 Prabu 20 56 56
