July 25, 2014 at 7:56 pm
Hi friends i have small doubt in sql server here i want data based on condition same id and status is equal to s then that date value be how to write query in sql server
Table :emp
id |status |date(mm-dd-yy) |fdate(mm-dd-yy) |tdate(mm-dd-yy)
1 | S |03-16-11 | |
1 | b | | 03-15-11 |03-18-11
1 | s |03-17-11 | |
1 | b | | 04-20-12 |04-30-12
1 | S |04-20-12 | |
1 | s |04-10-12 | |
1 | s |10-01-14 | |
1 | b | |10-02-14 |10-25-14
2 | s |01-18-12 | |
2 | b | |01-18-12 |01-28-12
2 | b | |03-10-13 |03-24-13
2 | s |03-16-13 | |
2 | s |03-10-13 | |
2 | s |03-23-13 | |
2 | b | |04-20-13 |04-27-13
2 | s |07-01-14 | |
the table (status = s, id, date) compare it with status = b, same id number and date ( Date value from status s) with the date range of fdate and tdate . if that data with in range then Billing yes other wise billing no
output like
id |status |date(mm-dd-yy) |fdate(mm-dd-yy) |tdate(mm-dd-yy) |Billing
1 | S |03-16-11 | | |yes
1 | s |03-17-11 | | |yes
1 | S |04-20-12 | | |yes
1 | s |04-10-12 | | |no
1 | s |10-01-14 | | |no
2 | s |01-18-12 | | |yes
2 | s |03-16-13 | | |yes
2 | s |03-10-13 | | |yes
2 | s |03-23-13 | | |yes
2 | s |07-01-14 | | |no
i tried query like below
select * from ( select * from emp a where status ='s')a
inner join
(select * from emp b where status='b')b
on a.pn=b.pn
where a.date<=b.date1 and a.date>=b.date2. || its not give exactely result. please tell me how to write query in sql server
July 25, 2014 at 9:39 pm
If you post proper CREATE TABLE scripts and INSERT scripts to populate your table(s), you will probably get an answer...
July 26, 2014 at 4:42 am
pietlinden (7/25/2014)
If you post proper CREATE TABLE scripts and INSERT scripts to populate your table(s), you will probably get an answer...
10 minutes preparing the data sample, 1 minute writing the code:pinch:
This is a self-join type solution
😎
USE tempdb;
GO
WITH TEST_DATA (id,status,date,fdate,tdate)
AS
(SELECT id,status,date,fdate,tdate FROM (VALUES
(1,'S','03-16-11' ,NULL,NULL)
,(1,'b',NULL ,'03-15-11','03-18-11')
,(1,'s','03-17-11' ,NULL,NULL)
,(1,'b',NULL ,'04-20-12','04-30-12')
,(1,'S','04-20-12' ,NULL,NULL)
,(1,'s','04-10-12' ,NULL,NULL)
,(1,'s','10-01-14' ,NULL,NULL)
,(1,'b',NULL ,'10-02-14','10-25-14')
,(2,'s','01-18-12' ,NULL,NULL)
,(2,'b',NULL ,'01-18-12','01-28-12')
,(2,'b',NULL ,'03-10-13','03-24-13')
,(2,'s','03-16-13' ,NULL,NULL)
,(2,'s','03-10-13' ,NULL,NULL)
,(2,'s','03-23-13' ,NULL,NULL)
,(2,'b',NULL ,'04-20-13','04-27-13')
,(2,'s','07-01-14' ,NULL,NULL)) AS X(id,status,date,fdate,tdate)
)
SELECT
TD.id
,TD.status
,TD.date
,TD.fdate
,TD.tdate
,CASE
WHEN TDB.id IS NULL THEN 'no'
ELSE 'yes'
END AS Billing
FROM TEST_DATA TD
LEFT OUTER JOIN TEST_DATA TDB
ON TD.id = TDB.id
AND TDB.status = 'B'
AND TD.date BETWEEN TDB.fdate AND TDB.tdate
WHERE TD.status = 'S';
Results
id status date fdate tdate Billing
----------- ------ -------- -------- -------- -------
1 S 03-16-11 NULL NULL yes
1 s 03-17-11 NULL NULL yes
1 S 04-20-12 NULL NULL yes
1 s 04-10-12 NULL NULL no
1 s 10-01-14 NULL NULL no
2 s 01-18-12 NULL NULL yes
2 s 03-16-13 NULL NULL yes
2 s 03-10-13 NULL NULL yes
2 s 03-23-13 NULL NULL yes
2 s 07-01-14 NULL NULL no
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply