October 15, 2017 at 12:17 pm
Hi Team,
I need your help to find a solution on below query.
Table Structure
CREATE TABLE [dbo].[Sampletable1](
[ServerName] [nvarchar](50) NULL,
[ID] [nvarchar](50) NOT NULL,
[Model] [nvarchar](50) NOT NULL,
[Host] [nvarchar](50) NOT NULL,
[BillDate] [date] NOT NULL
)
GO
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E02.NDC.COM', 'FCH19107Q89', 'SAN-B420-M3', 'SRMPV07PC01', '05-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E02.NDC.COM', 'FCH19107Q89', 'SAN-B420-M3', 'SRMPV07PC01', '19-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E01.NDC.COM', 'FCH19107M57', 'SAN-B420-M3', 'SRMPV07PC01', '12-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E01.NDC.COM', 'FCH19107M57', 'SAN-B420-M3', 'SRMPV07PC01', '19-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SOMPV03HC01E02.NDC.COM', 'FCH1806706Q', 'SAN-B420-M3', 'SOMPV03HC01', '05-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SOMPV03HC01E02.NDC.COM', 'FCH1806706Q', 'SAN-B420-M3', 'SOMPV03HC01', '12-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SOMPV03HC01E02.NDC.COM', 'FCH1806706Q', 'SAN-B420-M3', 'SOMPV03HC01', '19-SEP-2017')
Here my data's were loaded in 3 different dates
select DISTINCT [BillDate] from [dbo].[Sampletable1]
BillDate
2017-09-05
2017-09-12
2017-09-19
Now, I need to find daily values for each server. The server SRMPV07PC01E02.NDC.COM is loaded on Sep 5 then we can start the daily values as 1 from Sep 5 to next bill cycle Sep 12.(Sep 1 to Sep 4 value is 0 ). if server missed any billing cycle then put 0 on that period
I Need the o/p like below
| ServerName | ID | 10/1/2017 | 10/2/2017 | 10/3/2017 | 10/4/2017 | 10/5/2017 | 10/6/2017 | 10/7/2017 | 10/8/2017 | 10/9/2017 | 10/10/2017 | 10/11/2017 | 10/12/2017 | 10/13/2017 | 10/14/2017 | 10/15/2017 | 10/16/2017 | 10/17/2017 | 10/18/2017 | 10/19/2017 | 10/20/2017 | 10/21/2017 | 10/22/2017 | 10/23/2017 | 10/24/2017 | 10/25/2017 | 10/26/2017 | 10/27/2017 | 10/28/2017 | 10/29/2017 | 10/30/2017 | 10/31/2017 |
| SRMPV07PC01E02.NDC.COM | FCH19107Q89 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| SRMPV07PC01E01.NDC.COM | FCH19107M57 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| SOMPV03HC01E02.NDC.COM | FCH1806706Q | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Plese help me to solve this issue
Thanks
Torrid.
October 15, 2017 at 12:25 pm
torrid.inundate - Sunday, October 15, 2017 12:17 PMHi Team,I need your help to find a solution on below query.
Table Structure
CREATE TABLE [dbo].[Sampletable1](
[ServerName] [nvarchar](50) NULL,
[ID] [nvarchar](50) NOT NULL,
[Model] [nvarchar](50) NOT NULL,
[Host] [nvarchar](50) NOT NULL,
[BillDate] [date] NOT NULL
)
GOINSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E02.NDC.COM', 'FCH19107Q89', 'SAN-B420-M3', 'SRMPV07PC01', '05-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E02.NDC.COM', 'FCH19107Q89', 'SAN-B420-M3', 'SRMPV07PC01', '19-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E01.NDC.COM', 'FCH19107M57', 'SAN-B420-M3', 'SRMPV07PC01', '12-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SRMPV07PC01E01.NDC.COM', 'FCH19107M57', 'SAN-B420-M3', 'SRMPV07PC01', '19-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SOMPV03HC01E02.NDC.COM', 'FCH1806706Q', 'SAN-B420-M3', 'SOMPV03HC01', '05-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SOMPV03HC01E02.NDC.COM', 'FCH1806706Q', 'SAN-B420-M3', 'SOMPV03HC01', '12-SEP-2017')
INSERT [dbo].[Sampletable1] ([ServerName1], [ID], [Model], [Host], [BillDate]) VALUES (N'SOMPV03HC01E02.NDC.COM', 'FCH1806706Q', 'SAN-B420-M3', 'SOMPV03HC01', '19-SEP-2017')Here my data's were loaded in 3 different dates
select DISTINCT [BillDate] from [dbo].[Sampletable1]
BillDate
2017-09-05
2017-09-12
2017-09-19Now, I need to find daily values for each server. The server SRMPV07PC01E02.NDC.COM is loaded on Sep 5 then we can start the daily values as 1 from Sep 5 to next bill cycle Sep 12.(Sep 1 to Sep 4 value is 0 ). if server missed any billing cycle then put 0 on that period
I Need the o/p like below
ServerName ID 10/1/2017 10/2/2017 10/3/2017 10/4/2017 10/5/2017 10/6/2017 10/7/2017 10/8/2017 10/9/2017 10/10/2017 10/11/2017 10/12/2017 10/13/2017 10/14/2017 10/15/2017 10/16/2017 10/17/2017 10/18/2017 10/19/2017 10/20/2017 10/21/2017 10/22/2017 10/23/2017 10/24/2017 10/25/2017 10/26/2017 10/27/2017 10/28/2017 10/29/2017 10/30/2017 10/31/2017 SRMPV07PC01E02.NDC.COM FCH19107Q89 0 0 0 0 1 1 1 1 1 1 1 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 SRMPV07PC01E01.NDC.COM FCH19107M57 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 SOMPV03HC01E02.NDC.COM FCH1806706Q 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 Plese help me to solve this issue
Thanks
Torrid.
Please Note my Server billing circle Starts on Tuesday
October 15, 2017 at 1:01 pm
Hi Torrid,
Sorry, I think I'm probably missing something, as I'm not too sure what you're saying with regard to the billing cycles, but can't you just aggregate a bunch of case statements using DATEPART?
It's not the most elegant of solutions, but I've done the 1st 10 below?
Apologies if I've misunderstood.
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '2017-09-01'
,@DateEnd = '2017-10-01'
SELECT ServerName
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 1 THEN 1 ELSE 0 END) AS Day1
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 2 THEN 1 ELSE 0 END) AS Day2
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 3 THEN 1 ELSE 0 END) AS Day3
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 4 THEN 1 ELSE 0 END) AS Day4
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 5 THEN 1 ELSE 0 END) AS Day5
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 6 THEN 1 ELSE 0 END) AS Day6
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 7 THEN 1 ELSE 0 END) AS Day7
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 8 THEN 1 ELSE 0 END) AS Day8
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 9 THEN 1 ELSE 0 END) AS Day9
,SUM(CASE WHEN DATEPART(DAY, BillDate) = 10 THEN 1 ELSE 0 END) AS Day10
FROM [dbo].[Sampletable1]
WHERE BillDate >= @DateStart
AND BillDate < @DateEnd
GROUP BY ServerName
October 17, 2017 at 9:00 am
Hi Farlzy,
Thank you for your Reply. But the query o/p has slightly differed from my requirement. If the server is present on the weekly report ( It is nothing but BillDate) then I need mark that server is available for that entire week, not just on that day.
below is my exact Prerequisite
| ServerName | ID | 9/1/2017 | 9/2/2017 | 9/3/2017 | 9/4/2017 | 9/5/2017 | 9/6/2017 | 9/7/2017 | 9/8/2017 | 9/9/2017 | 9/10/2017 | 9/11/2017 | 9/12/2017 | 9/13/2017 | 9/14/2017 | 9/15/2017 | 9/16/2017 | 9/17/2017 | 9/18/2017 | 9/19/2017 | 9/20/2017 | 9/21/2017 | 9/22/2017 | 9/23/2017 | 9/24/2017 | 9/25/2017 | 9/26/2017 | 9/27/2017 | 9/28/2017 | 9/29/2017 | 9/30/2017 |
| SRMPV07PC01E02.NDC.COM | FCH19107Q89 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| SRMPV07PC01E01.NDC.COM | FCH19107M57 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| SOMPV03HC01E02.NDC.COM | FCH1806706Q | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply