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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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