Query TSQL

Question

Query TSQL

Right there with Babe

Points: 768

September 13, 2019 at 8:30 am

USE Test
GO

IF OBJECT_ID('customer') IS NOT NULL
DROP TABLE dbo.Customer
GO

-- PK or unique key is combined key(custid,procustid,team_id)

CREATE TABLE dbo.Customer
(custid int,
procustid varchar(50),
team_id int,
Sdate datetime
)

-- Sample data - sdate column can has future date

INSERT INTO dbo.Customer
values(100,'P1010',10,'2019-09-13') -- customer 1
,(100,'P1010',10,'2019-09-13')
,(100,'P1010',10,'2019-10-15') -- next month he is moving out
,(102,'P1111',12,'2019-08-10') -- customer 2
,(102,'P1111',12,'2019-08-10')
,(102,'P1111',12,'2019-09-13') -- this month he is moving IN
,(105,'P1015',15,'2019-09-13') -- customer 3
,(105,'P1010',15,'2019-10-15') -- No change
,(106,'P1116',14,'2019-08-10') -- customer 4
,(106,'P1116',14,'2019-08-10')
,(106,'P1116',14,'2019-10-17') -- next month he is moving out?

-- output i want (with resepect to current month if customer moving_out or moving_in)

-- select * from dbo.Customer

custid , procustid ,team_id ,Sdate AS start_date ,Sdate AS end_date, Moving_out/Moving_in , date_difference_in_days

This topic was modified 5 years, 2 months ago by Steve Jones - SSC Editor. Reason: Formatted code

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Is the data which you provided the desired output, or is it the base data?

Whichever it is, can you also provide the other part?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

As Phil mentioned, it's good to see what the data is. What I usually want is

-- base data
insert table values (a, 1), (b, 2), (c, 3)

--- results

create table #expected (...)

insert #expected values (a, 9)?

As a way to show us what you start with and what's expected as a result. This makes it easy for automated testing as well as you thinking through the exact results.

drew.allen SSC Guru Points: 76985 More actions · Answer 3

This problem is unsolvable with the information that you have given us. We don't know the logic for whether someone is moving out or moving in and there is nothing obvious in the data. It appears that even you don't know the logic, because you ask -- next month he is moving out?

Drew

This reply was modified 5 years, 2 months ago by Steve Jones - SSC Editor.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

I'll also state that if you have the UNIQUE index you say you do as...

-- PK or unique key is combined key(custid,procustid,team_id)

... we'd not be able to build the sample data you included because of duplicate key violations.

I also agree with Drew... there is insufficient data to guarantee that such code could work reliably.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)