I'm new to SQL and I will appreciate your help, my knowledge only goes so far and I think this task is above my skills. I'm trying to get information form a table in a certain format that allows me to summarize data on an hourly basis but I think I need need to use DATEPART.
I'm trying to summarize the data by Idx, I used this query:
select datepart(hour,date_changed) as Hour, Object,avg(Value) as Avg_Idx_10
from table1
where group = 295 and idx = 10 and Object = 'OBJ_1'
and datetime_changed < '2008-05-20'
group by datepart(hour,datetime_changed), Object
And I get the information I need but each query shows only the data for 1 Idx, what I was wondering is if I can create a query that will allow me to summarize data on average Idx for all the idx in just 1 row yo obtain an output like this:
I'm not a very strong SQL coder. I am having a problem regarding summarizing some data. I am hoping somebody can provide me with some direction.
I have two tables. One table represents a ticket: Ticket (ID, DateSold). Another table represents the status of a ticket: TicketStatus (ID, TicketID, StatusID) I need to create a query that can tell me how many tickets were sold in each month. But then, I need to be able to break that monthly ticket total down by StatusID. I have the following SQL Statement:
SELECT Count(1) as 'TicketCount', Month(DateSold) as 'SellMonth' FROM Ticket t GROUP BY Month(DateSold)
Which works up to the first part of the problem. However, how do I get the number sold per each status? Essentially, I would like my result set to look like the following (pipe-delimited):
As you can see in Table 1, if a CustomerID has only one StaffID associated to it, the "Percentage" Column's value is 1. In the cases where there is more than one StaffID associated to a particular CustomerID, the percentage column get's split up among the StaffIDs, however the sum of this split can never exceed 1 (100%) per CustomerID.
I need to know how to find the cases where there is more than one StaffID associated to a CustomerID, group the CustomerIDs, and build a string made up with the StaffID and Percentages as shown in Table 2.
Any help would be greatly appreciated, as I've struggled with this one for a while and have hit a brick wall.
I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM.
I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D').
When I run the query below it returns 4 million rows. How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?
SELECT edw_ar_fl.ARTBASE.MA_NUM, edw_ar_fl.ARTBASE.MA_PAID_AMT FROM edw_ar_fl.ARTBASE JOIN edw_sbx_bt.jjenkins_OVERAGE2 ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM WHERE edw_ar_fl.ARTBASE.CONTRACT_NUM = '593' AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = '
I have two tables one list changes of hospital ward and one lists changes of consultant doctor. These can change independently ie a ward change can occur without a consultant change and vice versa. I want to summarise these changes to give the status at each date_serial value.
select date_serial,consultant_id,null as ward_id from #temp_consultant_episode union select date_serial,null as consultant_id,ward_id from #temp_ward_stay
The sample data above shows 1 customer with multiple episodes (different attend dates – not important here), during the course of these attendances they moved home and moved GP practice.
Is there a simple way in Access to show a summary of this eh PTO1395164 = 2 postcodes, 2 GP’s
THe ultimate aim would be to identify where a customer has changed postcode or GP within a selected timeframe and disregard the rest.
I have a datetime field in a sql db named "arrdate". For what I am doing I only need to extract the date formatted as mm/dd/yyyy. Can someone give me an example of the proper syntax? Something like seems like it should work: SELECT id, DATEPART(mm/dd/yyyy, arrdate) FROM guest but of course it doesn't.....
SET NOCOUNT ON DECLARE @PROG1VARCHAR (20), @PROG2VARCHAR (20); --@PROG_YR VARCHAR (2);
SET @PROG1 = 'TRAK08' SET @PROG2 = 'TRAK208' SET @PROG_YR = Select DatePart(YY, GetDate()) as Current_Year
SELECT @Rpt_Yr = case @Frequency WHEN 'M' then (select dateadd(mm,datediff(mm,0,getdate())-1,0)) WHEN 'O' then convert(varchar,@Start_Date,101) + ' 00:00:00' ELSE convert(varchar,getdate()-1,101) end -------------------------------------------------------------------------------------------------- I have done most ot the script for the rest of my pgoram but I am having problems with the date requirements. I was told to parse the year so that the user can enter the 2 digit date.
When it is 'O' (other) was told build a string to parse the year using datepart so that the user can enter the program year (2 digit format). (@Rpt_Yr) When it is 'M' (monthly) then I am to goto to table tk_prog get the active program I have no clue how to correct the above.
i'm trying to format SQL so that I retrive the day of the week and the hr in the same column.
SELECT Datepart([hour], Time) as Hour, SUM(Total) as Sales, count(TransactionNumber) as Customers, SUM(Total)/count(TransactionNumber) as 'Ave Sale' FROM [transaction] WHERE time between '05/30/2008' and '05/31/08' GROUP BY datepart([hour],Time)
Hi , I am converting a datetime field to a string. The column is called DateScanDate.
This is my query;
SELECT CAST(DATEPART(Year, DateScanDate) AS VARCHAR(4)) + CAST(DATEPART(Month, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Day, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Hour, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Minute, DateScanDate) AS VARCHAR(2))FROM HAAneurysmScan
I would like the month of March to be '03' instead of '3' and the 9th day of the month to be '09' instead of '9' How can I do this? regards ICW
I would like to take the following code and display the data / count by month. I want to see how many people are logging in by month.. I tried using the datepart but I keep getting an aggrefate comannd error can anyone help modify this query
SELECT DISTINCT Count(login.login_time) AS CountOflogin_time FROM login WHERE login.login_time>=#10/1/2005#;
Hi everyone, Im currently using SSRS 2000. I have a report that pulls data since 2001. I used Datepart('m", Fields!Shipped_Date.Value) to break it down into months. It works except that it doesnt separate the year. It'll group all of january together but 2001,2002,2003, etc, together. Then when I used Y to break it down by year, it does break it by year but keeps all the months together.....well what I want is to be able to have it jan 2001, feb 2001, jan 2002, separately group. Hope I didnt confuse anyone. All pointers welcomed.
ok, following up on my previous post that I marked as answered a little premature. The query below works fine in sql studio: SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest but when I try to use it in a c# code behind file: comm = new SqlCommand("SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest WHERE id = @id", conn); it bombs??
I'm trying to get just the day part of the date - 2/22/2005 (getdate()) but instead of returning '22', it's returning '2'. Can someone please tell me what I'm doing wrong?
Thanks! Lynnette
Here's the code
declare @thisDay varchar set @thisDay = Convert(varchar, Datepart(day, getdate()))
Hello dears; I wanted to use DatePart function in an SQL statement using objectDataSource to extract the year only from a Date field, then populating only the years in Dropdown list, but it didn't worked. Is is possible to to use that function in an SQL statment.? Thanks alot VWD 2005
The first one needs to be the Date part, the second one needs to be the Time part. Anyone know who to do this? I saw DATEPART in SQL BOL but it didn't really show me.
We are looking on how to do a between statement for comparing only month and days(such as birthday.) The proc will be fed 2 parameters and we want to check if a date field (only the month and day) fall between the parameters. Such as the parms are 09/01/2000 and 10/31/2000 I want to return all records where the date field is between 09/01/xxxx and 10/31/xxxx. I tried the datepart function but couldn't find a way to combine them. Any solution greatly appreciated.
Will someone please tell me how to pull the time out of a smalldatetime field. The code i am trying to use is as follows:
Select datepart(hh:mm, TimeField1) from table1;
This gives me an error. I have also tried datepart('hh:mm'... datepart("hh:mm"... and other variations but i cant get anything to work. Thanks in advance for any help!!
returns 5 on one SQLServer, and 4 on another one. It cannot be a regional settings problem because 25 could never be a month. Any way, is there a deterministic way to create a DATE regardless of the server's regional settings?
I mean, I have the year, month and day... how do I tell the server "This is the year; This is the month; This is the day... convert this into a DATE, and the tell me which day of the week is"??
Hi, everyone! Could anyone help me with understanding of the datepart() function. I want to retrive data from a database that was added in this week, starting from Sunday. Suppose today is Tuesday, so I want to get all the records that were added on Sunday, Monday, and Tuesday. Can I use ... where datepart(wk, Date_Reported)=datepart(wk, getdate()) ? Thank you.
INSERT INTO [IMPAPT].[dbo].[YearMaster] ([yearID] ,[year] ,[IsActive] ,[creationDate]) VALUES (1 ,datepart(yy,getdate()) --------line need to be discuused ,1 ,getdate()) the output result is yearID Year IsActive creationDate 17/2/1905 12:00:00 AMTrue 6/11/2008 1:21:17 PM
But when i write query
select datepart(yy,getdate() As Year it throws result : 2008 Why this is not happenin in the insert query
I have a function that uses the following statement in it
SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype, src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus, src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent, src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate, src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent, src_tbl_rental.budgeted_occupancy FROM src_terrier INNER JOIN src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref
WHERE (src_terrier.datadate = @dt_src_date) AND (src_terrier.Areacode = @chr_div) AND (src_centre_list.Portfolio_no = @vch_portfolio_no) AND (src_centre_list.propcat = @vch_prop_cat) AND (src_tbl_rental.site_ref = src_terrier.siteref)
The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows
src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.
How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.
Therefore if some passes in 28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.
Hi - Can anyone tell me what the first day of the week (i.e. equal to 1) for the DATEPART("dw",<date>) expression in SSIS. Or can I specify the first day of the week somehow?
It looks like Monday=1 which seems a little odd as the equivilent T-SQL DATEPART has Sunday=1.
