February 18, 2022 at 4:09 pm
Hi Expert,
I am facing multple 0 value records and needs only expired and active records i.e. 0 ,1 for below table. Added sample table for your reference
div idCustomerID Div_Status LogDate
-1 -1 0 18-01-2022
-1 -1 0 19-01-2022
-1 -1 0 20-01-2022
-1 -1 0 22-01-2022
-1 -1 0 23-01-2022
-1 -1 1 18-01-2022
-1 -1 0 28-01-2022
-1 -1 0 23-01-2022
-1 -1 0 19-01-2022
2 -1 1 25-01-2022
2 -1 0 27-01-2022
2 -1 1 28-01-2022
THe above table requires following out put which is order by div id div- status should only filter 0 and 1 records 0- expired and 1 means active as there are so many expired reports i need only expired and active records
div id CustomerID Div_Status LogDate
-1 -1 0 18-01-2022
-1 -1 1 18-01-2022
-1 -1 0 23-01-2022
2 -1 0 23-01-2022
2 -1 1 25-01-2022
I tried
select Div Id as [Div ID],[divStatus],[CustomerID] as [Customer ID],LogDate ,DateID as [Date ID]
from ( SELECT [Fiv id], lag(Div status)over(partition by [Div id] order by
[CustomerID],LogDate) as previousvalue,
div status as [divStatus], lead (div status)over(partition by ,logdate
order by [CustomerID],LogDate) as nextvalue,
LogDate,[CustomerID]
FROM Table1 ) as TBL
where
(PREVIOUSVALUE IS NULL AND [contract Status]=0 AND NEXTVALUE=1) or
(PREVIOUSVALUE=0 AND [Contract Status]=0 AND (NEXTVALUE)=1 ) or
(PREVIOUSVALUE=0 AND [Contract Status]=1 AND NEXTVALUE=1)
but still getting multiple zero records
February 18, 2022 at 7:05 pm
I am struggling to understand your requirements, and your suggested query references columns that do not exist anywhere in your code ([Contract Status] and [Date ID]). I am also unclear what you mean when you say you only want active and expired rows. That sounds like all rows.
I do not see a row in your data with DivID = 2 and LogDate = 23-01-2022, so I don't know how you expect a query to return that data.
I have converted your spreadsheet into DDL and attempted to change the query so that it runs and returns some of the output required, but I don't understand what you need it to do. Maybe with the DDL you can experiment. Your query had the LEAD statement partitioned by LogDate. Was that deliberate? I changed it to DivID.
DROP TABLE IF EXISTS dbo.Table1
CREATE TABLE dbo.Table1
( DivID INT,
CustomerID INT,
DivStatus INT,
LogDate DATE
)
INSERT dbo.Table1 (DivID, CustomerID, DivStatus, LogDate)
VALUES (-1, -1, 0, '1/18/22'),
(-1, -1, 0, '1/19/22'),
(-1, -1, 0, '1/20/22'),
(-1, -1, 0, '1/22/22'),
(-1, -1, 0, '1/23/22'),
(-1, -1, 1, '1/18/22'),
(-1, -1, 0, '1/28/22'),
(-1, -1, 0, '1/23/22'),
(-1, -1, 0, '1/19/22'),
(2, -1,1, '1/25/22'),
(2, -1,0, '1/27/22'),
(2, 1,1, '1/28/22')
SELECT t.DivID,
t.CustomerID,
t.DivStatus,
t.LogDate ,
t.LogDate
FROM ( SELECT DivID,
CustomerID,
DivStatus,
LogDate,
LAG(DivStatus,1,0) OVER (PARTITION BY DivID ORDER BY CustomerID, LogDate) AS PreviousValue,
LEAD (DivStatus,1) OVER (PARTITION BY DivID ORDER BY CustomerID, LogDate) as NextValue
FROM dbo.Table1
) AS t
WHERE (t.PreviousValue = 0 AND DivStatus = 1 AND t.NextValue = 1 ) OR
(t.PreviousValue = 0 AND DivStatus = 0 AND t.NextValue = 1 ) OR
(t.PreviousValue = 0 AND DivStatus = 1 AND t.NextValue = 0 ) OR
(t.PreviousValue = 1 AND DivStatus = 0 AND t.NextValue = 0 )
DROP TABLE IF EXISTS dbo.Table1
February 18, 2022 at 7:13 pm
The requirements aren't clear. You should explain with an example of how you decide a row with xx as data is included and a row with yyy is not.
Remember that data isn't ordered in SQL Server, so this has to be based on the values themselves and any ORDER BY that can be applied.
February 21, 2022 at 8:09 pm
Hi Expert,
I pump more data and tried to get values 1,0(active and inactive) order by logdate but even changing in queries not getting proper values for whole table. Sample data and query output attached in excel sheet
Issue - the issue is 1,0 values are not coming uniform for whole table
here is the modified query
SELECT Div_ID,
CustomerID,
Div_Status,
LogDate ,
LogDate
FROM ( SELECT Div_ID,
CustomerID,
Div_Status,
LogDate,
LAG(Div_Status,1,0) OVER (PARTITION BY Div_ID ORDER BY logdate) AS PreviousValue,
LEAD (Div_Status,0,1) OVER (PARTITION BY Div_ID ORDER BY logdate) as NextValue
FROM dbo.Table_1 --1q1DS1ES1SE1EE41E41EE4EE4`E41R4EEr
) AS t
WHERE (PreviousValue = 0 AND Div_Status = 1 AND NextValue = 1 ) OR
(PreviousValue = 0 AND Div_Status = 0 AND NextValue = 1 ) OR
(PreviousValue = 0 AND Div_Status = 1 AND NextValue = 0 ) OR
(PreviousValue = 1 AND Div_Status = 0 AND NextValue = 0 )
Best Regards
February 21, 2022 at 8:15 pm
Table is attached in excel sheet due to format issue
Please see the article at the first link in my signature line below for how to post "readily consumable" data in future posts.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2022 at 8:23 pm
Attached excel sheet
See above.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2022 at 5:16 am
Hi Expert,
here is the query
Create table statement
CREATE TABLE [dbo].[Table_1](
[div_id] [nchar](10) NULL,
[customerid] [nchar](10) NULL,
[div_status] [nchar](10) NULL,
[logdate] [date] NULL
) ON [PRIMARY]
GO
Insert Data:
INSERT INTO [dbo].[Table_1]
([div_id]
,[customerid]
,[div_status]
,[logdate])
VALUES
('495','-1','-1','2021-02-14'),
('495','-1' ,'1','2021-02-14'),
('495','-1' ,'1','2021-02-14'),
('502' ,'-1', '1','2021-02-14'),
('513', '-1','1','2021-02-14'),
('538', '-1','1','2021-02-14'),
('545', '-1','1','2021-02-14'),
('563', '-1','1','2021-02-14'),
('577', '-1','1','2021-02-14');
February 22, 2022 at 7:42 pm
output is getting more 1,1,1 records 1,0,1,0..Any suggestion...
February 23, 2022 at 1:39 am
You're confused me and, perhaps, others. How is it that your test data now contains a -1 instead of a 0 in the Div_Status column? And what result do you expect to see from the test data that you've just provided?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2022 at 3:17 am
Actually more data loaded.. So instead of 1,01,0 records more 1,1,1 values coming div_status
February 23, 2022 at 8:10 am
sorry -1 mistakenly came. it is 1 only in the first row
February 23, 2022 at 3:12 pm
Suggestion please
February 24, 2022 at 7:59 am
You original post talked about 1's and 0's. You've now post data with only 1's and haven't posted the result that you're looking for. I don't know about anyone else but I don't actually know what you're trying to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply