0

I would like to ask a little help on using dynamic sql date header, i have data that i count transaction group by date then by hours. date range would be entered start date and end date.

my data is simple just date and time:

created_Date
'2020-01-14 13:25:20.147'
'2020-01-14 13:23:15.639'
'2020-01-14 12:27:48.896'
'2020-01-09 20:03:06.713'
'2020-01-09 19:33:05.032'
'2020-01-09 19:16:35.590'
'2020-01-09 19:08:19.788'
'2020-01-09 13:02:03.543'
'2020-01-09 12:23:12.595'
'2020-01-08 15:29:52.262'
'2020-01-08 15:17:31.247'
'2020-01-08 15:16:51.499'
'2020-01-08 13:29:47.661'
'2020-01-06 20:19:30.173'

currently found this code:

ALTER PROCEDURE "DBA"."test_trancountdaily"(@sdate datetime, @edate datetime)
BEGIN 
create table #trantable(TDate varchar(100),Hour varchar(2), count varchar(1000));
insert #trantable

SELECT CAST(created_date as date) AS ForDate,
       DATEPART(hour,created_date) AS OnHour,
       COUNT(*) AS Totals
FROM prescription
WHERE created_date >= @sdate and created_date <= @edate
GROUP BY CAST(created_date as date),
       DATEPART(hour,created_date)
ORDER BY CAST(created_date as date),
       DATEPART(hour,created_date) asc;
select * from #trantable;
END

my data are created_date datetime and would count how many transaction that is inside a Hour

but would like an output like this:

HR 2020-01-01 2020-01-02 2020-01-03 etc
1 1 0 3
2 0 1 1
3 1 1 1
4 1 0 2

thanks

bolivar1985

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Zumotakirukashi Vergara III
  • 1
  • 1
  • please include some sample data and the corresponding required result – Squirrel Jan 21 '21 at 07:36
  • @a_horse_with_no_name currently im using sybase anywhere, and need to make this as stored procedure – Zumotakirukashi Vergara III Jan 21 '21 at 07:51
  • This is something you wouldn't usually do in SQL, but in the GUI layer (your app or website that is running the query). – Thorsten Kettner Jan 21 '21 at 07:59
  • @ThorstenKettner just need a stored procedure for this, it will be called by powerbuilder datawindow to retrieved needed data in horizontal display, just as i posted. – Zumotakirukashi Vergara III Jan 21 '21 at 08:10
  • There are plenty of examples of dynamic pivots on [so]; [1](https://stackoverflow.com/q/10404348/2029983), [2](https://stackoverflow.com/q/39879697/2029983), [3](https://stackoverflow.com/q/52761674/2029983). What were wrong with all these examples? What was your attempts and why didn't it work? – Thom A Jan 21 '21 at 09:06
  • @Larnu problem is that in sybase, they don't have pivot which im trying to find if there is another way to do dynamic date header. – Zumotakirukashi Vergara III Jan 21 '21 at 10:26
  • Use conditional aggregation, @ZumotakirukashiVergaraIII instead of `PIVOT` then; it's far less restrictive then. But you've taggged t-sql, so you have tagged that you are able to use `PIVOT`. – Thom A Jan 21 '21 at 10:28

1 Answers1

0

Sample Result in interactive sql Good Day, Just solve query without using pivot in sybase it was mind troubling but got it.

set @sql_date = @sql_date + ', COUNT(CASE WHEN DATE(prescription.created_date) = ''' + @ls_date + ''' AND #time_table.hrs = HOUR(prescription.created_date) THEN prescription.tran_id END) AS [' + @ls_date + ']' ;

looping the date range to be given by user, and date as header. bolivar1985

Zumotakirukashi Vergara III
  • 1
  • 1