Help Needed For Date Field (Age) Calculation In SQl Query

Aug 30, 2007

Hi experts,

I am working on SQL server 2005 reporting services and i am getting a
problem in writting a query.

Situation is given below.

There is one table in database Named Child

Now i have to find the All childrens whoes Age is 13 years Base on
Some given parameter.

If User select Augus 2007 then It has to calculate the Childs who born
in August 1994 And if he select September Then query

should show only those child Who born in September 1994 and so
on..... And use can select another year month also like

August 2009 ...


I am writting the following query

Select Child_Name, DOb from Child
where ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1
And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)

If i know already month and year then i can write easily parameter1
and parameter2 But since these are comming from user so i m not
finding how to handle this.


Now please suggest me what i have to write in Where statement I think
a lot but not getting any idea about it.



Any help wil be appriciated.

Regards
Dinesh

View 2 Replies


ADVERTISEMENT

Some Kind Of Calculation Is Needed For This Query?

Sep 19, 2005

Hi,

I have a query which gives me the following results:

lLedgerCode sGLCode bDebit TotalSum sGLDesc
61 6843000701 0 600ALPS Holding
33 8345000701 0 1116ALPS Premium Due
56 1000000701 0 1116Regular Premium Income
63 6836000701 1 516ALPS Group Holding
61 6843000701 1 600ALPS Holding
30 6842000701 1 600ALPS Policy Clearing
33 8345000701 1 1116ALPS Premium Due


The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).

I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.

The current query is:
SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc
FROM dbo.tbGLTransactions
INNER JOIN dbo.tbLedgerCode
on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode
WHERE dbo.tbGLTransactions.lGLExtractRun = '452'
Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc



Is someone able to help me as to how i need to modify this query to get the desired result?

Thanks!

View 8 Replies View Related

Can I Add Query Field With Custom Calculation

Nov 20, 2007

i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?


thanks

View 2 Replies View Related

Can I Add Query Field With Custom Calculation

Nov 20, 2007

i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?


thanks

View 1 Replies View Related

Calculation With SUM (Help Needed)

Mar 25, 2008

Hello experts! After surfing this forum and reading many articles in the web I still haven't succeeded with a certain calculation that I need to perform. Thus I wanted to address you to ask if you could help me on this one:

I have 3 tables in a SQL Server 2005 DB and want to carry out the following calaculation:

[sum of lmenge/lmenge]* [1/werksmassFM]*p_transpreis

However, whatever I attempt to do, it seems to fail! I am quite a newbe and was never focussed with such comprehensive statements before. I would appreciate any help!

My statement looks like this so far (without the calculation):

SELECT l.bestellnr, l.lfsnr, l.ziel, l.lfs_refnr, l.lfsGuid, CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM, ls.lmenge, ls.pguid_ref, p.punktName, p.holzart, CONVERT(NUMERIC(9, 2), p.laenge) AS laenge, CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis
FROM dbo.lfs AS l INNER JOIN dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref INNER JOIN dbo.polter AS p ON ls.pguid_ref = p.pguid
WHERE (l.lfsnr LIKE '%1253')

The respective output data looks like this:

bestellnr >>> SJT07004A
lfsnr >>> HNAU01253
ziel >>> SE Ybbs
lfs_refnr >>> 1350-8
lfsGuid >>> de8ea3bdbdece
werksmassFM >>> 33.450
lmenge >>> 23,07692
pguid_ref >>> 53e7f6224435f244a6ba800bb33448b4
punktName >>> 470
holzart >>> Fi
laenge >>> 4.20
p_transpreis >>> 8.50

View 11 Replies View Related

Help Needed With Select Statement Using Calculation

May 2, 2008

Hi,

Any help with this would be great. I have SQL tables all in the following format:

ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE

Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g

(326-229) + 278 = new measure_value.

This is for each id-product where the following 3 conditions meet:

the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.

thanks

Jay

p.s this what i have started with at the moment as a test however it dont work.

SELECT ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT,
dbo.DPOUT_EXCEPTIONS_326.ID_MARKET,
dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET,
dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION,
dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE,
dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE'
FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on
dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product
where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product
and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET
and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)

View 3 Replies View Related

SQL Query Filtering Date Field By Today's Date?

Nov 3, 2006

Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?

View 2 Replies View Related

Note: Font, FontSize, BoldText, ShowBox, Character Count And More Factors Needed For Calculation(s)?

Jul 17, 2007

Situation: In this stored procedure, I have to calculate in some manner: Font, FontSize, BoldText, ShowBox and number of characters to see how many lines it will take on a Crystal Report. Wondering if you have seen some like this on Web or have an ideas? Measurements(length, width) and character count seem appropriate. How about a function?

Uses Arial and fontsize can be 8pt - 28pt.

Thanks. TAEG

View 3 Replies View Related

Max Of Date Field In Query

Mar 15, 2008

I have a table with date fielde.g.  SrNo         Date 1            1-MAR-2008 2            3-Mar-2008 3            7-Mar-2008 4            10-Mar-2008 5            15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' .  i.e i need single output '7-Mar-2008'anybody helpThanks 

View 2 Replies View Related

Query Field Relative To Max Date

Nov 10, 2005

How do I query to ONLY pull the location for the most recent date?

Date Location
1/2/97 ABC
3/4/02 DEF
2/8/89 GHI
4/30/05 JKL
2/28/03 MNO

View 1 Replies View Related

Calculation On A Varchar Field

Jan 17, 2005

I'm importing data from a text file into SQL as a varchar, and I'm leaving it a varchar in its final destination table. It is essentially a price, i.e., $25.65. I'm using this price field (varchar) to perform a calculation...

Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?

View 1 Replies View Related

How To Format A Date Field In Select Query

Oct 4, 2004

Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy)
I use the following query in stored proc. will be called in the asp.net page for population the datagrid.


select id, name, create_date from actionstable;

Please help, Thank you.

View 1 Replies View Related

Transact SQL :: Query DateTime Field By Date Range

Dec 2, 2015

I have a table of errors with a DateTime field for when the error occurred.  I want to query the table for a given date range omitting the time portion.  What is the most efficient way to perform this query?

View 5 Replies View Related

Date Calculation

Oct 12, 2004

I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.

View 4 Replies View Related

Date Calculation

Mar 14, 2001

hello everyone,
I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date,
and how do i find out the beginning date of the month and end date of the month for a certain date,
thanks

View 1 Replies View Related

Date Calculation

Nov 12, 1999

Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.

View 1 Replies View Related

SQl Date Calculation

Jan 10, 2006

I need to use the first day of previous month, can anybody help me with that please?

Thanks in advance!!

View 6 Replies View Related

Date Calculation

May 20, 2008

I want all dates from DATEADD(DAY,-90,GETDATE()).

1/1/2008
1/2/2008
1/3/2008 .....

and so on. This is probably easy, but I can't figure it out. Any help would be appreciated. Thanks.

View 11 Replies View Related

Date Calculation

Feb 4, 2008



I have an issue with a query.


How to calculate the last day of a previous month

that is for example
2007-09-30 23:59:59 is passed then how to calculate last date of a previous month as 2007-08-31 23:59:59


OR 2007-10-31 23:59:59 as 2007-09-30 23:59:59


Thanks for your Help.

Sri

View 3 Replies View Related

Transact SQL :: Query To Show Most Recent Value Based On Date Field

Jun 18, 2015

I simply need to list the most recent value for each employee here... the value at each person's maximum date

Sample Detail Data:

EmpID                   Date                      Value
1                              1/1/14                  27
1                              2/12/15                333
2                              5/5/15                   255
3                              5/4/15                   110  
3                              1/1/13                   67                          
3                              3/2/14                   80

[Code] ....

What is the most efficient way to display the most recent value for each employee ID via MS SQL.?

View 4 Replies View Related

Report Designer - How You Refer To An Existing Field In Calculation

Jun 2, 2007



Hi,



This seems simple but can't find. Say in my report I have 3 fields(A,B and C).



In field C I want to give a calcultion based on fields A and B. Say field C value = A+B



How do you do this? In expressions I couldn't see existing report fields, it only shows dataset columns.



Sonny

View 3 Replies View Related

Help: Date/time In Between And Calculation

Aug 23, 2005

I have a problem with date/time:

1. I have data something like this:
start 07.30
end 16.00
How can i count how many hours and minutes from start to end?

2. Another data
start: 20050805 -> August 5, 2005
end: 20050810
How can I return value that when i insert 20050809 it is between start and end, and if 20050811 it will say false that the date is between start and end

thx

View 1 Replies View Related

Detailed Date Calculation

Jul 25, 2007

Hello,

if a customer DOB is 1933-06-30 and a date of service is on 2007-06-21
that makes them 73 and not 74. How do I calculate an accurate years of age?

Thanks in advance.

View 7 Replies View Related

Really Complex Date Calculation

Jan 8, 2008

Hi,

Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.

ID FromDate ToDate
01 06/09/2006 06/09/2006
02 06/09/2006 06/12/2006
03 06/09/2006 06/12/2006
04 06/09/2006 06/12/2006
05 06/09/2006 06/12/2006
06 06/09/2006 06/12/2006
07 06/10/2006 06/10/2006
08 06/12/2006 06/12/2006
09 08/30/2006 09/05/2006
10 08/30/2006 09/05/2006
11 08/30/2006 09/05/2006
12 09/13/2006 09/17/2006
13 09/13/2006 09/26/2006
14 09/13/2006 09/26/2006
15 10/05/2006 10/10/2006
16 10/05/2006 10/10/2006
17 10/10/2006 10/15/2006
18 10/11/2006 10/11/2006
19 10/16/2006 10/16/2006
20 10/20/2006 10/22/2006
21 10/23/2006 10/25/2006

From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue.
Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.

The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:

ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate
05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006
09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006
13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006
? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006
? 10/20/2006 10/25/2006 0


View 3 Replies View Related

Urgent Help On Date Calculation

Jan 11, 2007

I have a table that hold a couple of fields.
STARTDATE - The day the service starts
FREQTYPE - This field tells me how often a schedule occurs. Here are the vaules -1=oncall, 0=days, 1=weeks, 2=months
FREQPERIOD - This is the number of days, weeks, or months between services. a value of -1 would indicate being oncall.
DayOfWeek, Day of week for week or month-based frequencies 0=sunday 6=saturday -1=if on call
WEEKOFMONTH - week of month for month based frequencies 1-4, value of 5 if it the last week of the month,

So what I need is for this report to look at the start date then figure out what the frequency is (days, weeks, months) then look at the frequency period (how many days are between services) what day of the week it falls on and list each service date on a report. There is an example at the bottom
I have tried numerous things and I can't get it and I need this asap today. I can provide what ever anybody needs, Many thanks.

Here is an example of the output that I need.

Service Frequency Every 12 weeks or 84 Days

Pickup Starting Point 1/4/2007

Pickup Schedule
3/29/2007
6/21/2007
9/13/2007
12/6/2007
2/28/2008
5/22/2008
8/14/2008
11/6/2008
1/29/2009
4/23/2009
7/16/2009
10/8/2009
12/31/2009


Here is sample data with the relationships
http://www.balzoutonline.com/reporting/database.jpg

Here is the service table schema
http://www.balzoutonline.com/reporting/service.jpg

Here is the serviceinfo table schema
http://www.balzoutonline.com/reporting/serviceinfo.jpg

Here is the serviceschedule table schema
http://www.balzoutonline.com/report...iceschedule.jpg

And here is the site table schema
http://www.balzoutonline.com/reporting/site.pdf

Here is something that was created in excel wich is pretty easy but a manual process. This is what I would like to replicate in crystal
http://www.balzoutonline.com/reporting/Schedule.xls

View 1 Replies View Related

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

Sep 28, 2007

Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,

I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.

I have no choice of making date column in SQL to string or Varchar etc,

is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).

many many thanks,

View 2 Replies View Related

Edit Year To Date Calculation

May 29, 2008

Hi -

I created a Year To Date Calculation via the Buisness Intellligence Wizard. The Script is as follows:

( [Acctg Date].[Acctg Time Acctg Date Calculations].[Year to Date],
[Acctg Date].[AcctgYear].[AcctgYear].Members ) =
Aggregate(
{ [Acctg Date].[Acctg Time Acctg Date Calculations].DefaultMember } *
PeriodsToDate(
[Acctg Date].[Acctg Time].[AcctgYear],
[Acctg Date].[Acctg Time].CurrentMember
)
)

Now I'd like to edit it so that it produces "Inception To Date" to date ("Inception To Date" picks up all amounts from the beginning which in my case is 2002, as opposed to Year To Date which is from the beginning of the year).

To change to Inception To Date can I change the PeriodsToDate function to:


PeriodsToDate(
[Acctg Date].[Acctg Time].[2002],
[Acctg Date].[Acctg Time].CurrentMember
)



By the way, I'm very new to SSAS so the code is foreign somewhat foreign at this point.

Thanks,
Mark

View 2 Replies View Related

Dateadd Date Calculation Anomaly

Apr 3, 2008

I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).

--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))


when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.

Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).


Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.

View 8 Replies View Related

Edited Version: Very Urgent Date Calculation

Nov 13, 2007

Hi, How can i calculate the date out? i am using vb sql server database...db saved my date as dd/MM/yyyy, form display my date as dd/MM/yyyy too
1.I have a selected date by user from calendar in tb_dop.text 
2.creditDate.text for user to enter number of days to add to tb_dop.text date
3.dDate.text to display the calculated date Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
'display selected date from calendar
tb_dop.Text = Calendar1.SelectedDate()
End SubPrivate Sub btn_add2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add2.ClickDim myDate As New DateTime
myDate = tb_dop.Text
Dim i As Integer
i = creditDate.TextDim dDate As New DateTime
dDate = myDate.AddDays(i)
dueDate.Text = dDate
ERROR
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.The statement has been terminated.
I know it is becos diff format of date calculation, there4, how can i change it to calculate in mm/dd/yyyy format? Funny rite? i can saved and display in dd/MM/yyyy but i cant calculate using this format
Note: i cant change my form display format to mm/dd/yyyy cos i need it to be user friendly in my country ty (URGENT)
Once again thanks

View 4 Replies View Related

Mdx/analysis/reporting Service For Date Calculation

Apr 8, 2008

Hi,

I have a cube with the following
DimStartDate
DimEndDate
DimArea
FactDataCount

I need to output the following report

Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2

1)Where do I do the dateDiff calculation, at report or cube level?
2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?

thanks

View 2 Replies View Related

SQL Server 2012 :: Date Range Calculation

Feb 3, 2015

I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.

E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.

create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp

View 6 Replies View Related

Power Pivot :: How To Do Nested Date Calculation With DAX

Aug 9, 2015

I have a CSV file that looks like below

JobOrderId CandidateId From_StatusID From_StatusDate To_StatusID

1234 5000 1 07/01/2015 2
1234 5000 2 08/01/2015

I've read this file in PowerPivot and I need to calculate the time in weeks between the two dates for the same JO, Candidate and when the to_StatusId = From_StatusID. In other words, the number of weeks that took from going From Status "1" to Status "2".

Is it possible to do something like this using DAX? Do I need to create a calculated Column?

View 4 Replies View Related

Power Pivot :: Prior Month To Date Calculation With DAX

Feb 9, 2013

I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:

Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))

View 5 Replies View Related







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