Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need urgent help with query Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:03 AM
Points: 6, Visits: 10
Hello friends,

One table (table1)
cardno, datetime1, channel_no

another table (table 2)
cardno, datetime1, channel_no

values in channel_no can be either 1 or 2.

I have 1 view for above table (view1)

What o/p I want is
cardno, datetime, min(datetime1) of channel_no 1 , max(datetime1) of channel_no 2

Application is kind of reading first in(1) and last out(2) timings ffrom datetime1


Please help me.

Thanks,
Post #1477392
Posted Thursday, July 25, 2013 2:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 1,037, Visits: 6,847
Can you post up a sample data script please?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1477400
Posted Thursday, July 25, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 13,093, Visits: 12,573
Hi and welcome to the forums. It is very unclear what you are trying to here. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1477501
Posted Thursday, July 25, 2013 10:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:03 AM
Points: 6, Visits: 10
Thanks for the reply.

Here is the sql
CREATE TABLE table1(
[CARDNO] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateTime1] [datetime] NULL,
[CHANNEL_NO] [smallint] NULL
) ON [PRIMARY]

CREATE TABLE table2(
[CARDNO] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTime1] [datetime] NOT NULL,
[action] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE VIEW view1 AS
SELECT CARDNO AS cardno, datetime1, channel_no as action FROM table1
UNION ALL
SELECT CARDNO AS cardno, datetime1, action FROM table2


INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 10:45:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 12:45:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 13:10:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 16:10:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 16:30:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222' ,'2013-02-04 11:45:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 13:00:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 13:45:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 16:10:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 16:30:00.000',1)


INSERT INTO table2 ([CARDNO] ,[DateTime1],[action]) VALUES('11111' ,'2013-02-04 08:45:00.000',1)
INSERT INTO table2 ([CARDNO] ,[DateTime1],[action]) VALUES('11111' ,'2013-02-04 18:30:00.000',1)


Post #1477845
Posted Friday, July 26, 2013 3:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 10:29 AM
Points: 11, Visits: 117
Create VIEW V2 as

SELECT
cardno
, MIN(datetime1) AS MINdatetime1
, MAX(datetime1) AS maxdatetime1
,[action]
FROM (
Select * FROM view1)A
GROUP BY cardno, [ACTION]



I think this is what you are after.
you could take it a step more and remove your first view and combine it in this.
Post #1477926
Posted Friday, July 26, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 13,093, Visits: 12,573
Or you could make this a bit simpler by eliminating the unnecessary subselect.

select cardno
, MIN(datetime1) AS MINdatetime1
, MAX(datetime1) AS maxdatetime1
,[action]
from view1
GROUP BY cardno, [ACTION]




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1477999
Posted Friday, July 26, 2013 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 10:29 AM
Points: 11, Visits: 117
Yeah Good call that.

Post #1478003
Posted Sunday, July 28, 2013 10:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:03 AM
Points: 6, Visits: 10
Hi,

Thanks for the reply.

However, the select query gives the results as min of dateime1 and max of datetime1. The o/p which I want is like

cardno, date, intime, outtime
1111, 04-02-2013, 08:45, 16:10
2222, 04-02-2013, 11:45, 16:10

i.e. datewise.

Please help.

Post #1478439
Posted Monday, July 29, 2013 2:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 10:29 AM
Points: 11, Visits: 117

select cardno
,CAST(datetime1 AS DATE)
, MIN(cast(datetime1 AS time)) AS MINdatetime1
, MAX(Cast(datetime1 AS time)) AS maxdatetime1
from view1
GROUP BY cardno, CAST(datetime1 AS DATE)


Give that a try.

J
Post #1478463
Posted Monday, July 29, 2013 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:03 AM
Points: 6, Visits: 10
Hi,

Thanks for all your replies. Here is the query worked as required.

select  t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,

(select SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,min(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='1'
)as InTime,
(select SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,max(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='2'
)as OutTime
from view1 t where t.cardno in ('111111','222222') and group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)

Post #1478477
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse