September 6, 2017 at 5:28 am
Hi Team,
I have data like below.
CREATE TABLE #Table
(
osid INT,Date DATETIME,Dgrt INT,Avol FLOAT,UpDnvl FLOAT ,Adnm INT ,RlSt INT ,GpRkINT INT
)
INSERT #Table
SELECT 14390 ,'2017-09-01 00:00:00.000', 80, 1530.2, 1.13, -3 ,66, 59
UNION ALL SELECT 14845 ,'2017-09-01 00:00:00.000', 67, 14592.1, 1.16, -14 ,66, 59
UNION ALL SELECT 14845 ,'2017-08-25 00:00:00.000', 68, 15025.9, 1.26, 0 ,76, 46
UNION ALL SELECT 14390 ,'2017-08-25 00:00:00.000', 90, 1615.1, 1.50, 10 ,81, 46
SELECT * FROM #Table
DROP TABLE #Table
If we see the results we RlSt value as 60 for the same dates. in that case, we need to take values from the previous week but if the previous date's values are also same we need to take before previous weeks value. Like that we have values for 11 weeks for each OSID. Please help me with the logic.
Regards,
Vijay
September 6, 2017 at 5:38 am
vijay.singh 46672 - Wednesday, September 6, 2017 5:28 AMIf we see the results we RlSt value as 60 for the same dates. in that case, we need to take values from the previous week but if the previous date's values are also same we need to take before previous weeks value. Like that we have values for 11 weeks for each OSID. Please help me with the logic.
Possibly due to the language barrier, but I don't really understand what you mean here. Could you provide expected results for your sample data?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 6, 2017 at 6:21 am
Hi,
If you see the result in my previous posted query, we get 2 different OSID's for the same date('2017-09-01 00:00:00.000') with RlSt value as 66. In this case, we need to find the values for RlSt from the previous date(2017-08-25 00:00:00.000).
If the RlSt values are different for both the OSID's for the same date we can use the value of RlSt to display else we need to follow the same process.
Actual result:
osid Date Dgrt Avol UpDnvl Adnm RlSt GpRkINT
----------- ----------------------- ----------- ---------------------- ---------------------- ----------- ----------- -----------
14390 2017-09-01 00:00:00.000 80 1530.2 1.13 -3 66 59
14845 2017-09-01 00:00:00.000 67 14592.1 1.16 -14 66 59
Below is the expected result.
osid Date Dgrt Avol UpDnvl Adnm RlSt GpRkINT
----------- ----------------------- ----------- ---------------------- ---------------------- ----------- ----------- -----------
14845 2017-08-25 00:00:00.000 68 15025.9 1.26 0 76 46
14390 2017-08-25 00:00:00.000 90 1615.1 1.5 10 81 46
Regards,
Vijay
September 6, 2017 at 8:30 am
I think we're still missing something, as SELECT * would return all 4 rows, not just 2, and it's not entirely clear other than you want rows that have different Rlst values for the same date. This is at least a starting point for further discussion though:SELECT agg.osid, agg.[Date], agg.Dgrt, agg.Avol, agg.UpDnvl, agg.Adnm, agg.RlSt, agg.GpRkINT
FROM
(SELECT osid, [Date], Dgrt, Avol, UpDnvl, Adnm, RlSt, GpRkINT,
MIN(Rlst) OVER (PARTITION BY [Date]) AS RlSt_min,
MAX(Rlst) OVER (PARTITION BY [Date]) AS RlSt_max
FROM #Table) agg
WHERE agg.RlSt_min <> agg.RlSt_max
September 6, 2017 at 11:17 pm
Hi,
Please find the attached excel and import into DB. In the attached file for date (2017-09-01 00:00:00.000) we will get 4 rows for RlSt= 60. so what I need is to traverse to the previous date(2017-08-25 00:00:00.000) and pick the RlSt value for those 4 OSID's. if again the value repeats, we need to go back to the previous date and again check for the RlSt value the values are unique for those 4 OSID's .
Regards,
Vijay
September 7, 2017 at 1:20 am
vijay.singh 46672 - Wednesday, September 6, 2017 11:17 PMHi,
Please find the attached excel and import into DB. In the attached file for date (2017-09-01 00:00:00.000) we will get 4 rows for RlSt= 60. so what I need is to traverse to the previous date(2017-08-25 00:00:00.000) and pick the RlSt value for those 4 OSID's. if again the value repeats, we need to go back to the previous date and again check for the RlSt value the values are unique for those 4 OSID's .Regards,
Vijay
You'll find a lot of people won't be happy download a xlsx file from an unknown source (I don't have my Linux box with me today, so I won't be). Also, Excel doesn't tell us what your data types are, so we have to guess. You'd be better provided DDL and DLM in T-SQL, have a look at the link in my signature.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 7, 2017 at 1:54 am
Hi,
Please find the script below.
USE [Wondb]
GO
/****** Object: Table [dbo].[Test] Script Date: 9/7/2017 1:11:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
[osid] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Dgrt] [int] NULL,
[Avol] [float] NULL,
[UpDnvl] [money] NULL,
[Adnm] [int] NULL,
[RlSt] [int] NULL,
[GpRk] [int] NULL,
[ID] [bigint] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 80, 1530.2, 1.1300, -3, 66, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 90, 1615.1, 1.5000, 10, 81, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 87, 1703.3, 1.6500, 2, 80, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 87, 1739.5, 1.9100, 4, 85, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 88, 1761.6, 1.9400, 11, 87, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 87, 1775.8, 1.7300, 5, 84, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 88, 1762.7, 1.6900, -2, 86, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 90, 1698.5, 1.8000, 14, 92, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 90, 1752.2, 1.7200, 18, 92, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 1894.5, 1.7200, 30, 91, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14390, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 83, 1743.6, 1.3600, 16, 85, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 67, 14592.1, 1.1600, -14, 66, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 68, 15025.9, 1.2600, 0, 76, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 62, 15960.1, 1.1800, -2, 72, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 66, 17024.4, 0.8900, 4, 81, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 67, 17212.6, 1.0300, 12, 87, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 65, 17389.5, 0.8100, 8, 83, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 65, 16868.8, 0.7700, 12, 83, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 67, 16999.6, 1.0000, 28, 87, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 67, 17629.9, 1.0300, 23, 88, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 18861.3, 0.8600, -16, 83, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14845, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 60, 18295.4, 0.9000, -13, 73, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 63, 5085, 1.1800, -20, 47, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 64, 5413.3, 1.1200, -9, 58, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 67, 5776.3, 1.1600, -8, 60, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 67, 5946.2, 1.2000, 5, 65, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 63, 6022.8, 1.0700, 16, 61, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 63, 6049.9, 1.0400, 13, 63, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 63, 5924.1, 0.8300, -4, 65, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 65, 5832.6, 0.8900, -19, 69, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 69, 5958.4, 0.8700, -18, 72, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 6464.9, 0.7900, 9, 58, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14863, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 59, 6371.1, 0.9200, 13, 53, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 69, 5521, 0.9300, -20, 66, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 71, 5665.8, 1.0300, -17, 76, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 72, 5936.4, 1.2100, -17, 76, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 73, 6009.4, 1.1600, -8, 79, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 69, 6021.1, 1.1300, -11, 82, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 68, 6185.4, 1.1400, -8, 73, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 68, 6041.7, 0.9600, -7, 70, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 74, 5841.5, 1.0500, -8, 80, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 70, 6040.8, 1.0100, -2, 83, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 6274.5, 0.8500, -2, 65, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (14925, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 50, 6121.8, 0.7700, -12, 48, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 60, 8641.7, 1.2500, -11, 47, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 61, 8797.5, 1.2300, -12, 58, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 60, 9191.7, 1.3500, -15, 63, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 61, 9350.1, 1.1600, -2, 69, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 69, 9187.8, 1.2200, -3, 74, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 68, 9154.6, 1.2200, 9, 69, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 69, 8703.7, 1.0200, -10, 82, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 68, 8547, 1.2100, 12, 85, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 64, 8418.7, 1.1000, 11, 85, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 9267.5, 0.8100, 7, 73, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15154, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 52, 9435.6, 0.7300, 8, 57, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 67, 10424.7, 0.7500, -32, 54, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 69, 10697.8, 0.8000, -29, 64, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 69, 11211, 0.9800, -29, 69, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 69, 11099.3, 1.0900, -15, 72, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 77, 10984.8, 1.3000, -7, 76, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 76, 10873.7, 1.2500, -14, 77, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 77, 10241, 1.1300, -19, 83, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 79, 9586.3, 1.4200, 11, 90, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 80, 9668.4, 1.5800, 19, 90, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 10487.6, 1.2800, 22, 85, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15447, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 73, 10462.8, 1.2300, 28, 80, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 69, 1222.7, 1.0600, 17, 60, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 75, 1262.6, 1.3000, 15, 72, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 74, 1256.9, 1.3800, 2, 69, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 73, 1254.8, 1.2200, -1, 65, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 74, 1215.2, 1.2500, 7, 68, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 71, 1219.4, 1.2200, 5, 63, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 66, 1157.6, 1.2100, 0, 68, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 77, 981.3, 1.8900, 29, 87, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 77, 1037.7, 1.8700, 29, 88, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 1037.3, 1.5000, 30, 81, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (15920, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 65, 1006.6, 1.2300, 14, 69, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 74, 2124.7, 1.0200, -28, 75, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 75, 2116.9, 1.2200, -16, 83, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 77, 2233.7, 1.1900, -15, 84, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 77, 2199.9, 1.2600, 4, 86, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 77, 2105.7, 1.4000, 26, 86, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 77, 2097.3, 1.3700, 15, 85, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 77, 2041.4, 1.2400, 15, 85, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 79, 2050.3, 1.3100, 24, 86, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 79, 1992.7, 1.3100, 20, 86, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 2199.7, 0.9500, -1, 80, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16189, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 66, 2177.3, 0.8900, -2, 73, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 71, 3938.6, 1.3500, -12, 63, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 71, 3873, 1.4700, -12, 67, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 70, 4005.3, 1.4800, -6, 69, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 71, 4055.3, 1.5400, 7, 76, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 71, 3891.7, 1.8000, 35, 76, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 70, 3881.4, 1.6800, 24, 71, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 69, 3671, 1.2800, 22, 68, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 66, 3587.5, 1.0300, 10, 66, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 63, 3638.8, 1.0000, 20, 67, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 3967.2, 0.8700, 21, 49, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (16520, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 50, 3951.8, 0.8900, 23, 39, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 68, 2885.4, 0.8600, -7, 57, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 73, 3021.8, 1.0600, 3, 70, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 75, 3163.9, 1.1400, -5, 69, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 70, 3289.4, 1.0100, -4, 72, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 72, 3217.9, 1.1300, 24, 74, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 70, 3361.9, 1.0100, 27, 69, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 69, 3257.8, 0.9100, 13, 62, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 70, 3189.7, 0.9100, 23, 76, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 71, 3468.4, 1.0900, 31, 77, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 3709.5, 0.9700, 20, 71, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (20607, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 62, 3636.9, 0.8900, 21, 60, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 71, 409.2, 1.1300, -3, 66, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 73, 426.2, 1.0800, -3, 72, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 74, 455, 1.1600, -2, 74, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 73, 532.2, 0.8800, 5, 82, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 78, 542.9, 0.8500, 3, 91, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 78, 549.7, 0.8000, -14, 86, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 76, 533, 0.7700, -16, 88, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 79, 548.6, 0.9100, -18, 89, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 79, 577, 0.9300, -26, 89, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 582.3, 0.9100, -13, 89, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (24769, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 72, 579, 0.8500, -16, 85, 160, 11)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-09-01 00:00:00.000' AS DateTime), 48, 225.2, 0.7800, -18, 22, 59, 1)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-08-25 00:00:00.000' AS DateTime), 50, 243.9, 0.7000, -13, 27, 46, 2)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-08-18 00:00:00.000' AS DateTime), 51, 275.8, 0.9000, -14, 29, 56, 3)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-08-11 00:00:00.000' AS DateTime), 50, 294.3, 0.8400, -13, 28, 52, 4)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-08-04 00:00:00.000' AS DateTime), 55, 298.4, 0.8400, -6, 43, 60, 5)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-07-28 00:00:00.000' AS DateTime), 51, 330, 0.6200, -22, 35, 69, 6)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-07-21 00:00:00.000' AS DateTime), 63, 356.8, 0.5100, -6, 50, 57, 7)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-07-14 00:00:00.000' AS DateTime), 66, 378.4, 0.4400, -6, 54, 55, 8)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-07-07 00:00:00.000' AS DateTime), 67, 391, 0.4900, -9, 61, NULL, 9)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-06-16 00:00:00.000' AS DateTime), NULL, 399.4, 0.5700, -12, 46, 151, 10)
GO
INSERT [dbo].[Test] ([osid], [Date], [Dgrt], [Avol], [UpDnvl], [Adnm], [RlSt], [GpRk], [ID]) VALUES (48580, CAST(N'2017-06-09 00:00:00.000' AS DateTime), 51, 393.2, 0.5800, -6, 42, 160, 11)
GO
Here i have 11 weeks data. All the date is of friday and Column ID is generated based on date descending.
Scenarios:
1) Order by the data by Date and Rlst Descending.
2) Store top 5 values order by date and RlSt Descending.
3) What ever the unique value for Rlst for the recent date(2017-09-01 00:00:00.000) need to be stored.
4) The RlSt values for the date (2017-09-01 00:00:00.000) are repeating we need to go back for the previous week and search. if we get the unique value for all OSID's that's well and good. for those which are repeating we need to and go back to the previous week. We need to follow the same till we get unique values for those OSIDs which have same Rlst Values. Agenda is for same date and OSID we should not have the repeating RlSt values. The last iteration we get the unique values, we are done else we need to search for all 11 weeks.
Please get back to me if required any thing from my end.
Regards,
Vijay
September 7, 2017 at 2:07 am
vijay.singh 46672 - Thursday, September 7, 2017 1:53 AM1) Order by the data by Date and Rlst Descending.
2) Store top 5 values order by date and RlSt Descending.
3) What ever the unique value for Rlst for the recent date(2017-09-01 00:00:00.000) need to be stored.
4) The RlSt values for the date (2017-09-01 00:00:00.000) are repeating we need to go back for the previous week and search. if we get the unique value for all OSID's that's well and good. for those which are repeating we need to and go back to the previous week. We need to follow the same till we get unique values for those OSIDs which have same Rlst Values. Agenda is for same date and OSID we should not have the repeating RlSt values. The last iteration we get the unique values, we are done else we need to search for all 11 weeks.
1)SELECT *
FROM Test
ORDER BY Date ASC, RlSt DESC;
2) Store them where? Is this an existing table, or a new one? You could achieve this by using TOP, and either INSERT or INTO.
3) See questions in regards to 2. You'd need to use a WHERE clause instead of TOP.
4) There are no duplicate RlSt values that also have the same osid value on 20170901 (see below). Can you provide sample data that shows your requirements and then provide the expected output for it?RlSt osid COUNT
----------- ----------- -----------
66 14390 1
66 14845 1
47 14863 1
66 14925 1
47 15154 1
54 15447 1
60 15920 1
75 16189 1
63 16520 1
57 20607 1
66 24769 1
22 48580 1
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 8, 2017 at 12:14 pm
Now we have to guess what the keys and constraINTEGER NOT NULLs are because your DDL is incomplete. Having been a Fortran programmer for a few decades, I would never use float in a database. Correcting the arithmetic is a real pain and SQL just doesn't do it. Back in my youth, we had to spend the better part of a week in a programming class, learning floating-point arithmetic. Do you know about epsilon? Do you know how to arrange summations, multiplications and advanced functions, so you don't get rounding errors? I bet you probably don't because nobody teaches this anymore.
I see that you have no idea what the ISO 11179 rules for naming data elements are; this is why we wind up with a bunch of really cryptic things. That's that are unreadable. Are these really standard abbreviations in your industry?
"Date" cannot be the name of data element; it's a data type! If you think floating-point numbers are bad, then Google how the MONEY datatypes work. Basically, they don't work at all. They rounded too soon and give incorrect results when you multiply or divide with them. You should be using the dB datatypes for them.
CREATE TABLE Something_Tests
(os_id CHAR(5) NOT NULL,
something_date DATE NOT NULL,
PRIMARY KEY (osid, something_date),
dgrt INTEGER NOT NULL,
avol DECIMAL(12,2) NOT NULL,
up_dn_value DECIMAL(12,2) NOT NULL,
adnm INTEGER NOT NULL,
rl_st INTEGER NOT NULL,
gp_rk INTEGER NOT NULL);
You've already been told this, but let me reiterate it; do not push links to spreadsheets or graphics. We don't like linking over and loading strange code from people we don't know or haven't been able to clean. Please tell me you're not one of those people that opens every attachment it comes in an email 🙁
Is a minor point. The ANSI/ISO syntax is "INSERT INTO <table name> VALUES ( <row constructor list>); you're still using the original Sybase select – union construct that nobody else in the world uses. The reason it was used back 50 years ago was so that the original SQL Server engine could process data as if it was a deck of cards being read one of the time. We really didn't have the concept of set oriented processing yet.
Would you like to try again, follow the forum rules and give us clearer specs?
Please post DDL and follow ANSI/ISO standards when asking for help.
September 8, 2017 at 12:35 pm
vijay.singh 46672 - Thursday, September 7, 2017 1:53 AMIs a minor point. The ANSI/ISO syntax is "INSERT INTO <table name> VALUES ( <row constructor list>); you're still using the original Sybase select – union construct that nobody else in the world uses. The reason it was used back 50 years ago was so that the original SQL Server engine could process data as if it was a deck of cards being read one of the time. We really didn't have the concept of set oriented processing yet.
Seriously Joe, would you stop that, please? The OP is simply providing SAMPLE data, I doubt very much they use that on a common basis. if you'd have looked at those forum rules/courtesy's yourself and read Jeff's article, you'd know that using the UNION ALL is simply there to make a QUICK way to extract Sample data in a consumable format for other forum others.
Rather than asking the new users to abide by the "rules", maybe you should check them out first. You'll find the forums much more enjoyable if you and everyone is on the same page. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply