[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Need help in MS SQL Query

Hi!

The following query gives me First In of the current date (new_date)

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '21-aug-2012'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%'

However, I would also like to add the following two columns in the same table i.e.:

i) Previous date (day-1 e.g. 20-aug-2012) and
ii) Last_Out (previous date)   [  MAX(a.TIMESTAMP) AS Last_Out ]

Can you please help me to generate the desired output,

Regards,
0
AmitavaCh
Asked:
AmitavaCh
  • 12
  • 7
  • 2
  • +1
1 Solution
 
teebonCommented:
DECLARE @Date AS DATETIME
SET @Date =  '21-aug-2012'

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
DATEADD(d, -1, @Date) previous_date,
MIN(a.TIMESTAMP) AS First_in,
MAX(a.TIMESTAMP) AS Last_out,
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = @Date
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%'

Open in new window

0
 
AmitavaChAuthor Commented:
Hi!
Will check and let you know

Tx,
0
 
keyuCommented:
SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date, dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime)) Previous_date,
MAX(cast((dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime))) as timestamp)  Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '21-aug-2012'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%'
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
AmitavaChAuthor Commented:
Hi!

It is giving the following error

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Last_Out'.

Can you please check,

Regards,
0
 
AmitavaChAuthor Commented:
Hi!

Query executed - however,

Previous_date is coming as 2012-08-20 00:00:00.000
Last_Out is coming as "0x0000A0B300000000"

can you check please.

Regards,
0
 
Bhavesh ShahLead AnalysistCommented:
ONE BRACKET IS MISSING.

MAX(cast((dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime))) as timestamp))  Last_Out,


SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date, dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime)) Previous_date,
MAX(cast((dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime))) as timestamp))  Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '21-aug-2012'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%'
0
 
AmitavaChAuthor Commented:
Hi!

Tx,

Query executed - however,

Previous_date is coming as 2012-08-20 00:00:00.000
Last_Out is coming as "0x0000A0B300000000"

can you check please.

Regards,
0
 
AmitavaChAuthor Commented:
Hi!
just for your information, the last_out of card # 777 on 20-Aug-2012 is 19:48:00.000
However, the record is coming as 00:00:00.000
0
 
teebonCommented:
Which query are you using? Can you post your latest query?
0
 
AmitavaChAuthor Commented:
Hi!
I am using the following query:

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date, dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime)) Previous_date,
MAX(cast((dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime))) as timestamp))  Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '21-aug-2012'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%'
0
 
teebonCommented:
The query will be checking last out for '21-aug-2012'.
Replace '21-aug-2012' with '20-aug-2012' as below and see whether you are getting  19:48:00.000:

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date, dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime)) Previous_date,
MAX(cast((dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime))) as timestamp))  Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '20-aug-2012'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%' 

Open in new window

0
 
keyuCommented:
try to use cast instead of convert might solve your issue..


SELECT * FROM
(
SELECT a.link3,
REPLACE(CONVERT(VARCHAR(11), cast(TIMESTAMP as datetime), 106), ' ', '-') new_date,
REPLACE(CONVERT(VARCHAR(11), dateadd(day,-1,cast(TIMESTAMP as datetime)), 106), ' ', '-') Previous_date,
MAX(cast(dateadd(day,-1,cast(TIMESTAMP as datetime)) as timestamp))  Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where
REPLACE(CONVERT(VARCHAR(11), cast(TIMESTAMP as datetime), 106), ' ', '-') = '21-aug-2012'
GROUP BY a.link3,
REPLACE(CONVERT(VARCHAR(11), cast(TIMESTAMP as datetime), 106), ' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%'
0
 
AmitavaChAuthor Commented:
@Teebon,

Query Executed for 21-aug-2012 (as 19 was a Sunday)

Previous_date is coming as 2012-08-20 00:00:00.000 [However, I want date in DD-MMM-YYYY format]

Last_Out is coming as "0x0000A0B300000000" [Actual: 2012-08-20 19:48:00.000]

Regards,
Amitava
0
 
AmitavaChAuthor Commented:
@Keyu

The following error is coming.

Msg 8120, Level 16, State 1, Line 1
Column 'History.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Regards,
0
 
teebonCommented:
Try this:

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date, 
CONVERT(VARCHAR(11),dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime)),106) Previous_date,
CONVERT(VARCHAR(11),MAX(cast((dateadd(day,-1,cast(REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') as datetime))) as timestamp)),106)  Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '21-aug-2012'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%' 

Open in new window

0
 
AmitavaChAuthor Commented:
@Teebon,

Tx, Date format changed
However, Last_Out field is still coming blank now

By any chance, is it picking up midnight timing (00:00:00.000) of the day as the Max Time of the day - Most probably, that's why it is coming blank.
0
 
teebonCommented:
Few things to clarify:

1. Is the First_in value coming out alright?
2. You want to get the max timestamp for previous one day?
0
 
AmitavaChAuthor Commented:
Absolutely yes,

1. The First_in value is coming alright
2. Reg. previous day, I want to capture the max timestamp

Thank you for you help,
0
 
teebonCommented:
It is still little confusing. Just to confirm the following is what you want:

If you supply '21-aug-2012',

1. The First_In should return the MIN timestamp for 21 Aug 2012
2. The Last_out should return the MAX timestamp for 20 Aug 2012
0
 
AmitavaChAuthor Commented:
Yes, Sir
Actually, we have HID access cards and I am working on a system, where whenever any user swipe his/her card, an email notification to be sent out, informing today's IN Time and Yesterday's Out Time.

Right now notifications is going with today's IN Time. I am just stuck at this point.

Your help will really a big help for me.

Regards,
0
 
teebonCommented:
try this:

DECLARE @Date AS DATETIME
SET @Date =  '21-aug-2012'

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date, 
DATEADD(d, -1, @Date) Previous_date,
(   SELECT MAX(a.TIMESTAMP)
       from History a
        where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = DATEADD(d, -1, @Date)
        GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) as Last_Out,
MIN(a.TIMESTAMP) AS First_in
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') =  @Date
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '777%' 

Open in new window

0
 
AmitavaChAuthor Commented:
Tx a lot. Sorry for the delay. I was not in town for a long time. Regards,
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now