CODEWITHSUNDEEP
sql-server-2008t-sql
user2342574
user2342574

Reputation: 107

Select Query From MSSQL group by weekly data

I used the following query with sql server 2008 to select group by records.

     Sum(ISNULL(ljoin,0)) AS ljoin,
     Sum(ISNULL(rjoin,0)) AS rjoin, DATEPART(wk, Date) AS WeekNumber,
     CASE 
     WHEN YEAR(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min([date])), Min([date]))) < YEAR(Min([date]))
     THEN CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0 ,GETDATE())), 0) AS      Varchar(50))   + ' TO ' + Cast(DATEADD(dd, 7-(DATEPART(dw, Min([date]))), Min([date]))  AS Varchar(50))
    ELSE
    Cast(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min([date])), Min([date])) AS Varchar(50)) + ' TO ' +       Cast(DATEADD(dd, 7-(DATEPART(dw, Min([date]))), Min([date]))  AS Varchar(50))
    END AS  DateRange
    FROM Pairs_Details  where userid='jitu'
    Group By userid,DATEPART(wk, Date)

it return following result. for perticular userid ='jitu'

   userid   ljoin   rjoin   WeekNumber  DateRange                             
   jitu 1   2   1   Jan  1 2014 12:00AM TO Jan  6 2013 12:00AM
   jitu 1   1   2   Jan  6 2014 12:00AM TO Jan 12 2014 12:00AM
   jitu 1   0   3   Jan 13 2014 12:00AM TO Jan 19 2014 12:00AM
   jitu 2   0   5   Jan 27 2014 10:00PM TO Feb  2 2014 10:00PM

It started with 2012-08-06 since the cycle should be start(2014-01-01)monday to Sunday. I've tried googling about an hour or so and it seems that no result fits with my problem, I Hope someone could help me there are my following table, data and desire output which I want

Pairs_Details table definition:

CREATE TABLE [dbo].[Pairs_Details](
[sno] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NULL,
[date] [datetime] NULL,
[ljoin] [int] NULL,
[rjoin] [int] NULL
   ) ON [PRIMARY]

Example data:

   sno  userid              date                            ljoin rjoin
   1    jitu                2013-01-01 00:00:00.000         1   NULL
   2    jitu                2014-01-02 00:00:00.000             NULL    1
   3    tetu1234            2014-01-03 00:00:00.000     1   NULL
   4    jitu                2014-01-04 00:00:00.000             NULL    1
   5    saurbh123           2014-01-05 00:00:00.000     1   NULL
   6    jitu                2014-01-06 00:00:00.000         1   NULL
   9    saurbh123           2014-01-12 00:00:00.000         NULL    1
  10    jitu                2014-01-13 00:00:00.000         1   NULL
  11    rajeev123           2014-01-29 22:00:13.000         1   NULL
  12    saurbh123           2014-01-29 22:00:13.000         1   NULL
  13    jitu                2014-01-29 22:00:13.000         1   NULL
  14    rajeev123           2014-01-29 22:01:03.000         NULL    1
  15    saurbh123           2014-01-29 22:01:03.000         1   NULL
  16    jitu                2014-01-29 22:01:03.000         1   NULL
  7         tetu1234        2014-01-07 00:00:00.000         NULL    1
  8         jitu                2014-01-08 00:00:00.000         NULL    1

i want the following result

 userid ljoin   rjoin   WeekNumber  DateRange                               pair        
   jitu 1   2   1      Jan  1 2014 12:00AM TO Jan  6 2013 12:00AM          1
   jitu 1   1   2      Jan  6 2014 12:00AM TO Jan 12 2014 12:00AM          1
   jitu 1   0   3      Jan 13 2014 12:00AM TO Jan 19 2014 12:00AM          0
   jitu 2   0   5      Jan 27 2014 10:00PM TO Feb  2 2014 10:00PM          0

this can be done using following query.

Select Case When ljoin <= rjoin  Then ljoin Else rjoin End As pair from Pairs_Details

but how this query can be used with above query pleae any one can tell us

please any can help me. so that we can retrieve desired result

thanks.

Upvotes: 0

Views: 695

Answers (1)

Naveen Kumar
Naveen Kumar

Reputation: 1541

--Updated Query I tried to get desired output but still you need to just play around as per your requirement; All the Best!!! 

SET DATEFIRST 1;
SELECT  userid,
Sum(ISNULL(ljoin,0)) AS lJoin,
Sum(ISNULL(rjoin,0)) AS rjoin, DATEPART(wk, Date) AS WeekNumber,
CASE 
WHEN YEAR(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min([date])), Min([date]))) < YEAR(Min([date]))
THEN CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0 ,GETDATE())), 0) AS Varchar(50))   + ' TO ' + Cast(DATEADD(dd, 7-(DATEPART(dw, Min([date]))), Min([date]))  AS Varchar(50))
ELSE
Cast(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min([date])), Min([date])) AS Varchar(50)) + ' TO ' + Cast(DATEADD(dd, 7-(DATEPART(dw, Min([date]))), Min([date]))  AS Varchar(50))
END AS  DateRange
FROM Pairs_Details
Group By userid,DATEPART(wk, Date)

Upvotes: 1

Related Questions