Transact SQL :: Display Records According To Date
Oct 2, 2015
I have a table that have student names and their birth dates just like below
a) tblStudentInfo
which shows records like below (Birth Dates are shown in Year-Month-Day format)
ID StudentName BirthDate
1 ABC 2002-12-25
2 DEF 2002-09-10
3 GHI 2002-09-19
4 JKL 2002-06-10
[code]...
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID StudentName BirthDate
1 ABC 2002-12-25
2 VWX 2002-01-01
3 STU 2002-02-03
4 PQR 2002-03-05
[code]...
View 3 Replies
ADVERTISEMENT
Oct 8, 2015
Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output
select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate
[Code] .....
View 5 Replies
View Related
Jul 30, 2015
I've come accross this code and need to alter it to display a normal General Date format.
CASE WHEN p.BIRTHDTTM IS NULL THEN ''
ELSE RIGHT('0' + LTRIM(CONVERT(VARCHAR(20), BIRTHDTTM, 113)) , 20)
END AS BIRTHDTTM
The original data (which is a DOB field) looks like this
1936-08-14 00:00:00.000
And the code above is turning it into this which is good but means I cannot then format in SSRS
14 Aug 1936 00:00:00
So ideally I'd like the code above to be altered so that it gives me
14/08/1936
View 14 Replies
View Related
Oct 14, 2015
This one is making my head hurt! Trying to figure out how to query for records between date range. The records have a start_date and an end_date field. The end_date field maybe null.
For example, say you wanted to see the records of everyone checked into a hotel during a given date range. You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.
fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.
View 7 Replies
View Related
Nov 18, 2015
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL
[code]...
View 7 Replies
View Related
Nov 19, 2015
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
View 3 Replies
View Related
Jul 28, 2015
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
View 4 Replies
View Related
Jul 2, 2015
I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)
1.) Latest Product type PFE should be A (active) status for the particular ID
2.) At the same time ALL the latest PRI should be C(closed) status for the same ID
I have give example with 3 scenarios and desired output
1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result
2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result
View 5 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Mar 16, 2014
I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
View 3 Replies
View Related
Oct 16, 2006
A SQL Server 2005 stored procedure expects a parameter UserID depending upon which it retrieves the no. of records & OrderIDs corresponding to the UserID from a DB table (note that OrderID & UserID are two of the columns in the DB table). So for e.g. consider a user whose UserID=6 & the DB table has 3 records where UserID=6. In other words, there are 3 OrderID records of the user whose UserID=6, say, OrderID=8, OrderID=17 & OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 for UserID=6) & the OrderID (which will be 8, 17 & 29 for UserID=6). This is the stored procedure:ALTER PROCEDURE dbo.OrderCount @UserID intASDECLARE @OrderCount intSET @OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @UserID)SELECT @OrderCount AS OrderCount, OrderIDFROMNETOrdersWHEREUserID = @UserIDIn a VB class file, I am invoking the stored procedure in a function named GetOrderCount which returns a SqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs of UserID=6 in a Label control. Unlike the DataBinding controls like DataList, DataGrid, Repeater controls, the Label control will not automatically loop through the recordset. So I tried to accomplish this using a For....Next loopDim i As IntegerDim sqlReader As SqlDataReaderiUserID = Request.Cookies("UserID").Value'ShopCart is the name of the class in the VB class fileboShopCart = New ShopCartsqlReader = boShopCart.GetOrderCount(iUserID)While (sqlReader.Read) If (sqlReader.GetValue(0) > 1) Then pnlLinks.Visible = True For i = 1 To sqlReader.GetValue(0) lblLinks.Text = sqlReader.GetValue(1)(i) Next i Else pnlLinks.Visible = False End IfEnd WhileBut this generates the following error:No default member found for type 'Integer'.pointing to the linelblLinks.Text = sqlReader.GetValue(1)(i)in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in a Label control?
View 8 Replies
View Related
Apr 11, 2008
I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID StoryTitle StoryAuthor Rating StoryID Comments
1 About Me goodyone 6 20 Great Story
2 About Me goodyone 5 20 Love your work
3 Hello World magicme 6 26 What a Story
4 Hello World magicme 7 26 This Reminds me of...
I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>"
SelectCommand="SELECT * FROM [iaw.comments]">
</asp:SqlDataSource>
View 5 Replies
View Related
Dec 7, 2011
TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar
I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.
File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'
File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'
Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:
Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'
View 3 Replies
View Related
Apr 15, 2008
hi friends,
how to display the records for this week?
for today i used this query to dispaly the values
select count(*) from tbl_voting v,tbl_lkvotefor l where v.voteforid=l.voteforid and v.creationdatetime=CONVERT(VARCHAR(10), GETDATE(), 101)
View 4 Replies
View Related
Apr 15, 2008
hi friends,
how to display the records for current month?
View 7 Replies
View Related
Mar 5, 2014
finding the solution for the below query?It displays repeated records.
select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from
[code]....
View 2 Replies
View Related
Mar 31, 2008
in my sql query,when it run then it displays records as follows
dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
i want to display records only once like as follows
dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
if i use group by on some fields then it gives error as follows
Column 'Daily_Time_Entry.daily_type' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
so how can i design query so records display only once not repeated,or is any
alternative,to solve this .
my sql query as follows
SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as
dates,dl.emp_mgmt_id,
emp.emp_name,emp.employee_id,dl.project_type,dl.project_id,
dl.cec_job_id,
cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id,
op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc,
m.material_id,m.material_type,m.material_date, m.project_type
,m.project_id,
m.qty,m.description,m.material_unit_price,m.material_markup,
m.material_subtotal,
m.cec_job_id,m.location
FROM material m left outer join
Other_Project op on m.project_id=op.other_proj_id left outer join
Daily_Time_Entry dl on
dl.project_id =op.other_proj_id inner join Employee_Mgmt emp
on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec
ON dl.cec_job_id = cec.cec_job_id
where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and
(dl.project_type='Other Project' and m.project_type = 'Other Project')
and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and
(dl.project_id='18')
uday
View 3 Replies
View Related
Mar 13, 2008
hi i want to display records as per data in ms sql 2005
my data is as follows
Table name & fields are as follows :--
1) Ab_Corporate_Project
Fields are :
Ab_crp_id- int
Contract_no varchar
Project_no varchar
Field_order_no varchar
Ab_type varchar
Owner_id int
Records in Table Ab_Corporate_Project is as follows :
Ab_crp_id Contract_no Project_no Field_order_no Ab_type Owner_id
1 123 pr123 fd222 LS 2
2 323 pr323 fo122 GMS 3
3 243 pr243 fo233 EMC 1
2) Ab_Plant_Project
Fields are :
Ab_plant_id int
Service_order varchar
p_type varchar
Owner_id int
Records in Ab_Plant_Project is as follows
Ab_plant_id Service_order p_type Owner_id
1 so111 LS 1
2 so123 T&M 3
3 so110 EMC 2
3)Other_Project
Fields are
Other_proj_id int
Po varchar
Otype varchar
Owner_id int
Records in Other_Project is as follows
Other_proj_id Po Otype Owner_id
1 p123 LS 3
2 p22 EMC 3
3 p231 GMS 2
4)Owner_representative
Fields are
Owner_id int
Owner_names varchar
Records in Owner_representative are as follows
Owner_id Owner_names
<!--[if !supportLists]-->1 <!--[endif]-->john
<!--[if !supportLists]-->2 <!--[endif]-->amey
<!--[if !supportLists]-->3 <!--[endif]-->nickols
5)Employee_table
Fields are
emp_id int
Emp_name varchar
Records in Employee_table are as follows
Emp_id Emp_name
1 andrue
2 handy
3 sims
4 sandy
6)Daily_Time_Entry
Fields are
Dt_id int
Daily_type varchar
Dt_date datetime
Emp_id int
Project_type varchar
Project_id int
Time_st int
Time_ot int
Time_dt int
Records in Daily_Time_Entry are as follows
Dt_id Daily_type Dt_date Emp_id Project_type Project_id Time_st Time_ot
1 Elect 12/03/2008 1 AB Corporate 2 10 8
2 Mech 13/03/2008 2 AB Corporate 1 10 10
3 Mech 13/03/2008 1 AB Plant 2 5 6
4 Elect 13/03/2008 3 AB Plant 3 7 8
5 Mech 13/03/2008 2 Other Project 2 5 6
6 Elect 13/03/2008 4 Other Project 3 10 8
Where dt_date is in dd/mm/yyyy format
Also in project_type field -- AB Corporate is taken for Ab_Corporate_Project ,
AB Plant for Ab_Plant_Project, Other Project for Other_Project
This means if Project_type = AB Corporate then search into Ab_Corporate_Project
Else if Project_type = AB Plant then search into Ab_Plant_Project
Else if Project_type = Other Project then search into Other_Project
Now I want to display records as per datewise suppose I want to view records by date 13/03/2008
Record display as follows
Dt_id,Dt_date,emp_id,Emp_name, Project_type ,project_id, contract_no, service_order,ab_type,p_type,otype,owner_id,owner_name,time_st,time_ot
So how can I write query to display records as per datewise display
Plz send me a query for this solution
View 1 Replies
View Related
Sep 1, 2006
I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.
I tried this code but didn't work..
[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]
View 12 Replies
View Related
Oct 31, 2015
I am using sql server 2012. suppose i have a table called cte which contains id and name columns . in name column there are null value . i want to display top row value instead of null value as like attached image. Here is query :
;with CTE As (
Select 1 as Id , 'Advance' as Name
union all
Select 2 as Id , NULL as Name
union all
Select 3 as Id , NULL as Name
[Code] ...
Expected Result :
I want to write normal select Query. i am not interest to using loop or cursor.
View 5 Replies
View Related
Dec 27, 2007
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique.
State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks
Craig
View 4 Replies
View Related
Feb 11, 2005
Hello,
I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.
There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.
I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.
For example:
PromoID StartDate EndDate
------- --------- -------
PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)
Here's a query I am using to display all active promotions:
select
pl.promoid,
pr.startdate,
pr.enddate,
pl.locid,
pl.skuid,
pl.promoprice,
pl.disclevel
from
promolocation pl
inner join
promotion pr
on
pl.promoid = pr.promoid
where
pr.enddate >= getdate()
Thanks for your help.
D
View 4 Replies
View Related
Feb 28, 2008
I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
-------------------------------------------
Result: Record count 3000.
Only field3 is populated everything else is null.
select field3 from tbl1 group by field2 - record count = 48
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL2
-------------------------------------
Result Record count = 0
------------------------------------------------------
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
minus
SELECT
nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM tbl2
---------------------------
Result: Record Count = 48
I used the left join and it didn't work.
I would like to build a query that will display all 3000 records.
Any help will be appreciated.
thank you!
View 4 Replies
View Related
Jan 26, 2008
I would like to run a SELECT statement using the SQLCMD, however, I do not like the count of records be displayed after the execution. How do I do that?
Thanks in advance.
View 1 Replies
View Related
Jun 15, 2015
If I have a dimension with 3 records like this:
BrokerDimId BrokerNm
1 Broker1
2 Broker2
3 Broker3
And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:
BrokerDimId Amt
1 $100
2 $200
I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this
BrokerDimId Amt
1 $100
2 $200
3 $0
Is this possible using MDX?
View 2 Replies
View Related
Aug 20, 2007
I've got a stored procedure that processes a TON of records...
What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed.
I have a simple counter defined in the procedure:
SET @COUNTER = 0
Each time the procedure loops through, it increments by 1:
SET @COUNTER = @COUNTER + 1
What I would like to do is write rows to a PROCESS table which would reads:
PROCESSED 1000 rows
PROCESSED 2000 rows
PROCESSED ...... rows
etc.
I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000.
If anyone has any insight it would be greatly appreciated!!
Thanks in advance!
View 3 Replies
View Related
Oct 2, 2007
I was wondering if there was a way to display the "other" total that does not appear in a TopN report.
I.e. I display the Top 10 records
I would then like a row saying "All Others" and the subtotal of the non Top 10 records
I would also like to display a Total of the TopN records and what % of the overall that amounts to
Simple example.....
Top 3 Report
1. AAA 10
2. BBB 9
3. CCC 8
4. Others 20
Top N Total 27 (% of total = 57%)
Overall Total 47
Lines in italics are the values I can't seem to get my head around
View 5 Replies
View Related
May 11, 2015
I have a column in the table which contains below values
Range
XXXX100
xxxx101
xxxx102
...
xxxx1020
below values are strings. I tried using below query but it does not return all values in the range.
Select * from table where Range Between 'XXXX100' AND 'xxxx1020'
May be in need to extract the number to display the values in given range.
View 4 Replies
View Related
Nov 10, 2015
I have a Cost table with the following field:
Year, Quarter, Cost1, Cost2
I would like to write a SQL so that it will give me something like that, so that I will have 2 years across the top with the same same columns.
2016
2015
Quarter
Cost1
Cost2
Cost1
Cost2
1
100
200
[code]....
View 3 Replies
View Related
Oct 29, 2015
I need to concatenate the DisplayName column with distinct UserName and SysName using XML path
SELECT [sysName], [User_Name],[DisplayName] FROM
(SELECT v_Add_Remove_Programs.DisplayName0 AS [DisplayName], v_Add_Remove_Programs.Publisher0 AS [Publisher]
, v_R_System_Valid.Netbios_Name0 AS[sysName],v_R_System_Valid.User_Name0 AS [User_Name],v_Add_Remove_Programs.Version0 as [Version]
FROM [Offline].[dbo].v_Add_Remove_Programs
JOIN [Offline].[dbo].v_R_System_Valid ON [Offline].[dbo].v_Add_Remove_Programs.ResourceID = [Offline].[dbo].v_R_System_Valid.ResourceID)
[code]....
View 3 Replies
View Related
Aug 25, 2015
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE
WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
View 5 Replies
View Related
Aug 5, 2015
I have a table like dependent eg.
Dependent
emp id Dept ID Child Name
1 11 C1
1 12 C2
1 13 C3
1 14 C4
1 15 C5
These is input table and i want output like
Child1 Child2 Child3
C1 C2 C3
C4 C5 null
View 8 Replies
View Related
