Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot 

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies


ADVERTISEMENT

Transact SQL :: How To Display Data Party Name And Time Interval Wise In Server

Sep 9, 2015

i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.

Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0

[code]...

now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.

For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation

[code]...

View 3 Replies View Related

How To Use Order By Clause Row Wise

Sep 9, 2015

i want to know how to use order by clause in row wise

for example, suppose the rows is
10 20 30 40

so the output is should be
10
20
30
40

View 3 Replies View Related

Transact SQL :: Order By A Column From Another Table

Sep 2, 2015

I have two tables, one is called (questions), the second one (answers).

questions columns are (ID,questionTitle)

answers columns are (ID,questionID,answer, answerDate)

I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.

View 12 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Transact SQL :: Unable To Change Column Order Of Existing Table In Server

Jul 23, 2015

In y sql server table has millions of records available. I don't want to drop the tables.

My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.

Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !

View 13 Replies View Related

Transact SQL :: Count Of Records By Month Wise

Oct 7, 2015

I am trying to get count of  records by month wise when they select year .It was showing  the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...

SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)

Output

Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139

View 4 Replies View Related

Transact SQL :: Passing Value In Where Condition Loop Wise

Jun 1, 2015

I want to calculate year to month data for month wise . Ihave start range and end range table like below

Start range and end range table

Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015

[Code] ....

Then I want to pass this value one by one in below table for get value month wise

Main table

Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404

[Code] ....

The below query is for november month: (Apr to Nov)

select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)

So I want to pass second row

select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'

Like wise I want to get month wise data of YTM data.

My expected output is

201411
201412
201501
201502
201503
201504
201505

[Code] ....

View 10 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')

[code]....

the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I have a table data like below

id         type      timestamp
1001    start1    10:34:23:545
1001    start2    10:34:24:545
1001    end2     10:34:24:845
1001    end1     10:34:25:545
1002    start1    10:34:25:645
1002    start2    10:34:25:745
1002    end2     10:34:25:945
1002    end1     10:34:25:965

I need the result as follows

id              millisecond diff start1end1                 millisecond diff start2end2
1001    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp
1002    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp

SQL Server 2008 R2

View 5 Replies View Related

Transact SQL :: Sum Time Column After Pivot Query

May 18, 2015

i have a table like below,

CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME

[code]....

and then i pivot table by date as column wise using the below query and also displays total time by rowswise

SELECT t1.*, t2.Total
FROM (
SELECT  name,[2015-08-01],[2015-08-02]
FROM (
SELECT  name, AttDate,PresentTime 

[code]....

now what i need is to display sum of time at last row as well, means total time of against date

View 16 Replies View Related

Transact SQL :: How To Set Case Sensitivity Of A Column In Design Time

Aug 3, 2015

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

N.B.: Using SQL Server 2005.

View 22 Replies View Related

Transact SQL :: Cumulative Values Quarter And Half Yearly Wise

Nov 23, 2015

Having table like below. Here want to cumulative the values quarter and half yearly wise...

declare @table table 
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )

[Code] ....

Like wise the data should added...

View 3 Replies View Related

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

Nov 28, 2012

I have a sales tables which looks as below.

DEPARTMENT
Barnd_Name
Item_Group
     S_DATE
          S_AMOUNT
Administration
IBM

[code]....

Now i need Month Wise Running Totals.but i should check the following group as show below i that order

1) DEPARTMENT
1) Brand
3) Item Group
4) Month

View 12 Replies View Related

Data In Column Wise

Apr 18, 2008

My Table(collection) Structure consist of below:
TypeDate ItemAmount
REVENUE2007-04-01serv002***
REVENUE2007-04-01Serv004***
REVENUE2007-04-01serv005***
REVENUE2007-04-01AMC-***
ROYALTY2007-04-01serv002***
ROYALTY2007-04-01Serv004***
ROYALTY2007-04-01serv005***
ROYALTY2007-04-01AMC***

O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

View 6 Replies View Related

Transact SQL :: Split Date Range Into Monthly Wise And Loop Through Each Month To Perform Some Operation

Oct 20, 2015

Let's say if the date is 01/01/2015 till 01/01/2016

I want split these dates monthly format wise and then use them in variable in cursors to loop

For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016

View 2 Replies View Related

Change Multiple Rows Into Column Wise

May 25, 2014

I have multiple number of rows i want to change into column wise. My data

PondCropDOCABWTargetABW
01PA01-18700.21
01PA01-18150.590.77
01PA01-18221.241.5
01PA01-18280.922.6
01PA01-18351.823.7

[Code] ....

I want the data should be like these

01PA01-18 01PA02-18 01PA03-18...206B15-01 Target ABW
Doc ABW Doc ABW Doc ABW Doc ABW
7 0 7 0 7 0 11 0.42 0.21
15 0.59 15 0.59 15 0.59 18 0.77 0.77
22 1.24 22 1.24 22 1.14 25 1.22 1.5
28 0.92 28 0.87 28 0.91 32 2.7 2.6
35 1.82 . . . . . . .
42 2.6 . . . . . . .
49 3.62 . . . . . . .
56 4.64
63 5.54
66 6.24
73 7.25

View 4 Replies View Related

Month Wise Date On Column Address

Nov 20, 2007

could u help me out to get the month wise date on column address

this is the query

SELECT[ID],
SUM(CASE WHEN week_no = 1 THEN Qty ELSE 0 END) AS week1,
SUM(CASE WHEN week_no = 2 THEN Qty ELSE 0 END) AS week2,
SUM(CASE WHEN week_no = 3 THEN Qty ELSE 0 END) AS week3,
SUM(CASE WHEN week_no = 4 THEN Qty ELSE 0 END) AS week4,
SUM(CASE WHEN week_no = 5 THEN Qty ELSE 0 END) AS week5
FROM
(
SELECT[ID],week_no = DATEPART(week, [Date]) - DATEPART(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)) + 1,Qty
FROM@sample
WHERE[Date]>= DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)
AND[Date]< DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date) + 1, 0)
) d
GROUP BY [ID]

for this query the output will be

/*
ID week1 week2 week3 week4 week5
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0


but i need date wise split up to be displayed for particular month

ID week1 week2 week3 week4 week5
(1-7) (8-14) (15-21) (22-28) (29-31)
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

pls help me out.






Shiney

View 6 Replies View Related

SQL Server 2008 :: Rows Holding Group-wise Maximum Of Certain Column?

May 13, 2015

is there any more efficient way for example to implement the next query?

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'

What indexes I should create for this query?

View 2 Replies View Related

Integration Services :: How To Split Column Wise Into Separate Flat Files In SSIS

Apr 25, 2015

IN SSIS...

1.---->I have a sales table country wise regions  like (india, usa, srilanka) ....

india usa
srilanka
a b
c
d e
f

So I want output like in

flat file1.txt has india               flat file2.txt has usa             flat file3.txt has srilanka

     a b
c
     d e
f

2.----->I dont know how many regions in my table....dynamically split into separate flat files ....

View 2 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Transact SQL :: Update Time Portion Of DateTime Field With Time Parameter

Oct 3, 2015

I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000

ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime

[code]...

View 14 Replies View Related

Transact SQL :: Converting 24hrs Time To 12 Hours Time Query Format?

Apr 21, 2015

SELECT 
    CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance

o/p
indate   04/18/2015
time part :17:45:00

I need to convert this 17:45:00 to 12 hours date format...

View 8 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm

Jun 4, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,





View 1 Replies View Related

Reporting Services :: Insert Row Number For Zone Wise (group By Zone Column) In SSRS Report

Jul 19, 2015

How to insert a row number for a zone wise(ie group by zone column) in ssrs report in zone column  i should get zone1 only once (should not get Zone1,zone1, zone1 -3 times)

sl.no     Zone    District    no.of.region

1                      hyd           24
2          ZONE1 chn           12
3                      bang         2
1                      raj            4
2          ZONE2  vizag       3
3                       bbb         34

View 4 Replies View Related

Order By Time

Mar 15, 2000

Hi Guys and Gals

I have a field with datetime type of data,
this field will hold entry date and time of data.

I got a problem when I wanna list of this field
by date and time, coz I use this sql sommand like this :

Select * from news
order by nws_date

the result is :
data sort by date but not sorted by time, so I can't get
the result the last time entry.

could you help me to solve this problem ?
thank you for any help that you gave.

regards,
Inu

View 2 Replies View Related

Transact SQL :: Using TOP And ORDER BY

Oct 9, 2015

I have a table where on all business days per month, for different trades, a price is uploaded, so a simplified extract:

Trade   PublishingDate   Price
10       2Jan15               100
:
10       31Jan15             150
20       2Jan15               75
:
20       31Jan15             110
:

I'm trying to write a view that will:

a) Give me the average for each trade, per month.
b) Give me the average of the last 7 days, for each trade, per month.

The first one is easy:
SELECT TradeWSRate.Trade,
CASE MONTH(TradeWSRate.PublishedDate) WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'

[code]...

Column "TradeWSRate.PublishedDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.How can I change the above select to work?  Ideally I want to do this all in one view, and not use functions.

View 5 Replies View Related

Transact SQL :: Order By Month

Apr 21, 2015

I am using below query i want to order it by month so that data is displayed like

Select (count(I.Colour)) as Litres,
DATEName(MONTH,ShipDate) as [Loaded Date] from InkData I
group by DATEName(MONTH,ShipDate)
order by DATEName(MONTH,ShipDate)

Right now it is not ordering by month, it is ordering alphabetically.I want this to be ordered like

Jan
Feb
March

View 6 Replies View Related

Transact SQL :: CASE Order Of Evaluation

Sep 24, 2015

I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.

SELECT CASE
WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END

This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?

View 10 Replies View Related

Transact SQL :: Get Active Order Dollars

Sep 18, 2015

This is kind of a follow-up on my previous post "Get 15th and last day trans".  Our client has asked for open sales orders dollars for a given date range to include order dollars active as of the 15th of the month and the last day of the month. 

Below is what I started and I hard-coded the Jan15Open and JanEndOpen so you could see what I am trying to accomplish.  So, for example, if I pass @StartDate of '01-01-2013' and @EndDate of '12-31-2014' I could get back results for 01-15-2013, 01-31-2015, 02-15-2013, 02-28-2013, etc. all the way to 12-31-2014.  Note that the results will depend on the TransDate datetime column.  Once I get the query correct I plan to create a temp table to hold the results so I can report them back in a web page. 

ALTER PROCEDURE [dbo].[afa_selSalesOrdersOpen]
@StartDatedatetime = null,
@EndDatedatetime = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 9 Replies View Related

Transact SQL :: Order By Used With Case Statement

Sep 24, 2015

I use Order By Used with Case Statement and may be this is the reason for which i am getting error

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Here is my complete sql where i use Row_Number function for pagination with sorting.

DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1

[Code] ....

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved