July 12, 2016 at 3:17 am
I've 4 tables namely LoginType, UsrReportType, UsrList, Log_UsrAccess. LoginType (Id) table has a bitwise integer type id's which is a foreign key in UsrReportType table (LoginTypeID). I need to pull data from UsrList table and Log_UsrAccess tables based on the input parameter(ReportTypeDescription column of UsrReportType table) sent from the UI.
I'm attaching the dml for the tables, can anyone please help me getting the data based on bitwise?
LoginType table
------------------------------------------------------------------------------------
CREATE TABLE [dbo].[LoginType](
[Id] [bigint] NOT NULL,
[Description] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_LoginType] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------
UsrReportType table
---------------------------------------------------------------------------------------
CREATE TABLE [dbo].[UsrReportType](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[LoginTypeID] [bigint] NOT NULL,
[ReportTypeDescription] [nvarchar](250) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UsrReportType] WITH CHECK ADD CONSTRAINT [FK_UsrReportType_ID_LoginType] FOREIGN KEY([LoginTypeID])
REFERENCES [dbo].[LoginType] ([Id])
GO
ALTER TABLE [dbo].[UsrReportType] CHECK CONSTRAINT [FK_UsrReportType_ID_LoginType]
GO
-------------------------------------------------------------------------------------
UsrList table
--------------------------------------------------------------------------------------
CREATE TABLE [dbo].[UsrList](
[UsrID] [bigint] IDENTITY(1,1) NOT NULL,
[LoginID] [nvarchar](20) NULL,
[UsrName] [nvarchar](120) NULL,
[Gender] [nvarchar](1) NULL,
[MailAddr] [nvarchar](max) NULL,
[Email] [nvarchar](60) NULL,
[CreatedDate] [datetime2](7) NOT NULL CONSTRAINT [DF_UsrList_CreatedDate] DEFAULT (sysdatetime())
CONSTRAINT [PK_UsrList] PRIMARY KEY CLUSTERED
(
[UsrID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------
Log_UsrAccess table
--------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[LOG_UsrAccess](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[LogType] [int] NULL,
[UsrID] [bigint] NOT NULL,
[AppVersion] [nvarchar](25) NULL,
[UsrLoginDate] [datetime] NULL,
[UsrLogoutDate] [datetime] NULL,
[Action] [nvarchar](10) NULL,
[Remark] [nvarchar](300) NULL,
CONSTRAINT [LOG_UsrAccess] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Below is the DDl for the 2 tables
INSERT INTO [dbo].[LoginType] ([Id], [Description]) VALUES
(1, N'Login')
, (2, N'Logout')
, (4, N'InvalidPassword')
, (8, N'Suspended')
, (16, N'AbnormalLogout')
, (32, N'Close')
, (64, N'ResetPassword')
, (128, N'InvalidPeriod')
, (256, N'ChangePassword')
, (512, N'Unsuspend')
, (1024, N'FailedLogin')
, (2048, N'InvalidLoginID')
;
INSERT INTO [dbo].[UsrReportType]
([LoginTypeID],[ReportTypeDescription]) VALUES
(1, N'Login Activities')
, (2, N'Login Activities')
, (4, N'Login Activities')
, (8, N'Status Change Activities')
, (16, N'Login Activities')
, (32, N'Status Change Activities')
, (64, N'Password Change Activities')
, (128, N'Status Change Activities')
, (256, N'Password Change Activities')
, (512, N'Status Change Activities')
, (1024, N'Login Activities')
, (2048, N'Login Activities')
;
Will get input parameter from the UI as 'Login Activities', Then i need to join the above 4 tables and fetch the data from Log_UsrAccess, UsrList table.
Thanks,
July 12, 2016 at 4:01 am
I've tried with the below query, But not able to getting records for lets say, I've a record with ID 260 which is a combination of 256 + 4,. (I should expect 2 records, rather getting only 1 record means bitwise is not being applied)
Select ul.LoginID, lo.UsrIPAddress, bo.Description, lo.Remark, lo.AppID, lo.AppVersion, lo.UsrLoginDate, lo.UsrLogoutDate
From LOG_UsrAccess lo
inner Join LoginType bo on bo.Id=lo.LogType
inner join UsrList ul on ul.UsrID=lo.UsrID
inner join UsrReportType ur on ur.LoginTypeID=bo.Id
Where lo.LogType & bo.Id <> 0 AND ur.ReportTypeDescription='Password Change Activities'
July 12, 2016 at 7:12 am
July 12, 2016 at 7:50 am
Can you share sample data (no need for real data) for UsrList and LOG_UsrAccess tables? Also the expected results from that sample data.
Thank you.
July 12, 2016 at 9:05 pm
Hi,
Please find the data for 2 tables below.
UsrList Table:
INSERT [dbo].[UsrList] ([UsrID], [LoginID], [UsrName], [Gender], [MailAddr], [Email], [CreatedDate]) VALUES
(1, N'B1ADMIN', B1A, B1Usr, M, NULL, b1@e.com, CAST(N'2015-01-12 00:00:00.0000000' AS DateTime2))
,(2, N'B2ADMIN', B2A, B2Usr, M, NULL, b2@e.com, CAST(N'2015-02-12 00:00:00.0000000' AS DateTime2))
,(3, N'B3ADMIN', B3A, B3Usr, M, NULL, b3@e.com, CAST(N'2015-03-12 00:00:00.0000000' AS DateTime2))
,(4, N'B4ADMIN', B4A, B4Usr, M, NULL, b4@e.com, CAST(N'2015-04-12 00:00:00.0000000' AS DateTime2))
,(5, N'B5ADMIN', B5A, B5Usr, M, NULL, b5@e.com, CAST(N'2015-05-12 00:00:00.0000000' AS DateTime2))
Log_UsrAccess table:
INSERT INTO [dbo].[LOG_UsrAccess]([LogType],[UsrID],[AppVersion],[UsrLoginDate],[UsrLogoutDate],[Action],[Remark])VALUES
(260, 1, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
,(32, 2, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
,(24, 3, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
,(512, 4, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
(1024, 2, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
,(1025, 2, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
,(33, 3, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
,(2052, 4, '0.0.0.1', GETDATE(), GETDATE(), 'Test', 'Remarks')
GO
LogType is primary key in LoginType table, So in Log_UsrAccess table i'm saving LogType as 260 (256 +4), 1025 (1024+1), 2052 (2048+4). When i try to retrieve users from Log_UsrAccess, I need to get 256 log type as well as 4 log type (2 records).
From frontend i'll simply pass 'Login Activities' which is in the table UsrReportType and which has LogType as foreign key, Then i need to fetch all the user individual log types associated with Login Activities. If the LOgType in Log_UsrAccess is 260, Then i need to show 2 records (256 + 4). I hope it is clear.
Thanks,
July 12, 2016 at 9:20 pm
With this query, I'm able to get the records.. Can you please check this query once?
with assoc as
(
Select l.UsrIPAddress,l.Action,l.AppVersion,bl.Description, l.AppID, l.UsrLoginDate, l.UsrLogoutDate
From LOG_UsrAccess l
Cross Join UsrReportType lt
inner join LoginType bl on bl.Id=lt.LoginTypeID
Where l.LogType & lt.LoginTypeID <> 0 AND ReportTypeDescription='Login Activities'
)
select * from assoc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply