CODEWITHSUNDEEP
sql-serversql-server-2008
Satinder singh
Satinder singh

Reputation: 10198

SQL Server query: convert rows to columns

I have a table containing columns VisitorName, vitising_date, location Id. I need to display record for current month visitor location wise along with last month visitor

SQL FIDDLE

Table schema:

CREATE TABLE visitors
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [name] varchar(50),
    [location_id] [bigint],
    [visited_date] [datetime]
);

name   location_id    visited_date
David      1           2016-01-04
John       1           2016-01-02
Andrea     1           2016-01-05
Ely        2           2016-01-04
Andrea     1           2016-02-02
John       1           2016-02-02
Peter      2           2016-02-02

Query for current month record:

select * 
from visitors 
where 
    DATEPART(month, visited_date) = DATEPART(MONTH, GETDATE()) 
    and DATEPART(year, visited_date) = DATEPART(year, getdate())

But i need data to be display last month record along current month something like this

  Locationid   Name     Date        LastMonthVisitor   LastMonthVisitingDate
   1           Andrea   2016-02-02  Andrea              2016-01-05 
   1             John   2016-02-02  David               2016-01-04 
   1            'NA'    'NA'        John                2016-01-02 

   2            Peter  2016-02-02   Ely                 2016-01-04
  1. The record shows list of visitors of current month group by location id (location id is foreign key)
  2. Now I need list of last month visitor group by Location ID

These result are place next to each other order by date. Their might be change where last month visitor record is 5 for location_id 1 and current month visitor would be 2 So finally it will show 5 record. Their is no relation between Current Month visitor name, LastMonth visitor name.

I can do did part on server side C# and dispaly list of CurrentMonthVisitors aong with LastMonthVisitor group by Location ID. But am trying in SQL.

Upvotes: 1

Views: 102

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this, it doesn't give the exact same result, but it seems to me that your expected result doesn't match the data:

;WITH CTE as
(
  SELECT
   *, 
   -- setting a row number based on month, and location
   row_number() over 
     (partition by datediff(month, 0, visited_date), 
     location_id order by visited_date, name) rn,
    datediff(month, 0, visited_date) month
  FROM visitors
  WHERE
    -- current and last month
    visited_date >= dateadd(month, datediff(month, 0, getdate()) - 1, 0) and
    visited_date < dateadd(month, datediff(month, -1, getdate()), 0)
)
SELECT 
  CTE.location_id Locationid,
  COALESCE(CTE.Name, 'N/A') Name,
  CAST(CTE.visited_date as Date) Date,
  COALESCE(CTE2.name, 'N/A') LastMonthVisitor,
  CAST(CTE2.visited_date as Date) LastMonthVisitingDate
FROM CTE
-- full join to get data when there is not matching data for the other month
FULL JOIN
CTE CTE2
ON CTE.rn = CTE2.rn
and CTE.location_id = CTE2.location_id
and CTE.month = CTE2.month-1
WHERE
  -- making sure CTE is last month or CTE2 is current month
  cte.month =datediff(month, 0, getdate()) - 1 or
  cte2.month=datediff(month, 0, getdate())

Upvotes: 1

Related Questions