Summarizing Data And DATEPART
May 26, 2008
Hi,
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.
The table looks like this:
Group Date_changed Object Proc Idx Value Offset
295 2008-05-19 00:05:00.000OBJ_1 PRC11020008544
295 2008-05-19 00:05:00.000OBJ_1 PRC11130006860
295 2008-05-19 00:05:00.000OBJ_1 PRC11240001372
295 2008-05-19 00:05:00.000OBJ_2 PRC11010002058
295 2008-05-19 00:05:00.000OBJ_2 PRC11120002744
295 2008-05-19 00:05:00.000OBJ_2 PRC11230003430
295 2008-05-19 00:05:00.000OBJ_3 PRC11070004116
295 2008-05-19 00:05:00.000OBJ_3 PRC111 60004102
295 2008-05-19 00:05:00.000OBJ_3 PRC112 50004802
295 2008-05-19 00:10:00.000OBJ_1 PRC11025004602
295 2008-05-19 00:10:00.000OBJ_1 PRC11135004502
295 2008-05-19 00:10:00.000OBJ_1 PRC11245005481
295 2008-05-19 00:10:00.000OBJ_2 PRC110 12005482
295 2008-05-19 00:10:00.000OBJ_2 PRC111 22005483
295 2008-05-19 00:10:00.000OBJ_2 PRC11232005484
295 2008-05-19 00:10:00.000OBJ_3 PRC11074506174
295 2008-05-19 00:10:00.000OBJ_3 PRC111 64506175
295 2008-05-19 00:10:00.000OBJ_3 PRC112 54506176
...
...
...
it contains data on 5 minutes intervals for each object and index.
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
Hour Object Avg_Idx_10
0 OBJ_16346
1 OBJ_15039
2 OBJ_14533
3 OBJ_15102
4 OBJ_19019
5 OBJ_123511
6 OBJ_152575
...
...
...
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:
Hour Object Idx_10_Avg Idx_11_Avg Idx_12_Avg
0 OBJ_1 7500 3200 9500
0 OBJ_2 8400 7600 5800
0 OBJ_3 4700 6500 3400
...
...
...
Again, I'll appreciate any help, thanks in advance.
View 8 Replies
ADVERTISEMENT
Nov 2, 2007
Hello,
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):
TicketCount | SellMonth | Status1Total | Status2Total
5 | 1 | 2 | 3
9 | 2 | 8 | 1
7 | 3 | 3 | 4
Can somebody please help? Thank you!
View 7 Replies
View Related
Mar 7, 2008
Hi there,
I'm faced with a bit of an issue within SSIS and I was wondering if anyone could help. I have data that looks like the following:
TABLE 1:::
CustomerID StaffID Percentage
A123 1234 1
A124 1245 1
A125 1234 0.7
A125 1443 0.3
B117 6653 1
B154 8899 1
C776 1443 0.2
C776 1337 0.6
C776 1245 0.2
I need to be able to summarize the data above and build up a string that should look like the following:
TABLE 2:::
CustomerID StringID
A123 1234||1
A124 1245||1
A125 1234||0.7||1443||0.3
B117 6653||1
B154 8899||1
C776 1443||0.2||1337||0.6||1245||0.2
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.
Ciao,
Colin
View 6 Replies
View Related
Nov 20, 2015
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 = '
View 4 Replies
View Related
Oct 27, 2015
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.
create table #temp_ward_stay(date_serial bigint,ward_id varchar(10))
;
insert into #temp_ward_stay
values
(201501021200,'W1'),(201501031201,'W2'),(201501091200,'W3'),(201501161200,'W4'),(201501161201,'W5')(201501271200,'W3'),(201502101200,'W5')
;
create table #temp_consultant_episode(
date_serial bigint,
consultant_id varchar(10))
;
insert into #temp_consultant_episode values
(201501021200,'C1'),(201501031200,'C2'),(201501031201,'C3'),(201501091201,'C4'),
(201501121200,'C3'),(201501301200,'C6'),(201502111200,'C6'),(201502111201,'C7')
;
If I use:
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
I get:
date_serial ward_id consultant_id
-------------------- ---------- -------------
201501021200 NULL C1
201501021200 W1 NULL
201501031200 NULL C2
201501031201 NULL C3
201501031201 W2 NULL
201501091200 W3 NULL
201501091201 NULL C4
201501121200 NULL C3
201501161200 W4 NULL
[code].....
whereas what I actually want is:
date_serialward_idconsultant_id
201501021200W1C1
201501031200W1C2
201501031201W2C3
201501091200W3C3
201501091201W3C4
201501121200W3C3
201501161200W4C3
201501161201W5C3
[code].....
I can see how I could remove the nulls where the date_serial values are the same:
select ce.date_serial,ward_id,consultant_id from #temp_consultant_episode ce
join #temp_ward_stay ws
on ce.date_serial=ws.date_serial
but I can't see how to move forward from here.
View 9 Replies
View Related
Apr 17, 2015
LOCALID - POSTCODE - GPCODE
PTO1395164 - DN34 1AB - G9999981
PTO1395164 - DN34 1AB - G9999981
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - TZ14 2AX - G8909058
PTO1395164 - TZ14 2AX - G8909058
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.
View 4 Replies
View Related
Jan 31, 2008
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.....
View 3 Replies
View Related
May 7, 2001
Why would this return a year of 1905: If @tempdate was a varchar
it would return just 2001, but by it being a dateime it returns
the year 1905.
declare @tempdate datetime
select @tempdate = (select datepart(yy,getdate()))
select @tempdate
View 4 Replies
View Related
Mar 18, 2006
Datepart function is different from general ms using in MSSQL
I need something like this
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])
I try to get which week of year.. but first day of week for some country is not sunday..
How can I determine that the first day of week is monday?
View 1 Replies
View Related
Apr 21, 2008
CREATE PROCEDURE [dbo].[sp_TRAK_PROG_TOTALS]
@Frequency varchar (1),
@Rpt_Yr smallint,
@Prog_Yr varchar (2)
AS
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.
View 2 Replies
View Related
May 31, 2008
Hi All,
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)
Output desired is
05/30/08 09
05/30/08 10
05/30/08 11
...
View 1 Replies
View Related
Mar 9, 2006
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
View 9 Replies
View Related
May 3, 2007
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#;
Thanks,
View 4 Replies
View Related
Sep 28, 2007
Using MS Reporting Services 2005
I have this expression in my field in my table and in my group by expression field
=Monthname(Datepart("m", Fields!CreatedDate.Value))
ok this gives me the month eg July etc
=Datepart("yyyy", Fields!CreatedDate.Value)
ok this gives me the year eg 2006 etc
if I try to add the two together I get the error (not in correct format) obviously as one is numeric and one alpha
=(Monthname(Datepart("m", Fields!CreatedDate.Value) & " " & (Datepart("yyyy", Fields!CreatedDate.Value))))
or I can do this:
=(Datepart("m", Fields!CreatedDate.Value) + (Datepart("yyyy", Fields!CreatedDate.Value)*100))
which gives me 200607
what do I need to do to have this show as
July 2006 etc
thanks
Dianne
View 4 Replies
View Related
May 30, 2008
How can I concat. these two into one column?
datepart(mm,DateCol), datepart(dd,DateCol) as MyDate
thanks.
View 7 Replies
View Related
Oct 9, 2007
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.
Thanks,
Abner
View 1 Replies
View Related
Aug 22, 2006
I am wanting to set any given @Date parameter to the most current recorded Monday in a table (tblMarketPricing). Would this work:
@Date = Max(Datepart(dw,MktDate) IN 2
I am unsure if you can use Max with the Datepart function.
***If this is not allowed, can anyone suggest anything different that I might try?
View 3 Replies
View Related
Jan 31, 2008
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??
View 5 Replies
View Related
Feb 22, 2005
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()))
View 3 Replies
View Related
Apr 9, 2006
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
View 2 Replies
View Related
Aug 24, 2001
I am updating a demo system and need to bring all the dates with 1998 to 2000 and 1999 to 2001.
Im trying something like this but cant get it work.
UPDATE mytable SET datepart(yyyy, mydate) = '2000' WHERE mydate BETWEEN '01/01/1998' AND '12/31/1998'
How can I get this to work, or am I totally off?
Thanks,Adrian
View 4 Replies
View Related
Jul 30, 2001
I am inserting two fields like this (TimeStamp):
7/30/01 1:26:01 PM
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.
TIA,
Bruce
View 1 Replies
View Related
Sep 19, 2000
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.
View 9 Replies
View Related
Feb 24, 2000
Hi,
I am having trouble trying to get a "week number" from a date field to store into a numeric table.
I have inserted the following into my DTS ActiveX code;
DTSDestination(15)=DatePart( wk , DTSSource(1) )
When I run the code the following error is shown;
ErrorDescription: Invalid procedure call or argument: ''DatePart'
Q - Does DatePart work in DTS ActiveX ?
Q - Is my syntax correct ?
david
View 1 Replies
View Related
Dec 16, 2003
I am converting a datetime value to char using datepart(mm,datefield).
I would like the month to be 2 digits - 01,02,03...11,12 - but it only returns 1,2,3...11,12. What can I add to do this? thanks in advance.
View 1 Replies
View Related
Aug 17, 2004
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!!
View 2 Replies
View Related
Nov 3, 2004
Can anyone explain me why this query
SELECT DATEPART(dw, CONVERT(DATETIME, '2001-01-25 01:00:00', 120))
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"??
TIA,
View 4 Replies
View Related
May 12, 2004
Functionally, is there any difference between DATEPART for m, d, and yyyy, verses simply using MONTH, DAY, and YEAR functions respectively?
For all intents and purposes, they seem identical, so is there any performance considerations using one verses another?
View 1 Replies
View Related
May 27, 2008
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.
View 1 Replies
View Related
Jun 11, 2008
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
View 6 Replies
View Related
May 11, 2006
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.
Anybody confused by that , cause I am!
Regards
View 6 Replies
View Related
May 31, 2007
Could someone tell me if they see something off about this expression? I'm using it in an expression. It's giving me a syntax error.
=IIf(Fields!NTFrequency.Value = "Quarterly",IIf((DatePart("m", Now)=1), 9, month(now)-3, IIf((DatePart("m", Now)=1), 12,month(now)-1)))
Thanks, Iris
View 5 Replies
View Related
Apr 1, 2008
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.
Thanks!
View 5 Replies
View Related
