query.Append("SELECT Name, Sex, Marital_Status, Dob, Anniversary, Mobile, Email FROM Client WHERE ");
if (startDate > new DateTime() && endDate > new DateTime())
query.Append("(DATEADD(YEAR,DATEDIFF(YEAR, DOB,'" + startDate + "'),DOB) BETWEEN '" + startDate + "'AND'" + endDate + "' OR DATEADD(YEAR,DATEDIFF(YEAR, ANNIVERSARY,'" + startDate + "'),ANNIVERSARY) BETWEEN '" + startDate + "'AND'" + endDate + "') AND ");
if (!string.IsNullOrEmpty(maritalStatus) && !string.Equals("All", maritalStatus))
query.Append(" Marital_Status = '" + maritalStatus + "'");
string resultQuery = query.ToString();
if (resultQuery.Trim().EndsWith("AND", StringComparison.OrdinalIgnoreCase))
int lastIndex = resultQuery.LastIndexOf("AND");
resultQuery = resultQuery.Remove(lastIndex, 3);
int lastIndex = resultQuery.LastIndexOf("WHERE");
resultQuery = resultQuery.Remove(lastIndex, 5);
I have used above code, This code working properly, but It shows randomly,
The Data should have to display in ascending order based on the Date and month.
2 Answers Found
In the end,before return
append order by clause.
Else explain with sample data that your query return and you want sort on which colmn.
I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known.
We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date.
I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or
For sorting, when records have same Year, I would have another column to do relative sorting...
So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any.
Thanks in advance.
--Sharad Read more...
Using the getdate() function, how do I select the last WHOLE 3 months,
So for example, for todays date (29/09/2010)
I want to return all records with the date between 01/06/2010 and 31/08/2010
Is this possible?Read more...
Hello, i am curious and also would like to know how to duplicate this user interface feature in Windows 7. I am aware that if you attempt to sort a column in Windows XP, there is no up/down arrow in the column.
Please have a look at
It shows part of the task manager process list, and image name column is sorted in descending order.
What i am trying to find out is how is the arrow pointing downwards (representing descending) is created.
Am i able to reproduce this "arrow pointing downwards" in VS 2005?
Looking forward to the feedback!Read more...
When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?Read more...
I am getting this error when I am trying to plot some dates.
I have 3 series of dates coming from the same dataset.
It is my belief that this is because some of the dates at the end of the list are blank. Is there a way to make the chart ignore points that have no dates, while still plotting the dates in the other series?
I know I can switch the Chart to 2D and it will plot them but I would get points that I don't want anyways because the dates are not valid.
Thanks for the help.Read more...
I have the following parameter
Dim sDate As String = "201003"
And i want to get the first date and last date of the month and convert it into integer value.
Given Parameter: 201003
Can someone please help me get around this query, I need to convert a date column in "Views" by returning the last date of the month (any month).
i.e. If the date shows 03-Mar-2010, I need to convert it to the last month which is 31-Mar-2010
Please assist :-(Read more...
I have two table Order and Order Detail.
What i need is i need to bind last 10 Order in Gridview/details view with paging, and the same order detail also fetch from database and shown in another gridview.. if user click any oderNo in first Gridview/DetailView then i need to show Respective order detail in anther gridview..
without postback coz we already fetch 10 order with orderdetails from the database..
and if user click next 10 orderNo using paging then again we need to load 10 orderNo with respective orderdeatils from database like this going on....
as well as let me know how to take print of particular oder and order detail....?Read more...
I have a pretty simple analytic chart on my sharepoint site. The columns are The name of the location, and the month, and then all of the data is in the rows.
so basically it looks like this
jan feb mar april may june jan feb mar april may june
You get the point. The problem is, I want to have two filters; one that filters by the locations, and one that filters by the months. When I created the locations filter and connected it to the grid, it made all of the months go in alphabetical order instead
of the correct order, like so:
aug dec jul nov oct
Obviously, this is not how a calendar is supposed to look. I already changed the properties in my sql cube to make the months go in the correct order in the unfiltered chart. They are ordered by the key, which is month, instead of monthname. What can I do?
Also, I would like my second filter to filter by months as a multiple select tree. When I create that, it also orders the months alphabetically instead of correctly. How can I change that?Read more...
When below SQL is used to print the MONTH Names in SQL SERVER 2000, order by Month name ordering in Alphabetical order, which is incorrect.
Let me know any work around to get this order by Montly order and then Year.
SELECTCASEWHENMONTH(a.registrationdate) = 1 THEN'Jan'WHENMONTH(a.registrationdate) = 2 THEN'Feb'WHENMONTH(a.registrationdate) = 3 THEN'Mar'WHENMONTH(a.registrationdate) = 4 THEN'Apr'WHENMONTH(a.registrationdate) = 5 THEN'May'WHENMONTH(a.registrationdate) = 6 THEN'Jun'WHENMONTH(a.registrationdate) = 7 THEN'Jul'WHENMONTH(a.registrationdate) = 8 THEN'Aug'WHENMONTH(a.registrationdate) = 9 THEN'Sep'WHENMONTH(a.registrationdate) = 10 THEN'Oct'WHENMONTH(a.registrationdate) = 11 THEN'Nov'WHENMONTH(a.registrationdate) = 12 THEN'Dec'ENDAS monthname,
when a phone call activity is created from the lead entity
Workflow as to check the due date attribute for the phone cal activity (which is created from the lead entity) if the due date is less than 1 day from the current date
its as to create a task for a phone cal activity owner
How can i achieve this task
Reply as soon as possible
I need to update all the rows in a table with a date that increments by two months each time.
I.e. Row1 should have a date of 1/3/2010, Row2 = 1/5/2010, Row3 1/7/2010
The tricky bit is that the user needs to be able to enter the row number, so that it can start on any row and the start date.
e.g. Input 7 and 1/9/2010 and the query update all rows from 7 onwards with date 1/9/2010 incrementing each time, so row8 is 1/11/2010 etc.
I know how to add months to a date, but I can't work out how to make the query work on the previous record.
Please help!!!Read more...
We have a month-end report that is run by each of our offices, the regional offices, the divisional offices, and at the global headquarters. This report asks for a date
range and returns the results based the actualclosedate of opportunity records. Now a number of the opportunity records are closed on the last day of the calendar month and the issue that we are running into is that the last day of the month at some
our locations is the first day of the next month for the some of the offices higher up in the office's reporting structure. Thus, when a side-by-side comparison is done of the reports there is a variance in what is reported. For example Office
A is in CST time zone and it reports to Office B in Tokyo Time time zone. If Office A closes records on 31 October they will show on the report run for 01 October – 31 October when the report is run in Office A; however, when the report is
run in Office B some of the records will not be included as some of the records may reflect the actual close date as being 01 November.
This depends on the time of day the records are closed on 31 October.
This results in questions about which version of the report is accurate though both reports are accurate when you account for how time works.
Unfortunately, this is not the result that is desired by the business.
I had considered using the UTC time but this does not always reflect the intent of the user closing the opportunity. For example, if a user in Office A closes an opportunity
after 18:00:00 or 19:00:00, with day lights saving time, the UTC will reflect as being on the next day. Though using UTC time would address the issue of records being included or not included when queried by date range, since it would standardized the
date used, it introduces a new issue of not reflecting the intent of the user closing the opportunity.
Ideally, I would evaluate the actualclosedateutc field by joining the UserSettings table on the modifiedby field and then using fn_UTCToTzSpecificLocalTime to get the date the user
closing the record actually provided. Unfortunately this will not work as workflows change the modifiedby field after the opportunity is closed.
Thus, the modified field does not always reflect the user whom closed the record.
Hopefully, someone on this forum will see something that I am missing and can provide a solution to address this issue.Read more...
I have this data that has a CaseDate column that gate populated every day BY DATE, my question is how can pull and filter data by day/month/year 30 days of a particuler month in SSIS to get porocessed each month.
' TO 31-MAY-2010]
SO THIS WILL BE AUTOMATED TO PULL DATA EACH MONTH.Read more...
hi gurus, how to calcuate months difference between two dates? pls let me know rgdsRead more...
How do i set dates for default of pram value in the strored proc...
i'e i want the last month 6/2010 - for end date
then the first month of the year in 2010Read more...
I am running a query that displays Specimen Type Name , Count of Each Specimen Type Name and Specimen Received Date to generate my SSRS report.
The report displays a column specimenTypeName, and several columns for Specimen Recieved Date. For each SpecimenTypeName it displays the count of SpecimenTypesName recieved on that date. I want to modify the report layout (Matrix) so that the Specimen Recieved
Date column is grouped by year and month with a little plus sign, so that it first displays the total number of that SpecimenTypeName recieved in that year, On clicking the plus sign it should display counts for each month in that year and on clicking the
plus for a month it should display count for that month in that year.
Can I do this without modifying the query, at the report level?
Please help me to accomplish this. I checked the tutorial in the help section but did not find any help there.
SELECT APVX.DOS.SpecimenType.SpecimenTypeName AS SpecimenTypeName, count(APVX.DOS.SpecimenType.SpecimenTypeName) as count, APVX.ORDERS.Specimen.ReceivedDate
FROM APVX.ORDERS.ComponentOrder INNER JOIN
APVX.ORDERS.ApCase INNER JOIN
APVX.ORDERS.TestOrder ON APVX.ORDERS.ApCase.CaseID = APVX.ORDERS.TestOrder.CaseID INNER JOIN
APVX.ORDERS.TestOrderSpecimen ON APVX.ORDERS.TestOrder.TestOrderID = APVX.ORDERS.TestOrderSpecimen.TestOrderID INNER JOIN
APVX.ORDERS.Specimen ON APVX.ORDERS.TestOrderSpecimen.SpecimenID = APVX.ORDERS.Specimen.SpecimenID ON
APVX.ORDERS.ComponentOrder.TestOrderID = APVX.ORDERS.TestOrder.TestOrderID INNER JOIN
APVX.DOS.SpecimenType ON APVX.ORDERS.Specimen.SpecimenTypeID = APVX.DOS.SpecimenType.SpecimenTypeID
WHERE APVX.ORDERS.ComponentOrder.ComponentID = 100129
group by APVX.DOS.SpecimenType.SpecimenTypeName, APVX.ORDERS.Specimen.ReceivedDateRead more...
i have created the calendar list.I have added some birthdays in that calendar.Now i want to display the person name with greeting in that month before oneday.Read more...
I have a SQL view that use todays date and queries 12 months of data. View works fine but I want to convert the format to a number that represents the latest month in this format "01", "02". For example
if I run the query in July 2010 then June 2010 is returned as "201006". I need it formated to "01" which represents one month in the past. "201001" would be "06" which is 6 months ago. Any ideas? Below is my view code.
SELECT dbo.oehdrhst_sql.cus_no AS Customer, SUM(dbo.oehdrhst_sql.tot_sls_amt) AS Sold, SUBSTRING(CAST(dbo.oehdrhst_sql.inv_dt AS varchar(8)), 1, 6)
AS YYYYMM, COUNT(dbo.oehdrhst_sql.ord_no) AS Orders, dbo.ARCUSFIL_SQL.slspsn_no AS Salesman, dbo.ARSLMFIL_SQL.email_addr,
dbo.ARCUSFIL_SQL.cus_type_cd, (dbo.oehdrhst_sql.tot_sls_amt - dbo.oehdrhst_sql.tot_cost) / NULLIF (dbo.oehdrhst_sql.tot_sls_amt, 0)
dbo.oehdrhst_sql.tot_sls_amt - dbo.oehdrhst_sql.tot_cost AS GP_amt
FROM dbo.oehdrhst_sql INNER JOIN
dbo.ARCUSFIL_SQL ON dbo.oehdrhst_sql.cus_no = dbo.ARCUSFIL_SQL.cus_no INNER JOIN
dbo.ARSLMFIL_SQL ON dbo.ARCUSFIL_SQL.slspsn_no = dbo.ARSLMFIL_SQL.slspsn_no
WHERE (dbo.oehdrhst_sql.inv_dt BETWEEN CAST(SUBSTRING(CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 112), 1, 4)
- 1 AS char(4)) + CAST(SUBSTRING(CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 112), 5, 4) AS char(4)) AND
CONVERT(varchar(25), DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)), 112)) AND (NOT (dbo.ARCUSFIL_SQL.slspsn_no IN ('S01',
'S03', 'S04', 'S05', 'S06', 'S07', 'S08', 'S09', 'SN1')))
GROUP BY dbo.oehdrhst_sql.cus_no, SUBSTRING(CAST(dbo.oehdrhst_sql.inv_dt AS varchar(8)), 1, 6), dbo.ARCUSFIL_SQL.slspsn_no,
dbo.ARSLMFIL_SQL.email_addr, dbo.ARCUSFIL_SQL.cus_type_cd, dbo.oehdrhst_sql.tot_sls_amt, dbo.oehdrhst_sql.tot_cost
I need to produce stats from July to July inclusive, is there a simple way of producing with the sample code i have below?
,SUM(CASE WHENRead more...
(Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-11,GetDate())))
ELSE 0 END) AS 'TotalMonth1'
(Month(SalesOrders.TransactionDate) = Year(DateAdd(Year,-1,GetDate())))
ELSE 0 END) AS 'TotalMonth0'