SQL median query -- grouping by two columns

[Homerboy]

This may be overly complicated to explain here, but I will give it a shot.

I have the following query that will get me the median days between MASTER.DATE_RECD and INFINITY.FIELD group by the month of MASTER.DATE_RECD where MASTER.USER = 2 for the last 12 months

However, what I am wanting to really get is for ALL users, group by those users. There are about 90 total different users, and each should return 12 months. I just have the "...and master.USER = '2'" in there now so I can pull some data. Obviously, that would have to be removed to pull all the USER information.

No matter what "group by" I toss in, it doesn't work. I get like 20 rows back for random USERs and DATE_RECD months.

I'm guessing there has to be some additional partitioning in there, but I have no idea where at this point and my mind is mush looking at it.

ANY help would be appreciated.

SELECT DATE, x.USER,
AVG(No_Days) As Median_Days_to_SUIT
From
(

select CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE) As Date, 
master.USER,
DATEDIFF(DAY,MASTER.DATE_RECD,INFINITY.FIELD) as No_Days, 
ROW_NUMBER() over (partition by CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE) order by DATEDIFF(DAY,MASTER.DATE_RECD,INFINITY.FIELD) ASC) as DaysRank,
COUNT(*) over (partition by CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE)) as EntryCount
from MASTER INNER JOIN INFINITY on master.FILENO = INFINITY.FILENO
WHERE INFINITY.CODE = '*LglDt'
AND DATEDIFF(MONTH,MASTER.DATE_RECD,GETDATE()) <= 11

and master.USER = '2'

) x
WHERE
x.DaysRank in (x.EntryCount/2+1, (x.EntryCount+1)/2)    
group by
   x.USER, x.DATE
    order by DATE

 

[KLin]

Please post the schema of the tables involved with sample data. Create scripts of the tables would be good.

 

[Homerboy]

CREATE TABLE [dbo].[MASTER](
	[Rec_No] [varchar](50) NULL,
	[FILENO] [varchar](50) NULL,
	[DATE_RECD] [date] NULL,
	[USER] [int] NULL,
	[BALANCE] [real] NULL,
	[FORW_NO] [int] NULL

) ON [PRIMARY]

GO

GO

CREATE TABLE [dbo].[INFINITY](
	[FILENO] [varchar](50) NULL,
	[CODE] [varchar](50) NULL,
	[TYPE] [varchar](50) NULL,
	[FIELD] [varchar](max) NULL,
	[DATE] [date] NULL,
	[TIME] [varchar](50) NULL,
	[USER_ID] [varchar](50) NULL
) ON [PRIMARY]

GO

Is that what you were looking for?

 

[KLin]

some sample data too, otherwise you're probably not going to get much help.

 

[Homerboy]

INFINITY table:

fileno	code	field
T716480	*LglDt	2012/05/17
T703657	*LglDt	2012/06/06
686594	*LglDt	2012/05/22
716342	*LglDt	2012/05/22
690402	*LglDt	2012/01/05
717724	*LglDt	2012/06/01
717703	*LglDt	2012/06/01
717633	*LglDt	2012/06/01
717669	*LglDt	2012/06/01
717587	*LglDt	2012/06/01
717598	*LglDt	2012/06/01
718566	*LglDt	2012/06/01

Master Table:

fileno	DATE_RECD	USER
686594	2011-10-04	73
690402	2011-11-01	13
716342	2012-04-15	27
717587	2012-04-27	44
717598	2012-04-27	40
717633	2012-04-27	40
717669	2012-04-27	40
717703	2012-04-27	40
717724	2012-04-27	40
718566	2012-05-08	73
T703657	2012-02-27	78
T716480	2012-04-17	78

 

[KLin]

The more I look at this, the more my brain wants to explode. I have no idea what you're trying to do.

 

[Homerboy]

The more I look at this, the more my brain wants to explode. I have no idea what you're trying to do.

LOL.
Now you know how I feel!

I'm to the point of just pulling the data and letting Excel do the number crunching.

 

[Tweak155]

An example of (desired) result data would probably help too.

 

[Homerboy]

I actually got it with a loop and a temp table. It's not "pretty" or "right" but this is run once a week in off hours, so I don't care too much 🙂

DECLARE @USER_var INT
DECLARE @temp TABLE(month date, USER int, days int)
SET @USER_var = 2
WHILE (@USER_var <=89)
BEGIN

INSERT INTO @temp (month, USER, days)
SELECT DATE, x.USER,
AVG(No_Days) As Median_Days_to_SUIT
From
(

select CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE) As Date, 
master.USER,
DATEDIFF(DAY,MASTER.DATE_RECD,INFINITY.FIELD) as No_Days, 
ROW_NUMBER() over (partition by CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE), master.USER order by DATEDIFF(DAY,MASTER.DATE_RECD,INFINITY.FIELD) ASC) as DaysRank,
COUNT(*) over (partition by CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE)) as EntryCount
from MASTER INNER JOIN INFINITY on master.FILENO = INFINITY.FILENO
WHERE INFINITY.CODE = '*LglDt'
AND DATEDIFF(MONTH,MASTER.DATE_RECD,GETDATE()) <= 11
and master.USER = @USER_var
) x
WHERE
x.DaysRank in (x.EntryCount/2+1, (x.EntryCount+1)/2)    
group by
   x.USER, x.DATE


SET @USER_var = @USER_var + 1

END
SELECT * from @temp
GO