Transact SQL :: Sorting By Minimum Of Multiple DATETIME Columns
Apr 22, 2015
I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-
Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport])
Days Remaining (x8 columns) (named as "TS_" + Credential)
I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-
SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass],
[Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE
WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND
[code]....
I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'm trying to unpivot a table in SSIS: The pivoted table basically looks like
ID DATE1 TEXT1 DATE2 TEXT2
----------------------------------
ID1 D1 T1 D2 T2
...
The unpivoted result looks like
ID DATE TEXT
-------------------
ID1 D1 T1
ID1 D2 T2
€¦
It works, but the one problem I am facing is: If D1 IS NULL in the pivoted table then D1 in the unpivoted table contains some strange value that is neither NULL nor a valid datetime. In a data viewer I get the in the DATE field "Fehler: Die Parameter Year, Month and Day beschreiben eine nicht darstellbare Datetime." (i. e. "Error: The parameters Year, Month and Day form an invalid datetime."). The loading of the OLE DB target yields an error because the DATE value cannot be converted to a valid datetime.
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].
-- drop table #temp create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))
insert into #temp select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)
I want to pivot this. My expected result look like this.
ID - Description - ET_A_Status - ET_A_Date - ET_A_IsValid -  ET_B_Status - ET_B_Date  - ET_B_IsValid - ET_C_Status  - ET_C_Date - ET_C_IsValidÂ
1  - Test   - 'Ack'    - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'
Select ID, FIRST(forename), FIRST(surname) from table1 GROUP BY ID;
I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.
There are records like:
ID     forename    surname -------------------------------- 1   John   Kormack 1  James Dope 2  Erin   Dupes 3  Will Hugh 3   Walter Heisenberg
I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.
Row data ID 1 2 3 4 5 6 7 8 9 ... 98 99 100
Output will be like c1Â c2Â Â c3Â Â Â c4Â Â Â c5.... 1Â Â Â 5Â Â Â 9Â Â Â Â Â 13 2Â Â Â 6Â Â Â 10Â Â Â 14 3Â Â Â 7Â Â Â 11Â Â Â 15 4Â Â Â 8Â Â Â 12Â Â Â 16
WITH summary AS (SELECT tu.SequenceNumber, tu.trialid, tu.SBOINumber, tu.DisplayFlag,
[Code] ....
I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.
Sample Data SequenceNumber  TrialID     SBOINumber 1                          1           5000 2                          1           5000 3                          2           5000 4                          2           5000 5                          1            5001 6                           3           5001 7                          3            5001
What is the most efficient way to write an SP to tackle all kinds of combinations here (where a user could give any search input).I know this must be fairly common to come across this situation.I have written an SP which will take in all the parameters and based on "IF" statements and using "LIKE" in SQL, this SP returns search results.But I wanted to know if there was more efficient ways of doing this, as you can imagine you might end up having several combinations of IF conditions.
1 2015 ba1 137 HL EL Eco 2 2015 ba1 138 EL SL HS 3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS 2 15120002 2015 ba1 HL EL Eco 3 15120003 2015 ba1 SL EL His 4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL Â Â Drop table Bezeichnungen; GO create table Bezeichnungen ( Â Artikelnummer nvarchar(18), Â Artikelbezeichnung nvarchar(80), Â Artikelgruppe nvarchar(13), Â [code]...
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
SELECT Â Â Â Â Â Â Â Â Â ID1, Â Â Â Â Â Â Â Â Â ID2, Â Â Â Â Â Â Â Â Â Â type, Â Â Â Â Â Â Â Â Â (case when type = '1' then sum(value) else '0' end) as Value1, Â Â Â Â Â Â Â Â Â (case when type = '3' then sum(value) else '0' end) as Value2, Â Â Â Â Â Â Â Â Â Â (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3 FROM table1 WHEREÂ ID1 = 'x' and ID2= 'y'Â GROUP BY ID1, ID2, Type
Which returns: ID1    ID2       Type    Value1    Value2    Value3 005   11547   0         0.00        0.00       279.23 005   11547   1         15.23      0.00       0.00 005   11547   3         0.00        245.50    0.00
And I want to obtain this result: ID1Â Â Â Â ID2Â Â Â Â Â Â Â Value1 Â Â Â Value2 Â Â Â Value3 005Â Â Â 11547Â Â Â Â 15.23Â Â Â Â Â Â 245.50Â Â Â Â 279.23
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans AEDÂ Â Â BNTÂ Â Â 1Â Â Â 1 AEDÂ Â Â BNTÂ Â Â 12Â Â Â 1 AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1 AEDÂ Â Â SNTÂ Â Â 1Â Â Â 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans AEDÂ Â Â BNTÂ Â 13Â Â Â 2 AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1 AEDÂ Â Â SNTÂ Â Â 11Â Â Â 7 AFNÂ Â Â BPCÂ Â Â 8Â Â Â 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
this is more of a theoretical question and not necessarily for best practice...though if reliable and efficient, i'd use it.
a lot of my sprocs return xml and are then processed in xslt...so when i need to sort by date (in xslt), i pass the datetime to a function that ultimately returns a bigint...it seems to work, but i'm not 100% confident...
can anyone confirm that the resulting integer values can be relied on for sorting?
function return: cast((cast(@dt as binary(8))) as bigint)
small test:
sql Code:
Original - sql Code
declare @i int, @d datetime declare @t table (id int identity, dt datetime, bd binary(8), bi bigint) set @i=1 set @d = getdate() while @i between 1 and 20 begin insert @t(dt, bd, bi) select @d, cast(@d as binary(8)), cast((cast(@d as binary(8))) as bigint)
set @d = dateadd(mi, @i, @d) set @i=@i+1 end
select * from @t order by bi
DECLARE @i int, @d datetimeDECLARE @t TABLE (id int identity, dt datetime, bd BINARY(8), bi bigint) SET @i=1SET @d = getdate()WHILE @i BETWEEN 1 AND 20BEGIN INSERT @t(dt, bd, bi) SELECT @d, CAST(@d AS BINARY(8)), CAST((CAST(@d AS BINARY(8))) AS bigint) SET @d = dateadd(mi, @i, @d) SET @i=@i+1END SELECT *FROM @tORDER BY bi
I have the following table (which is an import from another system Ican't mod):-CREATE TABLE [tbl_wsg_maternity_observations] ([documentname] [varchar] (40),[clientguid] [decimal](16, 0) ,[docguid] [decimal](16, 0) ,[displayname] [varchar] (80),[valuetext] [varchar] (255) ,[valuenum] [float] NULL) ON [PRIMARY]GOWheredocumentname is the name of the documentclientguid is the unique identifier for my patientdocguid is the unique id for the documentdisplayname is the dataitem (e.g. diagnosis)valuetext is the "answer" (e.g. kidney failure)valuenum is used instead if the valuetext is an integer (e.g.number of toes)I am trying to split/change this table so that I have a different tableper document, with one row per patient occurance with the displaynamesas columns.I have been using the following but it is slow and for large tablestakes hours (literally) to run:-SELECT distinctclientguid,(SELECT DISTINCT case when t2.[ValueText] is null thencast(t2.[Valuenum] as varchar(10)) else t2.[ValueText]end FROMtbl_wsg_maternity_observations t2 WHERE 'How many vessels present incord' = t2.[Displayname] AND t1.ClientGUID = t2.ClientGUID ANDt1.docGUID = t2.docGUID) as [How many vessels present in cord],<SNIP...more identical lines, one per dataitem>INTOtbl_wsg_baby_delivery_detailsFROMtbl_wsg_maternity_observations t1WHEREdocumentname = 'Mat Baby Delivery Details'Does anyone have any ideas how to do this faster and preferably moresimply?Will
I am trying to sort alphanumeric varchar in a column.Elsewhere PATINDEX is used for sql, but the function is not available in Report Builder. How to create an expression/
I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information. I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns I will use Honda Accord as an example. Honda has made the Accord since probably the 80's I know that if I SELECT yearnum FROM YearNum ORDER BY yearnum
I am using C++ Builder too.. it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord. Is there anyway to filter out multiple years, so I could just have the regular order of years?
Has anyone ever looked at the way the grid data viewer sorts it data when clicking on a column header? If you click on a column header, something happens to the sorting of the data in the viewer, but it's not always clear to me _what_ is happening. It appears the data is sorted ascending on the column you clicked on. If you click again the sort order seems to inverse to descending. However, if you look closely, it turns out that the data is not always sorted correctly, especially when you click on an integer valued column.
I found this when doing a demo on the AdventureWorks demo extracting data from the SalesOrderDetail table. If you sort on the OrderQty column, data is correctly sorted in ascending quantities. However, if you click the column again, orders with an order quantity of 2 are displayed on top (while there are orders with a much higher order quantity) and if you scroll down the list, you notice that there is no clear sorting anymore. The same happens with other columns.
Is this supposed to work as I would expect it to do or is there a logical explanation for the behaviour I see?
-- Best regards,
Hans Geurtsen Docent Kenniscentrum
Info Support De Smalle Zijde 39 3903 LM Veenendaal The Netherlands www.infosupport.nl
We have a matrix report which displays columns in a default sorting order. This report columns vary dynamically depending on the user input.
e.g. If user wants to see the report for column Alfa, Beta , Gama then a report will be genarted with column Alfa, Beta , Gama sorted in alphabetical order.
Site %Risk Alfa Beta Gama
X 2 1 2 3
Y 10 4 5 6 However the users want the Columns to be sorted in the order which they provide the inputs e.g. if the user entered Gama, Alfa, Beta the report should display the columns in the same order instead of applying the default sorting order.
Site %Risk Gama Alfa Beta
X 2 3 1 2
Y 10 6 4 5
Any thoughts on ways to achieve this in SSRS matrix report would really help.
Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube
I have a table that has a unique ID and a datetime of when something changed.
See example: IDÂ Â Â TimeStamp 16094Â Â Â 2013-11-25 11:46:38.357 16095Â Â Â 2013-11-25 11:46:38.430 16096Â Â Â 2013-11-25 11:46:38.713 16097Â Â Â 2013-11-25 11:46:38.717 16098Â Â Â 2013-11-25 11:46:38.780
[Code] ....
Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.
I have multiple drop down lists that I would like to have send parameters to a stored procedure that does a select on the data. (Kind of like a search feature) But if a ddl isnt populated, I want that to act like a * and get all. For instance, I have a persontype, state, and country drop down lists. If I drop the state one down to WA, I want the gridview to populate with all the people in WA regardless or their type. If I populate type and state, have it give me both regardless of country. What does the stored procedure or select syntax look like for this? (What would the default values of my dropdown boxes?) my current sql select statement (The bottom 3 are bit fields which are throwing me off a bit too). do i need to have some IsNull statments around each variable in the select?SELECT
tblPerson.PersonTypeID = @PersonTypeID and tblPerson.StateID = @StateID and tblPerson.CountryID = @CountryID and tblPerson.SchoolID = @SchoolID and tblPerson.InterestChapterLeaders = @InterestChapterLeaders and tblPerson.InterestMembers = @InterestMembers and tblPerson.InterestUniLeader = @InterestUniLeader