Having table like below. Here want to cumulative the values quarter and half yearly wise...
declare @table tableÂ
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )
[Code] ....
Like wise the data should added...
I have a process to rollover prior quarter data to new quarter in a table.
For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2
Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.
Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like
1,4
2,5
3,6
I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.
I have a simple table of yearly financials to transpose:
Year/Field      Financial1 Financial2 Financial3...
2014Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2013Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
....
The number of years available is dynamic. I would like to retrieve it as follows:
                    2014          2013       2012    Â
.....
Financial1Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial2Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial3Â Â Â 1000000Â Â 2000000Â 3000000$
What is the simplest way to achieve this?
I'm using this query to to calculate yearly finance values.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance]
Now I need to multiply the [FinanceValue Variance] * 2.50 and for that how can I use the alias name as column in the query. I tried this but it says invalid column name.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance], [FinanceValue Variance] * 2.50 as [NewVariance] from Finance
SumofVariance output will be like 5690.5893656 Also how can I show the SumofVariance to round off 4 decimal places like this 5690.5894.
Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
Hi
I am very new to analysis services and using MDX.
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
----
Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The case statement will take paramter values when finished
----------------end of code portion
Is this possible or is it suppose to 'split' the columns when moving to SSRS.
Thans in advance
Dev environment - SQL 2008 Feb CTP, VS 2008
I'm developing one leave application form in that I've declared "No of days"column as nvarchar type..If the user taking half(1/2) day leave then have to covert that 1/2 into 4 Â hours itseems..since I'm trying to display the table data on datagridview while displaying that half day in gridview again it has to conver 4 hours into 1.2 format..
My Table design:
 Create table Leave_Form
(
 Employee_id int primary key,
 Emp_Date date,
 Emp_name nvarchar(50),
[Code] ....
Windows form:
How I can split the half hour in number from this string?
Â
20130329070000 (it's varchar)
Easy for the date
cast (left(20130329070000,8)as date)
I can achieve even the Half Hour
select
left(SUBSTRING('20130329070000',9,4),2)+':'+RIGHT(SUBSTRING('20130329070000',9,4),2)
But what I need is the Half Hour in numeric format, so 00:00
should be 1, 00:30 should be 22, 01:00
should be 3, 01.30 should be 4......23:30 should be 24.
I already did but I can't find the piece of code I used.
I have a text field which displays quarter using YYYYQ (i.e. 20142, 20151, etc...)
I need to convert the field to a datetime representing the last day of that quarter. Â For example 20142 would be 06-30-2014 and 20151 would be 03-31-2015.
I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that
How would I iterate through this table and do a cumulative sum on the value column :
I'm trying to get the following result:
I have table like below, its period wise ,here the value get cumulative period wise.
amtname period
10CHR201202
20TNG201202
10CHR201203
20TNG201203
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
I have a table like below
CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')
I need result like below screen shotÂ
here 'I' stands for In,and 'O' for Out, in this example first In time not available
I am trying to get count of  records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
I want to calculate year to month data for month wise . Ihave start range and end range table like below
Start range and end range table
Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015
[Code] ....
Then I want to pass this value one by one in below table for get value month wise
Main table
Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404
[Code] ....
The below query is for november month: (Apr to Nov)
select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
My expected output is
201411
201412
201501
201502
201503
201504
201505
[Code] ....
I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..
View 7 Replies View RelatedI have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
   S_DATE
     S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".Â
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
[code]...
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
View 8 Replies View RelatedI am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â
          InsCode value 1002 should always mapped to 1006
           InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
[Code] ....
I have a SQL database which uses Access 2000 as the front end to do all queries. Currently i have a query that lists the amount an employee has spent on purchasing products i.e.:
Employee Name - Code - Description - Unit - Price - Month
Joe Bloggs - zzz - Pack of 10 CDRs - 1 - £3.99 - October
I would like to add another column that lists how much theyve spent so far in the current year. The employee name and Month has a parameters of =@Employee and =@Month so when the query is run i can enter the name of the employee and the month which then displays the results.
I tried to create a query where i entered Month under column (in access) followed by Total as this is what i used to do in an Access database but didnt work - how could the same be achieved in SQL?
Thanks in advance
Hi all,
I have the Following Query
It is working fine if the first month is 01 and Second month is 12
but i want like this if the user selects 200103 to 200203 i want the
value of composite should be added from 200103 to 200203 in one row
and other row from 200203 to 200303.
like this if the user selects from 200004 to 200304 i have to get 3 rows..
that is Yearly data..so how can i modify the following query or any new query.
select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and '200312'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)
Thanks
I am attempting to sum the gas production for each organization number, and separate gas production into each year as such:
Organization_Number20082009201020112012
103 774 7313868470
But currently I am having trouble displaying this, and it's coming out as such:
Organization_NumberMonth20082009201020112012
103 1 774731386847NULL
103 2 810656654674NULL
202 1 27262702293725122048
202 2 2913205120202064NULL
I want to remove the month and have one total per organization number, as well as remove NULL as show as 0.
Code:
select *
from yearproduction
pivot(
sum(Gas_Prod)
for Year in ([2008], [2009], [2010], [2011], [2012]))
as YearlyProduction
order by Organization_Number, Month
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.Â
View 5 Replies View RelatedI have to calculate the Total number of days present and absent for a singel student.
AS of now i have 3 tables.
1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]
So now i have loaded all the data into the table.
I can fetch the counts for total present and absent
Query i have tried is
Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
Hi all,
I am trying to create a table of counts but it is running too long and would be a waste of paper so in essence I want the table to wrap at the 50% mark. As far as I understand theres no way to do that with just one table in RS so Im planning on using two tables and filtering them somehow.
I have been looking at the top % and bottom % filters but they dont seem to work for my purposes.
Heres what I got:
Program
Count
Program 1
40
Program 2
25
Program 3
23
Program 4
18
Program 5
10
Program 6
5
Program 7
1
Heres what I want to do:
Program
Count
Program 1
40
Program 2
25
Program 3
23
Program 4
18
Program
Count
Program 5
10
Program 6
5
Program 7
1
gte 50%
lt 50%
Does anyone know an easy way to split a data set by group at the 50% mark?
Thanks!