Year (date) Totals Comparing To Column Values In Same Table?

Jan 31, 2014

how to write condition for self table year records, such 2012 name and acctno match with 2013 name and acctno then total, provided below,

create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
insert into #tab1 values (1003, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')

[code]....

i would like to get 4 columns output

how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,

MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key

so the request is

need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below

1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts Veriverted'
3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts NONVERT'
4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 last year accounts'
5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 This year accounts'

for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as

insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')

for ex 2)

insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')

for ex 4) 2012 records there is not match acctno and name in 2013 recods

insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')

for ex 5) 2013 records there is no match of name and acct no with 2012 records

insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')

Expected Results (just for format)

AcctTypeDescription,SumofShares, OtotalutStand
'2012 and 2013 accts UN Veriverted',2700,234
'2012 and 2013 accts Veriverted' ,2890,234
'2012 and 2013 accts NONVERT' ,4533,325
'2012 last year accounts' ,2334,567
'2012 This year accounts' ,2222,877

View 9 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

View 6 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

SQL Server 2012 :: How To Get This Year And Last Year Totals In Two Separate Columns

Jun 19, 2014

I have two queries that give me the total sales amount for the current year, and the last year.

SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'

[Code] ....

I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.

This is my query without the columns I need:

SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'

[Code] ....

View 1 Replies View Related

Comparing Date With NULL Values

May 8, 2001

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

View 4 Replies View Related

Comparing Date Records In One Column To Know Missing Date Interval?

Jun 6, 2012

I need to compare the contents of a column.

I have a table with 1 column. Below are the sample contents..

DateInterval
2012-06-01 00:30:00.000
2012-06-01 01:00:00.000
2012-06-01 01:30:00.000
2012-06-01 02:00:00.000
2012-06-01 02:30:00.000

[code]....

as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:

DateInterval
2012-06-01 18:00:00.000
2012-06-01 20:30:00.000

if it is not doable, we can get the nearest record of all missing records like this:

DateInterval
2012-06-01 17:30:00.000
2012-06-01 20:00:00.000

or this:

DateInterval
2012-06-01 18:30:00.000
2012-06-01 21:00:00.000

or this:

DateInterval
2012-06-01 17:30:00.000
2012-06-01 18:30:00.000
2012-06-01 20:00:00.000
2012-06-01 21:00:00.000

View 6 Replies View Related

Comparing Column Values Among 2 Or More Rows

Jun 29, 2012

I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.

Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.

Three tables to note in bold, with notable fields in italics below them:

EmployeeData
HRID (identity)

ResourceAllocation
ID (identity)
[Last Name] (linked to HRID)
Project
[Resource Start Date]
[Resource End Date]
[Percent Utilization]

tblHCvalues
RAID (linked to ResourceAllocation.ID)
a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...

And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have

Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.

We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want

All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:

Here are the rules, and they should work for any number of multiple resource allocations for one employee:

Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)

I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?

View 6 Replies View Related

Transact SQL :: Date Time Casting - Comparing Values And If Not Same Then Populating Record

Sep 9, 2015

I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.

I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"

Condition in Where clause:

cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime) 

Note: Both col1 columns are of type nvarchar 

View 6 Replies View Related

Integration Services :: For Each Loop - Place Full Set Of Values For Every Date In The Year

Jun 15, 2015

I have a table with 3000 values and What I need to do is place a full set of these values for every date in the year 2015. How to achieve this through SSIS?I know we can achieve through SQL using while loop.

EG: 1-1-2015  a b c d 
       1-2-2015 a b c d  like wise 12-31-2015 a b c d .

How to create the package.

View 8 Replies View Related

Comparing Values Within Same Table

May 1, 2014

I would like to compare some values in two columns which are in the same table. I want to check that there are no differences between the values if the ID is Test1 and Test2

Example table

IDValue1Value 2
TEST1HouseTango
TEST2HouseTango
with test as (
select * from ExampleTable where ID= 'TEST'
),

[Code] ....

View 4 Replies View Related

Extract Date,month, Year From The Date Getting From Sql Table

Apr 14, 2008

Hello All,
i have three textboxes in a page and i want fill those textboxes  with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne

View 3 Replies View Related

Extract Month & Year From Date Column

Feb 6, 2008

hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

View 11 Replies View Related

Totals For This Year AND Last Year

Feb 25, 2008

I want to write a query which returns count values sold of each product for last three months... which I can write...the hard bit is, I also want to return what the count value was last year for this month

i.e.
Feb Jan Dec
2007 2006 2007 2006 2007 2006
Soap 2 9 1 2 9 9
Kitchen Towel 1 34 3 34 23 45
Washing Up Liquid 33 3 5 6 7 33

How can I write a query which will show count totals for both years??????
Thanks

View 7 Replies View Related

SQL Server 2008 :: Comparing 2 Table Values

Jun 8, 2015

I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.

View 9 Replies View Related

Display First Date If Split Into A Year And Month Column?

Mar 27, 2012

How can I find the first date if the date is split into a year and month column?

I thought of using the Min function, which would work for the year column, but it would probably just return a 1 everytime in the month column.

View 2 Replies View Related

Comparing And Rounding User Input Variables To Table Values?

Oct 24, 2006

HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the  price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice! 

View 9 Replies View Related

Adding Column Where Adding Year To Date In Date Format

Aug 6, 2013

What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?

View 1 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

Comparing A Column List Split To A Table.

Jul 23, 2005

Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo

View 3 Replies View Related

SQL Server 2012 :: Date Table For Financial Year

Dec 23, 2014

I need to create a table which holds date information for our financial year.

I have all the dates between now and 2045 and the start of the week and the end of the week. What I also have is the first sunday of the previous week in the spreadsheet too.

Please see below attachment

What I need to autofill once I import these three dates into a database is the week and the month.

The difficulty surrounding the month is that, we start a new month on the FIRST Sunday of the month.

So dates 07/04/14 to 04/05/2014 would be month 1.

Month 2 would begin on 05/05/2014 as it is the day after the first Sunday of the month, and so on....Month 5 would start on the 04/08/14.

Need to script something that would automatically calculate the week and month for me on the basis on above, if I have the start date, end date and 1st sunday already in a table?

View 9 Replies View Related

How To Do A Year-to-date SQL Query Where Year Commences In August?

Jul 20, 2005

Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View 2 Replies View Related

Comparing Datime(SQL ) Year, Month, Day,Time

Sep 21, 2007

 hi    i want to compare tow dates in my procedure, comparing all(year, month, day, time).can anyone help me. thanhs.

View 1 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

SQL Server 2012 :: Comparing Data For A Certain Day Of Week By Month For Every Year

Oct 21, 2014

I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.

View 9 Replies View Related

Lookup / Merge Join / Script - Howto Look Up Values By Comparing To A Range Of Values?

Jun 4, 2007

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:



"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _

" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _

" AND street_suff = @street_suff AND street_post = @street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"



My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value



Any suggestions?



thanks for your time...

View 5 Replies View Related

Fiscal Year Date Help??? I Need The First Day Of The Year To Be 01/27.

Feb 4, 2008


Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck

View 2 Replies View Related

Converting Values In Column To A Date Value

Apr 15, 2015

I have a columns with datatype varchar(50) which has date values in it.

I am trying to convert the values in the column to a date value,but i am getting conversion error.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

select last_update from importtest where convert(datetime,last_update)<convert(datetime,'24 Dec 2014')

View 3 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Integration Services :: How To Load Multiple Date Format Column Date Into Table Using SSIS

Jun 15, 2015

i have a excel file in which i have a date column it having the below date formats below 

Install Date

20140721

31.07.2014

07.04.2015

20150108

20140811

20150216

7/21/2014

11.08.2014

07.08.2014

So using SSIS how we would load this date column into the table into one format like dd/mm/yyyy or any single date format

View 6 Replies View Related

Table Column Names = Dataset Column Values?!

Apr 28, 2008



I need to create the following table in reporting services



PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6





My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???


Thanks in advance!!!

View 1 Replies View Related

Calculate Relative Totals With Absolute Values

Feb 28, 2015

Have a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included.

---------------------------------------------------------------------------------------------------
-- Basic simplified structure of maintenance and usage tables
---------------------------------------------------------------------------------------------------
CREATE TABLE #Maintenance (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Maintenance PRIMARY KEY,
MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset
HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance
)

[Code] ....

---------------------------------------------------------------------------------------------------
-- Required output view so that at any given point in time I can determine hours remaining
---------------------------------------------------------------------------------------------------
-- AtDate HoursAvailable
-- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown
-- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset
-- '11-Jan-2015 07:30' 8.9 -- subtract 1.2
-- '11-Jan-2015 11:10' 2.9 -- subtract 6.0
-- '15-Jan-2015 00:00' -0.1 -- subtract 3.0
-- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset
-- '02-Feb-2015 13:00' 10.0 -- subtract 2.2

View 4 Replies View Related

Comparing Today's Date With Date In Field

May 19, 2008

I want to be able to compare today's date with the date that is in the database. Right now I have:

Select Field1, Field2
FROM table 1
Where Year(TS_Date)=Year('3/1/2006')and Month(TS_Date)=Month('3/1/2006')

Where I have to change the date every month. Is there a way to use GetDate or another type of code so it could automatically update every month.
Any suggestions would be very greatful.

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved