Link to home
Start Free TrialLog in
Avatar of dencom
dencom

asked on

Sql Query Help

DROP TABLE #ADL_TEST
GO

CREATE TABLE [dbo].[#ADL_Test](
      [cnt] [smallint] NOT NULL,
      [flag] [smallint] NOT NULL,
      [dr] [datetime] NOT NULL,
      [df] [datetime] NOT NULL
) ON [PRIMARY]

INSERT INTO #ADL_TEST VALUES (1,31,'Jan 31 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,31,'Jan 31 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  1 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  4 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb  4 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb  5 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb  5 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  5 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  6 2008 12:00AM','Feb 22 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  6 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  6 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  6 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  6 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  6 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  6 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  6 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  6 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  6 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  6 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,6,'Feb  7 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb  7 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb  8 2008 12:00AM','Feb  8 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  8 2008 12:00AM','Feb 20 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  8 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,31,'Feb  8 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb  8 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb  8 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  8 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb  8 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  8 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb  8 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,4,'Feb  8 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 11 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb 11 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 11 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,31,'Feb 11 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 11 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 11 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 11 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 11 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 11 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 11 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 11 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,60,'Feb 12 2008 12:00AM','Feb 12 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 12 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 12 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 12 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 12 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 12 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,1,'Feb 13 2008 12:00AM','Feb 13 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,1,'Feb 13 2008 12:00AM','Feb 13 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 13 2008 12:00AM','Feb 20 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 13 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb 13 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 13 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 13 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 14 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 15 2008 12:00AM','Feb 19 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 15 2008 12:00AM','Feb 20 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 15 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 15 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 15 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 15 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 15 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 19 2008 12:00AM','Feb 25 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 19 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,20,'Feb 21 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 21 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 21 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 22 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 22 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 22 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 22 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb 22 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 25 2008 12:00AM','Feb 26 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 25 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,20,'Feb 25 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 25 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 25 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,20,'Feb 25 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 25 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 25 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb 25 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 25 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 26 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 27 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb 27 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,12,'Feb 27 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 27 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 27 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 28 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 28 2008 12:00AM','Feb 28 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 28 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 28 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 28 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 28 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 28 2008 12:00AM','Feb 29 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 28 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 28 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 28 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 28 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,6,'Feb 28 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,2,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,5,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,-2,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')
INSERT INTO #ADL_TEST VALUES (1,41,'Feb 29 2008 12:00AM','Mar  3 2008 12:00AM')

SELECT * from #adl_test

(The above table for some data for the month of Feb-2008)

OK, I want a resultset like this:

DATE                  IN                          OUT
2/1/2008             0                             5
2/2/2008             3                             1
2/3/2008             9                             0
...
...
...
...
2/29/2008          0                              0

The way to calculate IN and OUT for any day (say 2/1/2008) are:
IN : sum(case when (dr='2/1/2008') Then 1 else 0 end) as 'IN'
OUT : sum(case when (df='2/1/2008' and flag=31) Then 1 else 0 end) as 'OUT'

Similarly for 2/2/2008:
IN : sum(case when (dr='2/2/2008') Then 1 else 0 end) as 'IN'
OUT : sum(case when (df='2/2/2008' and flag=31) Then 1 else 0 end) as 'OUT'

Here, the sum need to applied on the first column "cnt" which is always 1.

Any ideas? Thanks.

I can get the result in 1 row by doing everything manually,..


select      '2/1/2006' DT, sum(case when (dr='2/1/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/1/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/2/2006' DT, sum(case when (dr='2/2/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/2/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/3/2006' DT, sum(case when (dr='2/3/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/3/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/4/2006' DT, sum(case when (dr='2/4/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/4/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/5/2006' DT, sum(case when (dr='2/5/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/5/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/6/2006' DT, sum(case when (dr='2/6/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/6/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/7/2006' DT, sum(case when (dr='2/7/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/7/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/8/2006' DT, sum(case when (dr='2/8/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/8/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/9/2006' DT, sum(case when (dr='2/9/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/9/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/10/2006' DT, sum(case when (dr='2/10/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/10/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/11/2006' DT, sum(case when (dr='2/11/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/11/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/12/2006' DT, sum(case when (dr='2/12/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/12/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/13/2006' DT, sum(case when (dr='2/13/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/13/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/14/2006' DT, sum(case when (dr='2/14/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/14/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/15/2006' DT, sum(case when (dr='2/15/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/15/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/16/2006' DT, sum(case when (dr='2/16/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/16/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/17/2006' DT, sum(case when (dr='2/17/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/17/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/18/2006' DT, sum(case when (dr='2/18/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/18/2008' and flag=31) Then 1 else 0 end) as 'OUT',
            '2/19/2006' DT, sum(case when (dr='2/19/2008') Then 1 else 0 end) as 'IN',
                      sum(case when (df='2/19/2008' and flag=31) Then 1 else 0 end) as 'OUT'
from
#ADL_TEST
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

let's see:
with mycte as
(
  select convert(datetime, '2008-01-01', 120) DateValue
  union all
  select DateValue + 1
    from    mycte   
   where   DateValue + 1 < convert(datetime, '2050-01-01', 120) 
)
select mycte.DateValue
, COALESCE(( select count(*) from #adl_test t where t.[dr] between mycte.DateValue and dateadd(day,1, mycte.DateValue ) , 0) IN
, COALESCE(( select count(*) from #adl_test t where t.[df] between mycte.DateValue and dateadd(day,1, mycte.DateValue ) , 0) OUT
from    mycte
OPTION (MAXRECURSION 0)

Open in new window

Select DateValue, Sum(Case when dr = DateValue THEN 1 ELSE 0 END) as IN, Sum(Case When df = DateValue Then 1 ELSE 0 END) as OUT
FROM #ADL_TEST
Forgot the Group by

Select DateValue, Sum(Case when dr = DateValue THEN 1 ELSE 0 END) as IN, Sum(Case When df = DateValue Then 1 ELSE 0 END) as OUT
FROM #ADL_TEST
GROUP BY DateValue
ASKER CERTIFIED SOLUTION
Avatar of JimFive
JimFive
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial