T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates
Jul 31, 2014
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–
I have an Issue. Please find the sample of my data source
StudentID Fee 1 Fee 1 Currency Type Fee2 Fee 2 Currency Type
1 10 USD 20 INR
2 45 EUR 20 USD
If I need to transform this data, it should be like below Table.
ID StudentId FeeType FeeAmount CurrencyType
1 1 Fee 1 10 USD
2 1 Fee 2 20 INR
3 2 Fee 1 45 EUR
4 2 Fee 2 20 USD
I have selected columns Fee 1 and Fee 2 in Unpivot Transformation. I set the "FeeAmount" as Destination Column Name and "FeeType" as Pivot Key Value Column Name. I left the Pivot Key Value as is for all the selected columns. So far no issue that I am able to get the Fee Type and Fee Amount columns for my destination table.
But the issue is how to get the Currency Type for each corresponding coulmns? I was not able to get the currency type for each student. I hope you got my point. Please let me know If have further queries and hel me out to find this.
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006 This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table. How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006 1 Sovereign RUSSIA 05 null 173.21 2 Sovereign RUSSIA 07 102.99 102.22 3 Sovereign RUSSIA 10 114.33 104.63 4 Sovereign RUSSIA 18 115.50 145.50 ...
sql server table
create table tblData ( DataID int, Ref int, Sector varchar(20), Name varchar(20), Date datetime, value decimal(6,2) )
DataID Ref Sector Name Date value 1 1 Sovereign RUSSIA 05 28 Dec 2006 null 2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21 3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99 4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22 5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33 6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63 7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50 8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50 ...
Sales table Field Type salesidint(11) fidint(11) fsdatevarchar(10) quantityint(11) eidint(100)
employee table Field Type eidint(100) fnamechar(100) lnamechar(100) dobvarchar(100) genderchar(100) addressvarchar(100) statuschar(100) contactint(100) emailidvarchar(100) salaryint(100)
select f.furniturename as furniturename,f.quantity as tquantity,s.quantity as squantity,e.fname as fname,e.lname as lname from furnitures f inner join sales s inner join employee e on s.fid=f.fid and e.eid=s.eid";
furniture Name Total Quantity Sales quantity Remaining Quantity person book rack 40 2 roshan shri book rack 40 3 roshan shri
It displays 2 records , but it should display in 1 record.output should be like this
This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:
"Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "Table3.DocketId" could not be bound."
What is the cause of this issue?
Select Table3.DocketId, UP.AssignmentType, Up.AssignedStaff From ( Select distinct Table2.DocketId,
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,
Here is my code
@syear nvarchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
Is it possible to get difference between two dates - first one and last one per every group in table (records are sorted by ID)?
ID Group Date 739 Group 1 2015-04-17 14:27 740 Group 1 2015-04-19 06:51 760 Group 1 2015-04-19 11:51 762 Group 1 2015-04-21 09:30 763 Group 1 2015-04-20 09:20
I have a DATESMTD function which is not working. This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero. See below my formula:
MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])
Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.
So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date for Sept instead of zero. What can I do to make the system insert zero in the month to date column instead of the $200.00. What am I doing wrong in the formula.
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using the following query
DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END + CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END
I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015.
I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.
I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:
If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.
TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.
I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:
I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:
I have a table with Employee, it lists their department, and has a row for each "position/promotion". (Just the pertinent fields below in the example shown of course and only a single employee for example)
An employee may have a single row in a department, or if they were promoted from line staff to manager they could have many.
Additionally, an employee may move around departments during their employment.
I need to get the min and max dates (basically duration) of their time at each department.
The issue is because they repeated departments when I go to group it loses movement between departments.
I played with RANK and ROW_NUM hoping I could get it to group / partition on each department and repeat the number for each department so the first department 5 would be RowNum=1, then the next group of Dept 7 would be =2 and the last group of Dept 5 would = 3 and then I could min/max on that grouping, but I didn't have any luck with that approach either.
My table: CREATE TABLE [dbo].[EmployeeDepartmentHistory]( [Employee] [int] NOT NULL, [DepartmentId] [int] NOT NULL, [StartDt] [date] NOT NULL, [EndDt] [date] NOT NULL ) ON [PRIMARY]
Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.
select ( Column1 ,Column2 ,Column3 ,Column4 ,coloumn5 from Mytable ) x pivot ( case when Column1 = 6 then sum(Column3) else max(Column4) End for coloumn5 in (' + @COLS + ') )p
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
INSERT INTO #SessionTest SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.
SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated, datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails] WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7
However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..
I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.
SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated, datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails] WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6
In our contract management system, each contract has over 100 reference fields attached to it. These are all stored in single table with contract ID, reference GUID and value as the columns.
So you will have multiple rows for each contract....one for each of the reference fields and then the value attached to that reference.
I want to return the data so there is one row per contract with the reference fields as columns and the reference field values as the column data.
Can this be done using PIVOT as I have tried but not had any success?
I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):
AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'
I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin --output should be yyyy-01-01 00:00:00.0 where yyyy is current year SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE())) + '0101') AS nextyrbegin --output s/b nextyear-01-01-00:00:00.0
Then, change the CASE statement to read:
AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)
I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.
Below is my table, current result and desired results.
My Table:
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS ( SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014' UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014' UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014' UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014' UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014' ) SELECT * FROM SampleData
