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.
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?
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?
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?
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')
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)
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?
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
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?
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.
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?
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.
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
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.
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
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.
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
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 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
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:
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.
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
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?
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
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?
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))
