February 25, 2015 at 2:21 am
I want to generate diagram for score in every day - From Regdate For every User to now
(But Users Have only scores in some days)
----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[TBL_DayScore](
[Id] [int] NULL,
[UserId] [int] NULL,
[Score] [int] NULL,
[DateGetScore] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tbl_UserReg](
[Id] [int] NULL,
[UserId] [int] NULL,
[RegDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (1, 1, 50, CAST(N'2015-02-01 00:00:00.000' AS DateTime))
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (2, 1, 30, CAST(N'2015-02-02 00:00:00.000' AS DateTime))
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (3, 1, -10, CAST(N'2015-02-05 00:00:00.000' AS DateTime))
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (4, 1, 25, CAST(N'2015-02-10 00:00:00.000' AS DateTime))
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (5, 2, 7, CAST(N'2015-02-05 00:00:00.000' AS DateTime))
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (6, 2, 10, CAST(N'2015-02-25 00:00:00.000' AS DateTime))
INSERT [TBL_DayScore] ([Id], [UserId], [Score], [DateGetScore]) VALUES (7, 3, 100, CAST(N'2015-02-20 00:00:00.000' AS DateTime))
INSERT [Tbl_UserReg] ([Id], [UserId], [RegDate]) VALUES (1, 1, CAST(N'2015-02-01 00:00:00.000' AS DateTime))
INSERT [Tbl_UserReg] ([Id], [UserId], [RegDate]) VALUES (2, 2, CAST(N'2015-02-03 00:00:00.000' AS DateTime))
INSERT [Tbl_UserReg] ([Id], [UserId], [RegDate]) VALUES (3, 3, CAST(N'2015-02-15 00:00:00.000' AS DateTime))
-----------------------------------------
Result That I Needed : (I want to generate diagram for score in every day - From Regdate to now)
(And The other side : I have a table that have All Day Date and can join to these tables)
(Example For User1)
1 - 2015-02-01 - 50
1 - 2015-02-02 - 30
1 - 2015-02-03 - 0
1 - 2015-02-04 - 0
1 - 2015-02-05 - -10
1 - 2015-02-06 - 0
1 - 2015-02-07 - 0
1 - 2015-02-08 - 0
1 - 2015-02-09 - 0
1 - 2015-02-10 - 25
1 - 2015-02-11 - 0
...
1 - 2015-02-25 - 0
2 - 2015-02-03 - 0 (User 2 must Started in its register date)
2 - 2015-02-04 - 0
2 - 2015-02-05 - 7
2 - 2015-02-06 - 0
....
February 25, 2015 at 5:12 am
sm_iransoftware (2/25/2015)
(And The other side : I have a table that have All Day Date and can join to these tables)
Ok then what is your problem? Have you tried the join above ?
February 25, 2015 at 5:23 am
the best way this is done by joining to a permenant Calendar table first, which contains all possible dates, and left joins to your data.
if you don't have one, you can look at some of the articles or scripts on SSC:
http://www.sqlservercentral.com/search/?q=calendar+table&t=afbs&sort=relevance
you can build one dynamically as well, using a Tally or numbers table
here's just one inline example:
-- ten years before and after today
with TallyCalendar as (
SELECT dateadd( dd,-3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
FROM (
SELECT TOP 7300
row_number() OVER (ORDER BY sc1.id) AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X
)
SELECT T1.N As TheDate,
* FROM TallyCalendar T1
LEFT JOIN [TBL_DayScore] T2 ON T1.N = T2.DateGetScore
WHERE YEAR(N) = 2015
AND MONTH(N) = 2
Lowell
February 25, 2015 at 5:32 am
I Must Started For every User From Its Register Date !!!!
(And Inserted Date from That Date to now - For every User)
February 25, 2015 at 5:35 am
sm_iransoftware (2/25/2015)
I Must Started For every User From Its Register Date !!!!(And Inserted Date from That Date to now - For every User)
i showed you a basic example that gets you over the most difficult part: filling in gaps;
what have you tried and adapted,that is not working now that you have an example? can you see that now that you have all possible dates, you could join that to the users start date?
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply